How to handle the multiple columns in pandas with symbols

jaydoubleu79
jaydoubleu79 Newcomer
in Eikon Data APIs

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:

image


into this:


image

thanks!

Tagged:

Best Answer

  • antoniorodriguez
    antoniorodriguez Newcomer
    Answer ✓

    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

    image

Answers

  • Gurpreet
    Gurpreet admin

    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
    jaydoubleu79 Newcomer

    Thank you very much! that is awesome :)

Categories