So my dataframe is-
Name ID A B C Sam 11 1 1 1 Sam nan 0 1 4 Dan 12 1 4 4 Dan nan 1 5 0 Dan 12 2 4 4 Tom 15 1 4 6
I want to create an output containing-
Name ID Total Sam 11 8 Dan 12 25 Tom 15 11
While I do groupby Name and ID. I get Sam and Dan twice for its null ID. How do I aggregate there A,B,C and get there unique ID as there final output?
Here you go:
# First make dataframe with names and total scores. df['Total'] = df['A'] + df['B'] + df['C'] df_names = pd.DataFrame(df.groupby('Name')['Total'].sum()) # Then make dataframe that has the IDs. df_ID = pd.DataFrame(df[['Name', 'ID']].dropna()) df_ID.set_index('Name', inplace=True) # Then add in the IDs to the dataframe with total scores. df_names['ID'] = df_ID.drop_duplicates()
df_names is the final dataframe you’re looking for!