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.

September

Answer 1

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)

Overweary

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

Answer 2

Does this work?

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

Sublunary

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)) – Admiralty

Answer 3

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.

Windbound