How to group by consecutive dates when you have several groups?

user_11313803 0
Viewed 64

I thought this was going to be super straight forward but I'm stuck. I've tried to group by in several different ways as well as creating a .diff() column and looping through unique names, but I still can't get it to work.

The most I've gotten is to have the first "date sandwich" but then the rest gets ignored.

Basically I have a table that looks like this:

Name
Bob Smith   6/1/2020
Bob Smith   6/2/2020
Bob Smith   6/3/2020
Bob Smith   6/10/2020
Bob Smith   6/11/2020
Bob Smith   6/12/2020
Bob Smith   6/13/2020
Joe Doe     5/20/2020
Joe Doe     5/21/2020
Joe Doe     5/22/2020
Joe Doe     5/23/2020
Joe Doe     6/4/2020
Joe Doe     6/5/2020
Joe Doe     6/6/2020
Mark Scott  6/18/2020
Mark Scott  6/19/2020

And I wanted to extract the start and end dates for each consecutive date streak:

Name:           From:        To:
Bob Smith       6/1/2020     6/3/2020
Bob Smith       6/10/2020    6/13/2020
Joe Doe         5/20/2020    5/23/2020
Joe Doe         6/4/2020     6/6/2020
Mark Scott      6/18/2020    6/19/2020

Thanks in advance for any help!

pandas group-by datetime python
3 Answers

You need create a sub-group key with diff and cumsum , then do agg with first + last

df=pd.to_datetime(df).reset_index()
m=df.date.diff().dt.days.ne(1).cumsum()
s=df.groupby([df.Name,m]).date.agg(['first','last']).reset_index(level=1,drop=True)
s

               first       last
Name                           
BobSmith  2020-06-01 2020-06-03
BobSmith  2020-06-10 2020-06-13
JoeDoe    2020-05-20 2020-05-23
JoeDoe    2020-06-04 2020-06-06
MarkScott 2020-06-18 2020-06-19
user_79645270

I'd use groupby,diff, and cumsum with pd.NamedAgg:

df['Date'] = pd.to_datetime(df['Date'])
grp = df['Date'].diff().ne(pd.Timedelta(days=1)).cumsum()
df.groupby(['Name', grp])[['Name', 'Date']].agg(Name=('Name','first'),
                                      From=('Date','min'),
                                      To=('Date','max')).reset_index(drop=True)

Output:

         Name       From         To
0   Bob Smith 2020-06-01 2020-06-03
1   Bob Smith 2020-06-10 2020-06-13
2     Joe Doe 2020-05-20 2020-05-23
3     Joe Doe 2020-06-04 2020-06-06
4  Mark Scott 2020-06-18 2020-06-19
user_63615310

Another way without using groupby. Using mask to get first and last date of Name and dates and assign through ndarray

df.dates = pd.to_datetime(df.dates)
m = df.dates.diff().ne(pd.Timedelta(days=1))
df_final = df[m].assign(To_dates=df.loc[~m.cumsum().duplicated(keep='last'), 'dates']
                                   .values)

Out[111]:
          Name      dates   To_dates
0    Bob Smith 2020-06-01 2020-06-03
3    Bob Smith 2020-06-10 2020-06-13
7      Joe Doe 2020-05-20 2020-05-23
11     Joe Doe 2020-06-04 2020-06-06
14  Mark Scott 2020-06-18 2020-06-19
user_101892140
Related