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

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


Full Code From This Example:

YouTube Description:

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: workbook_2.xlsx: #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) ***************************************************************** 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!

Enjoy this content? Consider Subbing to the Youtube channel

Derrick Sherrill

By: Derrick Sherrill

Thanks for visiting my page! I'm working hard to make the best content I can for you. I love watching people learn and teaching others. Happy Coding!

Become a Patreon!