How to compare two Pandas DataFrames on values?

Question

I have two Pandas DataFrames (A & B) with latitude and longitude.

I need to compare them, and if latitude and longitude from DF A is present in DF B then append a 1 else 0.

DF A

 LatLong
-37.3794288,175.6697856
-37.0334148,174.8680204
-41.173852,174.981931

DF B
KBATMLongLat
-37.0334148,174.8680204
-37.5575605,175.1584622
-37.0334148,174.8680204

How can I achieve the expected output (see below)?

 Long lat               | Result
--------------------------------
-37.3794288,175.6697856 | False
-37.0334148,174.8680204 | True
-41.173852,174.981931   | False

I see result should be False, True, False ?

jpp
– jpp

2018-02-11 23:15:51 +00:00
Commented Feb 11, 2018 at 23:15 — jpp
– jpp, Commented Feb 11, 2018 at 23:15
That is what the result should be.

Gabriel A
– Gabriel A

2018-02-11 23:27:33 +00:00
Commented Feb 11, 2018 at 23:27 — Gabriel A
– Gabriel A, Commented Feb 11, 2018 at 23:27

jpp · Accepted Answer · 2018-02-11 23:45:39Z

2

This is one way:

import pandas as pd

df1 = pd.DataFrame([[-37.3794288,175.6697856],
                    [-37.0334148,174.8680204],
                    [-41.173852,174.981931]],
                   columns=['Long', 'Lat'])

df2 = pd.DataFrame([[-37.0334148,174.8680204],
                    [-37.5575605,175.1584622],
                    [-37.0334148,174.8680204]],
                   columns=['Long', 'Lat'])


df1['Result'] = [tuple(i) in set(map(tuple, df2.values)) for i in df1.values]

#         Long         Lat  Result
# 0 -37.379429  175.669786   False
# 1 -37.033415  174.868020    True
# 2 -41.173852  174.981931   False

Alternatively, more pandonic:

df = pd.merge(df1, df2, indicator=True, how='left').\
              drop_duplicates().rename(columns={'_merge': 'Result'})

df['Result'] = df['Result'].map({'left_only': False, 'both': True})

edited Feb 11, 2018 at 23:45

answered Feb 11, 2018 at 23:15

jpp

166k37 gold badges301 silver badges362 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Phil Baines Over a year ago

Thank you, this worked for me (the first answer is what I used) KBdf['Result'] = [tuple(i) in set(map(tuple, KBATMdf['column'].values)) for i in KBdf['column'].values]

Gabriel A · Accepted Answer · 2018-02-11 23:26:24Z

1

I'm not sure how efficient this is, but you could use a multi-index

df1 = df1.set_index(["Long","Lat"])
df2 = df2.set_index(["Long","Lat"])
df1["Result"] = df1.index.isin(df2.index)
df1 = df1.reset_index()
df1


    Long        Lat         Result
0   -37.379429  175.669786  False
1   -37.033415  174.868020  True
2   -41.173852  174.981931  False

answered Feb 11, 2018 at 23:26

Gabriel A

1,83712 silver badges12 bronze badges

Collectives™ on Stack Overflow

How to compare two Pandas DataFrames on values?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related