[데이터 전처리 비교- 테이블 결합] LEFT JOIN (왼쪽 결합) 으로 2개 테이블 결합 - 오른쪽 테이블에 미존재 데이터 추출 - 84

 

84. Display the details of those who do not have any person working under them.

* 관리자 업무를 수행하지 않는 직원의 정보를 출력하시오.

---

• 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
• [테이블 결합] LEFT JOIN (왼쪽 결합) 으로 2개 테이블 결합 - 왼쪽 테이블을 기준으로 오른쪽 테이블에 존재하지 않는 데이터 추출

Oracle : LEFT JOIN, IS NULL, 비상관 서브쿼리(Uncorrelated Subqueyr), Not in 파이썬 Pandas : Not 연산자('~') 와 isin(), notnull() R 프로그래밍 : Not 연산자('!') 와 %in%, unlist() R Dplyr : Not 연산자('!') 와 %in%, filter(), pull, is.na() R sqldf : LEFT JOIN, IS NULL, 비상관 서브쿼리(Uncorrelated Subqueyr), Not in Python pandasql : LEFT JOIN, IS NULL, 비상관 서브쿼리(Uncorrelated Subqueyr), Not in R data.table : Not 연산자('!') 와 %in%, is.na() SAS Proc SQL : LEFT JOIN, IS NULL SAS Data Step : Merge 테이블 결합, IN= Python Dfply : Not 연산자('~'), filter_by, isin(), notnull(), pull_list 함수 파이썬 Base 프로그래밍 :

 

---

1. Oracle(오라클)

emp 테이블을 셀프 조인(Self Join)을 수행하여서 사원번호(empno)가 관리자(mgr) 리스트에 존재하지 않는 직원의 정보를 출력한다.

Oracle Programming

select emp.empno, emp.ename, emp.job
from   emp 
       left join emp e 
            on  emp.empno = e.mgr
where  e.mgr is null

 

---

not in 조건을 사용하여서 사원번호(empno) 중에서 관리자(mgr)의 사원번호를 제외한 직원의 정보를 출력한다.

 

Oracle Programming

select empno 
from   emp 
where  empno not in (select mgr from emp where mgr is not null);

 

---

2. Python Pandas(파이썬)

사원번호('empno')중에서 관리자('mgr') 사원번호를 제외한 직원의 정보를 출력한다.

Python Programming

emp[~ emp['empno'].isin(emp[emp['mgr'].notnull() ] ['mgr']) ][['empno','ename','job']]

 

Results

 	empno	ename	job
0	7369	SMITH	CLERK
1	7499	ALLEN	SALESMAN
2	7521	WARD	SALESMAN
4	7654	MARTIN	SALESMAN
9	7844	TURNER	SALESMAN
10	7876	ADAMS	CLERK
11	7900	JAMES	CLERK
13	7934	MILLER	CLERK

 

---

3. R Programming (R Package)

• string Vs Base 함수 : https://stringr.tidyverse.org/articles/from-base.html

사원번호('empno')중에서 관리자('mgr') 사원번호를 제외한 직원의 정보를 출력한다.

 

R Programming

%%R

emp[ ! (emp$empno %in% unlist(emp[!is.na(emp$mgr), "mgr" ])),][c('empno','ename','job')]

 

Results

# A tibble: 8 x 3
  empno ename  job     
        
1  7369 SMITH  CLERK   
2  7499 ALLEN  SALESMAN
3  7521 WARD   SALESMAN
4  7654 MARTIN SALESMAN
5  7844 TURNER SALESMAN
6  7876 ADAMS  CLERK   
7  7900 JAMES  CLERK   
8  7934 MILLER CLERK

 

---

4. R Dplyr Package

1차적으로 emp 테이블에서 관리자('mgr') 사원번호를 선택하고, filter 함수를 사용하여서 다시 emp 테이블의 사원번호('empno')중에서 앞에서 선택한 관리자('mgr') 사원번호를 제외한 직원의 정보를 출력한다.

 

R Programming

%%R

emp %>%
  dplyr::filter( ! empno %in% ( emp  %>% dplyr::filter( !is.na(mgr) ) %>% dplyr::select(mgr) %>% unlist()  )) %>%
  dplyr::select(empno) # %>% pull

 

Results

# A tibble: 8 x 1
  empno
  
1  7369
2  7499
3  7521
4  7654
5  7844
6  7876
7  7900
8  7934

 

---

5. R sqldf Package

• https://en.wikibooks.org/wiki/SQL_Dialects_Reference/Functions_and_expressions/String_functions

emp 테이블에 대하여 사원번호('empno')와 관리자('mgr') 사원번호를 기준으로 셀프 조인(Self Join)을 수행하여서 사원번호(empno)가 관리자(mgr) 리스트에 존재하지 않는 직원의 정보를 출력한다.

 

R Programming

%%R

