How to access prior rows within a multiindex Panda dataframe
<p>How to reach within a Datetime indexed multilevel Dataframe such as the following: This is downloaded Fin data.
The tough part is getting inside the frame and accessing non adjacent rows of a particular inner level, without specifying explicitly the outer level date, since I have thousands of such rows..</p>

<pre><code>                                       ABC        DEF        GHI  \  
Date                STATS                                            
2012-07-19 00:00:00                    NaN         NaN         NaN   
                    investment        4             9          13        
                    price             5             8          1  
                    quantity          12            9          8   
</code></pre>

<p>So the 2 formulas i am searching could be summarized as  </p>

<pre><code>X(today row) = quantity(prior row)*price(prior row) 
or                           
X(today row) = quantity(prior row)*price(today)
</code></pre>

<p>The difficulty is how to formulate the access to those rows using numpy or panda for a multilevel index, and the rows are not adjacent.</p>

<p>In the end i would end up with this:</p>

<pre><code>                                         ABC        DEF        GHI    XN
Date                STATS                                            
2012-07-19 00:00:00                    NaN         NaN         NaN   
                    investment          4            9          13    X1
                    price               5            8           1   
                    quantity            12           9           8    

2012-07-18 00:00:00                    NaN         NaN         NaN   
                    investment          1             2          3    X2
                    price               2             3          4   
                    quantity           18             6          7    

X1= (18*2)+(6*3)+(7*4) (quantity_day_2 *price_day_2 data) 
or for the other formula
X1= (18*5)+(6*8)+(7*1) (quantity_day_2 *price_day_1 data)
</code></pre>

<p>Could I use a groupby?</p>

<p>If need add output to original <code>DataFrame</code>, then it is more complicated:</p>

<pre><code>print (df)
                        ABC  DEF   GHI
Date       STATS                      
2012-07-19              NaN  NaN   NaN
           investment   4.0  9.0  13.0
           price        5.0  8.0   1.0
           quantity    12.0  9.0   8.0
2012-07-18              NaN  NaN   NaN
           investment   1.0  2.0   3.0
           price        2.0  3.0   4.0
           quantity    18.0  6.0   7.0
2012-07-17              NaN  NaN   NaN
           investment   1.0  2.0   3.0
           price        0.0  1.0   4.0
           quantity     5.0  1.0   0.0
</code></pre>



<pre><code>df.sort_index(inplace=True)

#rename value in level to investment - align data in final concat
idx = pd.IndexSlice
p = df.loc[idx[:,'price'],:].rename(index={'price':'investment'})
q = df.loc[idx[:,'quantity'],:].rename(index={'quantity':'investment'})
print (p)
                       ABC  DEF  GHI
Date       STATS                    
2012-07-17 investment  0.0  1.0  4.0
2012-07-18 investment  2.0  3.0  4.0
2012-07-19 investment  5.0  8.0  1.0

print (q)
                        ABC  DEF  GHI
Date       STATS                     
2012-07-17 investment   5.0  1.0  0.0
2012-07-18 investment  18.0  6.0  7.0
2012-07-19 investment  12.0  9.0  8.0

#multiple and concat to original df
print (p * q)
                        ABC   DEF   GHI
Date       STATS                       
2012-07-17 investment   0.0   1.0   0.0
2012-07-18 investment  36.0  18.0  28.0
2012-07-19 investment  60.0  72.0   8.0
</code></pre>



<pre><code>a = (p * q).sum(axis=1).rename('col1')
print (pd.concat([df, a], axis=1))
                        ABC  DEF   GHI   col1
Date       STATS                             
2012-07-17              NaN  NaN   NaN    NaN
           investment   1.0  2.0   3.0    1.0
           price        0.0  1.0   4.0    NaN
           quantity     5.0  1.0   0.0    NaN
2012-07-18              NaN  NaN   NaN    NaN
           investment   1.0  2.0   3.0   82.0
           price        2.0  3.0   4.0    NaN
           quantity    18.0  6.0   7.0    NaN
2012-07-19              NaN  NaN   NaN    NaN
           investment   4.0  9.0  13.0  140.0
           price        5.0  8.0   1.0    NaN
           quantity    12.0  9.0   8.0    NaN
</code></pre>



<pre><code>#shift with Multiindex - not supported yet - first create Datatimeindex with unstack
#, then shift and last reshape to original by stack

#multiple and concat to original df
print (p.unstack().shift(-1, freq='D').stack() * q)
                        ABC   DEF  GHI
Date       STATS                      
2012-07-16 investment   NaN   NaN  NaN
2012-07-17 investment  10.0   3.0  0.0
2012-07-18 investment  90.0  48.0  7.0
2012-07-19 investment   NaN   NaN  NaN

b = (p.unstack().shift(-1, freq='D').stack() * q).sum(axis=1).rename('col2')
print (pd.concat([df, b], axis=1))
                        ABC  DEF   GHI   col2
Date       STATS                             
2012-07-16 investment   NaN  NaN   NaN    0.0
2012-07-17              NaN  NaN   NaN    NaN
           investment   1.0  2.0   3.0   13.0
           price        0.0  1.0   4.0    NaN
           quantity     5.0  1.0   0.0    NaN
2012-07-18              NaN  NaN   NaN    NaN
           investment   1.0  2.0   3.0  145.0
           price        2.0  3.0   4.0    NaN
           quantity    18.0  6.0   7.0    NaN
2012-07-19              NaN  NaN   NaN    NaN
           investment   4.0  9.0  13.0    0.0
           price        5.0  8.0   1.0    NaN
           quantity    12.0  9.0   8.0    NaN
</code></pre>

python pandas indexing dataframe multi-index