CODEWITHSUNDEEP
pythonpandasgroup-bymax
ceeka9388
ceeka9388

Reputation: 69

Assign Column Value by fetching Max Value of Pandas Group Count

I have the following Dataframe.

Year    Sector  Number      Veh Types   Month   Count
2015    AA      173         F           Apr     277
2015    AA      173         F           Aug     277
2015    AA      173         F           Dec     277
2015    AA      173         F           Feb     277
2015    AA      173         F           Jul     277
2015    AA      173         F           Jun     277
2015    AA      173         F           Mar     277
2015    AA      173         G           Feb     82
2015    AA      173         G           Jan     82
2015    AA      173         G           Mar     82
2016    AA      173         A           Apr     277
2016    AA      173         A           Aug     277
2016    AA      173         A           Dec     277
2016    AA      173         A           Feb     277
2016    AA      173         A           Jul     277
2016    AA      173         A           Jun     277
2016    AA      173         A           Mar     277
2016    AA      173         A           May     277
2016    AA      173         F           Nov     277
2016    AA      173         F           Oct     277
2016    AA      173         F           Sep     277
2016    AA      173         G           Feb     82
2016    AA      173         G           Jan     82
2016    AA      173         G           Mar     82

I need to get the maximum value of column Count in each year and get the corresponding 'Veh Type' Columnn and assign in the 'Max_Veh_Type' column in each group.

My Expected Output is:

Year    Sector  Number      Veh Types   Month   Count   Max_Veh_type     
2015    AA      173         F           Apr     277     F
2015    AA      173         F           Aug     277     F       
2015    AA      173         F           Dec     277     F
2015    AA      173         F           Feb     277     F
2015    AA      173         F           Jul     277     F
2015    AA      173         F           Jun     277     F   
2015    AA      173         F           Mar     277     F
2015    AA      173         G           Feb     82      F
2015    AA      173         G           Jan     82      F
2015    AA      173         G           Mar     82      F
2016    AA      173         A           Apr     277     A
2016    AA      173         A           Aug     277     A
2016    AA      173         A           Dec     277     A       
2016    AA      173         A           Feb     277     A
2016    AA      173         A           Jul     277     A
2016    AA      173         A           Jun     277     A
2016    AA      173         A           Mar     277     A   
2016    AA      173         A           May     277     A
2016    AA      173         F           Nov     277     A
2016    AA      173         F           Oct     277     A
2016    AA      173         F           Sep     277     A
2016    AA      173         G           Feb     82      A   
2016    AA      173         G           Jan     82      A
2016    AA      173         G           Mar     82      A

I am aware of getting the max value in a group. Any help to achieve the above would be great help.

Upvotes: 1

Views: 326

Answers (1)

piRSquared
piRSquared

Reputation: 294358

  • df.groupby('Year').Count.idxmax() gets me a handy series of where the max rows occur
  • I can use map on this with the dict like series vt in order to get a series of years in the index and Veh Type in values.
  • Then use this to map on the yr series to get the column we want
  • Use assign to create a new column
vt, yr = df['Veh Types'], df['Year']
df.assign(Max_Veh_Type=yr.map(df.groupby('Year').Count.idxmax().map(vt)))

    Year Sector  Number Veh Types Month  Count Max_Veh_Type
0   2015     AA     173         F   Apr    277            F
1   2015     AA     173         F   Aug    277            F
2   2015     AA     173         F   Dec    277            F
3   2015     AA     173         F   Feb    277            F
4   2015     AA     173         F   Jul    277            F
5   2015     AA     173         F   Jun    277            F
6   2015     AA     173         F   Mar    277            F
7   2015     AA     173         G   Feb     82            F
8   2015     AA     173         G   Jan     82            F
9   2015     AA     173         G   Mar     82            F
10  2016     AA     173         A   Apr    277            A
11  2016     AA     173         A   Aug    277            A
12  2016     AA     173         A   Dec    277            A
13  2016     AA     173         A   Feb    277            A
14  2016     AA     173         A   Jul    277            A
15  2016     AA     173         A   Jun    277            A
16  2016     AA     173         A   Mar    277            A
17  2016     AA     173         A   May    277            A
18  2016     AA     173         F   Nov    277            A
19  2016     AA     173         F   Oct    277            A
20  2016     AA     173         F   Sep    277            A
21  2016     AA     173         G   Feb     82            A
22  2016     AA     173         G   Jan     82            A
23  2016     AA     173         G   Mar     82            A

Upvotes: 3

Related Questions