Bernard Szlachta: /* Self-join */

2017-02-14T07:11:35Z

Self-join

New page

[[Category:Intro to R|040]]

We want to select the name of an employee (ENAME column in ''emp'' data frame) and the department name (DNAME column in ''dept'' data frame) in which the employee works.

First, merge two tables
<source lang="rsplus">
merge(emp,dept,by="DEPTNO")
</source>
DEPTNO ID ENAME JOB MGR HIREDATE SAL COMM DNAME LOC
1 10 7839 BUSH PRESIDENT NA 17/11/1981 5000 NA ACCOUNTING NEW YORK
2 10 7934 ELISON CLERK 7782 23/01/1982 1300 NA ACCOUNTING NEW YORK
3 10 7782 MERKEL MANAGER 7839 09/06/1981 2450 NA ACCOUNTING NEW YORK
4 20 7566 PUTIN MANAGER 7839 02/04/1981 2975 NA RESEARCH LONDON
5 20 7369 THATCHER CLERK 7902 17/12/1980 800 NA RESEARCH LONDON
6 20 7788 CARNEGIE ANALYST 7566 09/12/1982 3000 NA RESEARCH LONDON
7 20 7876 FORD CLERK 7788 12/01/1983 1100 NA RESEARCH LONDON
8 20 7902 TOOSK ANALYST 7566 03/12/1981 3000 NA RESEARCH LONDON
9 30 7900 BUFFETT CLERK 7698 03/12/1981 950 NA SALES PARIS
10 30 7654 CHIRACK SALESMAN 7698 28/09/1981 1250 1400 SALES PARIS
11 30 7698 BLAIR MANAGER 7839 01/05/1981 2850 NA SALES PARIS
12 30 7844 GATES SALESMAN 7698 08/09/1981 1500 0 SALES PARIS
13 30 7521 WALTON SALESMAN 7698 22/02/1981 1250 500 SALES PARIS
14 30 7499 BAROSSO SALESMAN 7698 20/02/1981 1600 300 SALES PARIS

Second, select ENAME and DNAME columns
<source lang="rsplus">
merge(emp,dept,by="DEPTNO")[,c("ENAME","DNAME")]
</source>
ENAME DNAME
1 BUSH ACCOUNTING
2 ELISON ACCOUNTING
3 MERKEL ACCOUNTING
4 PUTIN RESEARCH
5 THATCHER RESEARCH
6 CARNEGIE RESEARCH
7 FORD RESEARCH
8 TOOSK RESEARCH
9 BUFFETT SALES
10 CHIRACK SALES
11 BLAIR SALES
12 GATES SALES
13 WALTON SALES
14 BAROSSO SALES

You can show all rows, even if there are no corresponding rows in another data frame.
<source lang="rsplus">
merge(emp,dept,by="DEPTNO",all=T)[,c("ENAME","DNAME")]
</source>
ENAME DNAME
1 BUSH ACCOUNTING
2 ELISON ACCOUNTING
3 MERKEL ACCOUNTING
4 PUTIN RESEARCH
5 THATCHER RESEARCH
6 CARNEGIE RESEARCH
7 FORD RESEARCH
8 TOOSK RESEARCH
9 BUFFETT SALES
10 CHIRACK SALES
11 BLAIR SALES
12 GATES SALES
13 WALTON SALES
14 BAROSSO SALES
15 <NA> OPERATIONS

=== Non-equi join ===
If merge cannot find a common column or if the ''by'' parameter is not specified, ''merge'' function will return Cartesian Product of two data frames.

This allows us to filter out rows using any condition we want.

