Conditional Search Multiple Excel Files - Excel Python Automation - Five Minute Python Scripts
ฝัง
- เผยแพร่เมื่อ 4 ต.ค. 2024
- 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/g...
The code used in this video on GitHub:
github.com/Der...
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/Der...
Check out my website:
www.derrickshe...
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!
Just discovered your channel and really like your very easy-to-follow tutorials and simple yet powerful examples. Keep it coming!
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.
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)
I am honored! Thank you so much for your kind words!
@@CodeWithDerrick Really appreciate it :) thank you!
Excellent video! This again demos why Python is so powerful!
Omg you’re just great!! Can you do the same on directory. I’m interested in that too! Thanks a lot!
Would you mind uploading the excel files you use in the descrption, cannot find it :( Great vids, really appreciate them!
thank you a thousand time for sharing your knowledge for free with us
Great video Derrick, thank you. Could you please show us how to automatically loop through all Excel file in directory ? many thanks
Hi, Derrick, Great Videos. Thank you for all the help that you are providing.
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.
Great video Derrick. Great use cases. Very practical and bite sized tutorials!
This is magic.... Love from India.....
incredible, thank you for making these videos
Hi Derrick- do you have a video where you show how to export the search results to another excel workbook, or CSV? Thanks
Great content! Loved the dog at the end.
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 ?
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)
did you ever make the video about running this script for every file in a directory?
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
You are the best )🥰 my greetings from Egypt
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
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?
You are just excellent derrik
Super useful! Thank you!!
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
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?
Thank you very much, you are awesome!
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!
did you found the solution for it.
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?
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.
Derrick - did you ever make the video on accessing and working with a full directory of files?
Thank you!
Fantastic video Derrick! can i know how to get burn-down charts in python?
Hi Derrick, as usual great video.
I normally do:
df[df.Occupation=="Programmer"]
@- Eth
Yeah that's true.
Then you would have to use the square bracket notation
df[df["Occupation one"]=="Programmer"]
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?
very thanks it's very useful
Can it show the row instead of name?
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
the *B A S S* drop at the end thooooooo 4:58
Can you output a list of all files matching a certain string i.e. show all files within a directory containing "programmer2"
Thanks dude.. please how can I use a large Excel data set with figures in one frame?
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
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.
how to get the row number value saved to a variable?
Thank you
Ultimate brother thanks again great video but web scrape to excel on real time with real time basis m waiting brother for ur video????
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.?
@@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
@@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.....
Amazing! thank you so much
Very helpful, thanks a lot.
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.
thanks for sharing this video. How to get multiple columns in the result file?
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?
Hi! I am wondering how you could reach the excel file without defining the path :-: Could you help me with it :( ?
Is there a way to search multiple user inputs from a list which is already a dataframe.please make a video if possible
What if I wanted to grab the entire row based on the data in 2 cells?
how can i check int values to see if they are greater or smaller
As always great!!
What if the excel spreadsheets are links not saved to your computer ?
also can you show which excel files the results were in?
2.52 I want to know how to save this users name to a list and search of one particular user?
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.
How we can make in tkinter filter column searchbox if our search match that will return that total row..
Great job
how can we get the uniques of one column and paste it into another?
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?
Can you please create video how to download pyton IDE for excel
great.
thanks for this, anyone know how to drop the result " Name: Name, dtype: object? thanks in advance
Amazing
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!!
What's the name of the music at the end of the video?
How to read
Xlsb extn file please tell
Do you do tutoring ?
Can we save this work now in another excel workbook?
If yes, what will be the additional code for it?🤔
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
@@CodeWithDerrick thanks alot derrick.
Please provide excel file used above
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