Pandas pd.Grouper and Sequential Date Difference per group

Question

I have an example dataframe like this:

import pandas as pd
df = pd.DataFrame({"id": [0]*5 + [1]*5,
             "time": ['2015-01-01', '2015-01-03', '2015-01-04', '2015-01-08', '2015-01-10', '2015-02-02', '2015-02-04', '2015-02-06', '2015-02-11', '2015-02-13'],
             'hit': [0,3,8,2,5, 6,12,0,7,3]})
df.time = df.time.astype('datetime64[ns]')
df = df[['id', 'time', 'hit']]
df

Will output:

    id  time        hit
0   0   2015-01-01  0
1   0   2015-01-03  3
2   0   2015-01-04  8
3   0   2015-01-08  2
4   0   2015-01-10  5
5   1   2015-02-02  6
6   1   2015-02-04  12
7   1   2015-02-06  0
8   1   2015-02-11  7
9   1   2015-02-13  3

Then I performed a groupby with time (per day):

df.groupby(['id', pd.Grouper(key='time', freq='1D')]).hit.sum().to_frame()

Resulted in:

               hit
id  time    
0   2015-01-01  0
    2015-01-03  3
    2015-01-04  8
    2015-01-08  2
    2015-01-10  5
1   2015-02-02  6
    2015-02-04  12
    2015-02-06  0
    2015-02-11  7
    2015-02-13  3

However, I want to retain the daily hit even though the value = 0, and calculate the daily hit since the first day, per each id. My desired output:

               hit  day_since
id  time        
0   2015-01-01  0   1
    2015-01-02  0   2
    2015-01-03  3   3
    2015-01-04  8   4
    2015-01-05  0   5
    2015-01-06  0   6
    2015-01-07  0   7
1   2015-02-02  6   1
    2015-02-03  0   2
    2015-02-04  12  3
    2015-02-05  0   4
    2015-02-06  0   5
    2015-02-07  0   6
    2015-02-08  0   7

A cumcount does not work because it is numbering each item by group. But in my case, I wish to calculate the sequential date difference per group.

Does anyone have any ideas?

@jezrael I disagree it is duplicate. A `cumcount` does not count the sequential difference on time. It is just numbering each item, but not the sequential date difference in my case. — suvtfopw, Jul 04 '18 at 18:06
I've reopened this because it also needs reindexing/resampling. — ayhan, Jul 04 '18 at 18:21

gyoza · Accepted Answer · 2018-07-05T03:57:54.353

After your groupby,

df = df.reset_index(level=0)

# container for resulting dataframe
dfs = pd.DataFrame()

for i in df.id.unique():
    # prepare a series and upsample it within the same id
    chunk = pd.Series(df.loc[df.id == i, 'hit'])
    chunk = chunk.resample('1D').asfreq()

    # create dataframe and construct some additional columns
    chunk = pd.DataFrame(chunk, columns=['hit']).reset_index().fillna(0)
    chunk['hit'] = chunk['hit'].astype(int)
    chunk['id'] = i
    chunk['day_since'] = chunk.groupby('id').cumcount() + 1

    # accumulate id-wise dataframes 1 by 1 vertically
    dfs = pd.concat([dfs, chunk], axis=0, ignore_index=True)

dfs = dfs.set_index(['id', 'time'])

And you will get:

               hit  day_since
id time
0  2015-01-01    0          1
   2015-01-02    0          2
   2015-01-03    3          3
   2015-01-04    8          4
   2015-01-05    0          5
   2015-01-06    0          6
   2015-01-07    0          7
   2015-01-08    2          8
   2015-01-09    0          9
   2015-01-10    5         10
1  2015-02-02    6          1
   2015-02-03    0          2
   2015-02-04   12          3
   2015-02-05    0          4
   2015-02-06    0          5
   2015-02-07    0          6
   2015-02-08    0          7
   2015-02-09    0          8
   2015-02-10    0          9
   2015-02-11    7         10
   2015-02-12    0         11
   2015-02-13    3         12

Thanks. A quick question on possible simplifications: `chunk = pd.Series(df.loc[df.id == i, 'hit']) #, index=df.loc[df.id == i].index)` and `chunk = pd.DataFrame(chunk).reset_index().fillna(0)` I can't see the difference even if I omit the index values when creating Series or DataFrame? — suvtfopw, Jul 05 '18 at 03:42
You are right. Thanks for pointing it out. I edited my solution. — gyoza, Jul 05 '18 at 03:58

Pandas pd.Grouper and Sequential Date Difference per group

1 Answers1