If we want to find the name of an employee and their salary grade, we have to get data from two data frames: EMP and SALGRADE.
<source lang="rsplus">
empsalgrade <- merge(emp,salgrade)
with(empsalgrade,empsalgrade[SAL >= LOSAL & SAL <= HISAL,])
</source>
ID ENAME JOB MGR HIREDATE SAL COMM DEPTNO GRADE LOSAL HISAL
8 7900 BUFFETT CLERK 7698 03/12/1981 950 NA 30 1 700 1200
11 7369 THATCHER CLERK 7902 17/12/1980 800 NA 20 1 700 1200
13 7876 FORD CLERK 7788 12/01/1983 1100 NA 20 1 700 1200
19 7654 CHIRACK SALESMAN 7698 28/09/1981 1250 1400 30 2 1201 1400
23 7521 WALTON SALESMAN 7698 22/02/1981 1250 500 30 2 1201 1400
28 7934 ELISON CLERK 7782 23/01/1982 1300 NA 10 2 1201 1400
34 7499 BAROSSO SALESMAN 7698 20/02/1981 1600 300 30 3 1401 2000
35 7844 GATES SALESMAN 7698 08/09/1981 1500 0 30 3 1401 2000
44 7698 BLAIR MANAGER 7839 01/05/1981 2850 NA 30 4 2001 3000
45 7782 MERKEL MANAGER 7839 09/06/1981 2450 NA 10 4 2001 3000
46 7566 PUTIN MANAGER 7839 02/04/1981 2975 NA 20 4 2001 3000
52 7902 TOOSK ANALYST 7566 03/12/1981 3000 NA 20 4 2001 3000
54 7788 CARNEGIE ANALYST 7566 09/12/1982 3000 NA 20 4 2001 3000
57 7839 BUSH PRESIDENT NA 17/11/1981 5000 NA 10 5 3001 9999

Save the joined tables and show only appropriate columns
> empsalgrade <- with(empsalgrade,empsalgrade[SAL > LOSAL & SAL < HISAL,])
> empsalgrade[c("ENAME","SAL","GRADE")]
ENAME SAL GRADE
8 BUFFETT 950 1
11 THATCHER 800 1
13 FORD 1100 1
19 CHIRACK 1250 2
23 WALTON 1250 2
28 ELISON 1300 2
34 BAROSSO 1600 3
35 GATES 1500 3
44 BLAIR 2850 4
45 MERKEL 2450 4
46 PUTIN 2975 4
57 BUSH 5000 5

== Self-join ==
We want to know the person's boss name
<source lang="rsplus">
m1 <- merge(emp,emp,by.x="MGR",by.y="ID")[,c("ENAME.x","ENAME.y")]
library(plyr)
rename(m1,c("ENAME.x" = "Employee","ENAME.y" = "Boss"))
</source>
ENAME.x ENAME.y
1 TOOSK PUTIN
2 CARNEGIE PUTIN
3 CHIRACK BLAIR
4 BAROSSO BLAIR
5 GATES BLAIR
6 BUFFETT BLAIR
7 WALTON BLAIR
8 ELISON MERKEL
9 FORD CARNEGIE
10 BLAIR BUSH
11 MERKEL BUSH
12 PUTIN BUSH
13 THATCHER TOOSK

== Generating Cartesian Product ==

If merge criteria are more complex, you can always generate Cartesian product and use filtering criteria to get results you are interested in.
> merge(emp,emp,,all=T,by=NULL)
ID.x ENAME.x JOB.x MGR.x HIREDATE.x SAL.x COMM.x DEPTNO.x ID.y ENAME.y JOB.y MGR.y HIREDATE.y SAL.y COMM.y DEPTNO.y
1 7839 BUSH PRESIDENT NA 1981-11-17 5000 NA 10 7839 BUSH PRESIDENT NA 1981-11-17 5000 NA 10
2 7698 BLAIR MANAGER 7839 1981-05-01 2850 NA 30 7839 BUSH PRESIDENT NA 1981-11-17 5000 NA 10
...
195 7876 FORD CLERK 7788 1983-01-12 1100 NA 20 7934 ELISON CLERK 7782 1982-01-23 1300 NA 10
196 7934 ELISON CLERK 7782 1982-01-23 1300 NA 10 7934 ELISON CLERK 7782 1982-01-23 1300 NA 10

==Exercises==

'''31. Exercise'''

Select the name of the employee and the city (LOC column in DEPT table) in which they work.
ENAME LOC
1 BUSH NEW YORK
2 ELISON NEW YORK
3 MERKEL NEW YORK
4 PUTIN LONDON
5 THATCHER LONDON
6 CARNEGIE LONDON
7 FORD LONDON
8 TOOSK LONDON
9 BUFFETT PARIS
10 CHIRACK PARIS
11 BLAIR PARIS
12 GATES PARIS
13 WALTON PARIS
14 BAROSSO PARIS

'''32. Exercise'''

