How To Delete Empty Columns In Excel Using Python

In this tutorial, we will learn how to delete empty columns in an Excel file using Python. It can be a handy approach when working with a large number of Excel files or when the task is repetitive.

We will be using the pandas and openpyxl libraries, which are the most widely used libraries in Python for Excel file manipulation.

Step 1: Create an Excel file

Create an Excel file called “example.xlsx” and save it with the following content:

Step 2: Load Excel File

The next step is to load your Excel file into a Pandas DataFrame. Replace ‘file_path’ with the path of your file.

1
2
file_path = 'example.xlsx'
df = pd.read_excel(file_path)

Step 3: Identify and Delete Empty Columns

Now that we have our Excel file loaded in a pandas DataFrame, we can identify and delete the empty columns. We do this by iterating over the columns and checking if all the rows in the column are null.

1
2
3
4
5
for col in df.columns:
    # Check if the column is empty
    if df[col].isnull().all():
        # Delete the empty column
        df.drop(col, axis=1, inplace=True)

Step 4: Save the Edited Excel File

After deleting the empty columns, we can now save our DataFrame back to an Excel file. You can overwrite the existing file or save it in a new one.

1
df.to_excel('edited_example.xlsx', index=False)

We have successfully deleted the empty columns in an Excel file using Python.

Full Python Code

Here’s the complete Python code to delete empty columns in an Excel file, as discussed in the tutorial.

1
2
3
4
5
6
7
8
9
10
11
12
13
import pandas as pd
 
# Load Excel file
file_path = 'example.xlsx'
df = pd.read_excel(file_path)
 
# Check and delete empty columns
for col in df.columns:
    if df[col].isnull().all():
        df.drop(col, axis=1, inplace=True)
 
# Save the edited Excel file
df.to_excel('edited_example.xlsx', index=False)

Output

Conclusion

In this tutorial, we have learned how to delete empty columns in Excel using Python with the help of pandas and openpyxl libraries.

This approach can make our data cleaning and analysis process more efficient, especially when dealing with multiple or large Excel files.