CODEWITHSUNDEEP
pythonpandaspython-2.7join
Atihska
Atihska

Reputation: 5126

How can I subtract 2 columns if another column's value is same in pandas dataframe

I have 2 data frames, df1 and df2 both having columns 'A', 'count'. I want to join these based on column A and then subtract count from those rows if column is same. So for example: 

df1
A    count
s1   10
s2    5
s3    3
s4    7

df2
A    count
s1    2
s4    4

My final output should be

combined
A    count1   count2   change
s1    10        2       8 // count1-count2
s2    5         none    none
s3    3         none    none
s4    7         4        3

Upvotes: 3

Views: 805

Answers (1)

jpp
jpp

Reputation: 164713

You can perform an outer merge followed by a calculation:

res = pd.merge(df1.rename(columns={'count': 'count1'}),
               df2.rename(columns={'count': 'count2'}), how='outer')\
        .eval('change = count1 - count2')

print(res)

    A  count1  count2  change
0  s1      10     2.0     8.0
1  s2       5     NaN     NaN
2  s3       3     NaN     NaN
3  s4       7     4.0     3.0

Upvotes: 1

Related Questions