Pandas: group one column by date and count cumulated number of a specific value in another column

user_10852466 0
Viewed 293

I'm trying to group a Pandas dataframe by date based on one datetime column and, based on that, count the number of specific occurrences in another column based on a specific value. Let's say I have this dataframe:

df = pd.DataFrame({
    "customer": [
         "A", "A", "A", "A", "A", "B", "C", "C"        
    ],
    "datetime": pd.to_datetime([
        "2020-01-01 00:00:00", "2020-01-02 00:00:00", "2020-01-02 01:00:00", "2020-01-03 00:00:00", "2020-01-04 00:00:00", "2020-01-03 00:00:00", "2020-01-03 00:00:00", "2020-01-04 00:00:00"         
    ]),
    "enabled": [
      True, True, False, True, True, True, False, True            
    ]    
})

The dataframe looks like this:

customer    datetime                enabled
A           2020-01-01 00:00:00     True
A           2020-01-02 00:00:00     True
A           2020-01-02 01:00:00     False
A           2020-01-03 00:00:00     True
A           2020-01-04 00:00:00     True
B           2020-01-03 00:00:00     True
C           2020-01-03 00:00:00     False
C           2020-01-04 00:00:00     True

I would like to count, at the end of each day, the number of enabled customers. If a customer is enabled, it remains enabled for the following days, unless there's an enabled==False row on a later day. The expected output would be:

day           count_enabled_customers
2020-01-01    1      # A
2020-01-02    0      # A has been disabled
2020-01-03    2      # A, B
2020-01-04    3      # A, B, C

Does someone have an idea of how to proceed with this? Thanks a lot in advance!

pandas datetime python
1 Answers

Starting with your dataframe:

import pandas as pd

df = pd.DataFrame({
    "customer": [
         "A", "A", "A", "A", "A", "B", "C", "C"        
    ],
    "datetime": pd.to_datetime([
        "2020-01-01 00:00:00", "2020-01-02 00:00:00", "2020-01-02 01:00:00", "2020-01-03 00:00:00", "2020-01-04 00:00:00", "2020-01-03 00:00:00", "2020-01-03 00:00:00", "2020-01-04 00:00:00"         
    ]),
    "enabled": [
      True, True, False, True, True, True, False, True            
    ]    
})

print(df)

Out:
  customer            datetime  enabled
0        A 2020-01-01 00:00:00     True
1        A 2020-01-02 00:00:00     True
2        A 2020-01-02 01:00:00    False
3        A 2020-01-03 00:00:00     True
4        A 2020-01-04 00:00:00     True
5        B 2020-01-03 00:00:00     True
6        C 2020-01-03 00:00:00    False
7        C 2020-01-04 00:00:00     True

Use a pivot to get the customers as columns and the dates as an index

a = df.pivot(index='datetime', columns='customer', values='enabled')
print(a)

Out:
customer                 A     B      C
datetime                               
2020-01-01 00:00:00   True   NaN    NaN
2020-01-02 00:00:00   True   NaN    NaN
2020-01-02 01:00:00  False   NaN    NaN
2020-01-03 00:00:00   True  True  False
2020-01-04 00:00:00   True   NaN   True

Create an index of the dates you are interested in

dates = pd.date_range(df.datetime.min().date(), df.datetime.max().date() + pd.offsets.Day(1), freq='D') - pd.offsets.Second(1)
print(dates)

Out:
DatetimeIndex(['2019-12-31 23:59:59', '2020-01-01 23:59:59',
               '2020-01-02 23:59:59', '2020-01-03 23:59:59',
               '2020-01-04 23:59:59'],
              dtype='datetime64[ns]', freq='D')

Add the dates you are interested in to the index and sort it so we can ffill in the next step

a = a.reindex(a.index.union(dates)).sort_index()
print(a)

Out:
customer                 A     B      C
2019-12-31 23:59:59    NaN   NaN    NaN
2020-01-01 00:00:00   True   NaN    NaN
2020-01-01 23:59:59    NaN   NaN    NaN
2020-01-02 00:00:00   True   NaN    NaN
2020-01-02 01:00:00  False   NaN    NaN
2020-01-02 23:59:59    NaN   NaN    NaN
2020-01-03 00:00:00   True  True  False
2020-01-03 23:59:59    NaN   NaN    NaN
2020-01-04 00:00:00   True   NaN   True
2020-01-04 23:59:59    NaN   NaN    NaN

Forward fill the last value of the enabled state into future dates

a = a.ffill()
print(a)

Out: 
customer                 A     B      C
2019-12-31 23:59:59    NaN   NaN    NaN
2020-01-01 00:00:00   True   NaN    NaN
2020-01-01 23:59:59   True   NaN    NaN
2020-01-02 00:00:00   True   NaN    NaN
2020-01-02 01:00:00  False   NaN    NaN
2020-01-02 23:59:59  False   NaN    NaN
2020-01-03 00:00:00   True  True  False
2020-01-03 23:59:59   True  True  False
2020-01-04 00:00:00   True  True   True
2020-01-04 23:59:59   True  True   True

Sum across columns for the timestamps which represent the ends of each day

a.loc[dates].sum(axis=1)
print(a)

Out:
2019-12-31 23:59:59    0.0
2020-01-01 23:59:59    1.0
2020-01-02 23:59:59    0.0
2020-01-03 23:59:59    2.0
2020-01-04 23:59:59    3.0
Freq: D, dtype: float64
user_118694370
Related