0

I will like to add ranking column based on 3 conditions: Company Name, Brand, Year.

This is my raw data:

  CompanyName        Brand  Year
0           A        Razer  2019
1           A  SteelSeries  2019
2           A     Logitech  2019
3           A        Razer  2018
4           A  SteelSeries  2018
5           B     Logitech  2019
6           B         Asus  2019
7           B         Benq  2018
8           C         Asus  2017
9           C        Razer  2017

This is my desired outcome: (May be confusing for you)

  CompanyName        Brand  Year  Rank
0           A        Razer  2019     1
1           A  SteelSeries  2019     2
2           A     Logitech  2019     3
3           A        Razer  2018     1
4           A  SteelSeries  2018     2
5           B     Logitech  2019     1
6           B         Asus  2019     2
7           B         Benq  2018     1
8           C         Asus  2017     1
9           C        Razer  2017     2

Code I have tried but it only loop unique Brand:

df1 = pd.DataFrame()
for i,brands in enumerate(df['Brand'].unique):
     df1.loc[i-1,'Rank'] = i
     df1.loc[i-1, 'Brand']= brands

df = df.merge(df1,on='Brand',how='inner')
Umar.H
  • 22,559
  • 7
  • 39
  • 74

1 Answers1

0

please provide a textual dataframe next time in the following format -

please review How to make good reproducible pandas examples

 CompanyName        Brand  Year
0           A        Razer  2019
1           A  SteelSeries  2019
2           A     Logitech  2019
3           A        Razer  2018
4           A  SteelSeries  2018
5           B     Logitech  2019
6           B         Asus  2019
7           B         Benq  2018
8           C         Asus  2017
9           C        Razer  2017

quite simply, you can do a groupby your CompanyName and Year columns and apply a cumulative count : 

df['Rank'] = df.groupby(['CompanyName','Year']).cumcount()+1
print(df)
      CompanyName        Brand  Year  Rank
0           A        Razer  2019     1
1           A  SteelSeries  2019     2
2           A     Logitech  2019     3
3           A        Razer  2018     1
4           A  SteelSeries  2018     2
5           B     Logitech  2019     1
6           B         Asus  2019     2
7           B         Benq  2018     1
8           C         Asus  2017     1
9           C        Razer  2017     2
Umar.H
  • 22,559
  • 7
  • 39
  • 74
  • Did not work for me. It goes by unique value in Brand column –  Oct 16 '19 at 08:31
  • 1
    It goes by unique value in Brand column. For example, when they see razer, rank is 1, steelseries, rank is 2 etc.. for the whole row –  Oct 16 '19 at 08:35
  • hmm, I'm not to sure based on your sample data, can you explain the logic of the rank? – Umar.H Oct 16 '19 at 08:40
  • The rank is prioritize on the brand column. As you can see, 1 company can hold the same type of brand, but they are in different year. CompanyName: A –  Oct 16 '19 at 08:45
  • I've read your question a few times and I don't understand your logic vs your expected output can you explain why Razer is Rank 1 for 2019/2018 for Company A then Rank 2 for Company C in 2017. – Umar.H Oct 16 '19 at 10:30