Transforming data from wide to long format or horizontal to vertical format using df.melt().

This story will be a part of the task which I came across recently where I had to use df.melt() to transform the data I was given.

df.melt() unpivots a DataFrame from wide to long format (horizontal to vertical format) , optionally leaving identifiers set. This function is useful to massage a DataFrame into a format where one or more columns are identifier variables (id_vars), while all other columns, considered measured variables (value_vars), are “unpivoted” to the row axis, leaving just two non-identifier columns, ‘variable’ and ‘value’.

Below is the dummy data that I have prepared which resembles the data I was provided during the task. The columns 1 , 2, 3 , 4 , 5 , 6 are months of the year. It shows the score of the players of team A, B, C, D from January to June.

df = pd.DataFrame({'team': ['A', 'B', 'C', 'D'],
                   'first': ['Alex', 'Mark', 'Tom', 'Paul'],
                   'last': ['Diesel', 'Twin', 'Cruise', 'Graham'],
                   '1': [10, 11, 12, 19],
                  '2': [22,56,12,78],
                  '3':[34,77,9,0],
                  '4':[11,22,33,44],
                  '5':[88,99,12,11],
                  '6':[0,0,0,0]})
df

Now , I want to unpivot the dataframe from wide to long format (horizontal to vertical format).

syntax :

DataFrame.melt(id_vars=None, value_vars=None, var_name=None, value_name=’value’, col_level=None, ignore_index=True)

df1 = df.melt(id_vars =['team','first','last'], value_vars =['1','2','3','4','5','6'], var_name = 'Month')
df1

Output:

Checking the number of rows and columns

df1.shape

Renaming the column ‘value’ to ‘score’.

syntax :
df.rename(columns = {'old_column1':'new_column1','old_column2':'new_column2'},
inplace = True)
df1.rename(columns = {'value':'score'}, inplace = True)

Output:

Resources :

1. pandas.DataFrame.shape — pandas 1.4.0 documentation (pydata.org)
2. pandas.DataFrame.melt — pandas 1.4.0 documentation (pydata.org)
3. pandas.DataFrame.rename — pandas 1.4.0 documentation (pydata.org)
4. pandas.DataFrame.from_dict — pandas 1.4.0 documentation (pydata.org)

Hope you find this helpful :)