I have a dataframe with unix times and prices in it. I want to convert the index column so that it shows in human readable dates.
So for instance I have date
as 1349633705
in the index column but I'd want it to show as 10/07/2012
(or at least 10/07/2012 18:15
).
For some context, here is the code I'm working with and what I've tried already:
import json
import urllib2
from datetime import datetime
response = urllib2.urlopen('http://blockchain.info/charts/market-price?&format=json')
data = json.load(response)
df = DataFrame(data['values'])
df.columns = ["date","price"]
#convert dates
df.date = df.date.apply(lambda d: datetime.strptime(d, "%Y-%m-%d"))
df.index = df.date
As you can see I'm using
df.date = df.date.apply(lambda d: datetime.strptime(d, "%Y-%m-%d"))
here which doesn't work since I'm working with integers, not strings. I think I need to use datetime.date.fromtimestamp
but I'm not quite sure how to apply this to the whole of df.date
.
Thanks.
This question is related to
python
pandas
unix-timestamp
dataframe
Assuming we imported pandas as pd
and df
is our dataframe
pd.to_datetime(df['date'], unit='s')
works for me.
Alternatively, by changing a line of the above code:
# df.date = df.date.apply(lambda d: datetime.strptime(d, "%Y-%m-%d"))
df.date = df.date.apply(lambda d: datetime.datetime.fromtimestamp(int(d)).strftime('%Y-%m-%d'))
It should also work.
If you try using:
df[DATE_FIELD]=(pd.to_datetime(df[DATE_FIELD],***unit='s'***))
and receive an error :
"pandas.tslib.OutOfBoundsDatetime: cannot convert input with unit 's'"
This means the DATE_FIELD
is not specified in seconds.
In my case, it was milli seconds - EPOCH time
.
The conversion worked using below:
df[DATE_FIELD]=(pd.to_datetime(df[DATE_FIELD],unit='ms'))
Source: Stackoverflow.com