title: Python pandas time series w/ hierarchical indexing and rolling/ shifting
body: <p>Struggling with pandas' rolling and shifting concept. There are many good suggestions including in this forum but I failed miserably to apply these to my scenario.</p>
  
  <p>Now I use traditional looping over the time series but ugh, it took like 8 hours to iterate over 150,000 rows which is about 3 days of data for all tickers. Got 2 months data to process it probably won't finish after I come back from a sabbatical, not mentioning risk of electricity cut off after which I'd have to start over again this time no sabbatical while waiting.</p>
  
  <p>I have the following 15 min stock price time series (Hierarchical index on datetime(timestamp) and ticker, the only original column is closePrice):</p>
  
  <pre><code>                                     closePrice
      datetime               ticker
      2014-02-04 09:15:00    AAPL      xxx
                             EQIX      xxx
                             FB        xxx
                             GOOG      xxx
                             MSFT      xxx
      2014-02-04 09:30:00    AAPL      xxx
                             EQIX      xxx
                             FB        xxx
                             GOOG      xxx
                             MSFT      xxx
      2014-02-04 09:45:00    AAPL      xxx
                             EQIX      xxx
                             FB        xxx
                             GOOG      xxx
                             MSFT      xxx
  </code></pre>
  
  <p>I need to add two columns:</p>
  
  <ol>
  <li>12sma, 12 days moving average. Having searched SO for hours the best suggestion would be to use rolling_mean, so I tried. But it didn't work given my TS structure i.e. it works top down the first MA is calculated based on the first 12 rows regardless of different ticker values. How do I make it average based on the index i.e. first datetime then ticker so I get MA for say AAPL? Currently it does (AAPL+EQIX+FB+GOOG+MSFT+AAPL...up to 12th row) / 12</li>
  <li>Once I got the 12sma column, I need 12ema column, 12 days exponential MA. For the calculation, the first value in the time series for each ticker would just copy 12sma value from the same row. Subsequently, I'd need closePrice from the same row and 12ema from the previous row i.e. past 15 min. I did a long research seems like the solution would be a combination of rolling and shifting but I can't figure out how to put it together.</li>
  </ol>
  
  <p>Any help I'd be grateful.</p>
  
  <p>Thanks.</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Thanks to Jeff's tips, after swapping and sorting ix level I am able to get the 12sma right with rolling_mean() and with a effort managed to insert the first 12ema value copied from 12sma at the same timestamp:</p>
  
  <pre><code>                                 close  12sma  12ema
      sec_code datetime
      AAPL     2014-02-05 11:45:00 113.0  NaN    NaN
               2014-02-05 12:00:00 113.2  NaN    NaN
               2014-02-05 13:15:00 112.9  NaN    NaN
               2014-02-05 13:30:00 113.2  NaN    NaN
               2014-02-05 13:45:00 113.0  NaN    NaN
               2014-02-05 14:00:00 113.1  NaN    NaN
               2014-02-05 14:15:00 113.3  NaN    NaN
               2014-02-05 14:30:00 113.3  NaN    NaN
               2014-02-05 14:45:00 113.3  NaN    NaN
               2014-02-05 15:00:00 113.2  NaN    NaN
               2014-02-05 15:15:00 113.2  NaN    NaN
               2014-02-05 15:30:00 113.3  113.16 113.16
               2014-02-05 15:45:00 113.3  113.19 NaN
               2014-02-05 16:00:00 113.2  113.19 NaN
               2014-02-06 09:45:00 112.6  113.16 NaN
               2014-02-06 10:00:00 113.5  113.19 NaN
               2014-02-06 10:15:00 113.8  113.25 NaN
               2014-02-06 10:30:00 113.5  113.29 NaN
               2014-02-06 10:45:00 113.7  113.32 NaN
               2014-02-06 11:00:00 113.5  113.34 Nan
  </code></pre>
  
  <p>I understand pandas has pandas.stats.moments.ewma but I prefer to use a formula I got from a book which needs close price 'at the moment' and 12ema from previous row.</p>
  
  <p>So, I tried to fill 12ema column from Feb 5, 15:45 and onward. I tried apply() with a function but shift gave an error:</p>
  
  <pre><code>    def f12ema(x):
          K = 2 / (12 + 1)
          return x['price_nom'] * K + x['12ema'].shift(-1) * (1-K)
  
      df1.apply(f12ema, axis=1)
  
      AttributeError: ("'numpy.float64' object has no attribute 'shift'", u'occurred at index 2014-02-05 11:45:00')
  </code></pre>
  
  <p>Another possibility that crossed my mind is rolling_appy() but it is beyond my knowledge.</p>