CODEWITHSUNDEEP
pythonpandasdatetime
sjc725
sjc725

Reputation: 511

Converting column into proper timestamp using pandas read_csv

I have a time series csv file that consists of timestamps and financial data, like this:

20140804:10:00:13.281486,782.83,443355
20140804:10:00:13.400113,955.71,348603

Now, I would like to put this into a pandas.DataFrame, and parse the dates to yyyymmddhhmmss when I read in the csv. I searched around the threads and I see people using the datetime module, but I'm pretty new to Python, so I'm not sure how to use that module to parse the above data, and to do this all at the same time I read in the csv.

How best to go about this?

Upvotes: 2

Views: 2982

Answers (1)

jezrael
jezrael

Reputation: 862431

You need:

no header of csv:

import pandas as pd
from pandas.compat import StringIO

temp=u"""
20140804:10:00:13.281486,782.83,443355
20140804:10:00:13.400113,955.71,348603"""
#after testing replace 'StringIO(temp)' to 'filename.csv'
df = pd.read_csv(StringIO(temp), 
                 #parse first columns 
                 parse_dates=[0], 
                 #custom parse function 
                 date_parser = lambda x: pd.datetime.strptime(x, '%Y%m%d:%H:%M:%S.%f'), 
                 #no header of csv
                 header=None) 

print (df)
                           0       1       2
0 2014-08-04 10:00:13.281486  782.83  443355
1 2014-08-04 10:00:13.400113  955.71  348603

print (df.dtypes)
0    datetime64[ns]
1           float64
2             int64
dtype: object

header of csv

import pandas as pd
from pandas.compat import StringIO

temp=u"""dates,a,b
20140804:10:00:13.281486,782.83,443355
20140804:10:00:13.400113,955.71,348603"""
#after testing replace 'StringIO(temp)' to 'filename.csv'
df = pd.read_csv(StringIO(temp), 
                 parse_dates=[0], 
                 date_parser = lambda x: pd.datetime.strptime(x, '%Y%m%d:%H:%M:%S.%f'))

print (df)
                       dates       a       b
0 2014-08-04 10:00:13.281486  782.83  443355
1 2014-08-04 10:00:13.400113  955.71  348603     

print (df.dtypes)
dates    datetime64[ns]
a               float64
b                 int64
dtype: object

Upvotes: 1

Related Questions