Converting long dataframe and extracting string

Question

Hi I have Dataframe like this:

    Date A_2002 B_2003 C_2004 D_2005 Type
03-2002   20     30      12     42    X
04-2002   12     321     12     23    X
03-2002   10     31      2      3     Y

I want to convert it to long version and extract the string type from it so the end result would be this:

   Date NewCol Extracted Type Value
03-2002 A       2002      X    20
03-2002 B       2003      X    30
03-2002 C       2004      X    12
03-2002 D       2005      X    42
04-2002 A       2002      X    12
04-2002 B       2003      X    321
04-2002 C       2004      X    12
04-2002 D       2005      X    23
03-2002 A       2002      Y    10
03-2002 B       2003      Y    31
03-2002 C       2004      Y    2
03-2002 D       2005      Y    3

So the end result will convert value from column name into tow new values and melt the data as seen above. Is it possible with pandas?

Answer 1

you can do stack after set_index and str.split:

m = df.set_index(['Date','Type'])
m.columns = m.columns.str.split('_',expand=True)
out = (m.stack([0,1]).rename('Value').reset_index()
     .rename(columns={'level_2':'NewCol','level_3':'Extracted'}))

---

       Date Type NewCol Extracted  Value
0   03-2002    X      A      2002   20.0
1   03-2002    X      B      2003   30.0
2   03-2002    X      C      2004   12.0
3   03-2002    X      D      2005   42.0
4   04-2002    X      A      2002   12.0
5   04-2002    X      B      2003  321.0
6   04-2002    X      C      2004   12.0
7   04-2002    X      D      2005   23.0
8   03-2002    Y      A      2002   10.0
9   03-2002    Y      B      2003   31.0
10  03-2002    Y      C      2004    2.0
11  03-2002    Y      D      2005    3.0