CODEWITHSUNDEEP
pythonpython-3.xpandasnumpy
Opps_0
Opps_0

Reputation: 438

How to subtract all rows based on a column value and add them in extra column

I have a ndarray that contains 15 columns. The structure is like the bellow one

    col_1     col_2   col_3   col_4 Col_5
1   0.01      met_1   DT      2.3   0.78
2   0.01      met_1   DT_2    3.2   0.56
3   0.01      met_1   Lin     3.2   0.02

Now, I want to subtract col_4 and col_5 values for DT and DT_2 from Lin. Finally, I want to add them in 2 new columns. The expected output is given below

    col_1     col_2   col_3   col_4 Col_5   del_col_4 del_col_5
1   0.01      met_1   DT      2.3   0.78    0.90    -0.76
2   0.01      met_1   DT_2    3.2   0.56    0.00    -0.54
3   0.01      met_1   Lin     3.2   0.02    0.00    0.00

Please, be noted, as I have 15 columns and say I want to only 10 columns (then I also have to add the subtraction result in extra 10 columns also). Could you tell me how can I do this?

Upvotes: 0

Views: 49

Answers (2)

wwnde
wwnde

Reputation: 26676

df3=df.set_index('col_3')#Set index

df3.assign(del_col_4=df3.loc['Lin',  'col_4']-df3['col_4'],del_col_5=df3.loc['Lin',  'Col_5']-df3['Col_5']).reset_index()#slice index as you substract



col_3  col_1  col_2  col_4  Col_5  del_col_4  del_col_5
0    DT   0.01  met_1    2.3   0.78        0.9      -0.76
1  DT_2   0.01  met_1    3.2   0.56        0.0      -0.54
2   Lin   0.01  met_1    3.2   0.02        0.0       0.00

Upvotes: 1

BENY
BENY

Reputation: 323316

Check with

col = ['col_4','Col_5']
s = df[col].rsub(df.loc[df.col_3=='Lin',col].iloc[0],axis=1)
df = df.join(s.add_prefix('del_'))
df
Out[171]: 
   col_1  col_2 col_3  col_4  Col_5  del_col_4  del_Col_5
1   0.01  met_1    DT    2.3   0.78        0.9      -0.76
2   0.01  met_1  DT_2    3.2   0.56        0.0      -0.54
3   0.01  met_1   Lin    3.2   0.02        0.0       0.00

Upvotes: 0

Related Questions