CODEWITHSUNDEEP
python-3.xpandasdataframemergelookup
maede nasri
maede nasri

Reputation: 37

How to look up from two values of data frame and map them - Python

I have two pandas dataframe, name df1, and df2. df1 looks like below:

df1=
tagx tagy datetime
302.  303. 2020-07-01 12:22:31
402.  811. 2020-07-01 12:20:33
.
.
.
.

and df2 is like below:

df2=
Latitude. Longitude.  datetime                  tag
5.01.      52.0988.   2020-07-01 12:22:31       302
5.01.      52.0233.   2020-07-01 12:22:31       303
5.02.      52.0888.   2020-07-01 12:20:33       402
5.02.      52.0122.   2020-07-01 12:20:33       811
.
.
.

I want to look into the df1 and find the value of df1['tagx', 'datetime'], in df2 as df2['tag', 'datetime'], then if I could find, take the Latitude and Longitude to the df1 and add as LatitudeX and LongitudeX on df1[tagx, datetime] rows.(Then repeat the same for tagY)

This is what I like to have:

df1=
tagx tagy datetime              LatitudeX  LongitudeX     LatitudeY LongitudeY        
302.  303. 2020-07-01 12:22:31.  5.01.      52.0988.      5.01.      52.0233.
402.  811. 2020-07-01 12:20:33.  5.02.      52.0888.      5.02.      52.0122.
.
.
.

Note that in df2, datetime cannot be indexed as it doesn't have unique values.

Upvotes: 0

Views: 48

Answers (1)

Balaji Ambresh
Balaji Ambresh

Reputation: 5037

Here you go:

left = pd.merge(df1, df2, left_on=['tagx', 'datetime'], right_on=['tag', 'datetime'])\
    .rename(columns={'Latitude.': 'LatitudeX', 'Longitude.': 'LongitudeX'})\
    .drop(columns='tag')

right = pd.merge(df1, df2, left_on=['tagy', 'datetime'], right_on=['tag', 'datetime'])\
    .rename(columns={'Latitude.': 'LatitudeY', 'Longitude.': 'LongitudeY'})\
    .drop(columns='tag')

print(pd.merge(left, right, on=['tagx', 'tagy', 'datetime']))

Output

    tagx   tagy             datetime LatitudeX LongitudeX LatitudeY LongitudeY
0  302.0  303.0  2020-07-01 12:22:31     5.01.   52.0988.     5.01.   52.0233.
1  402.0  811.0  2020-07-01 12:20:33     5.02.   52.0888.     5.02.   52.0122.

Upvotes: 1

Related Questions