Advanced Excel Filtering Across Workbooks Tutorial - Excel Automation with Python Series

How to integrate excel workbooks and filter values across them using logical statements, loc method, and boolean series.

We'll cover:
- pandas merge
- pandas isin method
- python logical statements

Here's the spreadsheets if you want to use them yourself:

#Python #Tutorial #ExcelAutomationWithPython
Let me know your requests!! I'll do my best to get them out as quickly as possible.

Thanks so much for the opportunity to provide videos to you all. You're all incredible - thank you for all your continued support (4,400+ subscribers at the time of writing) Thank you all.

*****************************************************************
Full code from the video:

import pandas as pd

# denote spreadsheets

#read in spreadsheets

#print columns

# Filter by conditions

print((df1['Name'].isin(df2['Name'])))

all_frames = [df1, df2]
print(all_df)

print(filtered_frame_4)

*****************************************************************
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!

[paragparab]

Very Helpful video thanks and keep uploaded more such videos

[MrMorshelio]

This is great. Really clear and interesting and I'm a total beginner in Python. So much more agile than SQL. Cheers from Israel!

[nikh]

Thank you so much Sir, I learn a lot from your video 🙏 keep it up

[onyiduke]

Hello,
Could you create a video showing how to automate a python script. For example, the script would automatically update an excel sheet everyday at 7Am without

[elweeh]

As an R user I have to say that this looks rather complicated in comparison to the tidyverse/dplyr approach in R. I might be biased but using the pipe (%>%) and avoiding all these brackets seems to be way more easy for simple data wrangling tasks like this example

[ammarkhan]

Your courses have been really helpful. Thanks

[carloschaccon]

Great video Derrick, What would you do if the data in the excel sheet does not start in the first row, or if there is an empty column?

[AlexMar-r]

I love your tuts not too long and straight to the point . Im currently jumping into finance from we development, what would you recommend to learn and how could I contribute to my company with this new tools I have acquired . Thanks ! (I want to stay in the side of finance )

[ScienceSeekho]

Best video. Thankyou. I m falled in ❤️ with Python

Quick question: I have about 30 excel with ~30k records in each. I want to pull rows which are equal to my string.
How can I dynamically merge those files and filter? Can I iterate over excel file name list and pass in that merge function

[viktorkutsyi]

Derrick, Thanks a lot for the video!
May I have a small question please?
how can i make .isin function CASE unsensitive in this particular code?



My filter result might differ upon capital and small letters....What should i change in this code, If i may ask?
Thank You@!

[GreenBoyPocket]

Hello, great video and subscriber. I have a challenging one. New user of python here, but I have what I think is an interesting example. I need to compare two files, one is an excel file with some travel information and the second one is a csv file with a trip plan. So we compare the trip plan (csv file) with the travel information (excel file) and we want the result as an excel file. the csv file has 6 columns one with the route of the trip, one with the name of cities of origin, a second one with cities of destination, a third one that has the mean of transportation, country of origin in the fifth column and country of destination in the sixth column. There is a second file, in this case the second one is an excel file, where we have the names of the cities of origin and destination in the same column and there is another column that has information whether you need a stamp type A or type B to travel, and 4 columns for the means of transportation, Car, bus, train, plane and Ferry. What I would like to get is a third excel file as an output that contains each route and whether we can make the trip with the selected transportation or not. We need stamp A to leave the origin city and stamp B to arrive to the destination.

[moali]

Great videos, I'm stuck on one bit, how do you filter a column that would not co tain a specific text, example any name that contains ''SS'' should not be included

[Shaluferal]

you helped me alot, nice video, ty <3

[surendrarawat]

I have seen your 2-3 videos and felt this language is very good and easy. Is there any website where list of command, example and reference material are available? Pl suggest. Thanks.

[adilmajeed]

Great video, in order to make it more interesting if you can slow down in explanation so the developers can follow it accordingly. Thanks

[DylanRainbolt]

Derrick, what if I wanted to search for a column within an excel file so that I could check the series within that column? For instance, I am using pandas to look through a gradebook. Currently, every time I export my gradebook, it will export with the assignment name, the date, and then the amount of points the assignment is worth in the header cell of the column. I want to be able to find the column based on user input and then be able to search within that column to find the grades that are 0. I have been changing the name of the column manually so that it includes only the activity name, but would like to find a way so that I could automate it to only search for the activity name in the header cell that also contains the date and also the points the activity is worth.
Best,
Dylan

[rabioultab]

Derrick, Thank you so much for the Hard Work!

It should be save our time right? but when? What I do mean that the answer is while not sheet's column & row isn't changing. When changed any row or column of any sheet again we need to change in script that's also taken time because in reality in Business no format is permanent.

Actually I want to judge my understanding because where I can't see and enlarge my knowledge!

[khalidjj]

Another great video...
How can I extract a text or hex or integer from a csv
Thanks. Please keep up the videos

[BrWilsonOSRS]

Great videos, Derrick! What environment/text editor are you using that has the command window at the bottom?

[mdsamirulislam]

Is there any way to filter data from one value for a column or another value for another column? ex: all Sales position or all 4.5 interview score.(just using excel build in features)