Need to merge two pandas dataframe using two columns latitude and longitude

user_5602871 0
Viewed 1442

this is my dataframe#1 :city names with its latitude and longitude

df1 = {"city":['delhi','new york','london','paris','chennai'],"lat":[12.23,22.444,23.233,45.32,34.22],"long":[11.22,22.332,34.23,55.23,24.22]

this is dataframe#2 : country names with latitude and longitude

df2 = pd.DataFrame({"country":['India','US','UK','France','India'],"lat":[12.13,22.54,22.33,45.32,34.22],"long":[11.12,22.132,34.23,54.23,24.22]})

I need to match these two columns lat and long to merge these two tables. the problem is the lat and long is not exactly matching and the values are + or - 0.1 or 0.2. (if matched I can use the pd.merge option) lat and longs are not real here. just an example

Expected Result:

result = pd.DataFrame({"city":['delhi','new york','london','paris','chennai'],"country":['India','US','UK','France','India'],"lat":[12.13,22.54,22.33,45.32,34.22],"long":[11.12,22.132,34.23,54.23,24.22]})

what is the best approach to merge these tables?

pandas latitude-longitude merge python
2 Answers

Geopandas may be use here.

Provided that you have boundaries of countries as polygons, you can use spacial joins.

In your question, you are reducing countries to single points which may not be the best representation.

Example from the documentation:

In a Spatial Join, two geometry objects are merged based on their spatial relationship to one another.

# One GeoDataFrame of countries, one of Cities.
# Want to merge so we can get each city's country.
In [11]: countries.head()
Out[11]: 


                                           geometry                   country
0  MULTIPOLYGON (((180.000000000 -16.067132664, 1...                      Fiji
1  POLYGON ((33.903711197 -0.950000000, 34.072620...                  Tanzania
2  POLYGON ((-8.665589565 27.656425890, -8.665124...                 W. Sahara
3  MULTIPOLYGON (((-122.840000000 49.000000000, -...                    Canada
4  MULTIPOLYGON (((-122.840000000 49.000000000, -...  United States of America

In [12]: cities.head()
Out[12]: 
           name                           geometry
0  Vatican City  POINT (12.453386545 41.903282180)
1    San Marino  POINT (12.441770158 43.936095835)
2         Vaduz   POINT (9.516669473 47.133723774)
3    Luxembourg   POINT (6.130002806 49.611660379)
4       Palikir  POINT (158.149974324 6.916643696)

# Execute spatial join
In [13]: cities_with_country = geopandas.sjoin(cities, countries, how="inner", op='intersects')

In [14]: cities_with_country.head()
Out[14]: 
             name                           geometry  index_right  country
0    Vatican City  POINT (12.453386545 41.903282180)          141    Italy
1      San Marino  POINT (12.441770158 43.936095835)          141    Italy
192          Rome  POINT (12.481312563 41.897901485)          141    Italy
2           Vaduz   POINT (9.516669473 47.133723774)          114  Austria
184        Vienna  POINT (16.364693097 48.201961137)          114  Austria

If you don't have the polygons representing the countries, you need to extend the point representing each country to an area. You can do this using the buffer method in Shapely that is extending a point to an area given a distance:

Point(0, 0).buffer(10.0),

assuming a point at coordinates [0,0] and a distance of 10.0.

user_11006740

For example of a cross merge:

(df1.assign(dummy=1)
    .merge(df2.assign(dummy=1),on='dummy')
    .query('abs(lat_x-lat_y)<=0.1 and abs(long_x-long_y)<=0.2')
    .drop('dummy', axis=1)
)

Output:

        city   lat_x  long_x country  lat_y  long_y
0      delhi  12.230  11.220   India  12.13  11.120
6   new york  22.444  22.332      US  22.54  22.132
24   chennai  34.220  24.220   India  34.22  24.220
user_42384080
Related