Data Science

How to easily join data by location in Python - Spatial join

How to do spatial join easily in Python and Why it is a powerful tool often ignored in data science.

Abdishakur
October 22, 20195 min read

How to quickly join data by location in Python - Spatial join

The chances are high that you have joined two tables if you have used Pandas Library. It is incredible how easy it is to combine two different datasets that share columns. But do you know that there is an easy way to join tables (DataFrames) based on locations?

Spatial join example
Spatial join example

I show you an efficient way to join your tables using Spatial join. Spatial join is similar to joining data by attributes. The key difference is only that the tables are joined based on their locations in the spatial join. It is necessary to have matching keys in both tables to perform join by attributes; in contrast, you need locations (Latitude & Longitude) to perform the spatial join.

Use cases for spatial join are many. Let us say you have schools as points and districts as areas (Polygon) and you want to determine how many schools are in each district. By performing a spatial join, you can quickly transfer the point table into areas table or vice-versa.

Practical Application: An Example with Airbnb Dataset

Enough for the concept and hypothetical example. Let us take a concrete example. I want to get the count of how many Airbnb listings are in each voting district. I use Airbnb Dataset for Stockholm in this tutorial. The highlighted datasets are used to demonstrate the spatial join.

Airbnb Dataset Stockholm - Source
Airbnb Dataset Stockholm - Source

Let us read first the tables in pandas as usual with .read_csv(). In step 2, We convert the latitude and longitude into Geometry using Geopandas. The neighbourhoods data is in Geojson, so we can directly read in Geopandas. Geopandas is a high-level pandas library that makes it easy to work with location data.

text
import pandas as pdimport geopandas as gpd
text
# 1-  Listing pointslistings = pd.read_csv("data/stockholm_listings.csv")
text
# 2 - convert to Geopandas Geodataframegdf_listings = gpd.GeoDataFrame(listings,   geometry=gpd.points_from_xy(listings.longitude, listings.latitude))
text
# 3 - Neighbourhoodsgeojson_file = "data/stockholm_neighbourhoods.geojson"neighborhoods = gpd.read_file(geojson_file)

The gdf_listings table has different columns for each property, including hostname, neighbourhood, latitude and longitude. And Geopandas conversion adds the geometry column which allows us to do the spatial join.

Airbnb Listings table
Airbnb Listings table

On the other hand, the Geojson neighbourhood file holds the neighbourhoods name and Geometry.

Neighbourhood table
Neighbourhood table

Both datasets have neighbourhood column, and if you plot them overlayed, they perfectly align well (Map below), and we can easily Groupby and take the size of each neighbourhood to count the number of listings in each neighbourhood.

Airbnb Listing as Points and Neighborhoods as Areas
Airbnb Listing as Points and Neighborhoods as Areas

But, imagine you have a different set of neighbourhoods (Polygons). Let us say the voting districts (smaller areas) which offer a better granularity and have other interesting attributes like population totals. Let me read the data first with Geopandas.

text
stockholm_areas = gpd.read_file("data/stockholm_areas.geojson")stockholm_areas.head()

Each voting district has aDesocolumn which holds the code for the district and Totalwhich has a total population in each district. These new granular (smaller) areas also perfectly overlay with the points dataset, but the problem is that we don't have which voting district the point belongs. The data only came with neighbourhoods column

Listings map - Points of listings and voting districts
Listings map - Points of listings and voting districts

Spatial Join

Let us say we are interested in counting the number of listings per the voting area (voting districts). Although it is not readily available in our columns, we can do this easily and this where spatial join comes handy. With one line of code, you can determine which point is within each voting district.

text
sjoined_listings = gpd.sjoin(gdf_listings, stockholm_areas, op="within")
text
sjoined_listings.head()

The above code joins gdf_listings to stockholm_areasbased on their locations. The op parameter determines what kind of join you want. In this case, we provided "within" - that determines which area the point belongs. Here is a sample of 5 rows from the joined table.

Spatial join table
Spatial join table

As you can see, this table joins the two based on their locations. We have the attributes of the point as well as which area the point belongs. Now it is easy to count how many listings are in each area with pandas group-by function. We can perform pandas Groupby like this, to get how many listings are in each voting district area.

text
grouped = sjoined_listings.groupby("Deso").size()df = grouped.to_frame().reset_index()df.columns = ['Deso', 'listings_count']

We end up with a DataFrame of district area code Deso and listing counts in each area.

Grouped-by Dataframe - number of listings per voting district
Grouped-by Dataframe - number of listings per voting district

Notice that this grouped data is a Dataframe. If you want to have it with Voting areas dataset, you can simply use pandas merge function based on "Deso" column on both Dataframes.

text
merged_areas = stockholm_areas.merge(df, on='Deso', how='outer')

The result of the merge is GeoDataFrame which you can plot geographically. The following is Choroplpleth map for the number of listings in each voting district.

Choropleth map - Number of listings per voting district
Choropleth map - Number of listings per voting district

Give spatial join a try if you have some ideas otherwise try joining Airbnb dataset of a city of your choice and with different areas.

Conclusion

Spatial joins are a powerful tool not often used in data science. It is quite intuitive and straightforward to do in Python with the help of Geopandas. In this tutorial, we have performed a spatial join with a practical example using Airbnb dataset. I hope you find this useful to add it to your data science skills.

The code for this tutorial is available in this GitHub repository.

shakasom/spatialjoin

A google colab notebook is also included in the github repository if you want to run the code for this article directly.

Related Articles