How can I calculate a simple moving average given my dataframe characteristics with Pandas

Question

Using Python 3.6

I have a Pandas dataframe converted from a panel using the following code:

stocks = ['AAPL', 'JPM', 'JNJ', 'PG', 'XOM', 'VZ', 'IEF']
pnl = web.get_data_yahoo(stocks, start, end, interval = 'd')
df = pnl.to_frame().unstack(level=1)
df.columns = df.columns.swaplevel(0,1)

If I then run df in the console it returns in this format (with 'Open', 'Low', 'Adj Close', etc):

minor             IEF         JNJ         JPM          PG          VZ  
               Volume      Volume      Volume      Volume      Volume   
Date                                                                    
2017-11-17   941200.0   4112100.0   8989200.0   5919900.0  18732200.0   
2017-11-16  1749200.0   4193800.0   9808000.0   7205100.0  13818000.0   
2017-11-15  1933100.0   4811500.0  15384100.0   6688100.0  15726500.0

Now, the problem I am running into arises when I try and calculate a simple moving average, for let's say AAPL, using: df['30sma_AAPL'] = df['AAPL']['Adj Close'].rolling(window=30, min_periods=0).mean().

This command adds the new column, but doesn't put the values within the df['AAPL'] group (I don't know if group is the correct word?). For example, if I run df['AAPL'], it outputs the 'Adj Close', 'Close', 'High', etc. for AAPL specifically. Basically, I want to have a loop that calculates the sma for each stock and puts the values into their group. Is this possible with this dataframe?

EDIT: My desired output (fake #'s):

minor             IEF         JNJ         JPM          PG          VZ  
               Volume      Volume      Volume      Volume      Volume   
Date                                                                    
2017-11-17   941200.0   4112100.0   8989200.0   5919900.0  18732200.0   
2017-11-16  1749200.0   4193800.0   9808000.0   7205100.0  13818000.0   
2017-11-15  1933100.0   4811500.0  15384100.0   6688100.0  15726500.0 


minor             IEF         JNJ         JPM          PG          VZ  
               30_sma      30_sma      30_sma      30_sma      30_sma   
Date                                                                    
2017-11-17      100.0          50          25          26          45   
2017-11-16         25          40         100          89         100   
2017-11-15         75          30          12          75          60

I think I understand what you're asking but could you give an example of expected output Please? — roganjosh, Nov 19 '17 at 03:20

Scott Boston · Answer 1 · 2017-11-21T14:03:49.403

IIUC, I think you want something like this using tuple slicing from MultiIndex columns.

df.loc[:,(slice(None),'Adj Close')].rolling(window=30, min_periods=0)\
  .mean()\
  .rename(columns={'Adj Close':'Moving Avg Adj Close'})

Edit Another syntax (from SO Post by @TedPetrou) for selecting from a multiindex:

df.loc(axis=1)[:,'Adj Close'].rolling(window=30, min_periods=0)\
  .mean()\
  .rename(columns={'Adj Close':'Moving Avj Close'})

Output:

    minor                      AAPL                  IEF                  JNJ  \
           Moving Avg Adj Close Moving Avg Adj Close Moving Avg Adj Close   
Date                                                                        
2017-11-17           170.149994           106.180000           138.000000   
2017-11-16           170.625000           106.110001           138.434998   
2017-11-15           170.110001           106.176666           138.656667   
2017-11-14           170.417500           106.115000           138.865002   
2017-11-13           171.128000           106.048000           139.044000   

minor                       JPM                   PG                   VZ  \
           Moving Avg Adj Close Moving Avg Adj Close Moving Avg Adj Close   
Date                                                                        
2017-11-17            98.139999            88.430000            45.419998   
2017-11-16            98.305000            88.840000            45.094999   
2017-11-15            98.266667            88.636668            44.766666   
2017-11-14            98.017500            88.695002            44.630000   
2017-11-13            97.986000            88.756001            44.654000   

minor                       XOM  
           Moving Avg Adj Close  
Date                             
2017-11-17            80.239998  
2017-11-16            80.399998  
2017-11-15            80.669998  
2017-11-14            81.062498  
2017-11-13            81.427998

So those are the rolling moving averages? Is there anyway to title them "moving_avg" or something instead of "Adj Close" to avoid confusion? — NaN, Nov 20 '17 at 13:08
@NaN Yes, you can use rename to rename your column as I have modified the solution above to demonstrate. — Scott Boston, Nov 20 '17 at 14:56
I'm not sure of the syntax you used, specifically the \'s, but when I type this code in it gives the error: `MultiIndex Slicing requires the index to be fully lexsorted tuple len (2), lexsort depth (0)` — NaN, Nov 20 '17 at 23:54

How can I calculate a simple moving average given my dataframe characteristics with Pandas

1 Answers1

Edit Another syntax (from SO Post by @TedPetrou) for selecting from a multiindex: