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

Опубликовано: 29 Июль 2019
на канале: Derrick Sherrill

50,233

1.3k

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!