Merge pandas dataframes diff of columns

user_13320076 0
Viewed 110

I have 2 Dataframes that I would like to merge. Most of the columns are identical between the dataframes. Some columns however are different and I want the diff. my columns might look like this:

df1:
   A  B  C  D  E
0  0  3  8  7  6
1  1  4  1  1  3
2  2  5  2  2  4
df2:
   A  B  C  D  E
0  0  3  1  5  6
1  1  4  2  4  4
2  2  5  3  3  5
desired df3:
   A  B  C  D  E
0  0  3  7  2  0
1  1  4 -1 -3 -1
2  2  5 -1 -1 -1

Initially I tried doing a merge on all columns that are equal and then subtracting the columns with suffixes, but this seems unpandas like. Also I just manually did the merge by copying all columns that are equal and than diffing. I do not like this approach however since I wont to be sure the program is future proof in case a row is missing in one of the df at a later point so I wont to do some kind of inner merge.

Here is my code for the very unpandas like method.

import pandas as pd

df1 = pd.DataFrame(data = {'A':[0,1,2],'B':[3,4,5],'C':[8,1,2],'D':[7,1,2],'E':[6,3,4]})
df2 = pd.DataFrame(data = {'A':[0,1,2],'B':[3,4,5],'C':[1,2,3],'D':[5,4,3],'E':[6,4,5]})
df3 = df1.merge(df2, on=['A','B'])
all_columns = df1.columns.tolist()
shared_columns = ['A', 'B']
df4 = df1[shared_columns]
diff_columns = list(set(all_columns) - set(shared_columns))
df4[diff_columns] = df1[diff_columns] - df2[diff_columns]

df4 is gives me the results I want, but I am unhappy with the way I get them.

Edit: Adding additional Information: Whether to copy values or subtract them should be consistent for a column. (like a merge on rows would be)

pandas dataframe merge python-3.x python
1 Answers

Use where to keep the cells where the values are equal, and replace with a simple subtraction of the two dataframes if this is not the case:

df1.where(df1.eq(df2), df1-df2)

output:

   A  B  C  D  E
0  0  3  7  2  3
1  1  4 -1 -3 -1
2  2  5 -1 -1 -1

Edit: if you're not sure what to do when the values are equal, you can use mask to replace with a fixed value or the output of any function:

df1.sub(df2).mask(df1.eq(df2), '=')

output:

   A  B  C  D  E
0  =  =  7  2  3
1  =  = -1 -3 -1
2  =  = -1 -1 -1
user_163434640
Related