marco marco - 1 month ago 24
Python Question

Operations in multi index dataframe pandas

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.

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.

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


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.

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

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

Answer

You can first create mask from MultiIndex, compare with 0 and check at least one True (at least one 0) by any:

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

Then get max values by groupby per first, second and third index, but before filter by boolean indexing only values where are not True in mask:

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

Then reindex df1 by df.index, remove last level of Multiindex by reset_index, mask values where no change by mask (also is necessary remove last level) and fillna by 1, because dividing return same value.

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

Last divide by div:

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
Comments