I have the following dataframe that I am trying to melt:
import numpy as np
import pandas as pd
dates = pd.date_range('1/1/2014', periods=4)
df = pd.DataFrame(np.eye(4, ), index=dates, columns=['A_var1', 'A_var2', 'B_var1', 'B_var2'])
print(df)
A_var1 A_var2 B_var1 B_var2
2014-01-01 1.0 0.0 0.0 0.0
2014-01-02 0.0 1.0 0.0 0.0
2014-01-03 0.0 0.0 1.0 0.0
2014-01-04 0.0 0.0 0.0 1.0
I want to obtain the following:
type var1 var2
2014-01-01 A 1.0 0.0
2014-01-01 B 0.0 0.0
2014-01-02 A 0.0 1.0
2014-01-02 B 0.0 0.0
2014-01-03 A 0.0 0.0
2014-01-03 B 1.0 0.0
2014-01-04 A 0.0 0.0
2014-01-04 B 0.0 1.0
Any idea on how to do that efficiently? I know I can use the melt function but I can't get it to work in that context.
Many thanks,
You could use stack on multi-indexed columns.
In [304]: df.columns = df.columns.str.split('_', expand=True)
In [305]: df.stack(0).reset_index(1)
Out[305]:
level_1 var1 var2
2014-01-01 A 1.0 0.0
2014-01-01 B 0.0 0.0
2014-01-02 A 0.0 1.0
2014-01-02 B 0.0 0.0
2014-01-03 A 0.0 0.0
2014-01-03 B 1.0 0.0
2014-01-04 A 0.0 0.0
2014-01-04 B 0.0 1.0