Sum values in specific rows and place value in first column of that row

Lynn 0
Viewed 28

I wish to sum values in the row that contains 'BB' and place that value in first column of that row.

Data

ID  Q121 Q221 Q321 Q421
AA  8.0  4.8  3.1  5.3
BB  0.6  0.7  0.3  0.9

Desired

ID  Q121 Q221 Q321 Q421
AA  8.0  4.8  3.1  5.3
BB  2.5  0.0  0.0  0.0

Doing

 df.loc[df['ID'] == BB, 'Q121','Q221','Q321','Q421'].sum()

Not sure how to then place this value in the first column

I am still researching, any suggestion is appreciated.

pandas numpy python
1 Answers

Use pandas.DataFrame with a boolean mask to assign the new values :

mask = df['ID'].eq('BB')
cols = df.columns.difference(['ID'])
update = [df.sum(axis=1, numeric_only=True)]

df.loc[mask, cols] = pd.DataFrame(dict(zip(cols, update)), index=df.index)
df.fillna(0, inplace=True)

Note : pandas.DataFrame.sum is used to calculate the sum over the axis=1 (columns).

# Output :
print(df)

   ID  Q121  Q221  Q321  Q421
0  AA   8.0   4.8   3.1   5.3
1  BB   2.5   0.0   0.0   0.0
abokey0
Related