Transforming financial data from postgres to pandas dataframe for use with Zipline

etl, pandas, python, zipline

Solution

I suspect you are trying to set the date as the index too early. My suggestion would be to first `set_index` as date and company name, then you can `unstack` the company name and `resample`.

Something like this:

In [11]: df1
Out[11]: 
  ticker_symbol  monthly_return                date
0          AAPL           0.112 1992-02-28 00:00:00
1            GS           0.130 1981-11-30 00:00:00
2            GS          -0.230 1981-12-22 00:00:00

df2 = df2.set_index(['date','ticker_symbol'])
df3 = df2.unstack(level=1)
df4 = df.resample('M')

In [14]: df2
Out[14]: 
                          monthly_return
date       ticker_symbol                
1992-02-28 AAPL                    0.112
1981-11-30 GS                      0.130
1981-12-22 GS                     -0.230

In [15]: df3
Out[15]: 
               monthly_return      
ticker_symbol            AAPL    GS
date                               
1981-11-30                NaN  0.13
1981-12-22                NaN -0.23
1992-02-28              0.112   NaN

In [16]: df4
Out[16]: 
<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 124 entries, 1981-11-30 00:00:00 to 1992-02-29 00:00:00
Freq: M
Data columns:
(monthly_return, AAPL)    1  non-null values
(monthly_return, GS)      2  non-null values
dtypes: float64(2)

Problem

I'm new to Pandas and Zipline, and I'm trying to learn how to use them (and use them with this data that I have). Any sorts of tips, even if no full solution, would be much appreciated. I have tried a number of things, and have gotten quite close, but run into indexing issues, `Exception: Reindexing only valid with uniquely valued Index objects`, in particular. [Pandas 0.10.0, Python 2.7] I'm trying to transform monthly returns data I have for thousands of stocks in postgres from the form: ``` ticker_symbol :: String, monthly_return :: Float, date :: Timestamp ``` e.g. ``` AAPL, 0.112, 28/2/1992 GS, 0.13, 30/11/1981 GS, -0.23, 22/12/1981 ``` NB: The frequency of the reporting is monthly, but there is going to be considerable NaN data here, as not all of the over 6000 companies I have here are going to be around at the same time. …to the form described below, which is what Zipline needs to run its backtester. (I think. Can Zipline's backtester work with monthly data like this, easily? I know it can, but any tips for doing this?) The below is a DataFrame (of timeseries? How do you say this?), in the format I need: `> data`: ``` <class 'pandas.core.frame.DataFrame'> DatetimeIndex: 2268 entries, 1993-01-04 00:00:00+00:00 to 2001-12-31 00:00:00+00:00 Data columns: AA 2268 non-null values AAPL 2268 non-null values GE 2268 non-null values IBM 2268 non-null values JNJ 2268 non-null values KO 2268 non-null values MSFT 2268 non-null values PEP 2268 non-null values SPX 2268 non-null values XOM 2268 non-null values dtypes: float64(10) ``` The below is a TimeSeries, and is in the format I need. `> data.AAPL`: ``` Date 1993-01-04 00:00:00+00:00 73.00 1993-01-05 00:00:00+00:00 73.12 ... 2001-12-28 00:00:00+00:00 36.15 2001-12-31 00:00:00+00:00 35.55 Name: AAPL, Length: 2268 ``` Note, there isn't return data here, but prices instead. They're adjusted (by Zipline's `load_from_yahoo`—though, from reading the source, really by functions in pandas) for dividends, splits, etc, so there's an isomorphism (less the initial price) between that and my return data (so, no problem here). (EDIT: Let me know if you'd like me to write what I have, or attach my iPython notebook or a gist; I just doubt it'd be helpful, but I can absolutely do it if requested.)

Original source