Skip to content
geeksforgeeks
  • Courses
    • DSA to Development
    • Get IBM Certification
    • Newly Launched!
      • Master Django Framework
      • Become AWS Certified
    • For Working Professionals
      • Interview 101: DSA & System Design
      • Data Science Training Program
      • JAVA Backend Development (Live)
      • DevOps Engineering (LIVE)
      • Data Structures & Algorithms in Python
    • For Students
      • Placement Preparation Course
      • Data Science (Live)
      • Data Structure & Algorithm-Self Paced (C++/JAVA)
      • Master Competitive Programming (Live)
      • Full Stack Development with React & Node JS (Live)
    • Full Stack Development
    • Data Science Program
    • All Courses
  • Tutorials
    • Data Structures & Algorithms
    • ML & Data Science
    • Interview Corner
    • Programming Languages
    • Web Development
    • CS Subjects
    • DevOps And Linux
    • School Learning
  • Practice
    • Build your AI Agent
    • GfG 160
    • Problem of the Day
    • Practice Coding Problems
    • GfG SDE Sheet
  • Contests
    • Accenture Hackathon (Ending Soon!)
    • GfG Weekly [Rated Contest]
    • Job-A-Thon Hiring Challenge
    • All Contests and Events
  • Data preprocessing
  • Data Manipulation
  • Data Analysis using Pandas
  • EDA
  • Pandas Exercise
  • Pandas AI
  • Numpy
  • Matplotlib
  • Plotly
  • Data Analysis
  • Machine Learning
  • Data science
Open In App
Next Article:
How to Merge all excel files in a folder using Python?
Next article icon

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

Last Updated : 05 Feb, 2024
Comments
Improve
Suggest changes
Like Article
Like
Report

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. Pandas and Excel

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: 

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:

new created files 

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.



Next Article
How to Merge all excel files in a folder using Python?
author
amitkkumra
Improve
Article Tags :
  • Python
  • Python-pandas
Practice Tags :
  • python

Similar Reads

  • How to merge multiple excel files into a single files with Python ?
    Normally, we're working with Excel files, and we surely have come across a scenario where we need to merge multiple Excel files into one. The traditional method has always been using a VBA code inside excel which does the job but is a multi-step process and is not so easy to understand. Another meth
    4 min read
  • How to Filter DataFrame Rows Based on the Date in Pandas?
    Filtering a DataFrame rows by date selects all rows which satisfy specified date constraints, based on a column containing date data. For instance, selecting all rows between March 13, 2020, and December 31, 2020, would return all rows with date values in that range. Use DataFrame.loc() with the ind
    2 min read
  • How to Filter DataFrame Rows Based on the Date in Pandas?
    Different regions follow different date conventions (YYYY-MM-DD, YYYY-DD-MM, DD/MM/YY, etc.).  It is difficult to work with such strings in the data. Pandas to_datetime() function allows converting the date and time in string format to datetime64. This datatype helps extract features of date and tim
    5 min read
  • How to Merge all excel files in a folder using Python?
    In this article, we will see how to combine all Excel files present in a folder into a single file. Module used: The python libraries used are: Pandas: Pandas is a python library developed for a python programming language for manipulating data and analyzing the data. It is widely used in Data Scien
    3 min read
  • Joining Excel Data from Multiple files using Python Pandas
    Let us see how to join the data of two excel files and save the merged data as a new Excel file. We have 2 files, registration details.xlsx and exam results.xlsx. registration details.xlsx We are having 7 columns in this file with 14 unique students details. Column names are as follows : Admission D
    2 min read
  • How to import an excel file into Python using Pandas?
    It is not always possible to get the dataset in CSV format. So, Pandas provides us the functions to convert datasets in other formats to the Data frame. An excel file has a '.xlsx' format.  Before we get started,  we need to install a few libraries.  pip install pandas pip install xlrd  For importin
    2 min read
  • How to Create a Pivot table with multiple indexes from an excel sheet using Pandas in Python?
    The term Pivot Table can be defined as the Pandas function used to create a spreadsheet-style pivot table as a DataFrame. It can be created using the pivot_table() method. Syntax: pandas.pivot_table(data, index=None) Parameters: data : DataFrame index: column, Grouper, array, or list of the previous
    2 min read
  • How to Find the First Empty Row of an Excel File in Python
    When working with data in Excel files, one common task is to find the first empty row where new data can be added. Whether you're automating a data entry process or performing data analysis, Python offers several methods to accomplish this task efficiently. In this article, we'll explore different a
    2 min read
  • How to create a list of files, folders, and subfolders in Excel using Python ?
    In this article, we will learn How to create a list of Files, Folders, and Sub Folders and then export them to Excel using Python. We will create a list of names and paths using a few folder traversing methods explained below and store them in an Excel sheet by either using openpyxl or pandas module
    12 min read
  • How to read a CSV file to a Dataframe with custom delimiter in Pandas?
    Python is a good language for doing data analysis because of the amazing ecosystem of data-centric python packages. pandas package is one of them and makes importing and analyzing data so much easier.Here, we will discuss how to load a csv file into a Dataframe. It is done using a pandas.read_csv()
    3 min read
