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

user_13079256 0
Viewed 310

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?

pandas python
2 Answers

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!

user_141547840 
out = (df.drop("ID", axis=1)
         .groupby(["Name", df.ID.ffill(downcast="infer")], sort=False)
         .sum()
         .sum(axis=1)
         .to_frame("Total"))

First drop the ID column (but not in-place) to ignore it in the summation, then group by the Name and the forward-filled ID and sum the in-group values. Then sum all the column values to get the total and turn it into a dataframe with column Total

to get

>>> out

         Total
Name ID
Sam  11      8
Dan  12     25
Tom  15     11

downcast=infer helps keep the IDs integer after forward-filling and sort=False keeps the order in the dataframe while grouping.

user_93321870
Related