Python Dataframe Sum/Subtract Many Columns to Many Coumns in one line

user_11922765 0
Viewed 513

I want to sum many columns to many columns in a data frame.

My code:

df = 
    A1 B1  A2  B2
0  15  30  50  70
1  25  40  60  80
# I have many columns like this. I want to do something like this A1-A2, B1-B2, etc 
# My approach is 
first_cols = [A1,B1]
sec_cols = [A2,B2]
# New column names
sub_cols = [A_sub,B_sub]
df[sub_cols] = df[first_cols] - df[sec_cols]

Present output:

ValueError: Wrong number of items passed , placement implies 1

Expected output:

df = 
    A1 B1  A2  B2  A_sub  B_sub
0  15  30  50  70  -35     -40
1  25  40  60  80  -35     -40
pandas dataframe numpy python
3 Answers

I think what you are trying to do is similar to this post. In Dataframes generally the arithmetic operations are aligned on column and row indices. Since you are tring to subtract different columns, pandas doesn't carry out the operation. So, df[sub_cols] = df[first_cols] - df[second_cols] won't work.

However, if you were to use numpy array and do the operation, pandas carries it out elementwise. So, df[sub_cols] = df[first_cols] - df[second_cols].values will work and give you the expected result.

import pandas as pd

df = {"A1":[15,25], "B1": [30, 40], "A2":[50,60], "B2": [70, 80]}
df = pd.DataFrame(df)
first_cols = ["A1", "B1"]
second_cols = ["A2", "B2"]

sub_cols = ["A_sub","B_sub"]

df[sub_cols] = df[first_cols] - df[second_cols].values

print(df)

   A1  B1  A2  B2  A_sub  B_sub
0  15  30  50  70    -35    -40
1  25  40  60  80    -35    -40
user_134879150

You could also pull it off with a groupby on the columns:

subtraction = (df.groupby(df.columns.str[0], axis = 1)
                 .agg(np.subtract.reduce, axis = 1)
                 .add_suffix("_sub")
                )

df.assign(**subtraction) 
   A1  B1  A2  B2  A_sub  B_sub
0  15  30  50  70    -35    -40
1  25  40  60  80    -35    -40
user_71757130

It's not quite clear what you want. If you want one column that's A1-A2 and another that's B1-B2, you can do df[[A1,A,2]].sub(df[[B1,B2]]).

user_85441230
Related