Parse Bloomberg Excel/CSV with Pandas DataFrame

Question

I retrieved Bloomberg data using the Excel API. In the typical fashion, the first row contains tickers in every fourth column, and the second row has the labels Date, PX_LAST, [Empty Column], Date, PX_LAST, etc. The following rows have dates and last price.

   EHFI38 Index BBGID,                   , , EHFI139 Index BBGID,                    , ...
   Date              , PX_LAST           , , Date               , PX_LAST            , ...
   1999-12-31        , 100.0000          , , 1999-12-31         , 100.0000           , ...
   2000-01-31        , 100.1518          , , 2000-01-31         ,  98.6526           , ...
...

It seems that the proper data structure would be a DataFrame with dates as the index, and tickers as the column names.

    , Date,       EHFI38 Index BBGID, EHFI139 Index BBGID, EHFI139 Index BBGID, EHFI84 Index BBGID, ...
   0, 1999-12-31, 100.0000          , 100.0000           , 100.0000           , 100.0000, ...
   1, 2000-01-31, 100.1518          ,  98.6526           ,  98.6526           , 104.7575, ...
...

I wrote this code, which seems to work when I step through it, but I'm sure I'm not doing it well. I'd like to learn how to do it better.

# IMPORT
import pandas as pd
import numpy as np
import datetime

# READ IN CSV FILES
# EHFI38 Index BBGID,                   , , EHFI139 Index BBGID,                    , ...
# Date              , PX_LAST           , , Date               , PX_LAST            , ...
# 1999-12-31        , 100.0000          , , 1999-12-31         , 100.0000           , ...
# 2000-01-31        , 100.1518          , , 2000-01-31         ,  98.6526           , ...
# ...
px = pd.read_csv('Book1.csv', sep=',', parse_dates=True)

# REMOVE EMPTY COLUMNS
px = px.dropna(axis=1, how='all')

# CONVERT TO ARRAYS
M = np.array(px)
C = np.array(px.columns)

# FIX UNNAMED COLUMNS IN C
for i in arange( len(C)/2 ) * 2:
    C[i+1] = C[i]

# CONVERT EXCEL DATES FUNCTION (THANKS JOHN MACHIN)
def xl2pydate(xldate, datemode):
    # datemode: 0 for 1900-based, 1 for 1904-based
    return (
        datetime.datetime(1899, 12, 30)
        + datetime.timedelta(days=xldate + 1462 * datemode)
    )

# CONVERT DATES THE UGLY WAY
# LOOP THROUGH 1,2, ... last row
for i in arange( len(M)-1 ) + 1:

    # LOOP THROUGH 0,2, ... last column-1
    for j in arange( len(M.T)/2 ) * 2:

        # CONVERT DATE & STORE
        if isinstance(M[i,j],str) and M[i,j].isdigit():
            M[i,j] = xl2pydate(int(M[i,j]), 0)
        else:
            M[i,j] = NaN

# RECOMBINE IN A DATAFRAME
df = pd.DataFrame(M[1:,:], columns=[C,M[0,:]])

# MERGE DATES
#  , Date,       EHFI38 Index BBGID, EHFI139 Index BBGID, EHFI139 Index BBGID, EHFI84 Index BBGID, ...
# 0, 1999-12-31, 100.0000          , 100.0000           , 100.0000           , 100.0000, ...
# 1, 2000-01-31, 100.1518          ,  98.6526           ,  98.6526           , 104.7575, ...
# ...

# LOOP 0,2,...,len-1
for i in arange( (len(df.T))/2 ) * 2:

    # GET A DATE, LAST_PX FOR A SINGLE TICKER
    b = df[df.columns[i:(i+2)]]

    # CHANGE COLUMN NAMES TO DATE, [TICKER]
    b.columns = (df.columns[i][1], df.columns[i][0])

    # COMBINE
    if i==0:
        a = b
    else:
        a = pd.merge(a.dropna(), b.dropna(), on='Date', how='outer')

Answer 1

You can probably do most of what you want in native Pandas. It has functions for excel file IO that will probably take care of much of the date-munging.

If you want to carry on with the intermediate .csv file, the following should help.

Because you have an empty column and 3 commas between EHFI38 Index BBGID and EHFI139 Index BBGID, your data are in a slightly strange format.

import pandas as pd
from cStringIO import StringIO

data = """\
EHFI38 Index BBGID,                   , , EHFI139 Index BBGID,                   
Date              , PX_LAST           , , Date               , PX_LAST          
1999-12-31        , 100.0000          , , 1999-12-31         , 100.0000         
2000-01-31        , 100.1518          , , 2000-01-31         ,  98.6526
"""
df = pd.read_csv(StringIO(data), header=[0, 1], parse_dates=True)
df

There should be a way to sort out the strange indexing but I cannot easily work it out. Try searching for pandas multi-index and hierarchical data.