CODEWITHSUNDEEP
pythonpandas
user1234440
user1234440

Reputation: 23587

Python Reading in Tick Data Pandas

I got some data that looks like this:

    Symbol              Timestamp      bid      ask
0  EUR/USD  20140101 21:55:34.378  1.37622  1.37693
1  EUR/USD  20140101 21:55:40.410  1.37624  1.37698
2  EUR/USD  20140101 21:55:47.210  1.37619  1.37696
3  EUR/USD  20140101 21:55:57.963  1.37616  1.37696
4  EUR/USD  20140101 21:56:03.117  1.37616  1.37694

I am trying to convert it to pandas, but am stuck on the microsecond part....below is what I am i've got so far...

Read in data

sec = pd.read_csv('EURUSD.csv',header=None,parse_dates = 1)
sec.columns = ['Symbol', 'Timestamp', 'bid', 'ask']

Get the timestamp column to convert

sec['Timestamp'] = sec['Timestamp'].apply(lambda x: dt.datetime.strptime(x, '%Y%m%d %H%M%S'))

'%Y%m%d %H%M%S' In the above command, the part after %S should be wad? Should it be '%Y%m%d %H%M%S%f'? but that doesnt seem to work. The period in the time messes things up...any ideas?

Upvotes: 2

Views: 873

Answers (1)

Andy Hayden
Andy Hayden

Reputation: 375745

Just apply to_datetime:

In [11]: df
Out[11]:
    Symbol              Timestamp      bid      ask
0  EUR/USD  20140101 21:55:34.378  1.37622  1.37693
1  EUR/USD  20140101 21:55:40.410  1.37624  1.37698
2  EUR/USD  20140101 21:55:47.210  1.37619  1.37696
3  EUR/USD  20140101 21:55:57.963  1.37616  1.37696
4  EUR/USD  20140101 21:56:03.117  1.37616  1.37694

In [12]: pd.to_datetime(df.Timestamp)
Out[12]:
0   2014-01-01 21:55:34.378000
1   2014-01-01 21:55:40.410000
2   2014-01-01 21:55:47.210000
3   2014-01-01 21:55:57.963000
4   2014-01-01 21:56:03.117000
Name: Timestamp, dtype: datetime64[ns]

If you must pass a format, missing piece is %f:

In [12]: df['Timestamp'].apply(lambda x: dt.datetime.strptime(x, '%Y%m%d %H:%M:%S.%f'))
Out[12]:
0   2014-01-01 21:55:34.378000
1   2014-01-01 21:55:40.410000
2   2014-01-01 21:55:47.210000
3   2014-01-01 21:55:57.963000
4   2014-01-01 21:56:03.117000
Name: Timestamp, dtype: datetime64[ns]

Upvotes: 1

Related Questions