Creating custom column for week number of a column value-CodePudding

If I have a df such as

  Date     | User 
2019-08-05   Bob
2019-07-01   Chris
2019-08-10   Bob
2019-08-20   Chris
2019-09-24   Bob

Expected output

  Date     | User  | Week_number
2019-08-05   Bob         1
2019-07-01   Chris       1
2019-08-13   Bob         2
2019-08-20   Chris       9
2019-09-24   Bob         8

How can I create a new column that would give me the week # of that date range? (the week # for user would be the # in where the first date would be under week 1

in the past I have used methods such as df['Date'].dt.day and then used that to cut bins but this is different where I am assigning week number based on that users custom range

Thanks for taking time to read my post

CodePudding user response：

If you mean how many weeks have passed since the first date of the user:

df.Date = pd.to_datetime(df.Date)
df['WeekNumber'] = df.Date.groupby(df.User).diff().dt.days.fillna(0).astype(int) // 7   1

df

        Date   User  WeekNumber
0 2019-08-05    Bob           1
1 2019-07-01  Chris           1
2 2019-08-13    Bob           2
3 2019-08-20  Chris           8
4 2019-09-24    Bob           7

CodePudding user response：

Difference between weeks even if the first date is not on Monday:

df['Date'] = pd.to_datetime(df['Date'])
df['WeekNumber'] = df.groupby('User')['Date'] \
                     .apply(lambda x: 1   x.dt.isocalendar().week
                                      - min(x.dt.isocalendar().week))

Output:

>>> df
        Date   User  WeekNumber
0 2019-08-05    Bob           1
1 2019-07-01  Chris           1
2 2019-08-13    Bob           2
3 2019-08-20  Chris           8
4 2019-09-24    Bob           8