New level in MultiIndex DataFrame based on existing column level values

Question

Let's say I have a DataFrame like this:

df = pd.DataFrame(data = [[1,2,3,4,5,6], [3,4,5,6,7,8]], 
                  columns = pd.MultiIndex.from_product([('A1', 'B1', 'A2'), (10,20)], names=['level_0','level_1']))

Here's how it looks like: DataFrame image

I want to add a new level in the columns which contains 1 where level_0 value contains "1" and and 2 where level_0 value contains "2". So, basically:

Where level_0 == "A1" --> new_level = 1
Where level_0 == "B1" --> new_level = 1
Where level_0 == "A2" --> new_level = 2

Any suggestions on how to do it?

jezrael · Accepted Answer · 2022-02-15 08:48:44Z

1

Use lsit comprehension for extract number from first level values and create new MultiIndex by MultiIndex.from_tuples:

import re

df.columns = pd.MultiIndex.from_tuples([(re.findall(r'(\d+)$', x[0])[0], *x) 
                                         for x in df.columns.tolist()], 
                                       names=('new_level',*df.columns.names))
print (df)

new_level  1           2   
level_0   A1    B1    A2   
level_1   10 20 10 20 10 20
0          1  2  3  4  5  6
1          3  4  5  6  7  8

answered Feb 15, 2022 at 8:48

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

2 Comments

gioarma Over a year ago

Nice, thanks! Can I ask what *x is used for?

jezrael Over a year ago

@gioarma - it is for unpack tuples, check this

mozway · Accepted Answer · 2022-02-15 08:53:44Z

1

You could extract the values with a regex ((\d+)$ = last digits of the value) and rework the MultiIndex with MultiIndex.from_arrays:

values = df.columns.get_level_values('level_0').str.extract('(\d+)$', expand=False)
# ['1', '1', '1', '1', '2', '2']

df.columns = pd.MultiIndex.from_arrays([*zip(*df.columns.to_list()), values],
                                       names=[*df.columns.names, 'level_2']
                                      )

NB. this generalizes to any XXX00 value

output:

level_0 A1    B1    A2   
level_1 10 20 10 20 10 20
level_2  1  1  1  1  2  2
0        1  2  3  4  5  6
1        3  4  5  6  7  8

edited Feb 15, 2022 at 8:53

answered Feb 15, 2022 at 8:48

mozway

267k13 gold badges56 silver badges106 bronze badges

Collectives™ on Stack Overflow

New level in MultiIndex DataFrame based on existing column level values

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related