How to Filter and save the data as new files in Excel with Python Pandas

Sometimes you will want to filter and save the data as new files in Excel with Python Pandas as it can help you in selective data analysis, data organization, data sharing, etc.

In this tutorial, we will learn how to filter and save the data as new files in Excel with Python Pandas. This easy guide will tell you the techniques you need to perform these tasks.

Prerequisites:

Before working on Excel using Pandas, you should know the given Python concept:

Python Pandas 

Pandas is a Python library, mainly popular for importing and analyzing data much easier. Pandas is fast and it has high performance and productivity for users. 

In this article, we are trying to filter the data of an Excel sheet and save the filtered data as a new Excel file. You can also try the steps with us, using the given data set.

The Excel sheet provided is the same as what we have used in this tutorial. You can download it by clicking on datasets.xlsx

Excel Sheet used:

In this Excel sheet we have three categories in the Species column-

1. Setosa
2. Versicolor
3. Virginica

Now we aim to filter these data by species category and to save this filtered data in different sheet with filename = species.subcategory name i.e. after the execution of the code we will get three files of the following names-

1. Setosa.xlsx
2. Versicolor.xlsx
3. Virginica.xlsx

Python Code

Here is the Python code to filter and save the data as new files in Excel with Python Pandas:

Python3

# Python code to filter and save the
# data with different file names
import pandas


data = pandas.read_excel("datasets.xlsx")

speciesdata = data["Species"].unique()

for i in speciesdata:
    a = data[data["Species"].str.contains(i)]
    a.to_excel(i+".xlsx")

Output:

Explanation:

• Initially, the Pandas library is imported. 
• Then, the Excel file titled “datasets.xlsx” is loaded into the “data” object. 
• To retrieve unique values from the “Species” column, the “unique()“ function is employed, storing those values within the “speciesdata” object. 
• Next, a loop iterates through the “speciesdata” object, sequentially storing unique Species values (e.g., Setosa, Versicolor, Virginica). 
• Within each iteration, the “a” object filters the data to retain only rows where the “Species” column matches the current species value from “speciesdata”. 
• Finally, the “to_excel” function saves each filtered dataset, contained within “a”, as a separate Excel file named after the corresponding species value.

Conclusion

In this tutorial, we have explained how to filter and save data as new Excel files using the Python Pandas library. We have explained the process with an example, decoding each step for better understanding.

You can easily filter and save Excel data after reading this guide, as it shows the easiest approach to the problem.