Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Reshape wide to long in pandas

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).

like image 809
km1234 Avatar asked Apr 11 '16 00:04

km1234


People also ask

What are ways to reshape a Pandas DataFrame?

melt() function is used to reshape a DataFrame from a wide to a long format. It is useful to get a DataFrame where one or more columns are identifier variables, and the other columns are unpivoted to the row axis leaving only two non-identifier columns named variable and value by default.

How do you convert long to wide format in Python?

Reshaping a data from long to wide in python pandas is done with pivot() function. Pivot() function in pandas is one of the efficient function to transform the data from long to wide format.

What does melt () do in Python?

Pandas melt() function is used to change the DataFrame format from wide to long. It's used to create a specific format of the DataFrame object where one or more columns work as identifiers. All the remaining columns are treated as values and unpivoted to the row axis and only two columns - variable and value.

What does reshape do in Pandas?

In Pandas data reshaping means the transformation of the structure of a table or vector (i.e. DataFrame or Series) to make it suitable for further analysis. Some of Pandas reshaping capabilities do not readily exist in other environments (e.g. SQL or bare bone R) and can be tricky for a beginner.


1 Answers

Use pandas.melt to transform 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 final 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 
like image 155
George Liu Avatar answered Sep 29 '22 07:09

George Liu