5

I have following two GeoDataFrames. One with the crime count per polygon per year.

   Polygon  Year total_crime      shape_area          geometry
0        0  2009           1    3.266388e+06    POLYGON ((..))
1        0  2010           4    3.266388e+06    POLYGON ((..))
2        1  2009           2    3.266388e+06    POLYGON ((..))
3        1  2010           8    3.266388e+06    POLYGON ((..))
4        2  2009           4    3.266388e+06    POLYGON ((..))

and a GeoDataFrame where each row represents a Point of Interest.

    CATEGORY      geometry
13243   atm     POINT (..)
13244   bank    POINT (..)
13245   bar     POINT (..)
         ...
         ...
         ...

how would I join those GeoDataFrames to represent how many Points of Interests of each type are in every Polygon?

The Final GeoDataFrame should look something like this.

   Polygon  Year total_crime      shape_area    atm bank bar
0        0  2009           1    3.266388e+06      2    1   4
0        0  2010           4    3.266388e+06      2    1   4
1        1  2009           2    3.266388e+06      5    3   6
1        1  2010           8    3.266388e+06      5    3   6
2        2  2009           4    3.266388e+06      1    8   5
6

Try:

enter image description here

import geopandas as gpd
import pandas as pd

polys = gpd.read_file(r"C:\Test\Shapes.shp")
points = gpd.read_file(r"C:\Test\POI.shp")

dfsjoin = gpd.sjoin(polys,points) #Spatial join Points to polygons
dfpivot = pd.pivot_table(dfsjoin,index='PolyID',columns='Food',aggfunc={'Food':len})
dfpivot.columns = dfpivot.columns.droplevel()

dfpolynew = polys.merge(dfpivot, how='left',on='PolyID')

enter image description here

  • 1
    I thank you so much for this! I was looking for 3 days how to do this!!! You saved me thank you – Charles David Mupende Dec 20 '18 at 16:22
1
dfpivot = pd.pivot_table(df,index='PolyID',columns='Food',aggfunc={'Food':len})

Should be:

dfpivot = pd.pivot_table(dfsjoin,index='PolyID',columns='Food',aggfunc={'Food':len})
  • I've changed my answer, thanks. You should be able to edit my answer and propose the changes but you might need higher reputation – BERA Aug 25 at 7:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.