In this video we’ll cover how to can use Python and Pandas to conditionally search for values across multiple spreadsheets and return multiple values back when the conditional is true.
If you have any comments or suggestions for the next video, please let me know! I appreciate you all.
Kite helps fund the channel, thanks for checking them out and supporting me —
⭐ Kite is a free AI-powered coding assistant that will help you code faster and smarter. The Kite plugin integrates with all the top editors and IDEs to give you smart completions and documentation while you’re typing. https://www.kite.com/get-kite/?utm_medium=referral&utm_source=youtube&utm_campaign=derricksherrill&utm_content=description-only
The code used in this video on GitHub:
One of my videos talking about conditional statements:
Thanks so much for all the support!! 2600+ SUBSCRIBERS! You all are incredible. Thanks so much for your continued support. It means the world.
Full code from the video:
import numpy as np
import pandas as pd
excel_file = ‘Pandas_Workbook.xlsx’
df = pd.read_excel(excel_file)
print(df[‘Name’].where(df[‘Occupation’] == ‘Programmer’))
programmers = df[‘Name’].where(df[‘Occupation’] == ‘Programmer’)
excel_files = [‘Pandas_Workbook.xlsx’,’Pandas_Workbook_copy.xlsx’,’Pandas_Workbook_copy_2.xlsx’]
for individual_excel_file in excel_files:
df = pd.read_excel(individual_excel_file)
programmers = df[‘Name’].where(df[‘Occupation’] == ‘Programmer’).dropna()
print(“File Name” + individual_excel_file)
Code from this tutorial and all my others can be found on my GitHub:
Check out my website:
If you liked the video – please hit the like button. It means more than you know. Thanks for watching and thank you for all your support!!
Always looking for suggestions on what video to make next — leave me a comment with your project! Happy Coding!
Super useful! Thank you!!
Hi Derrick, thanks for the Vid, please can i make a vid request.basically i have a dictionary on my script but i would like to convert the dictionary into an excel sheet and my code reference the excel file as dictionary instead of it living in my code. i want other people to be able to update the excel dictionary instead of requesting me to update my code.
Hi! I am wondering how you could reach the excel file without defining the path :-: Could you help me with it 🙁 ?
What if the excel spreadsheets are links not saved to your computer ?
Do you do tutoring ?
Can you output a list of all files matching a certain string i.e. show all files within a directory containing "programmer2"
Do the programmatic walk through of all workbooks. To search instead of hard coding the list of workbooks
very thanks it's very useful
Can it show the row instead of name?
i faced this error
"Traceback (most recent call last):
File "<pyshell#39>", line 1, in <module>
df = pd.read_excel(Excel_file)
File "C:UsershuaweiAppDataLocalProgramsPythonPython38libsite-packagespandasutil_decorators.py", line 299, in wrapper
return func(*args, **kwargs)
File "C:UsershuaweiAppDataLocalProgramsPythonPython38libsite-packagespandasioexcel_base.py", line 336, in read_excel
io = ExcelFile(io, storage_options=storage_options, engine=engine)
File "C:UsershuaweiAppDataLocalProgramsPythonPython38libsite-packagespandasioexcel_base.py", line 1071, in _init_
ext = inspect_excel_format(
File "C:UsershuaweiAppDataLocalProgramsPythonPython38libsite-packagespandasioexcel_base.py", line 949, in inspect_excel_format
File "C:UsershuaweiAppDataLocalProgramsPythonPython38libsite-packagespandasiocommon.py", line 651, in get_handle
handle = open(handle, ioargs.mode)
FileNotFoundError: [Errno 2] No such file or directory: 'ppp' "
what is the cause?
Hi, Derrick, Great Videos. Thank you for all the help that you are providing.
2.52 I want to know how to save this users name to a list and search of one particular user?
Thank you very much, you are awesome!
Great video. I need to search one specific string from many in one of the column and save all row to new file/sheet. (Many authors of one document in one column- I'm searching for one author)
print("File Name" + individual_excel_file)
this line is showing me a syntax error under print. I did follow the way he showed yet no use.
Can anyone please help me out?
Very helpful , thanks so much . Is it possible for you to upload video on how to run the where conditions on parameter based input from an ui
thanks for this, anyone know how to drop the result " Name: Name, dtype: object? thanks in advance
Is there a way to search multiple user inputs from a list which is already a dataframe.please make a video if possible
Fantastic video Derrick! can i know how to get burn-down charts in python?
also can you show which excel files the results were in?
I want to this but I have a folder with 100+ excel files. Is there a way I can use the folder (call for every file in that folder) instead of plugging every single excel file like you did in line 12 ?
the B A S S drop at the end thooooooo 4:58
You are the best )🥰 my greetings from Egypt
Thanks dude.. please how can I use a large Excel data set with figures in one frame?
Here is a task for you if you can do:
There are 2 Excel files with same headers say old order and new order.
Headers are like:
ID, Dish, Your Cost, Your Tax, My Cost, My Tax
Now there is an ID say 15 with multiple dish like tea, coffee, sandwich and cost and tax in all 4 columns. Now ID and dish remain same in both excel but cost or tax is changing. So I need that particular row where there is a change and in output file I need the data from new file I mean new cost.
Can you do that? Up until now you are doing easy checks.. so it's a real task!!
how can we get the uniques of one column and paste it into another?
I'm enjoying your tutorial nuggets and would love to follow along. Maybe I missed it, but I haven't seen the Excel files listed in your Git repo. Are they hosted somewhere else?
Please provide excel file used above
Hi Derrick- do you have a video where you show how to export the search results to another excel workbook, or CSV? Thanks