Splitting datetime in csv to separate date and time columns

Question

I'm new to python 3 I have to get a datetime value from a .csv file and split into a separate date and time value column.

csv file:

C1,C2,V2,Time_Stamp
0.1,1.3,-0.9,13/6/2017 14:42
0.1,1.2,-0.9,13/6/2017 14:42
0.1,1.3,-0.9,13/6/2017 14:42
0.1,1.3,-0.9,13/6/2017 14:42

Initially, what I have is this under Time_Stamp:

Output after running:

timestamps = Data.Time_Stamp
for i in timestamps:
    dataobj = dt.datetime.strptime(i, "%d/%m/%Y %H:%M") #Time_Stamp datetime format - need set varibale to print
    print (dataobj) #Prints the following output

Output:

13/6/2017 14:42

13/6/2017 14:42

13/6/2017 14:42

13/6/2017 14:42

13/6/2017 14:42

Based on the above output, it has date and time together in a column, I want the date to be in a different column and the time to be in another.

   13/6/2017 14:42    13/6/2017   14:42

   13/6/2017 14:42    13/6/2017   14:42

   13/6/2017 14:42    13/6/2017   14:42

   13/6/2017 14:42    13/6/2017   14:42

   13/6/2017 14:42    13/6/2017   14:42

I tried following examples from this Splitting timestamp column into seperate date and time columns but i could not get similar outputs.

import pandas as pd
import csv 
import datetime as dt

print (row)

Data = pd.DataFrame.from_csv('csv_date.csv')
print (Data)

timestamps = Data.Time_Stamp
for i in timestamps:
    dataobj = datetime.strptime(i, "%d/%m/%Y %H:%M")

df['new_date'] = [i.date() for i in Data['Time_Stamp']]
df['new_time'] = [i.time() for i in Data['Time_Stamp']]

Error I faced:

AttributeError                            Traceback (most recent call last)
<ipython-input-137-ca9ed5c25ff2> in <module>()
----> 1 df['new_date'] = [i.date() for i in Data['Time_Stamp']]
      2 df['new_time'] = [i.time() for i in Data['Time_Stamp']]

<ipython-input-137-ca9ed5c25ff2> in <listcomp>(.0)
----> 1 df['new_date'] = [i.date() for i in Data['Time_Stamp']]
      2 df['new_time'] = [i.time() for i in Data['Time_Stamp']]

AttributeError: 'str' object has no attribute 'date'

cs95 · Accepted Answer · 2017-08-07T05:47:48.403

2

When reading, use pd.read_csv with parse_dates and infer_datetime_format as True.

Data = pd.read_csv('csv_date.csv', parse_dates=['Time_Stamp'], infer_datetime_format=True)

Next, you can just extract the date & time:

Data['Date'] = Data.Time_Stamp.dt.date
Data['Time'] = Data.Time_Stamp.dt.time

edited Aug 07 '17 at 05:47

answered Aug 07 '17 at 03:53

cs95

379,657
97
704
746

I've tried using the examples you provided but i had this error : **TypeError: cannot convert an object of type to a datetimelike index** as well as **AttributeError: Can only use .dt accessor with datetimelike values** – Sancta Ignis Aug 07 '17 at 04:05
@SanctaIgnis Please post some of your input data. – cs95 Aug 07 '17 at 04:36
I added the data I'm using with the datetime – Sancta Ignis Aug 07 '17 at 05:46
1

@SanctaIgnis Fixed, and it works now. Use `parse_dates=['Time_Stamp']` instead – cs95 Aug 07 '17 at 05:47
@SanctaIgnis No problemo. Future reference, data always always helps ;) – cs95 Aug 07 '17 at 06:01

score 0 · Answer 2 · answered Aug 07 '17 at 03:32

0

Data = pd.DataFrame.from_csv('csv_date.csv')
Data['new_times'] = Data['Time_Stamp'].apply(lambda x:datetime.strptime(x, "%d/%m/%Y %H:%M"))

use apply and lambda deal with Dataframe

answered Aug 07 '17 at 03:32

Vity Lin

499
2
5
11

I tried the above example you provided. I encountered this error **module 'datetime' has no attribute 'strptime'** Any idea why? – Sancta Ignis Aug 07 '17 at 04:02
datetime.datetime.strptime() and you can use pd.to_datetime() tranform the column – Vity Lin Aug 07 '17 at 09:34

Splitting datetime in csv to separate date and time columns

2 Answers2