(Geo)DataFrame left join leads to NaN in all right values

Question

Let these two CSV files on the disk:

$ cat /path/to/gdf0.csv

myid,geometry
332,"MULTIPOLYGON Z (((0 0 0, 0 0 1, 0 1 1, 0 0 0)))"
220,"MULTIPOLYGON Z (((1 1 1, 1 1 2, 1 2 2, 1 1 1)))"


$ cat /path/to/gdf1.csv

myid,other
395,"POLYGON Z ((0 0, 1 1, 0 0))"
220,"POLYGON Z ((1 1, 2 2, 1 1))"
394,"POLYGON Z ((2 2, 3 3, 2 2))"
332,"POLYGON Z ((3 3, 4 4, 3 3))"

And then in Python:

import pandas as pd # version: '1.5.2'
import geopandas as gpd # version: '0.12.2'

g0 = gpd.GeoDataFrame(pd.read_csv('/path/to/gdf0.csv'))
g1 = gpd.GeoDataFrame(pd.read_csv('/path/to/gdf1.csv'))

Why does a simple left join lead to right values as NaN in the resulting dataframe?

g0.join(g1, on='myid', how='left', lsuffix='_left', rsuffix='_right')

>: 
   myid_left                                 geometry  myid_right     other
0        332  MULTIPOLYGON Z (((0 0 0, 0 0 1, 0 1 ...         NaN       NaN
1        220  MULTIPOLYGON Z (((1 1 1, 1 1 2, 1 2 ...         NaN       NaN

My expectation was as follows:

g0.join(g1, on='myid', how='left', lsuffix='_left', rsuffix='_right')

>: 
   myid_left                                 geometry  myid_right                             other
0        332  MULTIPOLYGON Z (((0 0 0, 0 0 1, 0 1 ...         332       POLYGON Z ((3 3, 4 4, 3 3))
1        220  MULTIPOLYGON Z (((1 1 1, 1 1 2, 1 2 ...         220       POLYGON Z ((1 1, 2 2, 1 1))

The result is identical if I load the data as Panda's DataFrames:

g0 = pd.read_csv('/path/to/gdf0.csv')
g1 = pd.read_csv('/path/to/gdf1.csv')

I might have done something stupendously wrong, but what?

Answer 1

Because according to the documentation, the 'on' parameter is only for the joining (left) df, not the joined (right) df:

Another option to join using the key columns is to use the on parameter. DataFrame.join always uses other’s index but we can use any column in df. This method preserves the original DataFrame’s index in the result.

So the following will work:

gdf0.join(gdf1.set_index('myid'), on='myid', how='left', lsuffix='_left', rsuffix='_right')


   myid                                                                    geometry_left                        other                                                 geometry_right
0   332  MULTIPOLYGON Z (((0.00000 0.00000 0.00000, 0.00000 0.00000 1.00000, 0.00000 ...  POLYGON Z ((3 3, 4 4, 3 3))  POLYGON ((3.00000 3.00000, 4.00000 4.00000, 3.00000 3.00000))
1   220  MULTIPOLYGON Z (((1.00000 1.00000 1.00000, 1.00000 1.00000 2.00000, 1.00000 ...  POLYGON Z ((1 1, 2 2, 1 1))  POLYGON ((1.00000 1.00000, 2.00000 2.00000, 1.00000 1.00000))

But note that your GeoDataFrames do not actually have geometry, just WKT strings. You need to to convert the WKT to geometry manually or tell geopandas/GDAL to use the "geometry" or "other" fields as geometry.