Skip to content
Related Articles

Related Articles

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

View Discussion
Improve Article
Save Article
Like Article
  • Difficulty Level : Medium
  • Last Updated : 03 Jul, 2020

Prerequisistes: 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)]
    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.

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!