Select the names of the employees, and the name and number of their department.
ENAME DNAME DEPTNO
1 BUSH ACCOUNTING 10
2 ELISON ACCOUNTING 10
3 MERKEL ACCOUNTING 10
4 PUTIN RESEARCH 20
5 THATCHER RESEARCH 20
6 CARNEGIE RESEARCH 20
7 FORD RESEARCH 20
8 TOOSK RESEARCH 20
9 BUFFETT SALES 30
10 CHIRACK SALES 30
11 BLAIR SALES 30
12 GATES SALES 30
13 WALTON SALES 30
14 BAROSSO SALES 30

'''33. Exercise'''

Select the names of the employees, their salary and salary grade, but only those whose salary is more than 2000.
ENAME SAL GRADE
44 BLAIR 2850 4
45 MERKEL 2450 4
46 PUTIN 2975 4
52 TOOSK 3000 4
54 CARNEGIE 3000 4
57 BUSH 5000 5

'''34. Exercise'''

Select employees working in London.
ENAME LOC
4 PUTIN LONDON
5 THATCHER LONDON
6 CARNEGIE LONDON
7 FORD LONDON
8 TOOSK LONDON

'''35. Exercise'''

Select employees and their salary grades except those from London.
ENAME LOC GRADE
1 MERKEL NEW YORK 4
2 BUSH NEW YORK 5
3 ELISON NEW YORK 2
9 BAROSSO PARIS 3
10 WALTON PARIS 2
11 CHIRACK PARIS 2
12 BLAIR PARIS 4
13 GATES PARIS 3
14 BUFFETT PARIS 1

==Exercises==

'''36. Exercise'''

Find departments without employees.
DEPTNO DNAME
15 40 OPERATIONS

'''37. *Exercise'''

Select an employee’s name and his/her boss’s name.
Employee Boss
1 TOOSK PUTIN
2 CARNEGIE PUTIN
3 CHIRACK BLAIR
4 BAROSSO BLAIR
5 GATES BLAIR
6 BUFFETT BLAIR
7 WALTON BLAIR
8 ELISON MERKEL
9 FORD CARNEGIE
10 BLAIR BUSH
11 MERKEL BUSH
12 PUTIN BUSH
13 THATCHER TOOSK

'''38. Exercise'''

Select an employee’s name, his/her boss’s name and show the employees who have no superior.
> be
Employee Boss
1 TOOSK PUTIN
2 CARNEGIE PUTIN
3 CHIRACK BLAIR
4 BAROSSO BLAIR
5 GATES BLAIR
6 BUFFETT BLAIR
7 WALTON BLAIR
8 ELISON MERKEL
9 FORD CARNEGIE
10 BLAIR BUSH
11 MERKEL BUSH
12 PUTIN BUSH
13 THATCHER TOOSK
14 BUSH <NA>

'''39. Exercise'''

Select an employee’s name, his/her boss’s name and show all employees even though they have no subordinates.

+----------+----------+
| Employee | Boss |
+----------+----------+
| BLAIR | BUSH |
| MERKEL | BUSH |
| PUTIN | BUSH |
| CHIRACK | BLAIR |
| BAROSSO | BLAIR |
| GATES | BLAIR |
| BUFFETT | BLAIR |
| WALTON | BLAIR |
| ELISON | MERKEL |
| TOOSK | PUTIN |
| CARNEGIE | PUTIN |
| | CHIRACK |
| | BAROSSO |
| | GATES |
| | BUFFETT |
| | WALTON |
| THATCHER | TOOSK |
| | THATCHER |
| FORD | CARNEGIE |
| | FORD |
| | ELISON |
+----------+----------+

'''40. Exercise*'''

Select an employee’s name, his/her boss’s name and show all employees even though they have no subordinates and also those who have no superior.
Employee Boss
1 <NA> THATCHER
2 <NA> BAROSSO
3 <NA> WALTON
4 TOOSK PUTIN
5 CARNEGIE PUTIN
6 <NA> CHIRACK
7 CHIRACK BLAIR
8 BAROSSO BLAIR
9 GATES BLAIR
10 BUFFETT BLAIR
11 WALTON BLAIR
12 ELISON MERKEL
13 FORD CARNEGIE
14 BLAIR BUSH
15 MERKEL BUSH
16 PUTIN BUSH
17 <NA> GATES
18 <NA> FORD
19 <NA> BUFFETT
20 THATCHER TOOSK
21 <NA> ELISON
22 BUSH <NA>

