How To Convert Rows Into Columns In Excel Using Python

In today’s data-driven world, we often deal with large datasets containing a massive amount of information.

The need to organize, manipulate, and analyze this data is a must-have skill for any data analyst or professional working with data.

One such core manipulation functionality often involves converting rows into columns or vice versa in spreadsheet applications like Microsoft Excel.

This tutorial will guide you through the process of converting rows into columns in an Excel sheet using the Python programming language. We’ll be using the library openpyxl for this purpose, which makes it easy to work with Excel files in Python.

Step 1: Install openpyxl

To begin with, first, make sure that you have installed the required library – openpyxl. It is a widely-used library for reading and writing Excel files, specifically .xlsx files. To install this library, you can use the following pip command:

1	pip install openpyxl

Step 2: Load the Excel Workbook

After installing the required library, we need to load the Excel workbook containing the data. If you don’t have an Excel workbook ready, you can create a simple one for this tutorial. Here’s an example:

 Name    Age    Country
 John    28     USA
 Alice   25     Canada
 Bob     23     UK

Once the Excel file is ready, load the workbook using the following code:

from openpyxl import load_workbook

# Load the Excel workbook

file_path = "your_excel_file_path.xlsx"

workbook = load_workbook(file_path)

Make sure to replace “your_excel_file_path.xlsx” with the actual path to your Excel file.

Step 3: Read the Data from the Sheet

Now that we have the workbook loaded, we can read the data from it. To do this, first, get the active worksheet using the active method and then read the data:

# Get the active sheet (first sheet)

sheet = workbook.active

# Read the data from the sheet

data = []

for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row, values_only=True):

data.append(row)

Step 4: Convert Rows to Columns

Next, let’s convert the rows into columns which is also called transposing the data. This is a simple process where we use the zip function in Python to transpose our rows and columns:

1 2	# Convert rows to columns transposed_data = list(zip(*data))

Step 5: Create a New Sheet and Write Transposed Data

After transposing the data, we need to write it back to the Excel workbook. The first thing to do is to create a new sheet in the workbook:

1 2	# Create a new sheet to write transposed data new_sheet = workbook.create_sheet("Transposed_Data", 0)

Now we’ll write the transposed data to this new sheet:

# Write transposed data to the new sheet

for row in transposed_data:

new_sheet.append(row)

Step 6: Save the Transposed Data to a New Excel File

Finally, save the updated workbook to a new file:

# Save the updated workbook to a new file

output_file = "transposed_output.xlsx"

workbook.save(output_file)

Your transposed data is now successfully saved to a new Excel file.

Full Code