python - Reshape wide to long in pandas

Question

Let's assume that I have the following dataframe in pandas:

             AA  BB  CC     
   date
   05/03     1   2   3  
   06/03     4   5   6  
   07/03     7   8   9  
   08/03     5   7   1  

and I want to transform it to the following:

   AA 05/03    1
   AA 06/03    4
   AA 07/03    7
   AA 08/03    5
   BB 05/03    2
   BB 06/03    5
   BB 07/03    8
   BB 08/03    7
   CC 05/03    3
   CC 06/03    6
   CC 07/03    9
   CC 08/03    1

How can I do it?

The reason of the transformation from wide to long is that, in the next stage, I would like to merge this dataframe with another one, based on dates and the initial column names (AA, BB, CC).

Answer 1

Update

As George Liu has shown in another answer, pd.melt is the idiomatic, flexible and fast solution to this problem. Do not use unstack for this.

---

unstack returns a series with a multiindex:

    In [38]: df.unstack()
    Out[38]: 
        date 
    AA  05/03    1
        06/03    4
        07/03    7
        08/03    5
    BB  05/03    2
        06/03    5
        07/03    8
        08/03    7
    CC  05/03    3
        06/03    6
        07/03    9
        08/03    1
    dtype: int64

You can call reset_index on the returning series:

In [39]: df.unstack().reset_index() 
Out[39]:        
        
    level_0 date    0
0   AA      05-03   1
1   AA      06-03   4
2   AA      07-03   7
3   AA      08-03   5
4   BB      05-03   2
5   BB      06-03   5
6   BB      07-03   8
7   BB      08-03   7
8   CC      05-03   3
9   CC      06-03   6
10  CC      07-03   9
11  CC      08-03   1

Or construct a dataframe with a multiindex:

In [40]: pd.DataFrame(df.unstack())     
Out[40]:        
        
            0
    date    
AA  05-03   1
    06-03   4
    07-03   7
    08-03   5
BB  05-03   2
    06-03   5
    07-03   8
    08-03   7
CC  05-03   3
    06-03   6
    07-03   9
    08-03   1