SUM specific column values that have integers where row meets a condition and place in new row-CodePudding

I wish to SUM the first two column values that have numbers where row == 'BB' and place in new row below

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  0.6  0.7  0.3  0.9
NEW 1.3  0.0  1.2  0.0

Doing

   mask = df['ID'].eq('BB')
   df[NEW] = df.iloc[2,1:2].sum()

I am taking the sum row 2 and columns 1 and 2

Any suggestion is appreciated.

CodePudding user response：

You could create a list outside of the DF and then add it to the DataFrame

df = pd.DataFrame([['AA', 8.0, 4.8, 3.1, 5.3], ['BB', 0.6, 0.7, 0.3, 0.9]], columns=['ID', 'Q121', 'Q221', 'Q321', 'Q421']).set_index('ID')
new = []
for z in [x y for x,y in zip(df.loc['BB'][0:-1:2],df.loc['BB'][1::2])]:
    new.extend([z,0])
df.loc['New'] = new

CodePudding user response：

you can use rolling window to calculate the sum of pair of two columns, then concat the result

col=[1,0,1,0]
r=df.loc[df['ID'].eq('BB') ].rolling(2, axis=1).sum().shift(-1, axis=1).fillna(0).mul(col)

df=pd.concat([df, r])

df.ID.fillna('NEW', inplace=True)
df

OR to make it little concise

col=[1,0,1,0]
df2=pd.concat([df, 
               df.loc[df['ID'].eq('BB') ]
               .rolling(2, axis=1).sum() # calc rolling windows sum of two columns
               .shift(-1, axis=1) # shift result to left
               .fillna(0) # fill null values
               .mul(col) # multipley to keep sum in alternate columns
              ]
             )
df2['ID'].fillna('NEW', inplace=True)
df2

    ID     Q121     Q221    Q321    Q421
0   AA      8.0     4.8     3.1     5.3
1   BB      0.6     0.7     0.3     0.9
1   NEW     1.3     0.0     1.2     0.0