Assigning columns to a hierarchical higher Multiindex level

user_12533570 0
Viewed 123

I have a dataframe that looks somewhat like this:

   Ax  Ay  Bx  By
0   1  20   2  20
1   3  21   5  22
2   4  20   7  25

and I want to manually define that Ax and Ay have the hierarchical higher MultiIndex level of A (and also do the same for B). So that the dataframe looks like this:

   A      B
   x   y  x   y
0  1  20  2  20
1  3  21  5  22
2  4  20  7  25

So what I'm looking for is a statement that says:

Ax and Ay go under A as x and y

multi-index pandas dataframe python
3 Answers

Via split() and droplevel():

df.columns=df.columns.str.split('([A-Z])',expand=True).droplevel(0)

OR

Via pd.MultiIndex.from_arrays():

df.columns=pd.MultiIndex.from_arrays([['A','A','B','B'],['x','y','x','y']])

Output of df:

   A      B
   x   y  x   y
0  1  20  2  20
1  3  21  5  22
2  4  20  7  25
user_142898920

You can manually construct a pandas.MultiIndex using one of several constructors. From the docs:

All of these are useful and I'd check them each out to understand their use cases. For your case, I think pd.MultiIndex.from_tuples might do the trick:

In [4]: list_of_split_tuples = list(map(tuple, df.columns.values))

In [5]: df.columns = pd.MultiIndex.from_tuples(list_of_split_tuples)

In [6]: df
Out[6]:
   A      B
   x   y  x   y
0  1  20  2  20
1  3  21  5  22
2  4  20  7  25
user_38887190

Let's use regex and split with expand=True parameter:

df.columns = df.columns.str.split('(?<=[A-Z])', expand=True)

Output:

   A      B    
   x   y  x   y
0  1  20  2  20
1  3  21  5  22
2  4  20  7  25
user_63615310
Related