geeksforgeeks-footer-logo
Corporate & Communications Address:
A-143, 7th Floor, Sovereign Corporate Tower, Sector- 136, Noida, Uttar Pradesh (201305)
Registered Address:
K 061, Tower K, Gulshan Vivante Apartment, Sector 137, Noida, Gautam Buddh Nagar, Uttar Pradesh, 201305
GFG App on Play Store GFG App on App Store
Advertise with us
  • Company
  • About Us
  • Legal
  • Privacy Policy
  • In Media
  • Contact Us
  • Advertise with us
  • GFG Corporate Solution
  • Placement Training Program
  • Languages
  • Python
  • Java
  • C++
  • PHP
  • GoLang
  • SQL
  • R Language
  • Android Tutorial
  • Tutorials Archive
  • DSA
  • Data Structures
  • Algorithms
  • DSA for Beginners
  • Basic DSA Problems
  • DSA Roadmap
  • Top 100 DSA Interview Problems
  • DSA Roadmap by Sandeep Jain
  • All Cheat Sheets
  • Data Science & ML
  • Data Science With Python
  • Data Science For Beginner
  • Machine Learning
  • ML Maths
  • Data Visualisation
  • Pandas
  • NumPy
  • NLP
  • Deep Learning
  • Web Technologies
  • HTML
  • CSS
  • JavaScript
  • TypeScript
  • ReactJS
  • NextJS
  • Bootstrap
  • Web Design
  • Python Tutorial
  • Python Programming Examples
  • Python Projects
  • Python Tkinter
  • Python Web Scraping
  • OpenCV Tutorial
  • Python Interview Question
  • Django
  • Computer Science
  • Operating Systems
  • Computer Network
  • Database Management System
  • Software Engineering
  • Digital Logic Design
  • Engineering Maths
  • Software Development
  • Software Testing
  • DevOps
  • Git
  • Linux
  • AWS
  • Docker
  • Kubernetes
  • Azure
  • GCP
  • DevOps Roadmap
  • System Design
  • High Level Design
  • Low Level Design
  • UML Diagrams
  • Interview Guide
  • Design Patterns
  • OOAD
  • System Design Bootcamp
  • Interview Questions
  • Inteview Preparation
  • Competitive Programming
  • Top DS or Algo for CP
  • Company-Wise Recruitment Process
  • Company-Wise Preparation
  • Aptitude Preparation
  • Puzzles
  • School Subjects
  • Mathematics
  • Physics
  • Chemistry
  • Biology
  • Social Science
  • English Grammar
  • Commerce
  • World GK
  • GeeksforGeeks Videos
  • DSA
  • Python
  • Java
  • C++
  • Web Development
  • Data Science
  • CS Subjects
@GeeksforGeeks, Sanchhaya Education Private Limited, All rights reserved
We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox
Improvement
Suggest Changes
Help us improve. Share your suggestions to enhance the article. Contribute your expertise and make a difference in the GeeksforGeeks portal.
geeksforgeeks-suggest-icon
Create Improvement
Enhance the article with your expertise. Contribute to the GeeksforGeeks community and help create better learning resources for all.
geeksforgeeks-improvement-icon
Suggest Changes
min 4 words, max Words Limit:1000

Thank You!

Your suggestions are valuable to us.

What kind of Experience do you want to share?

Interview Experiences
Admission Experiences
Career Journeys
Work Experiences
Campus Experiences
Competitive Exam Experiences