How to change a Pandas DataFrame into a column Multi-Column?

Question

I have a Pandas DataFrame with a column index like the one below:

+----+----+----+----+----+----+
|  A1|  A2|  A3|  B1|  B2|  B3|
+----+----+----+----+----+----+
...the data

What I would like to do is to change the column index of this DataFrame to a multi-index one, as shown in the format below, without modifying the data and just simply adding an upper level in the index (with values A and B).

+--------------+--------------+
|        A     |        B     |
+----+----+----+----+----+----+
|  A1|  A2|  A3|  B1|  B2|  B3|
+----+----+----+----+----+----+
...the data

I have tried to use the pandas.MultiIndex function but with no luck. How can this be solved?

So using pd.MultiIndex.from_arrays is necessary? Not df.columns = [df.columns.str[0], df.columns] ? — jezrael
– jezrael, Commented Sep 20, 2021 at 8:48

Mortz · Accepted Answer · 2021-09-20 08:19:34Z

4

You could extract the first letter separately and create a MultiIndex -

multi_index_level_0 = [c[0] for c in df.columns]
multi_index = [multi_index_level_0, df.columns.values]
df.columns = pd.MultiIndex.from_arrays(multi_index)

answered Sep 20, 2021 at 8:19

Mortz

4,9591 gold badge23 silver badges39 bronze badges

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

Comments

jezrael · Accepted Answer · 2021-09-20 08:13:03Z

Simpliest is extract first value of columns and assign back in nested lists:

df = pd.DataFrame(0, columns=['A1','A2','A3','B1','B2','B3'], index=[0])

df.columns = [df.columns.str[0], df.columns]
print (df)
   A        B      
  A1 A2 A3 B1 B2 B3
0  0  0  0  0  0  0

If need extract all uppercases from start:

df = pd.DataFrame(0, columns=['ADa1','ADs2','AD3','B1','B2','B3'], index=[0])

df.columns = [df.columns.str.extract('(^[A-Z]+)', expand=False), df.columns]
print (df)

    AD           B      
  ADa1 ADs2 AD3 B1 B2 B3
0    0    0   0  0  0  0

If need set also colums names use MultiIndex.from_arrays:

df = pd.DataFrame(0, columns=['ADa1','ADs2','AD3','B1','B2','B3'], index=[0])

df.columns = pd.MultiIndex.from_arrays([df.columns.str.extract('(^[A-Z]+)', expand=False), 
                                       df.columns], 
                                       names=('a','b'))
print (df)

a   AD           B      
b ADa1 ADs2 AD3 B1 B2 B3
0    0    0   0  0  0  0

rhug123 · Accepted Answer · 2023-03-13 16:34:59Z

0

Here is an option using map

df.set_axis(df.columns.map(lambda x: (x[0],x)),axis=1)

Output:

   A        B      
  A1 A2 A3 B1 B2 B3
0  0  0  0  0  0  0

answered Mar 13, 2023 at 16:34

rhug123

8,8801 gold badge14 silver badges27 bronze badges

Collectives™ on Stack Overflow

How to change a Pandas DataFrame into a column Multi-Column?

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related