Matching employees from different school and hometown

Viewed 20

I am new to coding. Now I have a employee table looked like below:

Name Hometown School
Jeff Illinois Loyola University Chicago
Alice California New York University
William Michigan University of Illinois at Chicago
Fiona California Loyola University Chicago
Charles Michigan New York University
Linda Indiana Loyola University Chicago

I am trying to get those employees in pairs where two employees come from different state and different university. Each person can only be in one pair. The expected table should look like

employee1 employee2
Jeff Alice
William Fiona
Charles Linda

The real table is over 3,000 rows. I am trying to do it with SQL or Python, but I don't know where to start.

1 Answers

A straightforward approach is to pick employees one by one and search the table after the one for an appropriate peer; found peers are flagged in order to not be paired repeatedly. Since in your case a peer should be found after a few steps, this iteration will likely be faster than operations which construct whole data sets at once.

from io import StringIO
import pandas as pd
# read example employee table
df = pd.read_table(StringIO("""Name Hometown    School
Jeff    Illinois    Loyola University Chicago
Alice   California  New York University
William Michigan    University of Illinois at Chicago
Fiona   California  Loyola University Chicago
Charles Michigan    New York University
Linda   Indiana Loyola University Chicago
"""))
# create expected table; its length is half that of the above
ef = pd.DataFrame(index=pd.RangeIndex(len(df)/2), columns=['employee1', 'employee2'])
k = 0   # number of found pairs, index into expected table
# array of flags for already paired employees
paired = pd.Series(False, pd.RangeIndex(len(df)))
# go through the employee table and collect pairs
for i in range(len(df)):
    if paired[i]: continue
    for j in range(i+1, len(df)):
        if  not paired[j] \
        and df.iloc[j]['Hometown'] != df.iloc[i]['Hometown'] \
        and df.iloc[j]['School']   != df.iloc[i]['School']:
            # we found a pair - store it, mark employee j paired
            ef.iloc[k] = df.iloc[[i, j]]['Name']
            k += 1
            paired[j] = True
            break
    else:
        print("no peer for", df.iloc[i]['Name'])
print(ef)

output:

  employee1 employee2
0      Jeff     Alice
1   William     Fiona
2   Charles     Linda
Related