how to split date and time from same column in csv using python?

Question

I have first column (PERIOD_START_TIME) in csv file with date and time, but I need to separate them into two different columns (Date, Time), so I need your help...

PERIOD_START_TIME   
01.31.2017 13:00:00  
01.31.2017 14:00:00  
01.31.2017 15:00:00  
01.31.2017 16:00:00  
01.31.2017 17:00:00  
01.31.2017 18:00:00  
01.31.2017 19:00:00  
01.31.2017 20:00:00  
01.31.2017 21:00:00  
01.31.2017 22:00:00  
01.31.2017 23:00:00  
02.01.2017 00:00:00  
02.01.2017 01:00:00  
02.01.2017 02:00:00  
02.01.2017 03:00:00

piRSquared · Accepted Answer · 2017-02-09 01:53:39Z

1

Your PERIOD_START_TIME might not be datetime. To make sure that it is.

df['PERIOD_START_TIME'] = pd.to_datetime(df['PERIOD_START_TIME'])

Access the date and time attributes via the dt accessor.

df['date'] = df.PERIOD_START_TIME.dt.date
df['time'] = df.PERIOD_START_TIME.dt.time

print(df)

     PERIOD_START_TIME        date      time
0  2017-01-31 13:00:00  2017-01-31  13:00:00
1  2017-01-31 14:00:00  2017-01-31  14:00:00
2  2017-01-31 15:00:00  2017-01-31  15:00:00
3  2017-01-31 16:00:00  2017-01-31  16:00:00
4  2017-01-31 17:00:00  2017-01-31  17:00:00
5  2017-01-31 18:00:00  2017-01-31  18:00:00
6  2017-01-31 19:00:00  2017-01-31  19:00:00
7  2017-01-31 20:00:00  2017-01-31  20:00:00
8  2017-01-31 21:00:00  2017-01-31  21:00:00
9  2017-01-31 22:00:00  2017-01-31  22:00:00
10 2017-01-31 23:00:00  2017-01-31  23:00:00
11 2017-02-01 00:00:00  2017-02-01  00:00:00
12 2017-02-01 01:00:00  2017-02-01  01:00:00
13 2017-02-01 02:00:00  2017-02-01  02:00:00
14 2017-02-01 03:00:00  2017-02-01  03:00:00

setup

import pandas as pd
from io import StringIO

txt = """PERIOD_START_TIME
01.31.2017 13:00:00  
01.31.2017 14:00:00  
01.31.2017 15:00:00  
01.31.2017 16:00:00  
01.31.2017 17:00:00  
01.31.2017 18:00:00  
01.31.2017 19:00:00  
01.31.2017 20:00:00  
01.31.2017 21:00:00  
01.31.2017 22:00:00  
01.31.2017 23:00:00  
02.01.2017 00:00:00  
02.01.2017 01:00:00  
02.01.2017 02:00:00  
02.01.2017 03:00:00  """

df = pd.read_csv(StringIO(txt), parse_dates=[0])

edited Feb 9, 2017 at 1:53

answered Feb 9, 2017 at 1:30

piRSquared

296k68 gold badges509 silver badges654 bronze badges

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

6 Comments

jovicbg Over a year ago

Thanks, but it won't work: AttributeError: Can only use .dt accessor with datetimelike values

jovicbg Over a year ago

Now is unknown string format When I use this: df = df.assign(DATETIME = pd.to_datetime(df['PERIOD_START_TIME'])) df = df.assign(DATE = df['DATETIME'].map(lambda t: t.date())) df = df.assign(TIME = df['DATETIME'].map(lambda t: t.time())) It can't print df, there is no error, but nothing happen, like it's freezed

piRSquared Over a year ago

@jovicbg apologies... I had a typo. Try again.

jovicbg Over a year ago

I think it works, but like I said, when I do that, this way or other, I can't print that, I try with df and df.head(), but nothing works. Problem is something else, restarted kernel, doesn't help, can print just when I delete codelines for spliting datetime.

piRSquared Over a year ago

@jovicbg I'm sorry, I don't know what the problem could be.

|

sl0wcheetah · Accepted Answer · 2017-02-09 12:20:54Z

0

If what you need is to split the column "PERIOD_START_TIME" in a "DATE" and a "TIME" column, I believe the code below will do(Python 3):

#!/usr/bin/env python3
# you may have to change "python3" to "python" depending on your setup.

import csv

# assuming your csv is separated by spaces, like the sample 
# also assuming that the the input file is named 'input.csv'
with open('input.csv', newline='') as csv_input: 
    csv_reader = csv.reader(csv_input, delimiter=' ')
    next(csv_reader)  # skips the header
    with open('output.csv', 'w', newline='') as csv_output:
        csv_writer = csv.writer(csv_output, delimiter=' ')
        csv_writer.writerow(['DATE', 'TIME'])
        for row in csv_reader:
            csv_writer.writerow([row[0], row[1]])

edited Feb 9, 2017 at 12:20

answered Feb 9, 2017 at 2:16

sl0wcheetah

12 bronze badges

2 Comments

jovicbg Over a year ago

I found a lot of errors, newline, csv not defined etc, but I think I have a problem with my csv file so nothing can help right now. Thank you.

sl0wcheetah Over a year ago

Added the shebang line to specify the python environment, this should fix the "not defined" erros. You may change python3 to python, python3.6 etc depending on your setup. Also, add the blank line at the end of the file :)

Collectives™ on Stack Overflow

how to split date and time from same column in csv using python?

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related