sqldf(" select emp.empno, emp.ename, emp.job
        from   emp 
               left join emp e 
                    on  emp.empno = e.mgr
        where  e.mgr is null")

 

Results

  empno  ename      job
1  7369  SMITH    CLERK
2  7499  ALLEN SALESMAN
3  7521   WARD SALESMAN
4  7654 MARTIN SALESMAN
5  7844 TURNER SALESMAN
6  7876  ADAMS    CLERK
7  7900  JAMES    CLERK
8  7934 MILLER    CLERK

 

---

not in 조건을 사용하여서 사원번호(empno) 중에서 관리자(mgr)의 사원번호를 제외한 직원의 정보를 출력한다.

 

R Programming

%%R

sqldf(" select empno,ename,job from emp where empno not in (select mgr from emp where mgr is not null);")

 

Results

  empno  ename      job
1  7369  SMITH    CLERK
2  7499  ALLEN SALESMAN
3  7521   WARD SALESMAN
4  7654 MARTIN SALESMAN
5  7844 TURNER SALESMAN
6  7876  ADAMS    CLERK
7  7900  JAMES    CLERK
8  7934 MILLER    CLERK

 

---

6. Python pandasql Package

emp 테이블을 셀프 조인(Self Join)을 수행하여서 사원번호(empno)가 관리자(mgr) 리스트에 존재하지 않는 직원의 정보를 출력한다.

 

Python Programming

ps.sqldf("select empno,ename,job from emp where empno not in (select mgr from emp where mgr is not null); ")

 

Results

 	empno	ename	job
0	7369	SMITH	CLERK
1	7499	ALLEN	SALESMAN
2	7521	WARD	SALESMAN
3	7654	MARTIN	SALESMAN
4	7844	TURNER	SALESMAN
5	7876	ADAMS	CLERK
6	7900	JAMES	CLERK
7	7934	MILLER	CLERK

 

---

7. R data.table Package

1차적으로 emp 테이블에서 관리자('mgr') 사원번호를 선택하고, 다시 emp 테이블의 사원번호('empno')중에서 앞에서 선택한 관리자('mgr') 사원번호를 제외한("!") 직원의 정보를 출력한다.

 

R Programming

%%R
DT      <- data.table(emp)
dept_DT <- data.table(dept)

DT[ !empno %in% DT[!is.na(mgr), mgr ], ]

 

Results

   empno  ename      job  mgr   hiredate  sal comm deptno
1:  7369  SMITH    CLERK 7902 1980-12-17  800   NA     20
2:  7499  ALLEN SALESMAN 7698 1981-02-20 1600  300     30
3:  7521   WARD SALESMAN 7698 1981-02-22 1250  500     30
4:  7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400     30
5:  7844 TURNER SALESMAN 7698 1981-09-08 1500    0     30
6:  7876  ADAMS    CLERK 7788 1983-01-12 1100   NA     20
7:  7900  JAMES    CLERK 7698 1981-12-03  950   NA     30
8:  7934 MILLER    CLERK 7782 1982-01-23 1300   NA     10

 

---

8. SAS Proc SQL

emp 테이블을 셀프 조인(Self Join)을 수행하여서 사원번호(empno)가 관리자(mgr) 리스트에 존재하지 않는 직원의 정보를 출력한다.

 

SAS Statements

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    select emp.empno, emp.ename, emp.job
    from   emp 
           left join emp e 
                on  emp.empno = e.mgr
    where  e.mgr is null;
QUIT;
PROC PRINT;RUN;

 

Results

OBS	empno	ename	job
1	7369	SMITH	CLERK
2	7499	ALLEN	SALESMAN
3	7521	WARD	SALESMAN
4	7654	MARTIN	SALESMAN
5	7844	TURNER	SALESMAN
6	7876	ADAMS	CLERK
7	7900	JAMES	CLERK
8	7934	MILLER	CLERK

 

---

9. SAS Data Step

 

SAS Statements

%%SAS sas

PROC SORT DATA=EMP OUT=EMP_1;
     BY empno;
RUN;

PROC SORT DATA=EMP OUT=EMP_2;
     BY mgr;
RUN;

DATA STATSAS_2;
 MERGE EMP_1(KEEP=empno EMPNO job ENAME RENAME=empno=JOIN_KEY IN=A) 
       EMP_2(KEEP=mgr                   RENAME=mgr  =JOIN_KEY IN=B);
     BY JOIN_KEY;
     IF A=1 AND B=0 THEN OUTPUT STATSAS_2;   /* IF A=1; */
RUN;

PROC PRINT;RUN;

 

Results

OBS	JOIN_KEY ename	job
1	7369	SMITH	CLERK
2	7499	ALLEN	SALESMAN
3	7521	WARD	SALESMAN
4	7654	MARTIN	SALESMAN
5	7844	TURNER	SALESMAN
6	7876	ADAMS	CLERK
7	7900	JAMES	CLERK
8	7934	MILLER	CLERK

 

---

10. Python Dfply Package

 

Python Programming

# [참고] 함수 사용. (52번 예제 참고) : AttributeError: 'DataFrame' object has no attribute 'ix'
@pipe
def pull_list(df, column=-1):
    return df.loc[:, column]

emp >> filter_by(~ X.empno.isin( emp >> filter_by(X.mgr.notnull()) >> select(X.mgr) >> pull_list("mgr") ))

 

Results

 	empno	ename	job		mgr	hiredate	sal	comm	deptno
0	7369	SMITH	CLERK		7902.0	1980/12/17	800	NaN	20
1	7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30
2	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30
4	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30
9	7844	TURNER	SALESMAN	7698.0	1981/09/08	1500	0.0	30
10	7876	ADAMS	CLERK		7788.0	1983/01/12	1100	NaN	20
11	7900	JAMES	CLERK		7698.0	1981/12/03	950	NaN	30
13	7934	MILLER	CLERK		7782.0	1982/01/23	1300	NaN	10

 

 

---

[다양한 데이터 전처리 방법 목록 링크] SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE