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 P
andas 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:
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) :
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):
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)