Keep Consecutive Days in Data Frame

user_13688668 0
Viewed 68

I'm looking to only keep entries in a data frame that have consecutive days based on an individual.

Say my data frame is defined as-

dic = {'name':['John','John','John','Susan','Susan','Susan','Susan','Mike',
               'Mike','Mike'],
       'worked':['2020-03-12','2020-03-13','2020-03-15','2020-03-16',
                 '2020-03-18','2020-03-19','2020-03-20','2020-03-31',
                 '2020-03-29','2020-04-01'],
       'paid':[100,200,300,400,500,100,200,300,400,500]}
df = pd.DataFrame(dic)
df['worked'] = pd.to_datetime(df['worked'])
print(df)

with output-

    name     worked  paid
0   John 2020-03-12   100
1   John 2020-03-13   200
2   John 2020-03-15   300
3  Susan 2020-03-16   400
4  Susan 2020-03-18   500
5  Susan 2020-03-19   100
6  Susan 2020-03-20   200
7   Mike 2020-03-31   300
8   Mike 2020-03-29   400
9   Mike 2020-04-01   500

my desired output would look like this-

    name     worked  paid
0   John 2020-03-12   100
1   John 2020-03-13   200
2  Susan 2020-03-18   500
3  Susan 2020-03-19   100
4  Susan 2020-03-20   200
5   Mike 2020-03-31   300
6   Mike 2020-04-01   500
pandas filtering dataframe date python
3 Answers

My 2 cents with diff;

df = df.sort_values(['name','worked'])
c = df.groupby("name")['worked'].diff().dt.days.eq(1)
df[c|c.shift(-1)].sort_index()

    name     worked  paid
0   John 2020-03-12   100
1   John 2020-03-13   200
4  Susan 2020-03-18   500
5  Susan 2020-03-19   100
6  Susan 2020-03-20   200
7   Mike 2020-03-31   300
9   Mike 2020-04-01   500
user_98406370

My approach:

df['worked'] = pd.to_datetime(df['worked'])
df = df.sort_values(['name','worked'])
period = pd.to_timedelta('1 day')

groups = df.groupby('name')
s1 = df['worked'] - groups['worked'].shift()
s2 = groups['worked'].shift(-1) -df['worked']

df[(s1==period)|(s2==period)].sort_index()

Output:

    name     worked  paid
0   John 2020-03-12   100
1   John 2020-03-13   200
4  Susan 2020-03-18   500
5  Susan 2020-03-19   100
6  Susan 2020-03-20   200
7   Mike 2020-03-31   300
9   Mike 2020-04-01   500
user_42384080

Here's a solution, broken into several steps for clarity.

df = df.sort_values("worked")
df["prev_day"] = df.groupby("name")["worked"].shift()
df["days_diff"] = (df.worked - df.prev_day).dt.days
df["next_day_diff"] = df.days_diff.shift(-1)
df = df[(df.days_diff ==1) | (df.next_day_diff == 1)]

The output, which includes the intermediate steps (again, for clarity) is:

    name     worked  paid   prev_day  days_diff  next_day_diff
0   John 2020-03-12   100        NaT        NaN            1.0
1   John 2020-03-13   200 2020-03-12        1.0            NaN
4  Susan 2020-03-18   500        NaT        NaN            1.0
5  Susan 2020-03-19   100 2020-03-18        1.0            1.0
6  Susan 2020-03-20   200 2020-03-19        1.0            NaN
7   Mike 2020-03-31   300        NaT        NaN            1.0
9   Mike 2020-04-01   500 2020-03-31        1.0            NaN
user_11055600
Related