question

Upvotes
Accepted
7 3 5 6

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!

eikoneikon-data-apipythonworkspacerefinitiv-dataplatform-eikonworkspace-data-apiricstime-seriespandas
1592309436803.png (45.3 KiB)
1592309461729.png (8.9 KiB)
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Upvotes
Accepted
21 0 0 0

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


1618997646681.png (26.3 KiB)
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Upvote
24.8k 64 15 21

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]
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Upvotes
7 3 5 6

Thank you very much! that is awesome :)

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.