Convert unix time stamp since epoch to date in dataframe

Question

I am pulling financial data from an API and trying to put it into a readable dataframe. The dates, however, are coded in some ISO 8601 format and I can't tell what they are. Been trying a bunch of different things and all over StackOverflow but I can't figure it out. Help would be much appreciated. The date is supposed to be in column 0.

data = get.foo()
df = pd.DataFrame(data)
print(df.tail())

              0       1       2       3       4           5
295  1520942700  174.10  174.62  174.33  174.50  169.447085
296  1520942640  174.23  174.46  174.23  174.46   25.634600
297  1520942580  173.56  174.60  173.56  174.52  298.726679
298  1520942520  173.50  174.11  174.11  173.55  672.756311
299  1520942460  174.11  174.81  174.80  174.11  441.636742

I'm also unsure of how to change the [0,1,2,3,4,5] at the top of the columns from numbers to [time, low, high, open, close, volume].

Thank you!

Possible duplicate of [Convert Pandas Column to DateTime](https://stackoverflow.com/questions/26763344/convert-pandas-column-to-datetime) — Patrick Artner, Mar 13 '18 at 18:15

score 2 · Answer 1 · answered Mar 13 '18 at 18:04

2

Column 0 has UNIX timestamps, the number of seconds since midnight January 1, 1970, probably in the UTC time zone. See here: Converting unix timestamp string to readable date in Python

answered Mar 13 '18 at 18:04

Myria

3,372
1
24
42

score 2 · Accepted Answer · answered Mar 13 '18 at 18:11

Your times are not https://en.wikipedia.org/wiki/ISO_8601.

You can provide the header when creating the dataframe and apply a transformation to your time column:

import pandas as pd
import datetime  

data = [[  1520942700, 174.10, 174.62, 174.33, 174.50, 169.447085],
        [  1520942640, 174.23, 174.46, 174.23, 174.46,  25.634600],
        [  1520942580, 173.56, 174.60, 173.56, 174.52, 298.726679],
        [  1520942520, 173.50, 174.11, 174.11, 173.55, 672.756311],
        [  1520942460, 174.11, 174.81, 174.80, 174.11, 441.636742]]

# create with headers    
df = pd.DataFrame(data,None, ['time', 'low', 'high', 'open', 'close', 'volume']) 

# convert to datetime (adapted from https://stackoverflow.com/a/26763810/7505395)
df['time'] = df['time'].apply(lambda x:datetime.datetime.fromtimestamp(x))  

print(df)

Output:

                 time     low    high    open   close      volume
0 2018-03-13 13:05:00  174.10  174.62  174.33  174.50  169.447085
1 2018-03-13 13:04:00  174.23  174.46  174.23  174.46   25.634600
2 2018-03-13 13:03:00  173.56  174.60  173.56  174.52  298.726679
3 2018-03-13 13:02:00  173.50  174.11  174.11  173.55  672.756311
4 2018-03-13 13:01:00  174.11  174.81  174.80  174.11  441.636742

score 1 · Answer 3 · answered Mar 13 '18 at 18:12

It is surely not ISO-8601. My guess is: Unix Epoch format (= seconds since 1970-01-01 00:00:00 UTC). This is what I get when I transfer it to readable format:

$ date --date='@1520942700'
di 13 mrt 2018 13:05:00 CET

$ date --date='@1520942640'
di 13 mrt 2018 13:04:00 CET

$ date --date='@1520942580'
di 13 mrt 2018 13:03:00 CET

$ date --date='@1520942460'
di 13 mrt 2018 13:01:00 CET

Convert unix time stamp since epoch to date in dataframe

3 Answers3