How to sum specific columns while keeping the unique ID without nan

Question

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?

Answer 1

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()

Now df_names is the final dataframe you're looking for!