Open In App
Related Articles

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

Improve Article
Save Article
Like Article

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. 


# 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)]

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.
Whether you're preparing for your first job interview or aiming to upskill in this ever-evolving tech landscape, GeeksforGeeks Courses are your key to success. We provide top-quality content at affordable prices, all geared towards accelerating your growth in a time-bound manner. Join the millions we've already empowered, and we're here to do the same for you. Don't miss out - check it out now!

Last Updated : 29 Aug, 2022
Like Article
Save Article
Similar Reads
Complete Tutorials