Converting a csv file to Datetime

Question

I've searched thoroughly for an answer before asking, but all I've been able to find was how to convert one column of my csv file into a datetime object.

My problem is, my file has a separate column for year, month, day, hour, minute, and I've been struggling for a while to convert and combine them.

Any help would be much appreciated.

head of file content: viewed in excel

please provide file content here.. so we can get better idea! — DexJ, Apr 04 '17 at 06:53
Have you tried `strptime`? https://docs.python.org/3/library/datetime.html#strftime-strptime-behavior — Andrey Shipilov, Apr 04 '17 at 06:57
Possible duplicate of [datetime dtypes in pandas read\_csv](http://stackoverflow.com/questions/21269399/datetime-dtypes-in-pandas-read-csv) — firelynx, Apr 04 '17 at 08:08

jezrael · Accepted Answer · 2017-04-04T07:10:44.457

3

You can use read_csv with parameters parse_dates and date_parser. See also docs:

import pandas as pd
from pandas.compat import StringIO

temp=u"""year,month,day,hour,minute,a
2017,09,01,10,20,1
2017,10,01,10,20,2
2017,11,01,10,20,3"""
#after testing replace 'StringIO(temp)' to 'filename.csv'
parser = lambda x: pd.datetime.strptime(x, '%Y %m %d %H %M')
df = pd.read_csv(StringIO(temp), 
                 parse_dates={'date':['year','month','day','hour','minute']},
                 date_parser=parser)
print (df)
                 date  a
0 2017-09-01 10:20:00  1
1 2017-10-01 10:20:00  2
2 2017-11-01 10:20:00  3

edited Apr 04 '17 at 07:10

answered Apr 04 '17 at 06:58

jezrael

822,522
95
1,334
1,252

This has been the most helpful! Oh, to actually have a result after typing "print(df)" ... such a relief. – saremisona Apr 04 '17 at 07:10
So output is `file`? Or `dataframe`? – jezrael Apr 04 '17 at 07:14
Dataframe, but I only really needed this format to make my life easier while working with time series forecasting. – saremisona Apr 04 '17 at 07:19

score 2 · Answer 2 · answered Apr 04 '17 at 07:05

2

I'll write a simple way.

Suppose there is a CSV file like the one below.

year,month,day,hour,minute
2017,1,1,23,59
2017,2,1,23,59
2017,3,1,23,59

You can parse csv and create a datetime object.

import csv
from datetime import datetime

with open('test.csv') as fp:
    reader = csv.reader(fp)
    next(reader) # skip header
    for row in reader:
        row = [int(r) for r in row]
        print(datetime(row[0], row[1], row[2], row[3], row[4]))

The result is like this.

2017-01-01 23:59:00
2017-02-01 23:59:00
2017-03-01 23:59:00

thx:)

answered Apr 04 '17 at 07:05

tell k

605
2
7
18

@saremisona don't forget to upvote any answer you found useful :) – IanS Apr 04 '17 at 07:22
I have upvoted everything, it just says it's recorded but not shown because I just joined. I'm sorry. :( – saremisona Apr 04 '17 at 07:23

score 0 · Answer 3 · answered Apr 04 '17 at 06:57

this might be a little manual but you could:

read each line, split it into your various items (year, month etc) as string.
then recompose into a datetime object with datetime.datetime.strptime (which will convert a string with a given format to a datetime).

Converting a csv file to Datetime

3 Answers3