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:
workbook_1.xlsx:
https://drive.google.com/open?id=1VIK...
workbook_2.xlsx:
https://drive.google.com/open?id=1yaY...
#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
excel_file_1 = 'Workbook_1.xlsx'
excel_file_2 = 'Workbook_2.xlsx'
#read in spreadsheets
df1 = pd.read_excel(excel_file_1)
df2 = pd.read_excel(excel_file_2)
#print columns
print(df1.columns)
print(df2.columns)
Filter by conditions
print((df1['Name'].isin(df2['Name'])))
filtered_frame_1 = df1.loc[(df1['Name'].isin(df2['Name']))] # Based on another sheet
filtered_frame_2 = df1.loc[~(df1['Name'].isin(df2['Name']))] # Inverse
filtered_frame_3 = df1.loc[(df1['Name'].isin(df2['Name'])) & (df1['Interview Score'] == 4) | (df1['YR Experience'] == 3)] # Multiple Filters
all_frames = [df1, df2]
all_df = pd.merge(df1, df2, on='Name')
print(all_df)
filtered_frame_4 = all_df.loc[(all_df['YR Experience'] == 5) & (all_df['Group Interview Score'] == 4)]
print(filtered_frame_4)
https://github.com/Derrick-Sherrill/D...
*****************************************************************
Code from this tutorial and all my others can be found on my GitHub:
https://github.com/Derrick-Sherrill/D...
Check out my website:
https://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!