'''41. Exercise'''

Select employees hired earlier than their bosses.
Employee Employee hiredate Boss Boss hiredate
4 BAROSSO 1981-02-20 BLAIR 1981-05-01
7 WALTON 1981-02-22 BLAIR 1981-05-01
10 BLAIR 1981-05-01 BUSH 1981-11-17
11 MERKEL 1981-06-09 BUSH 1981-11-17
12 PUTIN 1981-04-02 BUSH 1981-11-17
13 THATCHER 1980-12-17 TOOSK 1981-12-03

'''42. Exercise*'''

Show employee, their boss and boss's boss
Employee Boss BossBoss
1 FORD CARNEGIE PUTIN
2 THATCHER TOOSK PUTIN
3 TOOSK PUTIN BUSH
4 GATES BLAIR BUSH
5 CHIRACK BLAIR BUSH
6 BAROSSO BLAIR BUSH
7 CARNEGIE PUTIN BUSH
8 WALTON BLAIR BUSH
9 ELISON MERKEL BUSH
10 BUFFETT BLAIR BUSH
11 BLAIR BUSH <NA>
12 MERKEL BUSH <NA>
13 PUTIN BUSH <NA>
14 BUSH <NA> <NA>

== Set operations ==

'''UNION'''

In order to combine two data frames we can use ''rbind'' function

We want to see people earning less than 2000 and more than 3000
> rbind(emp[SAL<2000,],emp[SAL>3000,])
ID ENAME JOB MGR HIREDATE SAL COMM DEPTNO
5 7654 CHIRACK SALESMAN 7698 1981-09-28 1250 1400 30
6 7499 BAROSSO SALESMAN 7698 1981-02-20 1600 300 30
7 7844 GATES SALESMAN 7698 1981-09-08 1500 0 30
8 7900 BUFFETT CLERK 7698 1981-12-03 950 NA 30
9 7521 WALTON SALESMAN 7698 1981-02-22 1250 500 30
11 7369 THATCHER CLERK 7902 1980-12-17 800 NA 20
13 7876 FORD CLERK 7788 1983-01-12 1100 NA 20
14 7934 ELISON CLERK 7782 1982-01-23 1300 NA 10
91 7839 BUSH PRESIDENT NA 1981-11-17 5000 NA 10

If we interested only in names
> union(emp[SAL<2000,"ENAME"],emp[SAL>3000,"ENAME"])
[1] "CHIRACK" "BAROSSO" "GATES" "BUFFETT" "WALTON" "THATCHER" "FORD" "ELISON" "BUSH"

'''INTERSECT Function'''

INTERSECT operator (set intersection) allows you to select rows common for two data frames

Find jobs existing in department 30 and 20
> intersect(emp[DEPTNO==20,"JOB"] ,emp[DEPTNO==30,"JOB"])
[1] "MANAGER" "CLERK"

'''SETDIFF Operator'''

This operator allows to to show rows present in the first query but not present in the second query.

Find jobs in department 30 which do not exist in department 20
> setdiff(emp[DEPTNO==30,"JOB"] ,emp[DEPTNO==20,"JOB"])
[1] "SALESMAN"

==Exercises==

'''43. *Exercise'''

Find post filled in 1982 and 1983.
[1] "CLERK"
<div class="toccolours mw-collapsible mw-collapsed" style="">
Answer >>
<div class="mw-collapsible-content">
intersect(emp[format(emp$HIREDATE,"%Y") == 1982,"JOB"] ,emp[format(emp$HIREDATE,"%Y") == 1981,"JOB"] )
</div>
</div>

'''44. ***Exercise'''

Find jobs which are in department 10 but not in department 20.
[1] "PRESIDENT"
<div class="toccolours mw-collapsible mw-collapsed" style="">
Answer >>
<div class="mw-collapsible-content"> > setdiff(emp[DEPTNO==10,"JOB"] ,emp[DEPTNO==20,"JOB"])

</div>
</div>

R - Merging Data Frames - Revision history

Bernard Szlachta: /* Self-join */