Replace operation on a dataframe MultiIndex

Question

I have two dataframes where I am trying to replace substring on level1 of multi index with another substring but this fails

For example I have a dataframe df

Index0   Index1    0     1     2
A        BX       .2    .3    .9      
         CX       .34   .55   .54           

D        EX       .34   .44   .32
         FX       .43.  .88.  .06

I am trying to replace the Index1 substring X by Y so that my result
looks like as follows

Index0   Index1    0     1     2
A        BY       .2    .3    .9      
         CY       .34   .55   .54           

D        EY       .34   .44   .32
         FY       .43.  .88.  .06

I am using the following function

df.replace('X','Y')

however i get the following error

AttributeError                   Traceback (most recent   call last)
<ipython-input-56-fc7014a2d950> in <module>()
  8 
  9 
---> 10 df.replace('X','Y')

AttributeError: 'MultiIndex' object has no attribute 'replace'

please add your code for creating df. Really, df looks like an Index — Oleh Rybalchenko
– Oleh Rybalchenko, Commented Sep 7, 2017 at 20:16
df is a dataframe. and Index0 and Index1 are the Index of df dataframe. — abhi_phoenix
– abhi_phoenix, Commented Sep 7, 2017 at 20:17
AttributeError Traceback (most recent call last) <ipython-input-56-fc7014a2d950> in <module>() 8 9 ---> 10 df.replace('X','Y') AttributeError: 'MultiIndex' object has no attribute 'replace' — abhi_phoenix
– abhi_phoenix, Commented Sep 7, 2017 at 20:19
Yes, and I want to modify the Multiindex substring with another substring as highlighted above but I am unable to do it and hence my question. — abhi_phoenix
– abhi_phoenix, Commented Sep 7, 2017 at 20:21

Brad Solomon · Accepted Answer · 2017-09-07 21:13:48Z

3

@cᴏʟᴅsᴘᴇᴇᴅ improved on my answer so I will leave just a slower alternate here...

import numpy as np
df = pd.DataFrame(np.random.randn(4,3), 
                  index=[list('aabb'), [n + 'X' for n in list('abcd')]])

Here's an alternate method using reset_index. This would be applicable if you wanted to replace in more than one column. The trick is that you can't use replace on the Index so you have to "bring it into" the DataFrame.

new = (df.reset_index()
           .select_dtypes(include=['object'])
           .apply(lambda col: col.str.replace('X', 'Y')))

df.index = pd.MultiIndex.from_tuples(new.values.tolist())

edited Sep 7, 2017 at 21:13

answered Sep 7, 2017 at 20:21

Brad Solomon

41.2k39 gold badges167 silver badges261 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jdg Over a year ago

Good approach - I was playing with one that used reset_index - pushing the index into editable dataframe columns - then using set_index to push those values back into the index. This is less invasive; more elegant.

cs95 Over a year ago

I don't think using a series just to do a simple replacement is the best way to do this.

Brad Solomon Over a year ago

@Coldspeed no but isn't that catchy??

BENY · Accepted Answer · 2017-09-07 21:16:56Z

3

Or Try this

df.index=pd.MultiIndex.from_tuples([(x[0], x[1].replace('X', 'Y')) for x in df.index])
df
Out[304]: 
             0         1         2
a aY -0.696181 -1.929523 -1.903956
  bY  0.071061 -0.594185 -2.005251
b cY -0.097761  0.093667  1.780550
  dY  0.127887  1.534395  0.352351

answered Sep 7, 2017 at 21:16

BENY

324k22 gold badges176 silver badges250 bronze badges

2 Comments

cs95 Over a year ago

There is a loop here. But still, better.

BND Over a year ago

The only solution I could understand ;-)

cs95 · Accepted Answer · 2017-09-07 21:04:18Z

2

You're doing more than you need to.

df 
                  0     1     2
Index0 Index1                  
A      BX        .2    .3  0.90
       CX       .34   .55  0.54
D      EX       .34   .44  0.32
       FX      .43.  .88.  0.06

Use pd.MultiIndex.from_arrays and you can do this in one step.

df.index = pd.MultiIndex.from_arrays([df.index.get_level_values(0),
                                       df.index.levels[1].str.replace('X', 'Y')])

df
                  0     1     2
Index0 Index1                  
A      BY        .2    .3  0.90
       CY       .34   .55  0.54
D      EY       .34   .44  0.32
       FY      .43.  .88.  0.06

Performance

%%timeit
new = (df.reset_index()
            .select_dtypes(include=['object'])
            .apply(lambda col: col.str.replace('X', 'Y')))

df.index = pd.MultiIndex.from_tuples(new.values.tolist())

10 loops, best of 3: 93.5 ms per loop

Nearly 100ms for a tiny dataframe. Contrast with:

%%timeit
df.index = pd.MultiIndex.from_arrays([df.index.get_level_values(0),
                                        df.index.levels[1].str.replace('X', 'Y')])

1000 loops, best of 3: 934 µs per loop

edited Sep 7, 2017 at 21:04

answered Sep 7, 2017 at 21:01

cs95

406k106 gold badges744 silver badges798 bronze badges

1 Comment

Brad Solomon Over a year ago

You are right, thought str.replace could not operate on Index. This should be the accepted answer then @user2560244

Collectives™ on Stack Overflow

Replace operation on a dataframe MultiIndex

3 Answers 3

3 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related