按日期和城市重新采样和聚合数据帧

Question

我想按日期对数据帧重新采样，并根据城市的总和创建一列 df:

+-----------------+-------------------+------------+
| booking_date    |  Cities           |  province  | 
+-----------------+-------------------+------------+
|  15-12-17       |  Kota Depok       | Jawa Barat |    
|  15-12-17       |  Bogor            | Jawa Barat |      
|  15-12-17       |  Kota Depok       | Jawa Barat |     
|  15-12-17       |  Kota Bandung     | Jawa Barat |    
|  15-12-17       |  Kota Bandung     | Jawa Barat |   
+-----------------+-------------------+------------+

输出可能如下所示：

df:

+-----------------+-------------------+------------+------------+
| booking_date    |  Cities           |  province  |  Count     | 
+-----------------+-------------------+------------+------------+
|  15-12-17       |  Kota Depok       | Jawa Barat |  2         |
|  15-12-17       |  Bogor            | Jawa Barat |  1         |
|  15-12-17       |  Kota Bandung     | Jawa Barat |  2         | 
+-----------------+-------------------+------------+------------+

如何实现

Answer 1

将^{}与^{}和name参数一起使用：

df = df.groupby(['booking_date','Cities','province']).size().reset_index(name='Count')

Answer 2

我想到的第一个解决方案与@jezrael的相同。然而，另一个可以是结合^{}、^{}、^{}和^{}的

代码如下

>>> df = df\
...     .assign(Count = df['Cities'].map(df['Cities'].value_counts()))\
...     .drop_duplicates()
>>> print(df)
  booking_date        Cities    province  Count
0     15-12-17    Kota Depok  Jawa Barat      2
1     15-12-17         Bogor  Jawa Barat      1
3     15-12-17  Kota Bandung  Jawa Barat      2