How to handle the multiple columns in pandas with symbols

Hi!

when I request timeseries data for a list of RICS instead of 1, the DF returned has an extra layer on top of the usual OHLC-V data with the symbol names. So the DF is horizontally built.

I cant seem to transform this to something I can easily work with.

Could anyone tell me how to get this:

into this:

thanks!

Find more posts tagged with

python

eikon

rics

time-series

pandas

workspace

eikon-data-api

refinitiv-dataplatform-eikon

workspace-data-api

Accepted answers

antoniorodriguez

Hello @jaydoubleu79

The above answer although valid, uses a loop to handle a dataframe, which is usually not a good deal.

This would be the genuine Pandas style way to do it.

import eikon as ek 
ek.set_app_key('<<your_api_key_here>>') 

symbols = ['RDSa.AS', 'NESN.S', 'RO.S'] 
df = ek.get_timeseries(symbols) 

df = df.stack(0).reset_index().set_index('Date')

display(df)

result

All comments

Gurpreet

Hi @jaydoubleu79

You can flatten the multiindex and append the dataframes like this:

apd = pd.DataFrame()

for sec in df.columns.levels[0]:
    dd = df[sec].copy().reset_index()
    dd['Security'] = sec
    apd = apd.append(dd, sort=True)

display(apd)

result:

>>> apd
         CLOSE     COUNT       Date       HIGH        LOW      OPEN Security       VOLUME
0   1215.56000  112119.0 2020-03-09  1254.7599  1200.0000  1205.300   GOOG.O    3365365.0
1   1280.39000   68048.0 2020-03-10  1281.1500  1218.7700  1260.000   GOOG.O    2611373.0
2   1215.41000   78221.0 2020-03-11  1260.9600  1196.0700  1249.700   GOOG.O    2611229.0
3   1114.91000  133400.0 2020-03-12  1193.8700  1113.3000  1126.000   GOOG.O    4226748.0
..         ...       ...        ...        ...        ...       ...      ...          ...
66   126.66380      -1.0 2020-06-11   132.3800   125.9400   131.480    VOD.L   76129921.0
67   126.10000      -1.0 2020-06-12   127.9000   122.2400   124.660    VOD.L   62619296.0
68   124.46000      -1.0 2020-06-15   125.0800   122.7000   123.760    VOD.L  129273352.0
69   129.18000   14970.0 2020-06-16   130.5600   126.2000   126.940    VOD.L   32893872.0

[140 rows x 8 columns]

jaydoubleu79

Thank you very much! that is awesome

antoniorodriguez

Hello @jaydoubleu79

The above answer although valid, uses a loop to handle a dataframe, which is usually not a good deal.

This would be the genuine Pandas style way to do it.

import eikon as ek 
ek.set_app_key('<<your_api_key_here>>') 

symbols = ['RDSa.AS', 'NESN.S', 'RO.S'] 
df = ek.get_timeseries(symbols) 

df = df.stack(0).reset_index().set_index('Date')

display(df)

result

EXPLORE OUR SITES