Get contacts with >=3 soft bounces of 421 SMTP reply code

421softbounces.py

#import the pandas library and aliasing as pd
import pandas as pd

#read log data from excel file
filename = 'APJC-FY18-Bounce history.xlsx'
df = pd.read_excel(filename)

#Group data based on email address and SMTP reply code and count the number of rows.  And rename the new column of counts as 'Count of Bounces'
df1 = df.groupby(['Email Address','SMTP Reply Code'], as_index=False).size().reset_index(name='Count of Bounces')

#Select rows that where SMTP Reply Code equals 421 from d1 table
df2 = df1.loc[df1['SMTP Reply Code'] == 421]

#Select rows that where Count of Bounces equals or more than 3 from d2 table
df3 = df2.loc[df2['Count of Bounces'] >=3 ]

#Merge table df3 with table df  based on shared email address column
df4 = pd.merge(df, df3, on=['Email Address'])

#remove duplicates but keep the last row which is the latest bounceback
df5 = df4.drop_duplicates(subset='Email Address', keep="last")

#read an excel file
df6 = pd.read_excel('total-hard-bounce.xlsx')

#Merge table df5 with table df6  based on shared email address column
df7 = pd.merge(df5, df6, on=['Email Address'])

#Output this result to a new csv file
df7.to_csv('C:/users/.../.../gc-bounce-421-hardbounce.csv')