Conditional Search Multiple Excel Files - Excel Python Automation - Five Minute Python Scripts
Ғылым және технология
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. www.kite.com/get-kite/?...
The code used in this video on GitHub:
github.com/Derrick-Sherrill/D...
One of my videos talking about conditional statements:
• Learn Python #11 - If ...
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)
print(df['Name'].where(df['Occupation'] == 'Programmer'))
programmers = df['Name'].where(df['Occupation'] == 'Programmer')
print(programmers.dropna())
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)
print(programmers)
*****************************************************************
Code from this tutorial and all my others can be found on my GitHub:
github.com/Derrick-Sherrill/D...
Check out my website:
www.derricksherrill.com/
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!
Пікірлер: 84
Just discovered your channel and really like your very easy-to-follow tutorials and simple yet powerful examples. Keep it coming!
Excellent video! This again demos why Python is so powerful!
Awesome vids!!! I'm great at VBA and I'm trying to transfer those skills into Python. You're helping me out a whole lot.
Great content! Loved the dog at the end.
Great video Derrick. Great use cases. Very practical and bite sized tutorials!
incredible, thank you for making these videos
Hi, Derrick, Great Videos. Thank you for all the help that you are providing.
Super useful! Thank you!!
As always great!!
thank you a thousand time for sharing your knowledge for free with us
You are just excellent derrik
Very helpful, thanks a lot.
I am from berlin, germany. Love your python + excel tutorials! Very helpful for my job. Totally on point and fun to watch. Thank u so much and all the best for you! (my first yt-comment ever)
@CodeWithDerrick
5 жыл бұрын
I am honored! Thank you so much for your kind words!
@RonKirchner
5 жыл бұрын
@@CodeWithDerrick Really appreciate it :) thank you!
Thank you very much, you are awesome!
Amazing! thank you so much
This is magic.... Love from India.....
Omg you’re just great!! Can you do the same on directory. I’m interested in that too! Thanks a lot!
Thanks Derrick - really great! I was wondering if you have a video trying to combine this vlookup with a kind of IF statement from excel, in the case where the look up references are not fixed or known - then place a "tag" in a different cell
You are the best )🥰 my greetings from Egypt
Great job
Hi Derrick, this is really helpful and clear, thanks a lot! One question, how would you read multiple tabs from the same excel file and write into a new file and keeping the same tabs of the input file?
Great video Derrick, thank you. Could you please show us how to automatically loop through all Excel file in directory ? many thanks
Would you mind uploading the excel files you use in the descrption, cannot find it :( Great vids, really appreciate them!
Great channel, just subbed! Been on the prowl for python code that renames and standardizes column names across hundreds of excel sheets. Would you find all the column names first, then make a key value pair where the standardized column names are the key and the varying column names are the value, then loop across the files?
Thank you
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
Please do a make video about going through a directory(You mentioned that at 3:00). Please also include how we can match columns in multiple excel workbooks and return a new column displaying results like match or differ.
Amazing
Hi Derrick- do you have a video where you show how to export the search results to another excel workbook, or CSV? Thanks
Great video. Makes me wonder how much vba I would need to push for same results. I was left to wonder why import numpy since I don't notice it being used there?
Derrick - did you ever make the video on accessing and working with a full directory of files? Thank you!
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)
Derrick, 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?
Hi Derrick, Many thanks for your super informative and to the point Python tutorials. I have started using python for my daily work, thanks to you. I have a problem as shared below. Would be thankful if you could guide me through. I have two excel files. 1) 1st file containing customer IDs and coordinates (Long, Lat) of various points they did testing on. 2) 2nd File containing Different sites and their coordinates (Long, Lat). I want to insert a new column in 1st file which should be the nearest site against each customer ID. Customer Site ID= Min of distance from all the sites in 2nd File It can be based on below distance formula : LatA, LongA and LatB, LongB (Coordinates in decimal degrees) Distance (km)=ACOS(COS(RADIANS(90-LatA)) *COS(RADIANS(90-LatB)) +SIN(RADIANS(90-LatA)) *SIN(RADIANS(90-LatB)) *COS(RADIANS(LongA-LongB))) *6371
Fantastic video Derrick! can i know how to get burn-down charts in python?
great.
did you ever make the video about running this script for every file in a directory?
Hi Derrick, as usual great video. I normally do: df[df.Occupation=="Programmer"]
@nowyouknow2249
5 жыл бұрын
@- Eth Yeah that's true. Then you would have to use the square bracket notation df[df["Occupation one"]=="Programmer"]
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 ?
Hi Derrick, Hope you're having a great day and hope you can answer my question. Is there any way i can have an access to the excel files? thank you.
thanks for sharing this video. How to get multiple columns in the result file?
the *B A S S* drop at the end thooooooo 4:58
Great Content Derrick! What do you recommend when dealing with .xls files. One way is to save it as .xlsx or .csv and continue to work as shown. I would love to know if there is another alternative that is straight forward. Thanks!
@Jitendrakumar-du1ng
2 жыл бұрын
did you found the solution for it.
how hard would it be to find matching key values (like "ABC_1234") in two different excel files, and then compare all of the remaining cells in those respective rows in order to identify any differences between the two excel files for those matching records ? Basically to identify any data that changed for record "ABC_1234" between the two excel files. And then loop that check to step through all matching records from the two sheets to identify all changes between the sheets. Not using cell numbers since the records may not be in the same order in both sheets.
very thanks it's very useful Can it show the row instead of name?
Thanks dude.. please how can I use a large Excel data set with figures in one frame?
Hi, I have an excel workbook with 13 worksheets. The data on each sheet should be unique to each sheet with exception to 1 sheet named "All". Which as it suggests is all sheets combined. Problem I have is that someone has corrupted the data and all sheets data is now mixed. How would use python to highlight the duplicate entries to allow me to remove the ones I don't need?
how to get the row number value saved to a variable?
How we can make in tkinter filter column searchbox if our search match that will return that total row..
This is a very nice coverage of requirement of me. But I have multiple excel files with file name containing data (aa_03degc.xlsx, aa_04degC.xlsx etc) I need the program to read all the files corresponding to filename and fileter
Can you output a list of all files matching a certain string i.e. show all files within a directory containing "programmer2"
how can i check int values to see if they are greater or smaller
how can we get the uniques of one column and paste it into another?
Ultimate brother thanks again great video but web scrape to excel on real time with real time basis m waiting brother for ur video????
@CodeWithDerrick
5 жыл бұрын
Thanks for the kind words! I’ll see what I can do, any specific website data you want to scrape? Stock market, temperature data, etc.?
@yatender4444
5 жыл бұрын
@@CodeWithDerrick yes brother I want to pull stock market data basically option chain data into Excel on real time basis below is the link of the same I have mentioned any help from your side highly appreciated www.nseindia.com/live_market/dynaContent/live_watch/option_chain/optionKeys.jsp?segmentLink=17&instrument=OPTIDX&symbol=NIFTY&date=27JUN2019
@yatender4444
5 жыл бұрын
@@CodeWithDerrick actually I want to learn python but don't know where to start searching lot of videos in you tube but stuck what to do.....
Hi there - I have asked this on Stack overflow but did not get an answer, I hope I can get one here - here is my question I am using Pycharm to driver my Selenium tests with an excel document being my data resource. Currently I am retrieving the data using the cell reference - example: jobtitle = CreateaJob['B6'].value. However if i edit my excel sheet by adding a new row into row A of the sheet, the reference is lost as the data is now in C6 Being familiar with excel I would like to have cell B6 called jobtitle as a named range so that if I did enter a new row the data reference would still work. Is there a way in Pycharm to bring in a single cell named range and a value ? 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
Can you please create video how to download pyton IDE for excel
What's the name of the music at the end of the video?
also can you show which excel files the results were in?
What if I wanted to grab the entire row based on the data in 2 cells?
2.52 I want to know how to save this users name to a list and search of one particular user?
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 ?
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?
How to read Xlsb extn file please tell
Hell Derrick, how can I list using python all possible results for a list of 13 soccer games. I watched your cartesian product video and I feel the answer to this question can be obtained from similar concept. Please teach me here🙂 Mathematically I know there are 3^13 possibilities.
thanks for this, anyone know how to drop the result " Name: Name, dtype: object? thanks in advance
Can we save this work now in another excel workbook? If yes, what will be the additional code for it?🤔
@CodeWithDerrick
5 жыл бұрын
Hey Saqib Rauf! Yes we can save things to another excel workbook. The function we can use is df=pd.to_excel("OutputExcelFile.xlsx") which will create a new workbook titled OutputExcelFile.xlsx with the contents of the variable df
@saqibrauf2191
5 жыл бұрын
@@CodeWithDerrick thanks alot derrick.
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.
Please provide excel file used above
Do you do tutoring ?
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!!
Can you extract non english words from a single column in excel and store in another excel sheet or in the another excel file using python. please help me with this code for example Name country telcom SU AD HP IN DELL US ОППО РУС RU РАССВЕТ С RU РТЕХНО LLC RU СALIBRA UA СТ Технолоджи RU Тander RU ТД МоскваБазар RU ТОВ "АЙ ПI УКРАЇНА" UA Тарасян RU HPI USA Harman USA LOEBESHOPDK DK Valeo Thermal Commercial Vehicles Germany GmbH DE “NTC NP” LTD KZ “TREOLAN” LLC RU ㈜ IM KIDS KR 中国民生银行MSB CN 勞保局 TW 宝尊 CN 泰州浩轩 CN 青岛银行 CN 귀뚜라미 KR