Skip to content
Advertisement

How to create a new index level with columns name in Pandas?

I would like to add a new level to the index of a dataframe, based on columns name. How can i do that ?

df
    home        city
A   -0.166809   0.213299
B   -0.040300   0.034583
C   -0.002245   0.001058

Desired result

A   home  -0.166809
    city   0.213299
B   home  -0.040300
    city   0.034583
C   home  -0.002245
    city   0.001058

I can build the multiindex with this but how can i create a new dataframe and map the data ?

idx = pd.MultiIndex.from_product ([df.index, df.columns])
pd.DataFrame(df.values, ix)

ValueError: Shape of passed values is (3, 2), indices imply (6, 2)

Advertisement

Answer

You can use df.stack()

print(df.stack())

A  home   -0.166809
   city    0.213299
B  home   -0.040300
   city    0.034583
C  home   -0.002245
   city    0.001058
dtype: float64

Back to your code, you can try numpy.ravel to flatten the df.values to match the idx shape (idx has 6 items, df.values only return rows which are 3)

idx = pd.MultiIndex.from_product([df.index, df.columns])
df = pd.DataFrame(df.values.ravel(), idx)
print(df)

               0
A home -0.166809
  city  0.213299
B home -0.040300
  city  0.034583
C home -0.002245
  city  0.001058
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement