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

Prerequisites: Python Pandas Pandas is mainly popular for importing and analyzing data much easier. Pandas is fast and it has high-performance & 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. Note: You can click on this filename to download this sheet datasets.xlsx Excel Sheet used: In this excel sheet we are having three categories in Species column-

1. Setosa
2. Versicolor
3. Virginica

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

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

Below is the implementation. 

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:

• First, we have imported the Pandas library.
• Then we have loaded the data.xlsx excel file in the data object.
• To fetch the unique values from that species column we have used unique() function. To check the unique values in the Species column we have called the unique() in speciesdata object.
• Then we will going to iterate the speciesdata object as we will going to store the Species column unique values(i.e. Setosa, Versicolor, Virginica) one by one.
• In object “a” we are filtering out the data that matches the Species.speciesdata i.e. in each iteration object a will going to store three different types of data i.e. data of Setosa type then data of Versicolor type and at last the data of Virginica type.
• Now to save the filtered data one by one in excel file we have used to_excel function, where, the file will going to be saved by the speciesdata name.