How to set values based on a condition on a subset of MultiIndex pandas dataframe

Question

I want to take a subset of a MultiIndex pandas dataframe, test for values less than zero and set them to zero.

For example:

df = pd.DataFrame({('A','a'): [-1,-1,0,10,12],
                   ('A','b'): [0,1,2,3,-1],
                   ('B','a'): [-20,-10,0,10,20],
                   ('B','b'): [-200,-100,0,100,200]})

df[df['A']<0] = 0.0

gives

    A        B
    a   b    a     b
0  -1   0  -20  -200
1  -1   1  -10  -100
2   0   2    0     0
3  10   3   10   100
4  12  -1   20   200

Which shows that it was not able to set based on the condition. Alternatively if I do chained assignment:

df.loc[:,'A'][df['A']<0] = 0.0

this gives the same result (and setting with copy warning).

I could loop through each column based on the condition that the first level is the one that I want:

for one,two in df.columns.values:
    if one == 'A':
        df.loc[df[(one,two)]<0, (one,two)] = 0.0

which gives the desired result:

    A       B
    a  b    a     b
0   0  0  -20  -200
1   0  1  -10  -100
2   0  2    0     0
3  10  3   10   100
4  12  0   20   200

What is the best way to do this in pandas?

score 20 · Accepted Answer · answered Jan 17 '15 at 17:38

This is an application of (and one of the main motivations for using MultiIndex slicers), see docs here

In [20]: df = pd.DataFrame({('A','a'): [-1,-1,0,10,12],
                   ('A','b'): [0,1,2,3,-1],
                   ('B','a'): [-20,-10,0,10,20],
                   ('B','b'): [-200,-100,0,100,200]})

In [21]: df
Out[21]: 
    A      B     
    a  b   a    b
0  -1  0 -20 -200
1  -1  1 -10 -100
2   0  2   0    0
3  10  3  10  100
4  12 -1  20  200

In [22]: idx = pd.IndexSlice

In [23]: mask = df.loc[:,idx['A',:]]<0

In [24]: mask
Out[24]: 
       A       
       a      b
0   True  False
1   True  False
2  False  False
3  False  False
4  False   True

In [25]: df[mask] = 0

In [26]: df
Out[26]: 
    A      B     
    a  b   a    b
0   0  0 -20 -200
1   0  1 -10 -100
2   0  2   0    0
3  10  3  10  100
4  12  0  20  200

Since you are working with the 1st level of the columns index, the following will work as well. The above example is more general, say you wanted to do this for 'a'.

In [30]: df[df[['A']]<0] = 0

In [31]: df
Out[31]: 
    A      B     
    a  b   a    b
0   0  0 -20 -200
1   0  1 -10 -100
2   0  2   0    0
3  10  3  10  100
4  12  0  20  200

Ah okay thanks! Using the slicers to create a mask looks really useful (may have to use this in more of my code). The second example does solve my specific problem. I wasn't aware of the difference between `df['A']` and `df[['A']]` — pbreach, Jan 17 '15 at 17:44

score 0 · Answer 2 · answered Apr 17 '23 at 18:25

You can also use the built-in slice() function. For example, to select 'A' columns, make a tuple (pandas interprets a tuple as MultiIndex) with slice(None) to indicate that you want everything in the second level (('A', slice(None))).

df = pd.DataFrame({
    ('A','a'): [-1,-1,0,10,12], ('A','b'): [0,1,2,3,-1], 
    ('B','a'): [-20,-10,0,10,20],('B','b'): [-200,-100,0,100,200]})


# replace every value in the 'A' columns less than 0 by 0
msk = df.loc[:, ('A', slice(None))] < 0
df[msk] = 0

To do the select columns in the second level; for example, select all 'a' columns, use (slice(None), 'a') where slice(None) indicates you don't care what's in the first level.

# replace every value in the 'a' columns less than 0 by 0
msk = df.loc[:, (slice(None), 'a')] < 0
df[msk] = 0

How to set values based on a condition on a subset of MultiIndex pandas dataframe

2 Answers2

Linked