For the given dataframe df as:
Election Yr. Party States Votes
0 2000 A a 50
1 2000 A b 30
2 2000 B a 40
3 2000 B b 50
4 2000 C a 30
5 2000 C b 40
6 2005 A a 50
7 2005 A b 30
8 2005 B a 40
9 2005 B b 50
10 2005 C a 30
11 2005 C b 40
I want to get the Party that got the maximum Votes for a corresponding year. I have used the following code to groupby "Election Year" and "Party" and then .sum() to get the total votes for each party in every year.
df = df.groupby(['Election Yr.', 'Party']).sum()
Now how to get the party with maximum Votes each year? I am unable to get this.
Any support is highly appreciated.
You can start off with df before doing your first groupby. Then you get the maximum number of votes each year and merge on the year-votes combination to get the party that got the most votes per year.
# Original data
df = pd.DataFrame({'Election Yr.':[2000,2000,2000,2000,2000,2000,2005,2005,2005,2005,2005,2005],
'Party':['A','A','B','B','C','C','A','A','B','B','C','C',],
'Votes':[50,30,40,50,30,40,50,30,40,50,30,40]})
# Get number of votes per year-party
df = df.groupby(['Election Yr.','Party'])['Votes'].sum().reset_index()
# Get max number of votes per year
max_ = df.groupby('Election Yr.')['Votes'].max().reset_index()
# Merge on key
max_ = max_.merge(df, on=['Election Yr.','Votes'])
# Results
print(max_)
> Election Yr. Votes Party
> 0 2000 90 B
> 1 2005 90 B
Alternatively, you can sort by votes per year:
df = df.groupby(['Election Yr.','Party'])['Votes'].sum().reset_index()
df = df.sort_values(['Election Yr.','Votes'], ascending=False)
print(df.groupby('Election Yr.').first().reset_index())
print(df)
> Election Yr. Party Votes
> 0 2000 B 90
> 1 2005 B 90