Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to melt first level column in multiindex with pandas

Tags:

python

pandas

I have this multiindex dataframe:

df=pd.DataFrame(np.zeros((3,6)))
df.columns=pd.MultiIndex.from_arrays([['a','a','b','b','c','c'],[1,2,1,2,1,2]])
df['a']=10
df['b']=20
df['c']=40

print(df)

Out[10]: 
    a       b       c    
    1   2   1   2   1   2
0  10  10  20  20  40  40
1  10  10  20  20  40  40
2  10  10  20  20  40  40

And i want to get this:

Out[10]: 

   names  1   2
0      a  10  10
1      a  10  10
2      a  10  10
0      b  20  20
1      b  20  20
2      b  20  20
0      c  40  40
1      c  40  40
2      c  40  40

I know that I can separate each column first level in a dataframe and then do an append, but I'm looking for a better way to do it.

I've tried to do it with the command melt but I always get an error.

Any ideas?

like image 793
Mateo Rod Avatar asked Jun 28 '18 08:06

Mateo Rod


People also ask

How do you melt a column in Pandas?

Pandas melt() function is used to change the DataFrame format from wide to long. It's used to create a specific format of the DataFrame object where one or more columns work as identifiers. All the remaining columns are treated as values and unpivoted to the row axis and only two columns - variable and value.

How do I drop one level of MultiIndex Pandas?

Drop Level Using MultiIndex.droplevel() to drop columns level. When you have Multi-level columns DataFrame. columns return MultiIndex object and use droplevel() on this object to drop level.

How do I reorder MultiIndex columns in Pandas?

To rearrange levels in MultiIndex, use the MultiIndex. reorder_levels() method in Pandas. Set the order of levels using the order parameter.


1 Answers

Use unstack:

print(df.unstack()
        .unstack(level=1)
        .reset_index(level=1, drop=True)
        .rename_axis('names')
        .reset_index())
  names   1   2
0     a  10  10
1     a  10  10
2     a  10  10
3     b  20  20
4     b  20  20
5     b  20  20
6     c  40  40
7     c  40  40
8     c  40  40
like image 148
jezrael Avatar answered Nov 01 '22 08:11

jezrael