subtracting two dataframes

pandas subtraction
codingknob picture codingknob · Dec 6, 2016 · Viewed 49.3k times · Source

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.

Answer

Ted Petrou picture Ted Petrou · Dec 6, 2016

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)

Related questions

How to remove a pandas dataframe from another dataframe

How to remove a pandas dataframe from another dataframe, just like the set subtraction: a=[1,2,3,4,5] b=[1,5] a-b=[2,3,4] And now we have two pandas dataframe, how to remove df2 from df1: In [5]: df1=pd.DataFrame([[1,2],[3,4],[5,6]],columns=['a','b']) In [6]: df1 Out[6]: …

Read More
Pandas subtract 2 rows from same dataframe

How do I subtract one row from another in the following dataframe (df): RECL_LCC 1 2 3 RECL_LCC 35.107655 36.015210 28.877135 RECL_PI 36.961519 43.499506 19.538975 I want to do something like: df['Difference'] = df['RECL_LCC']-df['RECL_PI'] but that gives: *** KeyError: 'RECL_LCC'

Read More
How to select duplicate rows with pandas?

I have a dataframe like this: import pandas as pd dic = {'A':[100,200,250,300], 'B':['ci','ci','po','pa'], 'C':['s','t','p','w']} df = pd.DataFrame(dic) My goal is to separate the row in 2 dataframes: df1 = contains all the rows …

Read More