How to convert pandas series to integer for use in datetime.fromisocalendar

Question

I am trying to transform a pandas series which has dates in it. I'd like to take the date that is in there and return the following Monday.

Here is what I have tried:

db['date'] = datetime.date.fromisocalendar(db['date'].dt.year.astype(np.int64),(db['date'].dt.week+1).astype(np.int64),1)

But I get the following error:

TypeError: 'Series' object cannot be interpreted as an integer

Is there a better way to do this?

Answer 1

I think this is pretty good:

db['date'] = db['date'] + pd.to_timedelta(7-db['date'].dt.dayofweek,'d')

Answer 2

I see two options:

Using manual computation of a timedelta:

df = pd.DataFrame({'date': pd.date_range('2024-01-01', '2024-01-10')})
df['next_Monday'] = df['date'].add(pd.to_timedelta(7-df['date'].dt.dayofweek, unit='D'))

Or with an Week offset:

df = pd.DataFrame({'date': pd.date_range('2024-01-01', '2024-01-10')})
df['next_Monday'] = df['date'].add(pd.offsets.Week(n=1, weekday=0))

Output:

        date next_Monday
0 2024-01-01  2024-01-08
1 2024-01-02  2024-01-08
2 2024-01-03  2024-01-08
3 2024-01-04  2024-01-08
4 2024-01-05  2024-01-08
5 2024-01-06  2024-01-08
6 2024-01-07  2024-01-08
7 2024-01-08  2024-01-15
8 2024-01-09  2024-01-15
9 2024-01-10  2024-01-15

If you only want to consider a next day when the day is over (i.e. if we are a Monday, keep the current date):

df['next_Monday'] = df['date'].add(pd.offsets.Week(n=0, weekday=0))

        date next_Monday
0 2024-01-01  2024-01-01
1 2024-01-02  2024-01-08
2 2024-01-03  2024-01-08
3 2024-01-04  2024-01-08
4 2024-01-05  2024-01-08
5 2024-01-06  2024-01-08
6 2024-01-07  2024-01-08
7 2024-01-08  2024-01-08
8 2024-01-09  2024-01-15
9 2024-01-10  2024-01-15