group columns and create a new column with binary values

user_11805652 0
Viewed 53

I have a df on election data

   county      party      totalvotes  
      
0  Maui County  Republican 780
1  Maui County  Democrat   1000 
2  May County   ...        ....

Now I want to return a new df that has a new column with the values of: 1 or 0 depending on if the Democrat(1) or Republican(0) won in that specific county. I also want to combine the rows so it just returns one row for the county. Here is an example of what I'm trying to return.

   county       party      totalvotes  result 
      
0  Maui County  Democrat   1000        1
1  May County   Republican 4480        0
2  ...          ....

What I've tried: I've tried using lambdas where I compare each row and I've also thought of separating the data into two dfs to iterrow() with a couple of for loops. What is the best way to tackle this?

pandas dataframe python
1 Answers

Group by county, then find the index of the row (in that group) that reached the maximum "totalvotes" in each group and use .loc to select those rows:

df = df.loc[df.groupby("county")["totalvotes"].idxmax()]

Now, compare the "party" to the string "Democrat" to create the "result" column:

df["result"] = (df["party"] == "Democrat").astype(int)
user_32810970
Related