Geek Answers Handbook

Change width to end in pandas

Suppose I have the following data frame 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 convert 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 this?

The reason for the transformation from wide to long is that in the next step I would like to combine this data file with another one based on the dates and names of the initial columns (AA, BB, CC).

+11
source share
2 answers

unstack returns a series with multiple indices:

  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 in the returned 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 create a data frame using 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
+19
source

Use pandas.melt to convert from wide to long:

 df = pd.DataFrame({ 'date' : ['05/03', '06/03', '07/03', '08/03'], 'AA' : [1, 4, 7, 5], 'BB' : [2, 5, 8, 7], 'CC' : [3, 6, 9, 1] }).set_index('date') df AA BB CC date 05/03 1 2 3 06/03 4 5 6 07/03 7 8 9 08/03 5 7 1

To convert, we just need to reset the index and then melt:

 df = df.reset_index() pd.melt(df, id_vars='date', value_vars=['AA', 'BB', 'CC'])

this is the end result:

  date variable value 0 05/03 AA 1 1 06/03 AA 4 2 07/03 AA 7 3 08/03 AA 5 4 05/03 BB 2 5 06/03 BB 5 6 07/03 BB 8 7 08/03 BB 7 8 05/03 CC 3 9 06/03 CC 6 10 07/03 CC 9 11 08/03 CC 1
+6
source

More articles:


All Articles