How to Convert Rows to Columns and Columns to Rows in Pandas DataFrame using Python

Introduction

In Python, you can use the pandas library to work with tabular data, and the core data type in pandas is the DataFrame. Sometimes, when working with DataFrame data, you may need to convert rows to columns or columns to rows. Here is a simple example demonstrating how to achieve this using the pandas library.

Create Table

First, let’s create a new example DataFrame.

import pandas as pd

# create a new DataFrame
df = pd.DataFrame({
    'name': ['John', 'Mary', 'Peter'],
    'math': [80, 90, 70],
    'english': [70, 85, 90],
    'science': [75, 95, 80]
})

print(df)

# output:
    name  math  english  science
0   John    80       70       75
1   Mary    90       85       95
2  Peter    70       90       80

Convert Rows to Columns

# Use the melt function to convert rows to columns
df_melt = pd.melt(df, id_vars=['name'], var_name='subject', value_name='score')

print(df_melt)

In the code above, we simply use the melt function to transform each student's subject grades into a column, which gives us the following output.

    name  subject  score
0   John     math     80
1   Mary     math     90
2  Peter     math     70
3   John  english     70
4   Mary  english     85
5  Peter  english     90
6   John  science     75
7   Mary  science     95
8  Peter  science     80

Convert Columns to Rows

Column-to-row transformation, also known as data unpivoting, can be achieved using the pivot function in the pandas library. Here is an example code:

# Use the pivot function to pivot rows to columns.
df_pivot = df_meld.pivot(index='name', columns='subject', values='score')
print(df_pivot)

subject  english  math  science
name
John          70    80       75
Mary          85    90       95
Peter         90    70       80

Thank you for taking the time to explore data-related insights with me. I appreciate your engagement. If you find this information helpful, I invite you to follow me or connect with me on LinkedIn or X(@Luca_DataTeam). You can also catch glimpses of my personal life on Instagram, Happy exploring!👋