Pandas: Parsing dates in different columns with read_csv

Question

I have an ascii file where the dates are formatted as follows:

Jan 20 2015 00:00:00.000
Jan 20 2015 00:10:00.000
Jan 20 2015 00:20:00.000
Jan 20 2015 00:30:00.000
Jan 20 2015 00:40:00.000

When loading the file into pandas, each column above gets its own column in a pandas dataframe. I've tried the variations of the following:

from pandas import read_csv
from datetime import datetime

df = read_csv('file.txt', header=None, delim_whitespace=True,
              parse_dates={'datetime': [0, 1, 2, 3]},
              date_parser=lambda x: datetime.strptime(x, '%b %d %Y %H %M %S'))

I get a couple errors:

TypeError: <lambda>() takes 1 positional argument but 4 were given
ValueError: time data 'Jun 29 2017 00:35:00.000' does not match format '%b %d %Y %H %M %S'

I'm confused because:

I'm passing a dict to parse_dates to parse the different columns as a single date.
I'm using: %b - abbreviated month name, %d - day of the month, %Y year with century, %H 24-hour, %M - minute, and %S - second

Anyone see what I'm doing incorrectly?

Edit:

I've tried date_parser=lambda x: datetime.strptime(x, '%b %d %Y %H:%M:%S') which returns ValueError: unconverted data remains: .000

Edit 2:

I tried what @MaxU suggested in his update, but it was problematic because my original data is formatted like the following:

Jan   1  2017  00:00:00.000   123 456 789 111 222 333

I'm only interested in the first 7 columns so I import my file with the following:

df = read_csv(fn, header=None, delim_whitespace=True, usecols=[0, 1, 2, 3, 4, 5, 6])

Then to create a column with datetime information from the first 4 columns I try:

df['datetime'] = to_datetime(df.ix[:, :3], format='%b %d %Y %H:%M:%S.%f')

However this doesn't work because to_datetime expects "integer, float, string, datetime, list, tuple, 1-d array, Series" as the first argument and df.ix[:, :3] returns a dataframe with the following format:

         0   1     2             3
0      Jan   1  2017  00:00:00.000

How do I feed in every row of the first four columns to to_datetime such that I get one column of datetimes?

Edit 3:

I think I solved my second problem. I just use to following command and do everything when I read my file in (I was basically just missing %f to parse past seconds):

df = read_csv(fileName, header=None, delim_whitespace=True,
              parse_dates={'datetime': [0, 1, 2, 3]},
              date_parser=lambda x: datetime.strptime(x, '%b %d %Y %H:%M:%S.%f'),
              usecols=[0, 1, 2, 3, 4, 5, 6])

The whole reason I wanted to parse manually instead of letting pandas handle it like @MaxU suggested was to see if manually feeding in instructions would be faster - and it is! From my tests the snippet above runs approximately 5-6 times faster than letting pandas infer parsing for you.

Diego Aguado · Accepted Answer · 2017-07-13 20:47:59Z

5

Have a go to this simpler approach:

df = pandas.read_csv('file.txt')
df.columns = ['date']

df should be a dataframe with a single column. After that try casting that column to datetime

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

answered Jul 13, 2017 at 20:47

Diego Aguado

1,61618 silver badges37 bronze badges

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

Comments

MaxU - stand with Ukraine · Accepted Answer · 2017-07-14 14:52:42Z

2

Pandas (tested with version 0.20.1) is smart enough to do it for you:

In [4]: pd.read_csv(fn, sep='\s+', parse_dates={'datetime': [0, 1, 2, 3]})
Out[4]:
             datetime
0 2015-01-20 00:10:00
1 2015-01-20 00:20:00
2 2015-01-20 00:30:00
3 2015-01-20 00:40:00

UPDATE: if all entries have the same format you can try to do it this way:

df = pd.read_csv(fn, sep='~', names=['datetime'])
df['datetime'] = pd.to_datetime(df['datetime'], format='%b %d %Y %H:%M:%S.%f')

edited Jul 14, 2017 at 14:52

answered Jul 13, 2017 at 20:49

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

Collectives™ on Stack Overflow

Pandas: Parsing dates in different columns with read_csv

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related