Copy Rows and Columns in Excel Using Python

Manipulating data in Excel often involves tasks such as copying specific rows or columns based on certain conditions. Python, with its powerful libraries like Pandas and openpyxl, provides efficient solutions for handling Excel files. In this article, we will explore how to copy rows and columns in Excel using Python, showcasing practical examples for different scenarios.

What is Openpyxl?

Openpyxl is a Python library for reading and writing Excel (xlsx) files. It allows developers to work with Excel files, manipulate data, formatting, and perform various operations programmatically. openpyxl provides a convenient and Pythonic way to interact with Excel files, making it easy to handle tasks like creating new workbooks, modifying existing ones, and extracting or inserting data.

Copy Rows and Columns in Excel Using Python

Below, are examples of how to Copy Rows And Columns In Excel Using perform. First, install the openpyxl library, which is essential for copying rows and columns in Excel using Python, use the following command:

pip install openpyxl

Excel1.xlsx:

excel1.xlsx

Example 1: Copy Specific Rows in Excel Using Python

In this example, below Python code uses the Pandas library to read data from an Excel file named ‘excel1.xlsx’ into a DataFrame. It then filters rows based on a specified condition where the ‘Salary’ column is greater than $50,000. The resulting DataFrame is saved as a new Excel file named ‘excel2.xlsx’, excluding the index column.

Python3

import pandas as pd

# Read the Excel file
df = pd.read_excel('excel1.xlsx')

# Copy rows based on condition
condition = df['Salary'] > 50000
result = df[condition]

# Save the result to a new Excel file
result.to_excel('excel2.xlsx', index=False)

Output (excel2.xlsx) :

excel2.xlsx

Example 2: Copy Specific Columns in Excel Using Python

In this example, below Python code, uses Pandas library, reads data from an Excel file named ‘excel1.xlsx’ into a DataFrame. It then selects specific columns (‘Name’ and ‘Age’) based on a condition where the ‘Age’ column is less than 30. The resulting DataFrame is saved as a new Excel file named ‘excel2.xlsx’, excluding the index column.

Python3

import pandas as pd

# Read the Excel file
df = pd.read_excel('excel1.xlsx')

# Copy columns based on condition
condition = df['Age'] < 30
result = df.loc[condition, ['Name', 'Age']]

# Save the result to a new Excel file
result.to_excel('excel2.xlsx', index=False)

Output (excel2.xlsx):

excel2.xlsx

Example 3: Copy Complete Content From One File to Another

In this example, below code using the openpyxl library, the code loads data from the source Excel file ‘excel1.xlsx’ into a workbook and worksheet. It then attempts to create a new Excel file ‘excel1.xlsx’ for the destination, which might be unintentional. The script iterates through rows in the source worksheet and appends them to the destination worksheet.

Python3

from openpyxl import load_workbook

# Load the source Excel file
source_wb = load_workbook('excel1.xlsx')
source_ws = source_wb.active

# Create a new Excel file
destination_wb = load_workbook('excel1.xlsx')
destination_ws = destination_wb.active

# Copy data from source to destination
for row in source_ws.iter_rows(min_row=1, max_row=source_ws.max_row, values_only=True):
    destination_ws.append(row)

# Save the destination file
destination_wb.save('excel2.xlsx')

Output (excel2.xlsx)

excel2.xlsx