The pandas.melt(frame, id_vars=None, value_vars=None, var_name=None, value_name='value', col_level=None, ignore_index=True) function unpivots a DataFrame from wide to long format, optionally leaving identifiers set.
Click here for the documentation.
import pandas as pd
import numpy as np
df_to_melt = pd.read_csv('to_melt.csv', header=0)
df_to_melt.head()
## The columns to unpivot will form the entries in the 'variable' column.
## So better to rename them before doing melt
df_to_melt.rename(columns={'BASIC_AMOUNT':'BASIC', 'DA_AMOUNT':'DA',
'HRA_AMOUNT':'HRA', 'SPECIAL_AMOUNT':'SPECIAL'
}, inplace=True)
df_to_melt.columnsIndex(['EMP_NO', 'BASIC', 'DA', 'HRA', 'SPECIAL'], dtype='object')
value_cols = df_to_melt.columns[1:]
value_colsIndex(['BASIC', 'DA', 'HRA', 'SPECIAL'], dtype='object')
## If 'value_vars' is not specified, it uses all columns that are not set as
## id_vars. Still, we are passing it for understanding
df_to_melt = pd.melt(df_to_melt, id_vars=['EMP_NO'], value_vars=value_cols,
value_name='AMOUNT', var_name='HEAD')
df_to_melt.head()
df_to_melt['HEAD'].unique()array(['BASIC', 'DA', 'HRA', 'SPECIAL'], dtype=object)