CODEWITHSUNDEEP
pythonpandas
andrew fay
andrew fay

Reputation: 95

Pandas find max value in one column and display from another row

Alabama 1 Byrne, Bradley 68.16 68.16 0.0 LeFlore, Burton R. 31.71 31.71 0.0 Unknown 0.13 0.13 0.0

I have a dataset that looks like such:

STATE | DISTRICT | CANDIDATE NAME | GENERAL VOTE
Alabama | 1 | Byrne, Bradley |  68.16
Alabama | 1 | LeFlore, Burton R. | 31.71
Alabama | 1 | Unknown | 0.13
Alabama | 2 | Name | 65.43
Alabama | 2 | Name | 0.13

I have to group by State and district because each state has multiple districts and there are many states. I have done this already. However, I need to find the maximum of each grouping, and display the candidate name that coincides with this maximum. I also have to display the difference between the maximum and minimum general vote in each grouping. I have done some of this already but I am stuck

 df_out = dfworking.groupby(["STATE", "D", "CANDIDATE NAME"])['GENERAL PERCENT'].agg(['max','min'])
df_out['Margin'] = df_out['max']-df_out['min']
df_new_out = dfworking.groupby(['STATE','D'])['GENERAL PERCENT'].max()

I am not sure how to display the margin column, and the name that coincides with the maximum vote in the same dataframe. Thank you!

Upvotes: 3

Views: 1243

Answers (1)

jezrael
jezrael

Reputation: 862471

Notice - values in STATE, DISTRICT and GENERAL VOTE columns has to be sorted first.

#sorting
dfworking = dfworking.sort_values(['STATE','DISTRICT','GENERAL VOTE'], 
                                  ascending=[True, True, False])

#get index of max value in GENERAL VOTE column
df1 = dfworking.groupby(["STATE", "DISTRICT"])['GENERAL VOTE'].idxmax()
#create new column - not matched value return NaN
dfworking['cand'] = dfworking.loc[df1, 'CANDIDATE NAME']
#replace NaN by forward  filling
dfworking['cand'] = dfworking['cand'].ffill()

print (dfworking)
     STATE  DISTRICT      CANDIDATE NAME  GENERAL VOTE            cand
0  Alabama         1      Byrne, Bradley         68.16  Byrne, Bradley
1  Alabama         1  LeFlore, Burton R.         31.71  Byrne, Bradley
2  Alabama         1             Unknown          0.13  Byrne, Bradley
3  Alabama         2                Name         65.43            Name
4  Alabama         2                Name          0.13            Name

Another solution is create df with top candidate and join to original:

df1 = dfworking.loc[dfworking.groupby(["STATE", "DISTRICT"])['GENERAL VOTE'].idxmax()]
df1 = df1.set_index(['STATE','DISTRICT'])['CANDIDATE NAME'].rename('cand')
dfworking = dfworking.join(df1, on=['STATE','DISTRICT'])
print (dfworking)
     STATE  DISTRICT      CANDIDATE NAME  GENERAL VOTE            cand
0  Alabama         1      Byrne, Bradley         68.16  Byrne, Bradley
1  Alabama         1  LeFlore, Burton R.         31.71  Byrne, Bradley
2  Alabama         1             Unknown          0.13  Byrne, Bradley
3  Alabama         2                Name         65.43            Name
4  Alabama         2                Name          0.13            Name

Upvotes: 3

Related Questions