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