Working in Python 3.6 with Pandas, I'm trying to work out a simple way to subtract multiple columns from one another.
Imagine a simple dataframe, df, of two rows and four columns:
A B C D
0 1 2 3 4
1 5 6 7 8
I'm trying to subtract A from B and C from D to achieve:
A B C D
0 1 1 3 1
1 5 1 7 1
This can be done quite easily done column by column, e.g. for the B-A operation:
df["B"] = df["B"] - df["A"]
or
df["B"] = df["B"].sub(df["A"], axis=0)
I've tried:
df[["B", "D"]] = df[["B", "D"]] - df[["A", "C"]]
and
df[["B", "D"]] = df[["B", "D"]].sub(df[["A", "C"]], axis=0)
But both give an error about columns not matching, so I suspect I'm inadvertently telling it to do some sort of multi-axis operation or something. Regardless, neither of those work.
ValueError: Columns must be same length as key
Sooo what is the easiest way to accomplish this for both B-A and D-C in the same line ... my actual dataframe has about 30 columns, so line-by-line isn't really feasible :P
Thanks :)
A numpy operation is generally used in this case, as the operation is to be done elementwise. There is a very minor addition which fixes the problem. Change df[['A','C']]
to df[['A','C']].values
.
Code:
df[['B', 'D']] = df[['B','D']] - df[['A','C']].values
Output:
A B C D
0 1 1 3 1
1 5 1 7 1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With