How to change the date column based on time column in pandas

Viewed 212

I have a dataframe which looks like this:

ID      Date     Type     Time
1   02.12.2021   Car      12:23
1   02.12.2021   Car      12:25
1   02.12.2021   Car      12:27
2   02.12.2021   Train    23:40
2   02.12.2021   Train    23:56
2   02.12.2021   Train    00:20
2   02.12.2021   Train    00:40
4   02.12.2021   Car      04:20
4   02.12.2021   Car      04:25
4   02.12.2021   Car      04:30

I want change the date for those rows which have the same "ID" and "Type". So if the time of next row passes 23:59 and has same ID and Type like the row before, the date should change for the next rows. It should be something like this:

ID      Date     Type     Time
1   02.12.2021   Car      12:23
1   02.12.2021   Car      12:25
1   02.12.2021   Car      12:27
2   02.12.2021   Train    23:40
2   02.12.2021   Train    23:56
2   03.12.2021   Train    00:20
2   03.12.2021   Train    00:40
4   03.12.2021   Car      04:20
4   03.12.2021   Car      04:25
4   03.12.2021   Car      04:30

Any help will be appreciated. Many thanks

2 Answers

Let's try to create a boolean series where the new date starts and that number of days to each Date:

# Ensure Types Are Correct
df['Date'] = pd.to_datetime(df['Date'], format="%d.%m.%Y")
df['Time'] = pd.to_datetime(df['Time'])


df['Date'] = df['Date'] + \
             df['Time'].diff().dt.days.lt(0).cumsum().astype('timedelta64[D]')

# Fix Formats
df['Date'] = df['Date'].apply(lambda x: x.strftime("%d.%m.%Y"))
df['Time'] = df['Time'].apply(lambda x: x.strftime('%H:%M'))
print(df)

Output:

   ID        Date   Type   Time
0   1  02.12.2021    Car  12:23
1   1  02.12.2021    Car  12:25
2   1  02.12.2021    Car  12:27
3   2  02.12.2021  Train  23:40
4   2  02.12.2021  Train  23:56
5   2  03.12.2021  Train  00:20
6   2  03.12.2021  Train  00:40
7   4  03.12.2021    Car  04:20
8   4  03.12.2021    Car  04:25
9   4  03.12.2021    Car  04:30

df['Time'].diff() gets the difference between this and the previous Time value as a TimeDelta. If it belongs to the previous day as in 23:56 moves to 00:20, the value of days will be -1. For this reason, a boolean index can be created based on where there are -1s. df['Time'].diff().dt.days.dt.days.lt(0). With this information cumsum can be applied to create a series of values that represent grouped days together, starting at 0 and increasing by 1 each time a -1 days is encountered.

print(df['Time'].diff().dt.days.lt(0).cumsum())
0    0
1    0
2    0
3    0
4    0
5    1
6    1
7    1
8    1
9    1
Name: Time, dtype: int32

Then change type to timedelta64[D] to add the offset to each date.

print(df['Time'].diff().dt.days.lt(0).cumsum().astype('timedelta64[D]'))
0   0 days
1   0 days
2   0 days
3   0 days
4   0 days
5   1 days
6   1 days
7   1 days
8   1 days
9   1 days
Name: Time, dtype: timedelta64[ns]

What you can do is a first step adding the start of trip to each row and then simply compare and add one day:

first get the start date/time

df=df.merge(df.groupby(["ID","Type"]).first(),on=["ID","Type"], suffixes=(None,'Start')) 

then compare and add if applicable:

df.loc[(df["Time"]<df["TimeStart"]),"Date"]=df["Date"]+pd.Timedelta(days=1)
Related