In today’s data-driven business environment, dealing with spreadsheets and extracting required information has become a common task. One such task is searching for a particular word in an Excel spreadsheet.
Normally, we can use Excel’s built-in Find function for this purpose, but what if you have multiple spreadsheets or huge data? That’s when Python comes in handy! Python, with its powerful libraries, can automate this task easily. Let’s discuss how to make this happen.
Step 1: Install Necessary Libraries
The first step is to install the required Python libraries. We’ll be using Pandas (a fast, powerful, flexible, and easy-to-use open-source data analysis and manipulation tool) and Openpyxl (a Python library to read/write Excel xlsx/xlsm files). You can install these using pip:
|
1 |
pip install pandas openpyxl |
Step 2: Import the Libraries
Next, we need to import the installed libraries into our Python script:
|
1 |
import pandas as pd |
Step 3: Read the Excel File
Now, let’s load our Excel file using the Pandas read_excel method:
|
1 |
dataframe = pd.read_excel('filename.xlsx') |
Step 4: Searching for the Word
For searching a particular word in the Dataframe, we can use the str.contains method:
|
1 |
search_word = dataframe[dataframe.apply(lambda row: row.astype(str).str.contains('Teacher').any(), axis=1)] |
Full Code:
|
1 2 3 4 5 6 7 |
import pandas as pd dataframe = pd.read_excel('filename.xlsx') search_word = dataframe[dataframe.apply(lambda row: row.astype(str).str.contains('Teacher').any(), axis=1)] print(search_word) |
Output:
Name Age Profession 2 Tom 45 Teacher
Conclusion
There you have it – a basic Python script that automates the task of searching for a word in an Excel file! This will save you time and effort, especially when handling large spreadsheets. However, this is just the tip of the iceberg; Python’s powerful libraries can help automate a wide range of Excel tasks, making your life easier!