Pandas Subset Secondary DataFrame Index and Remapping Values

Question

This may be a two-part question, but I'm looking for the best way to rescale (or perform any operation) in a subset of the records identified by their secondary index.

For example, let's say I have the following data frame:

>>> df=pd.DataFrame(data=[[1,2,3],[.4,.5,.6],[7,8,9],[.10,.11,.12]], index=pd.MultiIndex.from_tuples([(1,'a'), (1,'b'), (2,'a'), (2,'b')]), columns=['Var1','Var2','Var3'])
>>> df.index.names=['Number','Letter']
>>> print df
               Var1  Var2  Var3
Number Letter                  
1      a        1.0  2.00  3.00
       b        0.4  0.50  0.60
2      a        7.0  8.00  9.00
       b        0.1  0.11  0.12

I want the two entries indicated by the letter “b” to have all 3 of the variables multiplied by 10.

The first aspect I'm struggling with is how to choose the second multi-index. I can do this with the following messy workaround, but I would suggest that there is a cleaner way:

>>> df=df.reset_index().set_index(['Letter','Number'])
>>> Records=df.loc['b']
>>> print Records
        Var1  Var2  Var3
Number                  
1        0.4  0.50  0.60
2        0.1  0.11  0.12

Any suggestions on a better way to subset the second index?

And then I can rescale them:

>>> print Records*10
        Var1  Var2  Var3
Number                  
1          4     5     6
2         10    11    12

However, how do I replace the original values ​​with these newly changed values?

Answer 1

Pandas MultiIndex :

df.loc[df.index.isin("b", level="Letter")]
               Var1  Var2  Var3
Number Letter                  
1      b        0.4  0.50  0.60
2      b        0.1  0.11  0.12

df.xs("b", level="Letter")
        Var1  Var2  Var3
Number                  
1        0.4  0.50  0.60
2        0.1  0.11  0.12

, ( loc , ):

df.loc[df.index.isin("b", level="Letter")] = df.loc[df.index.isin("b", level="Letter")]*10

df
               Var1  Var2  Var3
Number Letter                  
1      a          1   2.0   3.0
       b          4   5.0   6.0
2      a          7   8.0   9.0
       b          1   1.1   1.2

, :

df.loc[df.index.isin("b", level="Letter"), "Var3"] = "Foo"
df

               Var1  Var2 Var3
Number Letter                 
1      a          1   2.0    3
       b          4   5.0  Foo
2      a          7   8.0    9
       b          1   1.1  Foo

,

Answer 2

10 , 2- 'b'?:

In [82]:

print pd.DataFrame(data=df.values*np.where(df.index.get_level_values(1) == 'a', 1, 10).reshape((-1,1)), 
                   index=df.index)
               0    1    2
Number Letter             
1      a       1  2.0  3.0
       b       4  5.0  6.0
2      a       7  8.0  9.0
       b       1  1.1  1.2

:

In [94]:

print (df.T * np.where(df.index.get_level_values(1) == 'a', 1, 10)).T
               Var1  Var2  Var3
Number Letter                  
1      a          1   2.0   3.0
       b          4   5.0   6.0
2      a          7   8.0   9.0
       b          1   1.1   1.2

Answer 3

I would go through stack to make the multi-index level first, and then cut it:

    In [72]: df=pd.DataFrame(data=[[1,2,3],[.4,.5,.6],[7,8,9],[.10,.11,.12]], index=pd.MultiIndex.from_tuples([(1,'a'), (1,'b'), (2,'a'), (2,'b')]),         columns=['Var1','Var2','Var3'])        

    In [73]: df
    Out[73]: 
         Var1  Var2  Var3
    1 a   1.0  2.00  3.00
      b   0.4  0.50  0.60
    2 a   7.0  8.00  9.00
      b   0.1  0.11  0.12

    In [89]: df1 = df.unstack(-2) # the same as level=0
    In [90]: df1
    Out[90]: 
       Var1       Var2        Var3      
          1    2     1     2     1     2
    a   1.0  7.0   2.0  8.00   3.0  9.00
    b   0.4  0.1   0.5  0.11   0.6  0.12

    In [91]: df1.loc['a']*=10
    In [92]: df1
    Out[92]: 
       Var1        Var2         Var3       
          1     2     1      2     1      2
    a  10.0  70.0  20.0  80.00  30.0  90.00
    b   0.4   0.1   0.5   0.11   0.6   0.12

    df = df1.stack().swaplevel(0,1) # return back to the multi-index