How to count consecutive weeks in a list?

Serena 0
Viewed 30

I have a dataframe with a list of string dates, and the length of each list:

data = [['a', ['8/5/2021'], 1], ['b', ['12/21/2021', '12/28/2021'], 2], ['c', ['8/10/2021',  '8/27/2021', '9/3/2021', '9/10/2021', '9/17/2021'], 5], ['d', ['8/23/2021', '9/23/2021', '10/23/2021'], 3], ['e', ['8/10/2021',  '9/27/2021', '10/3/2021', '11/10/2021', '11/17/2021', '12/27/2021'], 6]]
df = pd.DataFrame(data, columns=['Name', 'Dates', 'Total Weeks'])

I want to count the number of consecutive weeks in each list, and it'll be "one period". No matter how long it'll be, it'll be one period. One week on its own would also be considered "one period". I want to append this to a new column of the dataframe.

Finally, I'll like to count only the "periods" that are at least 2 weeks long or more. This would be appended to the dataframe as a new column. The end result should look like this:

enter image description here

I was thinking of using datetime.timedelta and groupby but can't seem to figure it out. Thank you in advance!

group-by list dataframe pandas python-3.x
1 Answers

IIUC: I made a second dataframe (df_long) to do the calculations using df.explode().

By using df.groupby('Name') I created new columns. non_consec_weeks is a boolean column which checks if the row is a new period (week). If false, it is part of week of another row within the group. consec_weeks is the negation of the column non_consec_weeks. And finally week_periods is similar to consec_weeks but sets only a single True value for every group of consecutive weeks.

df_long = df.explode('Dates').reset_index(drop=True)
df_long['Dates'] = pd.to_datetime(df_long.Dates) #, format="%d/%m/%Y")

df_long['non_consec_weeks'] = ~df_long.groupby('Name').Dates.diff().dt.days.le(7)
df_long['consec_weeks'] = df_long.groupby('Name').Dates.diff().dt.days.le(7)
df_long['week_periods'] = df_long['consec_weeks'] & ~(df_long['consec_weeks'] & (df_long.groupby('Name').consec_weeks.shift() == df_long.consec_weeks))

df['Periods'] = df_long.groupby('Name').non_consec_weeks.sum().values
df['>=2'] = df_long.groupby('Name').week_periods.sum().values

Output:

  Name                                              Dates  ...  Periods  >=2
0    a                                         [8/5/2021]  ...        1    0
1    b                           [12/21/2021, 12/28/2021]  ...        1    1
2    c  [8/10/2021, 8/27/2021, 9/3/2021, 9/10/2021, 9/...  ...        2    1
3    d                 [8/23/2021, 9/23/2021, 10/23/2021]  ...        3    0
4    e  [8/10/2021, 9/27/2021, 10/3/2021, 11/10/2021, ...  ...        4    2
T C Molenaar0
Related