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>You can use:</p>

<pre><code>#add new datetime with data for better testing
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>#lexsorted Multiindex           
df.sort_index(inplace=True)

#select data and remove last level, because:
#1. need shift
#2. easier working
idx = pd.IndexSlice
p = df.loc[idx[:,'price'],:]
p.index = p.index.droplevel(-1)
q = df.loc[idx[:,'quantity'],:]
q.index = q.index.droplevel(-1)
print (p)
            ABC  DEF  GHI
Date                     
2012-07-17  0.0  1.0  4.0
2012-07-18  2.0  3.0  4.0
2012-07-19  5.0  8.0  1.0

print (q)
             ABC  DEF  GHI
Date                      
2012-07-17   5.0  1.0  0.0
2012-07-18  18.0  6.0  7.0
2012-07-19  12.0  9.0  8.0
</code></pre>



<pre><code>print (p * q)
             ABC   DEF   GHI
Date                        
2012-07-17   0.0   1.0   0.0
2012-07-18  36.0  18.0  28.0
2012-07-19  60.0  72.0   8.0

print ((p * q).sum(axis=1).to_frame().rename(columns={0:'col1'}))
             col1
Date             
2012-07-17    1.0
2012-07-18   82.0
2012-07-19  140.0
</code></pre>



<pre><code>#shift row with -1, because lexsorted df
print (p.shift(-1, freq='D') * q)
             ABC   DEF  GHI
Date                       
2012-07-16   NaN   NaN  NaN
2012-07-17  10.0   3.0  0.0
2012-07-18  90.0  48.0  7.0
2012-07-19   NaN   NaN  NaN

print ((p.shift(-1, freq='D') * q).sum(axis=1).to_frame().rename(columns={0:'col2'}))
             col2
Date             
2012-07-16    0.0
2012-07-17   13.0
2012-07-18  145.0
2012-07-19    0.0
</code></pre>

python pandas indexing dataframe multi-index