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).
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.
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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With