Extracting column from dataframe based on matching three column values in another dataframe

Question

I have two dataframes :

import pandas as pd

df1 = pd.DataFrame(columns={'Date', 'latitude' , 'longitude' , 'tg'})

df1.head(5).to_dict() : 

{'Date': {0: Timestamp('1984-01-01 00:00:00'), 1: Timestamp('1984-01-02 
00:00:00'), 2: Timestamp('1984-01-03 00:00:00'), 3: Timestamp('1984-01-04 00:00:00'), 4: Timestamp('1984-01-05 00:00:00')}, 'longitude': {0: 13.875,
 1: 13.875, 2: 13.875, 3: 13.875, 4: 13.875}, 'tg': {0: 5.339999675750732, 
1: 2.559999942779541, 2: 4.019999980926514, 3: -0.07000000029802322, 4: 
-2.259999990463257}, 'latitude': {0: 49.125, 1: 49.125, 2: 49.125, 3: 
49.125, 4: 49.125}}



df2 = pd.DataFrame(columns={'Date', 'latitude_rr' , 'longitude_rr' , 'Runoff' , 'rr'})

df2.head(5).to_dict() : 

{'Date': {0: Timestamp('1984-01-01 00:00:00'), 1: Timestamp('1984-01-02 
00:00:00'), 2: Timestamp('1984-01-03 00:00:00'), 3: Timestamp('1984-01-04 
00:00:00'), 4: Timestamp('1984-01-05 00:00:00')}, 'latitude_rr': {0: 49.125, 
1: 49.125, 2: 49.125, 3: 49.125, 4: 49.125}, 'longitude_rr': {0: 13.875, 1: 
13.875, 2: 13.875, 3: 13.875, 4: 13.875}, 'Runoff': {0: 0.3808451, 1: 
0.3808451, 2: 0.6760563, 3: 1.464789, 4: 2.456338}, 'rr': {0: 0.0, 1: 0.0, 
2: 2.799999952316284, 3: 2.200000047683716, 4: 0.4000000059604645}}

I am trying to get the 'tg' values when df1.Date equals df2.Date and df1.latitude equals df2.latitude and df1.longitude equals df2.longitude then add them to df2.

df1 and df2 don't have the same size. I tried :

df3 = df1[df1.latitude.isin(df2.latitude_rr) & df1.longitude.isin(df2.longitude_rr) & df1.Date.isin(df2.Date)].tg

I tried to add that to df2 but since df2 is larger than df1 , it leads to wrong matches.

I am new to Pandas so any help is greatly appreciated. Thank you!

If they are not the same size, what unique identification do you have in order to compare them? Is there an `id` field that two dataframes can be compared based on? — TheFaultInOurStars, Feb 21 '22 at 14:54
The unique identifications would be the date , longitude and latitude. — zorals, Feb 21 '22 at 14:55

Corralien · Accepted Answer · 2022-02-21T16:30:50.087

1

If it's sufficient, use df.merge:

out = df1.merge(df2.rename(columns={'latitude_rr': 'latitude', 
                                    'longitude_rr': 'longitude'}),
                on=['Date', 'latitude', 'longitude'])
print(out)

# Output
        Date  longitude    tg  latitude    Runoff   rr
0 1984-01-01     13.875  5.34    49.125  0.380845  0.0
1 1984-01-02     13.875  2.56    49.125  0.380845  0.0
2 1984-01-03     13.875  4.02    49.125  0.676056  2.8
3 1984-01-04     13.875 -0.07    49.125  1.464789  2.2
4 1984-01-05     13.875 -2.26    49.125  2.456338  0.4

If it's not sufficient, you can use KDTree. This answer can help you.

First merge your 2 dataframes only by date because it's not a good choice to merge on latitude and longitude (float precision). You need a function like np.close to match coordinates:

out = df1.merge(df2, on='Date')

m1 = np.isclose(out['latitude'], out['latitude_rr'], atol=0.00001)
m2 = np.isclose(out['longitude'], out['longitude_rr'], atol=0.00001)
out = out[m1 & m2]

You have to adjust the tolerance atol

edited Feb 21 '22 at 16:30

answered Feb 21 '22 at 15:09

Corralien

109,409
8
28
52

I have the same date sequence for different latitudes and longitudes. I get a MemoryError: Unable to allocate 1.93 GiB for an array with shape (259324578,) and data type int64 when trying to merge. – zorals Feb 21 '22 at 15:11
1

Please update your post with a significant sample. `print(df1.head(5).to_dict())` and `print(df2.head(5).to_dict())` – Corralien Feb 21 '22 at 15:15
Maybe you should use `KDTree`. Look at https://stackoverflow.com/a/67134845/15239951 – Corralien Feb 21 '22 at 15:17
Post updated! I'll have a look at KDTree. – zorals Feb 21 '22 at 15:31
I updated my answer. Can you check it please? – Corralien Feb 21 '22 at 16:31

Extracting column from dataframe based on matching three column values in another dataframe

1 Answers1