Foreign key join 1
Task:Find out employees with couple salary more than 10000.
Python
| 1 | import pandas as pd |
| 2 | emp_file = "E:\\txt\\Employees.txt" |
| 3 | rel_file = "E:\\txt\\EmpRel.txt" |
| 4 | emp_info = pd.read_csv(emp_file,sep='\t') |
| 5 | rel_info = pd.read_csv(rel_file,sep='\t') |
| 6 | rel_info = rel_info[rel_info['Relationship']=="Spouse"] |
| 7 | emp1_join = pd.merge(rel_info,emp_info,left_on='Emp1',right_on='ID') |
| 8 | emp2_join = pd.merge(emp1_join,emp_info,left_on='Emp2',right_on='ID') |
| 9 | res = emp2_join[emp2_join['BasePay_x']+emp2_join['BasePay_y']>=10000] |
| 10 | r = res[['Name_x','Name_x']] |
| 11 | print(r) |
Pandas can only join twice to get two columns of information, and then filter.
esProc
| A | |
| 1 | =file("E:\\txt\\Employees.txt").import@t().keys(ID) |
| 2 | =file("E:\\txt\\EmpRel.txt").import@t() |
| 3 | =A2.select(Relationship=="Spouse") |
| 4 | >A3.switch(Emp1,A1;Emp2,A1) |
| 5 | =A3.select(Emp1.BasePay+Emp2.BasePay>=10000) |
| 6 | >A5.run(Emp1=Emp1.Name,Emp2=Emp2.Name) |
Through foreign key objectification, esProc converts the foreign key field into the corresponding reference in the foreign key table, so that it can be treated as a single table.
