pandas, renaming a multiindex column (order of data is changed)

user_3608005 0
Viewed 3151

I do have following dataframe:

{'e1.data_280': {0: 10, 1: 20, 2: 30},
 'e1.data_603': {0: 7, 1: 8, 2: 9},
 'e2.data_280': {0: 30, 1: 20, 2: 10},
 'e2.data_603': {0: 8, 1: 9, 2: 1}}

after:

df.columns = df.columns.str.split('.', expand=True)

it looks like:

enter image description here

Now I would like to get rid of the phrase data_:

get the three digits behind the underscore:

cols = [item.split('_')[1] for item in df.columns.get_level_values(1)]
cols
['603', '280', '603', '280']

If I replace the old labels:

df.columns.set_levels(cols, level=1, inplace=True)

The data is changed:

enter image description here

I see that cols has more entries than the name of the multiindex at level 1:

MultiIndex(levels=[['e1', 'e2'], ['data_280', 'data_603']],
           labels=[[0, 0, 1, 1], [1, 0, 1, 0]])

But how can I rename the first level of multiindex columns in a dataframe ?

EDIT: A workaround

df.unstack().reset_index()

together with renaming the column and splitting the column values works:

multi-index pandas python
2 Answers

Setup 

df = pd.DataFrame({
    'e1.data_280': {0: 10, 1: 20, 2: 30},
    'e1.data_603': {0: 7, 1: 8, 2: 9},
    'e2.data_280': {0: 30, 1: 20, 2: 10},
    'e2.data_603': {0: 8, 1: 9, 2: 1}})

Option 1
Easiest thing would've been to include that in your first split.

df = pd.DataFrame({
    'e1.data_280': {0: 10, 1: 20, 2: 30},
    'e1.data_603': {0: 7, 1: 8, 2: 9},
    'e2.data_280': {0: 30, 1: 20, 2: 10},
    'e2.data_603': {0: 8, 1: 9, 2: 1}})

df.columns = df.columns.str.split('.data_', expand=True)

df

   e1      e2    
  280 603 280 603
0  10   7  30   8
1  20   8  20   9
2  30   9  10   1

Option 2
After the fact, you could do 

df.rename(columns=lambda x: x.replace('data_', ''))

   e1      e2    
  280 603 280 603
0  10   7  30   8
1  20   8  20   9
2  30   9  10   1

You can even narrow the scope of rename by passing a level. This ensures we don't perform the replace on level=0 column objects. 

df.rename(columns=lambda x: x.replace('data_', ''), level=1)

   e1      e2    
  280 603 280 603
0  10   7  30   8
1  20   8  20   9
2  30   9  10   1
user_23366540

You can use cols = [item.split('_')[1] for item in df.columns.levels[1]]; this will preserve the alignment.

user_45859630
Related