import pandas as pd
from icecream import ic

reqs = pd.read_excel('Stepdown-reqs.xlsx')
#ic(reqs.axes)

reqsEmail = reqs['Email address used for PhD Application']
ic(reqsEmail)


print("===============================================")


phdData = pd.read_excel('PhD2MS-Stepdown.xlsx')
ic(phdData.axes)

phdEmail = list(phdData['Email'])
ic(phdEmail)

R1 = phdData['R1']
ic(R1)

R2 = phdData['R2']
R3 = phdData['R3']


R1x = [ x[-10] if len(str(x)) > 11 else None for x in R1 ]
#ic(R1x)

R2x = [ x[-10] if len(str(x)) > 11 else None for x in R2 ]

#ic(R3)
R3x = [ x[-10] if len(str(x)) > 11 else None for x in R3 ]
#ic(R3x)


print("============================================================")

out = set()

for i in reqsEmail:

#   ic(i)

    try:
        idx = phdEmail.index(i)
    except ValueError:
        idx = None

    if idx != None:
        stepDown = True

        if ( R1x[idx] != None and int(R1x[idx]) < 3):
            stepDown = False
        if ( R2x[idx] != None and int(R2x[idx]) < 3):
            stepDown = False
        if ( R3x[idx] != None and int(R3x[idx]) < 3):
            stepDown = False

        if stepDown:
            out.add( phdEmail[idx] )
        else:
            print("Reject: ", phdEmail[idx] )

print("\n\nN = ", len(out))
print("==========================================")
ic(out)