CODEWITHSUNDEEP
pythonpandascsvdatetime
ankur .k
ankur .k

Reputation: 17

Date and Time change using csv column data in Python

I have a column name Date_and_Time having thousands of data in csv shown below:

Input:

Date_and_Time
2021-06-02T13:00:00.000+05:30
2021-06-02T15:00:00.000+05:30
2021-06-02T19:00:00.000+05:30
2021-06-02T21:00:00.000+05:30
2021-06-02T22:00:00.000+05:30

I want to change in 6 columns shown below in csv file. 'New Time' change by adding 5.30 hrs. Because of this, 'New Date' also change. There is a 'Time Gap' column too. Please help in Python. Thanks.

Output:

Date and Time                    Old Date    Old Time   New Date     New Time    Time Gap    
2021-06-02T13:00:00.000+05:30    2021-06-02  13:00:00   2021-06-02   18:30:00    NA
2021-06-02T15:00:00.000+05:30    2021-06-02  15:00:00   2021-06-02   20:30:00    2
2021-06-02T19:00:00.000+05:30    2021-06-02  19:00:00   2021-06-03   0:30:00     4
2021-06-02T21:00:00.000+05:30    2021-06-02  21:00:00   2021-06-03   2:30:00     2
2021-06-02T22:00:00.000+05:30    2021-06-02  22:00:00   2021-06-03   3:30:00     1

Upvotes: 0

Views: 453

Answers (1)

Martin Evans
Martin Evans

Reputation: 46759

If you are trying to create the output format from the input column the following approach might help:

import pandas as pd

add_time = pd.Timedelta(hours=5, minutes=30)
df = pd.read_csv('input.csv')

df['OldDateTime'] = pd.to_datetime(df['Date_and_Time'])
df['Old Date'] = df['OldDateTime'].dt.date
df['Old Time'] = df['OldDateTime'].dt.time
df['New Date'] = (df['OldDateTime'] + add_time).dt.date
df['New Time'] = (df['OldDateTime'] + add_time).dt.time
df['Time Gap'] = (df['OldDateTime'] - df['OldDateTime'].shift(1)).dt.total_seconds() / 3600
del df['OldDateTime']

print(df)

Giving you:

                   Date_and_Time    Old Date  Old Time    New Date  New Time  Time Gap
0  2021-06-02T13:00:00.000+05:30  2021-06-02  13:00:00  2021-06-02  18:30:00       NaN
1  2021-06-02T15:00:00.000+05:30  2021-06-02  15:00:00  2021-06-02  20:30:00       2.0
2  2021-06-02T19:00:00.000+05:30  2021-06-02  19:00:00  2021-06-03  00:30:00       4.0
3  2021-06-02T21:00:00.000+05:30  2021-06-02  21:00:00  2021-06-03  02:30:00       2.0
4  2021-06-02T22:00:00.000+05:30  2021-06-02  22:00:00  2021-06-03  03:30:00       1.0

This works by taking the original column and first converting it into a proper datetime object. This can then be used to carry out calculations such as extracting just the date or time, or for adding a fixed time to. Once the calculations have been done this extra column is removed (but could be kept).

Note: The Time Gap can be calculated using the difference in adjacent OldDateTime entries as this will always be identical to if it was calculated with add_time added to both. i.e. the difference will not change.

If you want the Time Gap in seconds, remove / 3600 which was converting total seconds into hours (as per your example).

Upvotes: 1

Related Questions