subtracting two dataframes

Question

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.

Ted Petrou · Accepted Answer · 2016-12-06 06:00:37Z

36

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)

answered Dec 6, 2016 at 6:00

Ted Petrou

62.4k19 gold badges139 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BossRoyce Over a year ago

how would I do this if my dataframe also contains non-numerical columns?

BallpointBen · Accepted Answer · 2016-12-06 05:09:10Z

2

Does this work?

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

answered Dec 6, 2016 at 5:09

BallpointBen

15.6k2 gold badges46 silver badges81 bronze badges

1 Comment

M_Idk392845 Over a year ago

Thanks, it works for me in a similar case, but adding axis=1, basically in this form: df2.drop(['City'],axis=1).subtract(df1.drop(['City'],axis=1))

Ludooohhh · Accepted Answer · 2023-07-31 13:09:27Z

2

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.

answered Jul 31, 2023 at 13:09

Ludooohhh

211 bronze badge

Collectives™ on Stack Overflow

subtracting two dataframes

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related