Compare 2 dataframes Pandas, returns wrong values

Question

There are 2 dfs

datatypes are the same

df1 =

ID city      name    value
1  LA        John    111
2  NY        Sam     222
3  SF        Foo     333
4  Berlin    Bar     444

df2 =

ID  city      name   value
1   NY        Sam    223
2   LA        John   111
3   SF        Foo    335
4   London    Foo1   999
5   Berlin    Bar    444

I need to compare them and produce a new df, only with values, which are in df2, but not in df1

By some reason results after applying different methods are wrong

So far I've tried

pd.concat([df1, df2],  join='inner', ignore_index=True)

but it returns all values together

pd.merge(df1, df2, how='inner')

it returns df1

then this one

df1[~(df1.iloc[:, 0].isin(list(df2.iloc[:, 0])))

it returns df1

The desired output is

ID city      name    value
1   NY        Sam    223
2   SF        Foo    335
3   London    Foo1   999

Check this out. https://stackoverflow.com/questions/48647534/python-pandas-find-difference-between-two-data-frames — user17144, Oct 22 '19 at 07:39

score 4 · Accepted Answer · answered Oct 22 '19 at 07:44

4

Use DataFrame.merge by all columns without first and indicator parameter:

c = df1.columns[1:].tolist()

Or:

c = ['city', 'name', 'value']

df = (df2.merge(df1,on=c, indicator = True, how='left', suffixes=('','_'))
       .query("_merge == 'left_only'")[df1.columns])

print (df)
   ID    city  name  value
0   1      NY   Sam    223
2   3      SF   Foo    335
3   4  London  Foo1    999

answered Oct 22 '19 at 07:44

jezrael

822,522
95
1,334
1,252

1

Nice soln, nice use of `query` here to get a concise answer – yatu Oct 22 '19 at 07:57

score 0 · Answer 2 · answered Oct 22 '19 at 07:56

Try this:

print("------------------------------")
print(df1)

df2 = DataFrameFromString(s, columns)
print("------------------------------")
print(df2)

common = df1.merge(df2,on=["city","name"]).rename(columns = {"value_y":"value", "ID_y":"ID"}).drop("value_x", 1).drop("ID_x", 1)
print("------------------------------")
print(common)

OUTPUT:

------------------------------
   ID    city  name  value
0  ID    city  name  value
1   1      LA  John    111
2   2      NY   Sam    222
3   3      SF   Foo    333
4   4  Berlin   Bar    444
------------------------------
   ID    city  name  value
0   1      NY   Sam    223
1   2      LA  John    111
2   3      SF   Foo    335
3   4  London  Foo1    999
4   5  Berlin   Bar    444
------------------------------
     city  name  ID  value
0      LA  John   2    111
1      NY   Sam   1    223
2      SF   Foo   3    335
3  Berlin   Bar   5    444

Compare 2 dataframes Pandas, returns wrong values

2 Answers2