CODEWITHSUNDEEP
pythonpandas
Maxime
Maxime

Reputation: 634

unpivot MultiIndex DataFrame with pd.melt()

I would like to unpivot a DataFrame with MultiIndex columns, but I struggle to get the exact output I want. I played with all the parameters of the pd.melt() function but couldn't make it...

Here is the kind of input I have :

import pandas as pd
indexes = [['TC1', 'TC2'], ['x', 'z', 'Temp']]
data = pd.DataFrame(columns=pd.MultiIndex.from_product(indexes))

data.loc[0,('TC1', 'x')] = 10
data.loc[0,('TC1', 'z')] = 100
data.loc[0,('TC1', 'Temp')] = 250
data.loc[0,('TC2', 'x')] = 20
data.loc[0,('TC2', 'z')] = 200
data.loc[0,('TC2', 'Temp')] = 255

And here is the kind of output I would like, with "Time" column being the index of data

  Time   TC   x    z Temp
0    0  TC1  10  100  250
1    0  TC2  20  200  255

My real data have far more columns of kind TCx. Any clue?

Upvotes: 5

Views: 2533

Answers (2)

Sander van den Oord
Sander van den Oord

Reputation: 12808

How about:

data.stack(0).reset_index().rename(
    columns={'level_0': 'Time', 'level_1': 'TC'})

So in this case .stack() is what you need:
https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.stack.html

Upvotes: 4

Andy L.
Andy L.

Reputation: 25239

Give this a try

data_out = data.stack(level=0).rename_axis(['Time','TC']).reset_index()

Out[87]:
   Time   TC Temp   x    z
0     0  TC1  250  10  100
1     0  TC2  255  20  200

Upvotes: 7

Related Questions