Arvinth Kumar Arvinth Kumar - 3 years ago 135
Python Question

How to access a multilevel Pandas Dataframe in Python - Storing Bloomberg data in dataframe

I have installed Bloomberg API and pdblp library. I am able to get historical data and store it in Dataframe. But i am not sure how to access data from multilevel dataframe.

import pdblp
import pandas as pd

con = pdblp.BCon(debug=True, port=8194)
con.start()

start = datetime.datetime.strptime("19800101", '%Y%m%d').strftime("%Y%m%d")
end = datetime.date.today().strftime("%Y%m%d")


df = pd.DataFrame(con.bdh('SPY US Equity',['PX_LAST', 'VOLUME'],start, end))
print(df)


I am not able access the date column in Dataframe. Can anyone please help me.
If i try -- df.columns i am getting below output:

MultiIndex(levels=[['SPY US Equity'], ['PX_LAST', 'VOLUME']],
labels=[[0, 0], [0, 1]],
names=['ticker', 'field'])


Below is the data from Dataframe

ticker SPY US Equity
field PX_LAST VOLUME
date
1993-01-29 43.9375 1003200.0
1993-02-01 44.2500 480500.0
1993-02-02 44.3438 201300.0
1993-02-03 44.8125 529400.0
1993-02-04 45.0000 531500.0
1993-02-05 44.9688 492100.0
1993-02-08 44.9688 596100.0
1993-02-09 44.6563 122100.0
1993-02-10 44.7188 379600.0
1993-02-11 44.9375 19500.0
1993-02-12 44.5938 42500.0
1993-02-16 43.4688 374800.0
1993-02-17 43.4375 210900.0
1993-02-18 43.4063 378100.0
1993-02-19 43.5625 34900.0
1993-02-22 43.7188 513600.0
1993-02-23 43.6875 373700.0
1993-02-24 44.2500 26300.0
1993-02-25 44.3438 44500.0
1993-02-26 44.4063 66200.0
1993-03-01 44.2813 66500.0
1993-03-02 44.9375 182400.0


df.index gives below result:

DatetimeIndex(['1993-01-29', '1993-02-01', '1993-02-02', '1993-02-03',
'1993-02-04', '1993-02-05', '1993-02-08', '1993-02-09',
'1993-02-10', '1993-02-11',
...
'2017-07-13', '2017-07-14', '2017-07-17', '2017-07-18',
'2017-07-19', '2017-07-20', '2017-07-21', '2017-07-24',
'2017-07-25', '2017-07-26'],
dtype='datetime64[ns]', name='date', length=6168, freq=None)


df.loc['1993-02-22'] gives following result:

ticker field
SPY US Equity PX_LAST 43.7188
VOLUME 513600.0000

Answer Source

The 'date' is in your index. Pull it out with reset_index.

df = df.reset_index()

Now, you can see your 'date' column using:

df['date']
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download