count consecutive days python dataframe

Question

I'm trying to group IDs by consecutive dates.

ID     Date   
abc    2017-01-07  
abc    2017-01-08  
abc    2017-01-09  
abc    2017-12-09  
xyz    2017-01-05  
xyz    2017-01-06 
xyz    2017-04-15  
xyz    2017-04-16

Need to return:

ID     Count
abc    3
abc    1
xyz    2
xyz    2

I've tried:

d = {'ID': ['abc', 'abc', 'abc', 'abc', 'xyz', 'xyz', 'xyz', 'xyz'], 'Date': ['2017-01-07','2017-01-08', '2017-01-09', '2017-12-09', '2017-01-05', '2017-01-06', '2017-04-15', '2017-04-16']}
df = pd.DataFrame(data=d)
df['Date'] =  pd.to_datetime(df['Date'])

today = pd.to_datetime('2018-10-23')   
x = df.sort_values('Date', ascending=0)
g = x.groupby(['ID'])
x[(today - x['Date']).dt.days == g.cumcount()].groupby(['ID']).size()

Is there a simple way to do this in order to obtain the counts of all date ranges by ID?

ALollz · Accepted Answer · 2020-07-17T14:26:37.483

13

Create a Series which checks for the difference between Dates within each ID. Check if that's not 1 day, and then groupby the ID and the cumulative sum of that Series.

import pandas as pd

s = df.groupby('ID').Date.diff().dt.days.ne(1).cumsum()
df.groupby(['ID', s]).size().reset_index(level=1, drop=True)

Output:

ID
abc    3
abc    1
xyz    2
xyz    2
dtype: int64

edited Jul 17 '20 at 14:26

answered Oct 23 '18 at 21:13

ALollz

57,915
7
66
89

1

may change s to `df.groupby('ID').Date.diff().dt.days.ne(1).cumsum() ` – BENY Oct 23 '18 at 21:19
Hi, a question, why to remove the fillna()? Thanks – Catarina Nogueira Jul 17 '20 at 13:20
1

@CatarinaNogueira I guess it's really not needed. the `diff` makes the first group `NaN` so I filled it, but without filling the grouping Series just starts counting at 1 instead of 0, which makes no difference. – ALollz Jul 17 '20 at 14:26
@ALollz how do I get addition of all ids? instead of getting xyz twice. Get it as one xyz=4 – kahuria00 Aug 07 '21 at 00:38
This `df.groupby('ID').Date.diff()` returns `'DataFrameGroupBy' object has no attribute 'Date'` – EKivutha Dec 11 '21 at 10:44
1

@Geraldkibz should be the name of your date column in df – kahuria00 Dec 11 '21 at 11:19

count consecutive days python dataframe

1 Answers1

Output:

Linked