Date and Time change using csv column data in Python

Question

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

Could you edit the question to show what the expected output would be for your example — Martin Evans
– Martin Evans, Commented Jun 11, 2022 at 15:00
As per your comment, I have edited it with Input and Output. Thanks — ankur .k
– ankur .k, Commented Jun 11, 2022 at 15:03
Please provide enough code so others can better understand or reproduce the problem. — Community
– Community Bot, Commented Jun 11, 2022 at 15:11
@FObersteiner: New Time adds 5.30 hrs in Old Time. Based on this, New Date will change. Time Gap is based on New Time. — ankur .k
– ankur .k, Commented Jun 11, 2022 at 15:30

Martin Evans · Accepted Answer · 2022-06-11 17:52:37Z

1

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).

edited Jun 11, 2022 at 17:52

answered Jun 11, 2022 at 15:28

Martin Evans

46.9k17 gold badges88 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

ankur .k Over a year ago

Thanks Martin for the code. I have small query. When I use my csv file, time gap is not coming correctly. Actually, difference between New Time is very small. Their difference in seconds. Please suggest.

ankur .k Over a year ago

'Time Gap' is the difference of 'New Time'

ankur .k Over a year ago

Thanks Martin, but 'Time Gap' is not coming correctly. In my csv data, difference in 'New Time' in seconds.

Martin Evans Over a year ago

Please update you example to show when Time Gap does not work - My output is identical to your expected output. The gap could be calculated from New Time but mathematically it is identical to calculating it from OldDateTime. Adding a constant to both sides and calculating a difference will not change anything

ankur .k Over a year ago

'Time Gap' is fixed now by removing /3600. Its now showing in sec. Thanks for help.

|

Collectives™ on Stack Overflow

Date and Time change using csv column data in Python

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related