Reputation: 429
I have a dataframe that consists of date and time column like showed below, with the date format (MM/DD/YYYY);
|Date | Time |
|--------|--------|
|1/1/2019|1200hrs |
|1/1/2019|1300hrs |
|1/1/2019|1400hrs |
|1/2/2019|1200hrs |
|1/2/2019|1300hrs |
|1/2/2019|1400hrs |
|1/2/2019|1700hrs |
I want to generate another column that shows the difference between each time like the dataframe shown below,
|Date | Time |Time difference(mins)|
|--------|--------|---------------------|
|1/1/2019|1200hrs |- |
|1/1/2019|1300hrs |60 |
|1/1/2019|1400hrs |60 |
|1/2/2019|1200hrs |- |
|1/2/2019|1300hrs |60 |
|1/2/2019|1400hrs |60 |
|1/2/2019|1700hrs |180 |
The time difference should be calculated on the same date. Can anyone give me some idea of how to do it? Thank you!
Upvotes: 2
Views: 107
Reputation: 863791
Use DataFrameGroupBy.diff
with convert to datetimes, last convert timedeltas to seconds by Series.dt.total_seconds
and last to minutes by division of 60
:
df['Time'] = pd.to_datetime(df['Date'] + df['Time'], format='%m/%d/%Y%H%Mhrs')
df['Time difference(mins)'] = df.groupby('Date')['Time'].diff().dt.total_seconds().div(60)
print (df)
Date Time Time difference(mins)
0 1/1/2019 2019-01-01 12:00:00 NaN
1 1/1/2019 2019-01-01 13:00:00 60.0
2 1/1/2019 2019-01-01 14:00:00 60.0
3 1/2/2019 2019-01-02 12:00:00 NaN
4 1/2/2019 2019-01-02 13:00:00 60.0
5 1/2/2019 2019-01-02 14:00:00 60.0
6 1/2/2019 2019-01-02 17:00:00 180.0
Upvotes: 3