Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Swap level-one from index with column level (Pandas Multiindex Dataframe)

Tags:

python

pandas

I have a multiindexed pandas.Dataframe which is something like this:

          BAZ    PAL
Foo  Bar          
124   1    A     B
      2    C     D
134   1    E     F
      2    G     H

I need to swap level-one from index with columns in appropriate way. I need to end up with something like this:

         124 134
Coo Bar
BAZ 1    A   E
    2    C   G
PAL 1    B   F
    2    D   H
like image 630
gokhan k Avatar asked Feb 16 '16 21:02

gokhan k


People also ask

How do I move an index to a column in a DataFrame?

Convert the Index to Column using reset_index() Dataframe. reset_index() function to convert the index as a column.

How do I convert MultiIndex to single index in pandas?

To revert the index of the dataframe from multi-index to a single index using the Pandas inbuilt function reset_index(). Returns: (Data Frame or None) DataFrame with the new index or None if inplace=True.

How do I change the index level in pandas?

To change the index values we need to use the set_index method which is available in pandas allows specifying the indexes. where, inplace parameter accepts True or False, which specifies that change in index is permanent or temporary. True indicates that change is Permanent.

How do I change an index to a column?

Method 2: We can also use the Dataframe. reset_index function to convert the index as a column. The inplace parameter reflects the change in the dataframe to stay permanent.


1 Answers

You need to unstack your existing index level Foo, stack your desired column 'Coo', and then rearrange the index levels. After swapping your index levels, you probably want to sort it. As a final touch, you may want to drop the column name of all the values (val).

df = (pd.DataFrame({'Foo': [124, 124, 134, 134] * 2, 
                    'Bar': [1, 2, 1, 2] * 2, 
                    'Coo': ['BAZ'] * 4 + ['PAL'] * 4, 
                    'val': list('ACEGBDFH')})
      .set_index(['Foo', 'Bar', 'Coo'])
      .unstack('Coo'))

>>> df
        val    
Coo     BAZ PAL
Foo Bar        
124 1     A   B
    2     C   D
134 1     E   F
    2     G   H

df = df.unstack('Foo').stack('Coo')
df.index = df.index.swaplevel(0, 1)

>>> df
        val    
Foo     124 134
Coo Bar        
BAZ 1     A   E
PAL 1     B   F
BAZ 2     C   G
PAL 2     D   H

df.sort_index(inplace=True)

>>> df
        val    
Foo     124 134
Coo Bar        
BAZ 1     A   E
    2     C   G
PAL 1     B   F
    2     D   H

df.columns = df.columns.droplevel()

>>> df
Foo     124 134
Coo Bar        
BAZ 1     A   E
    2     C   G
PAL 1     B   F
    2     D   H
like image 157
Alexander Avatar answered Oct 13 '22 12:10

Alexander