如何计算包括零出现在内的分类值? [英] how to count categorical values including zero occurrence?

问题描述

我想按月计算代码数量.这是我的示例数据框.

I want to count number of code by month. This is my example dataframe.

        id    month  code
0     sally    0  s_A
1     sally    0    s_B
2     sally    0   s_C
3     sally    0   s_D
4     sally    0    s_E
5     sally    0   s_A
6     sally    0    s_A
7     sally    0   s_B
8     sally    0   s_C
9     sally    0   s_A

我使用 count() 转换为这个系列.

I transformed to this Series using count().

df.groupby(['id', 'code', 'month']).month.count()

id      code   month  count
sally  s_A      0    12
                1    10
                2     3
                7    15

但是，我想包括零出现，就像这样.

But, I want to include zero occurrence, like this.

id      code   month  count
sally  s_A      0    12
                1    10
                2     3
                3    0
                4    0
                5    0
                6    0
                7    15
                8    0
                9    0
                10   0
                11   0

推荐答案

您可以使用 reindex 使用新的 Multindex 创建 from_product:

You can use reindex with new Multindex created from_product:

注意:
pandas 中的大小和计数有什么区别?

df = pd.DataFrame({
'month': [0, 0, 0, 0, 1, 1, 1, 2, 2, 7], 
'code': ['s_A', 's_A', 's_A', 's_A', 's_A', 's_A', 's_A', 's_B', 's_B', 's_B'], 
'id': ['sally1','sally1','sally1','sally','sally','sally','sally','sally','sally','sally']})

print (df)
  code      id  month
0  s_A  sally1      0
1  s_A  sally1      0
2  s_A  sally1      0
3  s_A   sally      0
4  s_A   sally      1
5  s_A   sally      1
6  s_A   sally      1
7  s_B   sally      2
8  s_B   sally      2
9  s_B   sally      7

<小时>

df = df.groupby(['id', 'code', 'month']).size()
n = ['id','code','month']
mux = pd.MultiIndex.from_product([df.index.levels[0],df.index.levels[1], range(13)], names=n)
df = df.reindex(mux, fill_value=0)
print (df)
id      code  month
sally   s_A   0        1
              1        3
              2        0
              3        0
              4        0
              5        0
              6        0
              7        0
              8        0
              9        0
              10       0
              11       0
              12       0
        s_B   0        0
              1        0
              2        2
              3        0
...
...

旧解决方案:

reindex 使用 unstack 和 stack，但是需要一些数据清理:

reindex with unstack and stack, but then need some data cleaning:

df = df.groupby(['id', 'code', 'month']).size() \
       .to_frame('count') \
       .unstack([0,1], fill_value=0) \
       .reindex(range(13), fill_value=0) \
       .stack([1,2], dropna=False) \
       .fillna(0) \
       .astype(int) \
       .swaplevel(0,2) \
       .sort_index()
print (df)
                   count
code id     month       
s_A  sally  0          1
            1          3
            2          0
            3          0
            4          0
            5          0
            6          0
            7          0
            8          0
            9          0
            10         0
            11         0
            12         0
     sally1 0          3
            1          0
            2          0
            3          0
            4          0

这篇关于如何计算包括零出现在内的分类值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！