GeoPandas GeoDataFrame转换

Question

尊敬的stackoverflow社区：

在过去的几周里，我一直在阅读有关Python、熊猫和地质公园的文献和文章。可悲的是，编程对我来说并不像我希望的那样直观，而且由于我不是来自与GeoPandas打交道的编程背景，这对我来说是一场纯粹的噩梦。在

我有一个相当复杂的（至少对我来说）geopandas.GeoDataFrame，我需要转换它来做进一步的回归分析。可悲的是，即使在stackoverflow和许多其他互联网页面上进行了无数次搜索，我仍然无法以适当的方式转换数据。在

---

我的地理数据框架如下：

         INCIDENTDATE       CATEGORY_left    CATEGORY_right  \
POLYGON                                                       
1                2009            BURGLARY        restaurant   
1                2009            HOMICIDE        restaurant   
1                2010             ASSAULT        restaurant   
1                2011             ASSAULT        restaurant   
1                2012             LARCENY        restaurant   
1                2012  AGGRAVATED ASSAULT        restaurant   
1                2012            BURGLARY        restaurant   
1                2012  DAMAGE TO PROPERTY        restaurant   
1                2013  AGGRAVATED ASSAULT        restaurant   
1                2014            BURGLARY        restaurant   
3                2010  MURDER/INFORMATION          crossing   
3                2011  AGGRAVATED ASSAULT          crossing   
3                2011            BURGLARY          crossing   
3                2011             ASSAULT          crossing   
3                2012  AGGRAVATED ASSAULT          crossing   
3                2012  MURDER/INFORMATION          crossing   
3                2013     DANGEROUS DRUGS          crossing   
3                2014  DAMAGE TO PROPERTY          crossing   
3                2015             ASSAULT          crossing   

                                                  geometry    shape_area  
POLYGON                                                                   
1        POLYGON ((-83.13630642653472 42.43895550416347...  3.959841e+06  
1        POLYGON ((-83.13630642653472 42.43895550416347...  3.959841e+06  
1        POLYGON ((-83.13630642653472 42.43895550416347...  3.959841e+06  
1        POLYGON ((-83.13630642653472 42.43895550416347...  3.959841e+06  
1        POLYGON ((-83.13630642653472 42.43895550416347...  3.959841e+06  
1        POLYGON ((-83.13630642653472 42.43895550416347...  3.959841e+06  
1        POLYGON ((-83.13630642653472 42.43895550416347...  3.959841e+06  
1        POLYGON ((-83.13630642653472 42.43895550416347...  3.959841e+06  
1        POLYGON ((-83.13630642653472 42.43895550416347...  3.959841e+06  
1        POLYGON ((-83.13630642653472 42.43895550416347...  3.959841e+06  
3        POLYGON ((-83.17870657596477 42.39269734838572...  3.918602e+06  
3        POLYGON ((-83.17870657596477 42.39269734838572...  3.918602e+06  
3        POLYGON ((-83.17870657596477 42.39269734838572...  3.918602e+06  
3        POLYGON ((-83.17870657596477 42.39269734838572...  3.918602e+06  
3        POLYGON ((-83.17870657596477 42.39269734838572...  3.918602e+06  
3        POLYGON ((-83.17870657596477 42.39269734838572...  3.918602e+06  
3        POLYGON ((-83.17870657596477 42.39269734838572...  3.918602e+06  
3        POLYGON ((-83.17870657596477 42.39269734838572...  3.918602e+06  
3        POLYGON ((-83.17870657596477 42.39269734838572...  3.918602e+06

---

'CATEGORY_left'是使用geopandas.sjoin和几何点连接的geopandas.GeoDataFrame。它包含不同类别的犯罪相关事件，如下所示：

'CATEGORY_left'

^{pr2}$

'CATEGORY_right'也是我与geopandas.sjoin一起加入的'CATEGORY_right'。它包含只依赖于'POLYGON'的不同兴趣点。它们不会随时间而改变。在

'CATEGORY_right'

          CATEGORY                                          geometry
13243          atm      POINT (-83.06221670000002 42.32472120000001)
13244          atm                    POINT (-83.0711901 42.3213266)
13245          atm             POINT (-83.0232692 42.34089829999999)
24624  supermarket             POINT (-83.2400998 42.37158820000001)
24625  supermarket                    POINT (-82.9728123 42.3872246)

---

为了做回归分析，我需要它的形状如下。在

最终：

         INCIDENTDATE       TOTAL_CRIME_COUNT    RESTAURANT_COUNT\
POLYGON                                                       
1                2009                    4396                 30
1                2010                    6455                 30
1                2011                    7434                 30
1                2012                    3843                 30
1                2013                    5354                 30
1                2014                    3425                 30
3                2010                    4564                 10
3                2011                    3234                 10
3                2012                    8754                 10
3                2013                    4829                 10
3                2014                    9583                 10
3                2015                    4354                 10

                                                  geometry    shape_area  
POLYGON                                                                   
1        POLYGON ((-83.13630642653472 42.43895550416347...  3.959841e+06  
1        POLYGON ((-83.13630642653472 42.43895550416347...  3.959841e+06  
1        POLYGON ((-83.13630642653472 42.43895550416347...  3.959841e+06  
1        POLYGON ((-83.13630642653472 42.43895550416347...  3.959841e+06  
1        POLYGON ((-83.13630642653472 42.43895550416347...  3.959841e+06  
1        POLYGON ((-83.13630642653472 42.43895550416347...  3.959841e+06  
1        POLYGON ((-83.13630642653472 42.43895550416347...  3.959841e+06  
1        POLYGON ((-83.13630642653472 42.43895550416347...  3.959841e+06  
1        POLYGON ((-83.13630642653472 42.43895550416347...  3.959841e+06  
1        POLYGON ((-83.13630642653472 42.43895550416347...  3.959841e+06  
3        POLYGON ((-83.17870657596477 42.39269734838572...  3.918602e+06  
3        POLYGON ((-83.17870657596477 42.39269734838572...  3.918602e+06  
3        POLYGON ((-83.17870657596477 42.39269734838572...  3.918602e+06  
3        POLYGON ((-83.17870657596477 42.39269734838572...  3.918602e+06  
3        POLYGON ((-83.17870657596477 42.39269734838572...  3.918602e+06  
3        POLYGON ((-83.17870657596477 42.39269734838572...  3.918602e+06  
3        POLYGON ((-83.17870657596477 42.39269734838572...  3.918602e+06  
3        POLYGON ((-83.17870657596477 42.39269734838572...  3.918602e+06  
3        POLYGON ((-83.17870657596477 42.39269734838572...  3.918602e+06

需要注意的重要事项：

1. 这些行由'INCIDENTDATE'中的相同值聚合
2. 每年每个多边形的犯罪事件总数在'TOTAL_CRIME_COUNT'列中
3. 每个多边形的不同兴趣点之和。每 兴趣点需要在它自己的专栏里。在

我会很高兴哪怕是一点解决办法的提示。 我也对完全不同的方法来达到我的最终数据帧持开放态度，因为我甚至不确定我是否以正确的方式开始。在

如果你能做到这一点，非常感谢你！在

查尔斯

附言：我为语法错误道歉。英语不是我的第一语言。在