OpenPyXL – How to Delete Rows from an Excel File Based on Some Condition

When working with Excel files in Python, OpenPyXL is a versatile library that provides various functionalities to manipulate data. One common task is to delete rows from an Excel file based on certain conditions. In this tutorial, we will explore how to achieve this using OpenPyXL and Python.

Prerequisites

Before we begin, make sure you have OpenPyXL installed. You can install it using pip:

code
pip install openpyxl

Additionally, ensure you have an Excel file ready with the data you want to process.

Deleting Rows with OpenPyXL

To delete rows from an Excel file using OpenPyXL, we need to follow these steps:

Step 1: Load the Excel File

First, we need to load the Excel file using OpenPyXL. Here’s an example:

code












from openpyxl import load_workbook

# Load the workbook
workbook = load_workbook('your_file.xlsx')

# Select the desired sheet
sheet = workbook['Sheet1'] 

Make sure to replace 'your_file.xlsx' with the actual path to your Excel file.

Step 2: Define the Condition

Next, we need to define the condition based on which we want to delete the rows. For example, let’s say we want to delete all rows where the value in column A is greater than 10. Modify the condition as per your requirements.

code
condition = lambda row: row[0] .value > 10

In this example, row[0] represents the value in the first column (column A) of each row. Adjust the index according to the column you want to check.

Step 3: Delete Rows

Now, we can iterate over the rows in reverse order and delete the ones that match our condition. Reversing the order ensures that deleting rows doesn’t affect the iteration.

code




for row in reversed(sheet.iter_rows()):
    if condition(row):
        sheet.delete_rows(row[0] .row)

Here, sheet.iter_rows() returns an iterator over all rows in the sheet, and row[0] .row gives us the row number for each row.

Step 4: Save the Modified Excel File

Finally, save the modified Excel file after deleting the rows:

code
workbook.save('modified_file.xlsx')

Make sure to replace 'modified_file.xlsx' with the desired filename for the modified file.

Conclusion

In this tutorial, we have learned how to delete rows from an Excel file based on a condition using OpenPyXL and Python. By following the provided steps, you can efficiently remove unwanted rows from your data, improving its quality and making it more manageable.

Remember to adjust the code as per your specific condition and Excel file structure. Experiment with different conditions and explore other functionalities offered by OpenPyXL to further enhance your data manipulation capabilities.

If you have any questions or need further assistance, feel free to leave a comment below. Happy data manipulation with OpenPyXL!