CODEWITHSUNDEEP
pythonpandasdataframe
Phil Baines
Phil Baines

Reputation: 467

How to compare two Pandas DataFrames on values?

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

Upvotes: 0

Views: 78

Answers (2)

jpp
jpp

Reputation: 164673

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})

Upvotes: 2

Gabriel A
Gabriel A

Reputation: 1827

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

Upvotes: 1

Related Questions