3
$\begingroup$

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

I transformed to this Series using count().

df.groupby(['id', 'code', 'month']).m.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
$\endgroup$
  • $\begingroup$ Without transforming it into a Series, just try this: df['month'].value_counts(), where df is your pandas dataframe $\endgroup$ – enterML Apr 6 '17 at 9:41
  • $\begingroup$ @Nain thanks, but I need to group by 'sally' and there are missing months like the example above. $\endgroup$ – planaria Apr 6 '17 at 9:44
  • $\begingroup$ @Kyle. Could you explain more? Months are not complete. How can I insert month with zero count? $\endgroup$ – planaria Apr 6 '17 at 22:21
  • $\begingroup$ df.month.value_counts(dropna=False) $\endgroup$ – Andrew L May 15 '17 at 9:03
4
$\begingroup$

Based on the short example DataFrame you provided, this block of code will include all of the months. It is based on using the Series.reindex method and creating a new MultiIndex with the additional values for the months:

import pandas as pd
# Load example data into DataFrame
df = pd.read_table("categorical_data.txt", delim_whitespace=True)

# Transform to a count
count = df.groupby(['id', 'code', 'month']).month.count()

# Re-create a new array of levels, now including all 12 months
levels = [count.index.levels[0].values, count.index.levels[1].values, range(12)]
new_index = pd.MultiIndex.from_product(levels, names=count.index.names)

# Reindex the count and fill empty values with zero (NaN by default)
count = count.reindex(new_index, fill_value=0)
print(count)

Printing the result, I get something like this (only showing the first entry for sally/s_A):

id     code  month
sally  s_A   0        4
             1        0
             2        0
             3        0
             4        0
             5        0
             6        0
             7        0
             8        0
             9        0
             10       0
             11       0
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.