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!
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