CODEWITHSUNDEEP
pandassubtraction
codingknob
codingknob

Reputation: 11700

subtracting two dataframes

df1: 

City, 2015-12-31, 2016-01-31, ...
YYZ  562.14, -701.18, ...
DFW  562.14, -701.18, ...
YYC  562.14, -701.18, ...

df2: 

City, 2015-12-31, 2016-01-31, ...
SFO  562.14, -701.18, ...
PDX  562.14, -701.18, ...
LAX  562.14, -701.18, ...

I want to subtract df1 from df2. i.e. subtract values in respective date columns.

I tried the following:

df2.subtract(df1, fill_value=0)

But I receive the following error:

TypeError: unsupported operand type(s) for -: 'str' and 'float'

I think the error is because the operation cannot understand how to subtract strings in the City column, which obviously makes sense since subtracting the Cities is nonsensical.

The accepted answer in this post [link] seems to suggest this is possible. I am the author of that question but can't seem to get it to work now.

Upvotes: 18

Views: 114302

Answers (3)

Ludooohhh
Ludooohhh

Reputation: 21

There is another, quite simple way to subtract columns from two dataframes: copy one, and subtract the columns you want in the copy.

df_diff = df1.copy()
df_diff["date"] = df1["date"] - df2["date"]

That way you can control which columns you want to subtract, without losing any info.

Upvotes: 2

Ted Petrou
Ted Petrou

Reputation: 62037

Move the City column into the index. The DataFrames will align by both index and columns first and then do subtraction. Any combination not present will result in NaN.

df2.set_index('City').subtract(df1.set_index('City'), fill_value=0)

Upvotes: 35

BallpointBen
BallpointBen

Reputation: 13999

Does this work?

df2.drop(['City']).subtract(df1.drop(['City']))

Upvotes: 2

Related Questions