2

I can't find this in the Pandas docs or SO, but how do I move some columns from level 0 in a Pandas dataframe with multi-indexed columns to level 1? I don't want to swap them or re-order them, I just want to move selected columns from level 0 to level 1, so that those columns end up being replicated in level 1. So the dataframe I have originally looks like this

A      B     C     X                                    Y           Z
                   X1               X2                  Y1          Z1
a      b     c     x1               x2                  y1          z1
..     ..    ..    ..               ..                  ..          ..

I want to move the columns A, B and C to replicate across the levels X, Y and Z to produce something like this

X                         Y                     Z
X1    X2    A    B    C   Y1    A    B    C     Z1    A    B    C
x1    x2    a    b    c   y1    a    b    c     z1    a    b    c
..    ..    ..   ..   ..  ..    ..   ..   ..    ..    ..   ..   ..

I tried to do this in a simple way by looping over X, Y and Z and assigning the A, B and C columns to each, but this does not work for me. So this below does not work.

    for level in ['X', 'Y', 'Z']:
        df[level] = df[level].assign(A=df['A'], B=df['B'], C=df['C']

To reproduce the original example dataframe above use this code

import pandas as pd

data = [['a', 'b', 'c', 'x1', 'x2', 'y1', 'z1']]

columns = pd.MultiIndex.from_tuples(
    [
        ('A',''),
        ('B', ''),
        ('C', ''),
        ('X', 'X1'), ('X', 'X2'),
        ('Y', 'Y1'),
        ('Z', 'Z1')
    ]
)

df = pd.DataFrame(data=data, columns=columns)

So the input dataframe (with just one data row for simplicity) looks as below.

>>> df
   A  B  C   X       Y   Z
            X1  X2  Y1  Z1
0  a  b  c  x1  x2  y1  z1
srm
  • 548
  • 1
  • 4
  • 19
  • 1
    Please provide a sample data. Since your data has multiIndex, it's best if you provide the code to replicate/generate your data. – Quang Hoang Nov 05 '20 at 16:08
  • I cannot share the data. It should be clear from the pattern in the example what is being attempted - moving columns from one level to another. The actual data doesn't matter, it could be anything, strings, numbers etc. – srm Nov 05 '20 at 16:10
  • I'm not asking you to **share** your data. I'm asking you to provide the code to generate **mock data**. – Quang Hoang Nov 05 '20 at 16:10
  • Please refer to [this question](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples). – Quang Hoang Nov 05 '20 at 16:12
  • 1
    I've added the example dataframe creation code above. – srm Nov 05 '20 at 16:25

1 Answers1

1

You can do it this way:

move = ['A', 'B', 'C']
keep = ['X', 'Y', 'Z']
for item in keep:
    for key in move:
        df[(item, key)] = df[key]
df = df.drop(move, axis=1)
df

which will result in:

dataframe_output

If you add one more line before you print the data frame, you get the result you are looking for:

df.sort_index(1, level=0, inplace=True)
df

enter image description here

mosc9575
  • 5,618
  • 2
  • 9
  • 32
  • OK, looks like this appends `A`, `B`, `C` to the ends of each section, but I was looking for a more built-in approach. – srm Nov 05 '20 at 17:02
  • The row sorting does not match the column sorting. – srm Nov 05 '20 at 17:31