Pandas - Using pandas.melt

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

Index(['EMP_NO', 'BASIC', 'DA', 'HRA', 'SPECIAL'], dtype='object')

 
value_cols = df_to_melt.columns[1:]
value_cols

Index(['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)