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)
CodePudding user response:
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