Operations in multi index dataframe pandas
<p>I need to process geographic and statistical data from a big data csv. It contains data from geographical administrative and geostatistical. Municipality, Location, geostatistical basic division and block constitute the hierarchical indexes.</p>

<p>I have to create a new column ['data2'] for every element the max value of the data in the geo index, and divide each block value by that value. For each index level, and the index level value must be different from 0, because the 0 index level value accounts for other types of info not used in the calculation.</p>

<pre><code>                       data1  data2
mun  loc  geo  block
1    0    0    0       20     20
1    1    0    0       10     10
1    1    1    0       10     10   
1    1    1    1       3      3/4
1    1    1    2       4      4/4
1    1    2    0       30     30   
1    1    2    1       1      1/3
1    1    2    2       3      3/3
1    2    1    1       10     10/12
1    2    1    2       12     12/12
2    1    1    1       123    123/123
2    1    1    2       7      7/123
2    1    2    1       6      6/6
2    1    2    2       1      1/6
</code></pre>

<p>Any ideas? I have tried with for loops, converting the indexes in columns with reset_index() and iterating by column and row values but the computation is taking forever and I think that is not the correct way to do this kind of operations.</p>

<p>Also, what if I want to get my masks like this, so I can run my calculations to every level. </p>

<pre><code>mun  loc  geo  block
1    0    0    0     False       
1    1    0    0     False       
1    1    1    0     True          
1    1    1    1     False        
1    1    1    2     False        
1    1    2    0     True          
1    1    2    1     False        
1    1    2    2     False        

mun  loc  geo  block
1    0    0    0     False       
1    1    0    0     True       
1    1    1    0     False          
1    1    1    1     False        
1    1    1    2     False
1    2    0    0     True
1    2    2    0     False          
1    2    2    1     False        

mun  loc  geo  block
1    0    0    0     True       
1    1    0    0     False       
1    1    1    0     False          
1    1    1    1     False        
1    1    1    2     False
2    0    0    0     True
2    1    1    0     False          
2    1    2    1     False   
</code></pre>

<p>You can first create <code>mask</code> from <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.MultiIndex.html" rel="nofollow"><code>MultiIndex</code></a>, compare with <code>0</code> and check at least one <code>True</code> (at least one <code>0</code>) by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.any.html" rel="nofollow"><code>any</code></a>:</p>

<pre><code>mask = (pd.DataFrame(df.index.values.tolist(), index=df.index) == 0).any(axis=1)
print (mask)
mun  loc  geo  block
1    0    0    0         True
     1    0    0         True
          1    0         True
               1        False
               2        False
          2    0         True
               1        False
               2        False
     2    1    1        False
               2        False
2    1    1    1        False
               2        False
          2    1        False
               2        False
dtype: bool
</code></pre>

<p>Then get <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.GroupBy.max.html" rel="nofollow"><code>max</code></a> values by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.groupby.html" rel="nofollow"><code>groupby</code></a> per first, second and third index, but before filter by <a href="http://pandas.pydata.org/pandas-docs/stable/indexing.html#boolean-indexing" rel="nofollow"><code>boolean indexing</code></a> only values where are not <code>True</code> in <code>mask</code>:</p>

<pre><code>df1 = df.ix[~mask, 'data1'].groupby(level=['mun','loc','geo']).max()
print (df1)
mun  loc  geo
1    1    1        4
          2        3
     2    1       12
2    1    1      123
          2        6
</code></pre>

<p>Then <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reindex.html" rel="nofollow"><code>reindex</code></a> <code>df1</code> by <code>df.index</code>, remove last level of <code>Multiindex</code> by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reset_index.html" rel="nofollow"><code>reset_index</code></a>, <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.mask.html" rel="nofollow"><code>mask</code></a> values where no change by <code>mask</code> (also is necessary remove last level) and <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.fillna.html" rel="nofollow"><code>fillna</code></a> by <code>1</code>, because dividing return same value.</p>

<pre><code>df1 = df1.reindex(df.reset_index(level=3, drop=True).index)
         .mask(mask.reset_index(level=3, drop=True)).fillna(1)
print (df1)
Name: data1, dtype: int64
mun  loc  geo
1    0    0        1.0
     1    0        1.0
          1        1.0
          1        4.0
          1        4.0
          2        1.0
          2        3.0
          2        3.0
     2    1       12.0
          1       12.0
2    1    1      123.0
          1      123.0
          2        6.0
          2        6.0
Name: data1, dtype: float64
</code></pre>

<p>Last divide by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.div.html" rel="nofollow"><code>div</code></a>:</p>

<pre><code>print (df['data1'].div(df1.values,axis=0))
mun  loc  geo  block
1    0    0    0        20.000000
     1    0    0        10.000000
          1    0        10.000000
               1         0.750000
               2         1.000000
          2    0        30.000000
               1         0.333333
               2         1.000000
     2    1    1         0.833333
               2         1.000000
2    1    1    1         1.000000
               2         0.056911
          2    1         1.000000
               2         0.166667
dtype: float64
</code></pre>

python pandas