How to keep Zero counts for pandas groupby count for 2 columns dataframe?

Question

If the data frame has 3 columns, I found this StackOverflow answer that gives zero counts: Pandas groupby for zero values

But, HOW to do this for the data frame having only two columns:

Question
NOTE: Answer preferable in Chain operations:

import numpy as np
import pandas as pd

df = pd.DataFrame({'date': pd.date_range('2018-01-01', periods=6),
                   'a': range(6),
                   })

df.iloc[2,0] = df.iloc[1,0]
print(df)
        date  a
0 2018-01-01  0
1 2018-01-02  1
2 2018-01-02  2
3 2018-01-04  3
4 2018-01-05  4
5 2018-01-06  5

To geth the counts of a I do this:

df1 = (df.query("a > 0")
    .groupby(['date'])[['a']]
    .count()
    .add_suffix('_count')
    .reset_index() 
     )

print(df1)
        date  a_count
0 2018-01-02        2
1 2018-01-04        1
2 2018-01-05        1
3 2018-01-06        1

Required Answer from Chain operation

        date  a_count
0 2018-01-01        0  # also include this row
0 2018-01-02        2
1 2018-01-04        1
2 2018-01-05        1
3 2018-01-06        1

My attempt:

df1 = (df.query("a > 0")
    .groupby(['date'])[['a']]
    .count()
    .add_suffix('_count')
    .unstack(fill_value=0)
    .to_frame()
    .stack()
    .reset_index() 
     )

print(df1)
   level_0       date  level_2  0
0  a_count 2018-01-02        0  2
1  a_count 2018-01-04        0  1
2  a_count 2018-01-05        0  1
3  a_count 2018-01-06        0  1

This does not work.

How to fix this ?

Related links:
Pandas groupby for zero values

ALollz · Accepted Answer · 2019-04-27T21:39:32.563

Assign a column of the thing you want to count prior to the groupby:

df.assign(to_sum = df.a.gt(0).astype(int)).groupby('date').to_sum.sum()
#date
#2018-01-01    0
#2018-01-02    2
#2018-01-04    1
#2018-01-05    1
#2018-01-06    1
#Name: to_sum, dtype: int32

Can tac on .rename('a_count').reset_index() to get your exact output.

Alternatively if the use case is a bit more complicated and that isn't possible, you can always reindex + fillna after the groupby

(df[df.a > 0].groupby('date').a.count()
     .reindex(df.date.unique()).fillna(0).astype(int)
     .rename('a_count').reset_index())

#        date  a_count
#0 2018-01-01        0
#1 2018-01-02        2
#2 2018-01-04        1
#3 2018-01-05        1
#4 2018-01-06        1

score 2 · Answer 2 · answered Apr 27 '19 at 23:23

2

As simple as you see

(df['a'].gt(0)).groupby(df['date']).sum().to_frame('count_a').reset_index()
        date  count_a
0 2018-01-01      0.0
1 2018-01-02      2.0
2 2018-01-04      1.0
3 2018-01-05      1.0
4 2018-01-06      1.0

answered Apr 27 '19 at 23:23

BENY

317,841
20
164
234

score -1 · Answer 3 · answered Apr 27 '19 at 21:50

Just making @ALollz's answer more beautiful for aesthetics:

df1 = (df.assign(
           to_sum = lambda x: (x['a']> 0).astype(int)
                 )
 .groupby('date')['to_sum']
 .sum()
 .rename('a_count')
 .to_frame()
 .reset_index() 

)

print(df1)
print(df1)
        date  a_count
0 2018-01-01        0
1 2018-01-02        2
2 2018-01-04        1
3 2018-01-05        1
4 2018-01-06        1

How to keep Zero counts for pandas groupby count for 2 columns dataframe?

3 Answers3

Linked