CODEWITHSUNDEEP
pythonpandasdataframe
Bounty Collector
Bounty Collector

Reputation: 635

How to do Pandas data frame comparison on one column, row by row?

Lets consider two data frames

df1=
 A    B    C   D  E    F  G
 a1   b1  c1  d1  e1  f1  1
 a2   b2  c2  d2  e2  f2  3
 a3   b3  c3  d3  e3  f2  5
 a4   b4  c4  d4  e4  f4  Nan

df2=
 A    B    C   D  E    F  G
 a1   b1  c1  d1  e1  f1  1
 a2   b2  c2  d2  e2  f2  3
 a3   b3  c3  d3  e3  f2  4
 a4   b4  c4  d4  e4  f4  Nan
 a5   b5  c5  d5  e5  f5  7

I want to compare the two dataframes on the column G, but we should do it only if each row in each dataframe as same value., So from A to F, if each row is same in df1 and df2 generate a column called result which shows column G from df1 - column G from df2 to yield a dataframe like this. 

resultdf=
 A    B    C   D  E    F G_DF1 G_DF2  Result
 a1   b1  c1  d1  e1  f1   1     1     0
 a2   b2  c2  d2  e2  f2   3     3     0
 a3   b3  c3  d3  e3  f2   5     4     1
 a4   b4  c4  d4  e4  f4  Nan    Nan   Nan

The row number 5 in df2 should be discarded.

I tried 

result=pd.merge(df1, df2, on=[A,B,C,D,E,F]) but it doesn't seem to work.

Upvotes: 0

Views: 74

Answers (2)

Erfan
Erfan

Reputation: 42916

First we get the column names in a generalized way without hardcoding it with iloc and tolist. Then we merge on these columns. Finally we assign your Result column and drop the G columns:

cols = [col for col in df2.columns if col != 'G']
df2 = df2.merge(df1, on=cols)
df2.assign(Result=df2['G_y'] - df2['G_x']).drop(columns=['G_x', 'G_y'])

Output

    A   B   C   D   E   F  Result
0  a1  b1  c1  d1  e1  f1     0.0
1  a2  b2  c2  d2  e2  f2     0.0
2  a3  b3  c3  d3  e3  f2     1.0
3  a4  b4  c4  d4  e4  f4     NaN

Or we can do this in a one liner with apply, but this would not be my preferred solution:

cols = [col for col in df2.columns if col != 'G']

df2.set_index(cols).merge(df1.set_index(cols), 
                          left_index=True,
                          right_index=True).apply(lambda x: x['G_x'] - x['G_y'], axis=1)\
                                           .reset_index(name="Result")

Upvotes: 2

it's-yer-boy-chet
it's-yer-boy-chet

Reputation: 2017

I think this should work:

 result = df1.merge(df2, on=['A','B','C','D','E','F'], suffixes=('_DF1','_DF2')).reset_index()
 result['Result'] = result['G_DF1'] - result['G_DF2']

Upvotes: 1

Related Questions