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=1VIKlBY_Ql10Fd9EtLC3WSGBitMERwyHh
workbook_2.xlsx:
https://drive.google.com/open?id=1yaYREor6-Ij2NSe2XuLhJflyse5TO9p9

#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/DerrickSherrill.com/blob/master/pandasfilter.py
*****************************************************************
Code from this tutorial and all my others can be found on my GitHub:
https://github.com/Derrick-Sherrill/DerrickSherrill.com

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!