pandas Conditional update with multiindex

Question

Say I have following DataFrame with multiple index on both index and columns.

 first   x    y
 second  m    n
A  B
A0 B0    0    0
   B1    0    0
A1 B0    0    0
   B1    0    0

I'm trying to update the values with the condition.The condition will be something like: `rules:

[{condition:{'A':'A0,'B':'B0'},value:5},
  {condition:{'B':'B1'},value:3},
 .....]

I'm trying to find something that has similar functionality to

Use pandas.DataFrame.xs for setting value:

for each rule in rules:
    df.xs((conditions.values), level=[conditions.keys]) = value

Pass more than one level to pandas.Index.get_level_values for setting value:

for each rule in rules:
    df.loc[df.index.get_level_values(conditions.keys) == [conditions.values] = value

The result should be

 first   x    y
 second  m    n
A  B
A0 B0    5    5
   B1    3    3
A1 B0    0    0
   B1    3    3`

score 1 · Accepted Answer · answered May 12 '18 at 07:33

Unfortunately selection by dictionary in MultiIndex in pandas is yet not supported, so need custom function adapted for you:

rules = [{'condition':{'A':'A0','B':'B0'},'value':5},
         {'condition':{'B':'B1'},'value':3}]

for rule in rules:
    d = rule['condition']
    indexer = [d[name] if name in d else slice(None) for name in df.index.names]
    df.loc[tuple(indexer),] = rule['value']

print (df)
first   x  y
second  m  n
A  B        
A0 B0   5  5
   B1   3  3
A1 B0   0  0
   B1   3  3

pandas Conditional update with multiindex

1 Answers1