GeoPandas spatial join and count

Question

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

Answer 1

Try:

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')

Data: