3

I'm having a bit of trouble getting the right time index for my pandas dataframe. 

import pandas as pd
from datetime import strptime
import numpy as np

stockdata = pd.read_csv("/home/stff/symbol_2012-02.csv", parse_dates =[[0,1,2]])
stockdata.columns = ['date_time','ticker','exch','salcond','vol','price','stopstockind','corrind','seqnum','source','trf','symroot','symsuffix']

I think the problem is that the time stuff comes in the first three columns: year/month/date, hour/minute/second, millisecond. Also, the hour/minute/second column drops the first zero if its before noon. 

print(stockdata['date_time'][0])
20120201 41206 300

print(stockdata['date_time'][50000])
20120201 151117 770

Ideally, I would like to define my own function that could be called by the converters argument in the read_csv function.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Taylor
  • 1,797
  • 4
  • 26
  • 51

1 Answers1

2

Suppose you have a csv file that looks like this:

date,time,milliseconds,value
20120201,41206,300,1
20120201,151117,770,2

Then using parse_dates, index_cols and date_parser parameters of read_csv method, one could construct a pandas DataFrame with time index like this:

import datetime as dt
import pandas as pd
parse = lambda x: dt.datetime.strptime(x, '%Y%m%d %H%M%S %f')
df = pd.read_csv('test.csv', parse_dates=[['date', 'time', 'milliseconds']],
                 index_col=0, date_parser=parse)

This yields:

                            value
date_time_milliseconds           
2012-02-01 04:12:06.300000      1
2012-02-01 15:11:17.770000      2

And df.index:

<class 'pandas.tseries.index.DatetimeIndex'>
[2012-02-01 04:12:06.300000, 2012-02-01 15:11:17.770000]
Length: 2, Freq: None, Timezone: None

This answer is based on a similar solution proposed here.

Community
  • 1
  • 1
abudis
  • 2,841
  • 6
  • 32
  • 41