Скачать или смотреть Filtering Excel Sheets with Python and Pandas to Save Specific Keywords as CSV Files

Learn how to filter Excel sheets containing specific keywords in their names using Python and Pandas, ensuring only the relevant sheets are saved as CSV files.
---
This video is based on the question https://stackoverflow.com/q/71168001/ asked by the user 'Codegator' ( https://stackoverflow.com/u/5680996/ ) and on the answer https://stackoverflow.com/a/71168144/ provided by the user 'quasi-human' ( https://stackoverflow.com/u/18059879/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Python/Pandas: Filter out files with specific keyword

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Filtering Excel Sheets in Python: How to Save Only Specific Keywords as CSV Files

When working with Excel files, especially those with multiple sheets, you might find yourself in a situation where you need to extract only certain sheets based on specific keywords in their names. For example, you may have a file that contains sheets named "Robot_Data" and "Auto_Sales", but your code is currently saving all sheets indiscriminately into CSV files. In this guide, we’ll learn how to filter Excel sheets using Python and Pandas so that only the sheets containing specific keywords, such as "Robot" or "Auto", are saved as CSV files.

Problem Description

In Python, specifically using the Pandas library, you can read and manipulate Excel files with ease. However, if you're only interested in certain sheets based on their names, you need an efficient way to filter those sheets before saving them as CSV files.

Here’s an example of the initial code that saves all sheets from an Excel file to CSV:

[[See Video to Reveal this Text or Code Snippet]]

As it stands, this code will convert every single sheet in the Excel file into CSV format. But what if you want to save only the sheets whose names contain the words "Robot" or "Auto"?

The Solution

To achieve this filtering, we can modify the code using the re module, which allows us to use regular expressions for more complex string searching. This way, we only process sheets whose names match our specified keywords.

Step-by-Step Code Example

Here’s how you can implement the solution:

Import the Necessary Libraries: We will be using pandas for working with the Excel file and re to use regular expressions.

Read the Excel File: Load your Excel file using pd.ExcelFile().

Loop Through the Sheets: Use a loop to iterate through all sheet names in the Excel file.

Check for Keywords: Use a regular expression to check if the sheet name contains the keywords.

Save to CSV: If the condition is met, read the sheet into a DataFrame and save it as a CSV file.

Here’s the updated code:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Code

import pandas as pd and import re load the required libraries.

xl = pd.ExcelFile('Sample_File.xlsm') opens the specified Excel file.

The for loop iterates through each sheet name retrieved from xl.sheet_names.

The if re.search('Robot|Auto', sheet): line checks if the sheet name contains the keywords, where | acts as an OR operator.

If the condition is satisfied, the DataFrame is created from the specific sheet and saved as a CSV file using df.to_csv().

Conclusion

By implementing regular expressions in combination with pandas, you've successfully filtered out sheets based on specific keywords in their names before saving them as CSV files. This approach not only simplifies your workflow but also ensures that you only keep the data that matters to you. Now, your Excel to CSV file conversion is not just more efficient, it’s also tailored specifically for your needs!

If you have any questions or need further clarification on any steps, feel free to leave a comment below. Happy coding!