pythonpandasbloombergpdblp

Looping through a CUSIPs and pulling data from Bloomberg using pdblp

I have a list of CUSIPs that I would like gather data for using pdblp (https://matthewgilbert.github.io/pdblp/tutorial.html).

For example:

List of CUSIPs (around 5,000 in total):

/cusip/xxxxxxxxx, /cusip/yyyyyyyyy ...

con = pdblp.BCon(debug=False, port=8194, timeout=5000)
con.start()

con.ref(['/cusip/xxxxxxxxx', '/cusip/yyyyyyyyy'], ['Feature1', 'Feature2', 'Feature3',...])

this gives me a dataframe:

Original DataFrame

However, I would like to create a data frame which has the features as columns with the CUSIPs as either another column or an index:

Wanted DataFrame

I wasn't sure if the bdh function would get me what I was looking for either given that it is for historical data (I am looking for most recent). I guess I could also just create a long DataFrame and just melt or pivot it?

Solution

  • You can create a pivot table:

    df = con.ref(['/cusip/xxxxxxxxx', '/cusip/yyyyyyyyy'], ['Feature1', 'Feature2', 'Feature3',...])
df.pivot(index='ticker', columns='field', values='value')

    Result:

    ticker Feature1 Feature2 Feature3
    /cusip/xxxxxxxxx value1 value2 value3
    /cusip/yyyyyyyyy value1 value2 value3