How to change the date when using pd.to

I am having a bit of a struggle to use a dataframe that I have created. The dataframe is to keep track each day of the wake up time, 1st meal, last meal (2ndMeal here) and time when the person goes to sleep (Sleep time).

Here attached you can see what the initial dataframe looks like:

    Unnamed: 1  Unnamed: 2  Unnamed: 3  Unnamed: 4  Unnamed: 5
0   2022-09-06  08:03:00    12:09:00    20:19:00    22:35:00
1   2022-09-07  07:30:00    12:20:00    20:35:00    00:10:00
2   2022-09-08  08:30:00    12:15:00    21:30:00    00:33:00
3   2022-09-09  08:56:00    11:00:00    23:00:00    02:00:00

I convert the columns into datetime with

test['Date'] = pd.to_datetime(df['Unnamed: 1'])

for the first column and

test['WakeUp'] = pd.to_datetime(df['Unnamed: 2'], format='%H:%M:%S')

for the rest (cannot use the same code line as the first column or otherwise I get an error) and get this:

    Date    WakeUp  1stMeal 2ndMeal Sleep 
0   2022-09-06  1900-01-01 08:03:00 1900-01-01 12:09:00 1900-01-01 20:19:00 1900-01-01 22:35:00
1   2022-09-07  1900-01-01 07:30:00 1900-01-01 12:20:00 1900-01-01 20:35:00 1900-01-01 00:10:00
2   2022-09-08  1900-01-01 08:30:00 1900-01-01 12:15:00 1900-01-01 21:30:00 1900-01-01 00:33:00
3   2022-09-09  1900-01-01 08:56:00 1900-01-01 11:00:00 1900-01-01 23:00:00 1900-01-01 02:00:00

My problem is that I would like the dates of the WakeUp, 1stMeal, 2ndMeal and Sleep to be the same of the Date column but I am not managing to do so...

Additionally, if the time is beyond 00:00, I'd like the date to change so that it is show the day after and not the same day (i.e. I wake up at 7:00 of day1 but go to sleep at 2:00 of day2)

I have tried to extract the times and used them independently but with no success (I am not sure how to manipulate them).

I have tried to use:

pd.to_datetime(test['Date'].dt.date)   pd.to_datetime(test['WakeUp'].dt.time)

but with no success.

I was expecting for the sleep time to not have those large gaps due to the different dates...

All the help that I found online is people using dataframes with date and/or times but as x axis and never as y axis which is slowly making think that there is no solution to this...

CodePudding user response：

The second part of the question is a bit trickier, which requires contextual knowledge to solve. There is no guarantee that the go-to-sleep hours are recognizable as the next day. What makes 02:00 the next day, but 22:00 not? Unless you KNOW that nobody ever stayed awake that long, there is no way to tell. A pragmatic approach would be that you check for a reasonable time (say 10:00) and if the go-to-sleep time is before that time, you add a day to the date by using https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.tseries.offsets.DateOffset.html

timestamp = timestamp   DateOffset(day=1)

CodePudding user response：

You can concatenate the first column to the other before using to_datetime, then convert the first column separately:

time_cols = df.columns[1:]
df[time_cols] = (df[time_cols].radd(df['Unnamed: 1'] ' ', axis=0)
                 .apply(pd.to_datetime)
                 )
df['Unnamed: 1'] = pd.to_datetime(df['Unnamed: 1'])

output:

  Unnamed: 1           Unnamed: 2           Unnamed: 3           Unnamed: 4           Unnamed: 5
0 2022-09-06  2022-09-06 08:03:00  2022-09-06 12:09:00  2022-09-06 20:19:00  2022-09-06 22:35:00
1 2022-09-07  2022-09-07 07:30:00  2022-09-07 12:20:00  2022-09-07 20:35:00  2022-09-07 00:10:00
2 2022-09-08  2022-09-08 08:30:00  2022-09-08 12:15:00  2022-09-08 21:30:00  2022-09-08 00:33:00
3 2022-09-09  2022-09-09 08:56:00  2022-09-09 11:00:00  2022-09-09 23:00:00  2022-09-09 02:00:00

correcting Sleep

df.columns = ['Date', 'WakeUp', '1stMeal', '2ndMeal', 'Sleep']

df['Sleep']  = df['Sleep'].lt(df['WakeUp']).astype(int).mul(pd.DateOffset(days=1))

output:

        Date               WakeUp              1stMeal              2ndMeal               Sleep
0 2022-09-06  2022-09-06 08:03:00  2022-09-06 12:09:00  2022-09-06 20:19:00 2022-09-06 22:35:00
1 2022-09-07  2022-09-07 07:30:00  2022-09-07 12:20:00  2022-09-07 20:35:00 2022-09-08 00:10:00
2 2022-09-08  2022-09-08 08:30:00  2022-09-08 12:15:00  2022-09-08 21:30:00 2022-09-09 00:33:00
3 2022-09-09  2022-09-09 08:56:00  2022-09-09 11:00:00  2022-09-09 23:00:00 2022-09-10 02:00:00