时间戳

首页 > 解决方案 > 每月对该月每个国家的计数相加

python - 每月对该月每个国家的计数相加

问题描述

我有一个 2000 行数据集列表，列表中有一个国家，然后是它们的计数。我想通过分解列表并将每个月、每个月的列表组合在一起来汇总所有计数。

df_grouped=df.pivot_table(index=('month','month_int', 'year'),values='views',aggfunc='max')

count   period_start    year    month_int   month   Countries
1       06/08/2018      2018    6           August  []
1       06/08/2018      2018    6           August  ['Spain', 'Brazil', 'Porgutal', 'France', 'Romania', 'Germany#', 'Norway']
1       06/08/2018      2018    6           August  ['Spain', 'Brazil', 'Porgutal', 'France', 'Romania', 'Germany#', 'Norway']
1       06/08/2018      2018    6           August  ['Porgutal', 'Canada', 'USA', 'Croatia', 'Egypt', 'Netherlands', 'Swizerland', 'Japan']
2       06/08/2018      2018    6           August  ['China', 'India', 'Vietnam']
1       06/08/2018      2018    6           August  ['Indai', ' Pakistan', 'Mongolia']
1       06/08/2018      2018    6           August  ['Indai', ' Pakistan', 'Mongolia']
1       06/08/2018      2018    6           August  ['Indai', ' Pakistan', 'Mongolia']
1       06/08/2018      2018    6           August  []
1       06/08/2018      2018    6           August  ['Germany', 'Spain', 'China', 'USA']
6       06/08/2018      2018    6           August  ['Germany', 'Spain', 'China', 'USA']
1       06/08/2018      2018    6           Sept    ['Germany', 'Spain', 'China', 'USA']
5       06/08/2018      2018    6           Sept    ['Germany', 'Spain', 'China', 'USA']
4       06/08/2018      2018    6           Sept    ['Germany', 'Spain', 'China', 'USA']
....

我不确定如何分解国家主题，将每行的计数相加并按国家分组。

标签： pythonpython-3.xpandas

解决方案

使用.explode()和.groupby()。您需要将其reset_index()设为数据框并传递name='Countries Count'或任何不同于Countries; 否则，你会得到一个错误，因为列名已经存在：

df = (df.explode('Countries')
      .groupby(['year','month','Countries'])['Countries'].count().reset_index(name='Countries Count'))
df
Out[1]: 
    year   month    Countries  Countries Count
0   2018  August     Pakistan                3
1   2018  August       Brazil                2
2   2018  August       Canada                1
3   2018  August        China                3
4   2018  August      Croatia                1
5   2018  August        Egypt                1
6   2018  August       France                2
7   2018  August      Germany                2
8   2018  August     Germany#                2
9   2018  August        Indai                3
10  2018  August        India                1
11  2018  August        Japan                1
12  2018  August     Mongolia                3
13  2018  August  Netherlands                1
14  2018  August       Norway                2
15  2018  August     Porgutal                3
16  2018  August      Romania                2
17  2018  August        Spain                4
18  2018  August   Swizerland                1
19  2018  August          USA                3
20  2018  August      Vietnam                1
21  2018    Sept        China                3
22  2018    Sept      Germany                3
23  2018    Sept        Spain                3
24  2018    Sept          USA                3

推荐阅读