[데이터 추출] 상관 서브쿼리 & 카테시안 곱(Cartesian Product)

포스팅 목차

114. Display those employees whose salary is less than his manager but more than salary of any other managers.

* 급여가 담당 관리자보다 적지만 다른 부서의 관리자보다 더 많은 급여를 수령하는 직원의 정보를 출력하시오.

파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
61/62번 참조(Any)
94번 서브쿼리
[데이터 추출] 특정 조건을 만족하는 데이터 추출 - 상관 서브쿼리 & 카테시안 곱(Cartesian Product)
데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql

Oracle : 상관 서브쿼리, Any() 연산자
파이썬 Pandas : pd.merge() 와 assign(foo=1) - 카테시안 곱(Cartesian Product), Query(), lambda 함수, re.sub() - 변수명 변경
R 프로그래밍 : subset(), merge() 의 by= character() 옵션 - 카테시안 곱(Cartesian Product), rename(), %like%, sub(), colnames()
R Dplyr Package : dplyr::inner_join(), dplyr::rename_all(), gsub(), dplyr::full_join() 의 by = character() 옵션 - 카테시안 곱(Cartesian Product), dplyr::summarise(), min()
R sqldf Package : 상관 서브쿼리, EXISTS() 연산자, min()
Python pandasql Package : 상관 서브쿼리, EXISTS() 연산자, min()
R data.table Package : DT Syntax 데이터 결합의 [,k:=1] 와 allow.cartesian=TRUE - 카테시안 곱(Cartesian Product), grep(), colnames()
SAS Proc SQL : 상관 서브쿼리, Any() 연산자, EXISTS() 연산자, min()
SAS Data Step : CROSS JOIN (카테시안 곱) 구현, PROC SUMMARY의 Min, MERGE 구문, IF 조건문
Python Dfply Package : @pipe & def 사용자 정의 함수(inner_join_merge), full_join() & .assign(foo=1) - 카테시안 곱(Cartesian Product), summarize(), Min()
파이썬 Base 프로그래밍 :

1. Oracle(오라클)

상관 서브쿼리를 사용하여서 담당 관리자의 급여보다 적은 급여를 수령하고, 담당 관리자를 제외한 다른 부서의 관리자보다 더 많은 급여를 수령하는 직원의 정보를 출력한다.

Oracle Programming

select * 
from   emp e 
where  sal <    (select sal from emp where empno  = e.mgr) 
  and  sal > any(select sal from emp where empno != e.mgr);

2. Python Pandas(파이썬)

emp 테이블을 self join 형식으로 내부조인을 수행하여서 담당 관리자의 급여보다 적은 급여를 수령하는 직원들을 선택하여서 Table_1을 생성한다. 두 번째 emp 테이블을 self join 형식으로 카테시안 조인을 수행 후 담당 관리자를 제외한 다른 부서의 관리자들의 최소 급여를 선택하여서 Table_2 테이블을 생성한다. 앞 에서 생성한 2개의 테이블을 내부조인(inner Join)으로 결합하여서 Table_1의 직원의 급여가 Table_2의 다른 부서 관리자의 최소 급여보다 큰 급여를 수령하는 직원들을 선택한다.

Python Programming

import re

Table_1 = pd.merge(emp, 
         emp, 
         how='inner', 
         left_on=['mgr'], right_on=['empno']).query('sal_x < sal_y')

Table_2 = pd.merge(emp.assign(foo=1), emp.assign(foo=1), on ='foo').query('empno_x != mgr_y').groupby(['empno_x'])['sal_y'].agg(['min']).reset_index().rename(columns = {'empno_x' : 'empno', 'min' : 'sal_min'})

pd.merge(Table_1,Table_2, 
         how='inner', 
         left_on=['empno_x'], right_on=['empno']).query('sal_x > sal_min').filter(regex='_x').rename(columns=lambda x: re.sub('_x','',x)).head()

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
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
3	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30
4	7844	TURNER	SALESMAN	7698.0	1981/09/08	1500	0.0	30
5	7900	JAMES	CLERK	7698.0	1981/12/03	950	NaN	30
6	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20
7	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30
8	7782	CLARK	MANAGER	7839.0	1981/01/09	2450	NaN	10
9	7876	ADAMS	CLERK	7788.0	1983/01/12	1100	NaN	20
10	7934	MILLER	CLERK	7782.0	1982/01/23	1300	NaN	10

3. R Programming (R Package)

emp 테이블을 self join 형식으로 내부조인을 수행하여서 담당 관리자의 급여보다 적은 급여를 수령하는 직원들을 선택하여서 Table_1을 생성한다. 두 번째 emp 테이블을 self join 형식으로 카테시안 조인을 수행 후 담당 관리자를 제외한 다른 부서의 관리자들의 정보를 선택한 후 aggregate 함수를 사용하여서 최소 급여를 검색하여서 Table_2 테이블을 생성한다. 앞 에서 생성한 2개의 테이블을 내부조인(inner Join)으로 결합하여서 Table_1의 직원의 급여가 Table_2의 다른 부서 관리자의 최소 급여보다 큰 급여를 수령하는 직원들을 선택한다.

R Programming

%%R

Table_1 <- subset( merge(emp, emp,,by.x=c("mgr"),by.y=c("empno"),all=F), sal.x < sal.y )
Table_2 <- rename( aggregate( sal.y~empno.x,FUN=min,data= (subset( merge(emp, emp,by= character(),all.x=TRUE, all.y=TRUE),empno.x != mgr.y ))) ,
                   c('empno'='empno.x' , 'sal_min' = 'sal.y'))

withmooc <- subset( (merge( Table_1,
                            Table_2,
                            by.x=c("empno"),
                            by.y=c("empno"),all=F) ) , 
                    sal.x   >  sal_min)

withmooc <- withmooc[,names(withmooc) %like% "empno|.x"]

colnames(withmooc) <- sub(".x", "", colnames(withmooc))
withmooc

Results

   empno  ename      job   hiredate  sal comm deptno
2   7499  ALLEN SALESMAN 1981-02-20 1600  300     30
3   7521   WARD SALESMAN 1981-02-22 1250  500     30
4   7566  JONES  MANAGER 1981-04-02 2975   NA     20
5   7654 MARTIN SALESMAN 1981-09-28 1250 1400     30
6   7698  BLAKE  MANAGER 1981-03-01 2850   NA     30
7   7782  CLARK  MANAGER 1981-01-09 2450   NA     10
8   7844 TURNER SALESMAN 1981-09-08 1500    0     30
9   7876  ADAMS    CLERK 1983-01-12 1100   NA     20
10  7900  JAMES    CLERK 1981-12-03  950   NA     30
11  7934 MILLER    CLERK 1982-01-23 1300   NA     10

4. R Dplyr Package

1차로 emp 테이블을 self join 형식으로 내부조인을 수행하여서 담당 관리자의 급여보다 적은 급여를 수령하는 직원들을 선택하고, 2차로 emp 테이블을 self join 형식으로 카테시안 조인을 수행 후 담당 관리자를 제외한 다른 부서의 관리자들의 정보를 선택한 후 사원별 summarise 함수를 사용하여서 다른 부서 관리자들의 최소 급여를 생성한다. 앞 에서 생성한 2개의 결과를 내부조인(inner Join)으로 결합하여서 1차로 선택한 직원의 급여가 2차로 선택한 다른 부서 관리자의 최소 급여보다 큰 급여를 수령하는 직원들을 선택한다.

R Programming

%%R

emp %>%
  dplyr::inner_join( emp, by = c('mgr' = 'empno') ) %>%
  dplyr::filter(sal.x < sal.y) %>%
  dplyr::select(empno, ends_with('.x')) %>%
  dplyr::rename_all(~ gsub(".x", "", .)) %>%
  dplyr::inner_join( ( emp %>%
                       dplyr::full_join(emp, by = character() ) %>%
                       dplyr::filter(empno.x != mgr.y) %>%
                       dplyr::group_by(empno.x) %>%
                       dplyr::summarise(sal_min = min(sal.y)) ),
                    by = c('empno' = 'empno.x')
                  ) %>%
  dplyr::filter( sal > sal_min)

Results

`summarise()` ungrouping output (override with `.groups` argument)
# A tibble: 10 x 8
   empno ename  job      hiredate     sal  comm deptno sal_min
   <dbl> <chr>  <chr>    <date>     <dbl> <dbl>  <dbl>   <dbl>
 1  7499 ALLEN  SALESMAN 1981-02-20  1600   300     30     800
 2  7521 WARD   SALESMAN 1981-02-22  1250   500     30     800
 3  7566 JONES  MANAGER  1981-04-02  2975    NA     20     800
 4  7654 MARTIN SALESMAN 1981-09-28  1250  1400     30     800
 5  7698 BLAKE  MANAGER  1981-03-01  2850    NA     30     800
 6  7782 CLARK  MANAGER  1981-01-09  2450    NA     10     800
 7  7844 TURNER SALESMAN 1981-09-08  1500     0     30     800
 8  7876 ADAMS  CLERK    1983-01-12  1100    NA     20     800
 9  7900 JAMES  CLERK    1981-12-03   950    NA     30     800
10  7934 MILLER CLERK    1982-01-23  1300    NA     10     800

5. R sqldf Package

SQLite에서는 ANY / ALL 연산자를 지원하지 않음. Min / Max로 대체 가능.
ANY : sal > ANY(SELECT sal FROM emp WHERE job=’SALESMAN’)
min : sal > (SELECT min(sal) FROM emp WHERE job=’SALESMAN’)
ALL : sal > ALL(SELECT sal FROM emp WHERE job=’SALESMAN’)
max : sal > (SELECT max(sal) FROM emp WHERE job=’SALESMAN’)
상관 서브쿼리를 사용하여서 담당 관리자의 급여보다 적은 급여를 수령하고, 담당 관리자를 제외한 다른 부서의 관리자보다 더 많은 급여를 수령하는 직원의 정보를 출력한다.

R Programming

%%R

sqldf("select *                                                   \
          from emp e                                              \
          where sal<   (select sal from emp where empno = e.mgr)  \
            and sal>   (select min(sal) from emp where empno!=e.mgr);") %>% head()

Results

  empno  ename      job  mgr   hiredate  sal comm deptno
1  7499  ALLEN SALESMAN 7698 1981-02-20 1600  300     30
2  7521   WARD SALESMAN 7698 1981-02-22 1250  500     30
3  7566  JONES  MANAGER 7839 1981-04-02 2975   NA     20
4  7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400     30
5  7698  BLAKE  MANAGER 7839 1981-03-01 2850   NA     30
6  7782  CLARK  MANAGER 7839 1981-01-09 2450   NA     10

R Programming

%%R

sqldf("select ename, sal, comm, sal+ifnull(comm,0) netPay
       from emp e 
       where  sal<   ( select sal from emp where empno = e.mgr)
         and  EXISTS ( select sal from emp where empno!=e.mgr AND e.sal > sal)")

Results

    ename  sal comm netPay
1   ALLEN 1600  300   1900
2    WARD 1250  500   1750
3   JONES 2975   NA   2975
4  MARTIN 1250 1400   2650
5   BLAKE 2850   NA   2850
6   CLARK 2450   NA   2450
7  TURNER 1500    0   1500
8   ADAMS 1100   NA   1100
9   JAMES  950   NA    950
10 MILLER 1300   NA   1300

emp 테이블을 self join 형식으로 내부 조인을 수행하여서 담당 관리자의 급여보다 적은 급여를 수령하는 직원들을 선택하고, 2차로 비등가 카테시안 조인(CROSS JOIN)을 수행하여서 사원번호를 기준으로 담당 관리자를 제외한 다른 부서 관리자들의 최소 급여를 계산 후 앞에서 구한 결과에 내부조인을 수행하여서 다른 부서 관리자들의 최소 급여보다 더 많은 급여를 수령하는 직원 정보를 추출한다.

R Programming

%%R

sqldf("SELECT a.*
       FROM   EMP A,
              EMP B,
              ( SELECT a.empno,min(b.sal) sal_min 
                FROM   emp a,
                       emp b
                WHERE  a.empno <> b.mgr
                group by a.empno ) c
       WHERE  A.mgr   = b.empno
         and  a.sal   <  b.sal
         and  A.empno = c.empno
         and  A.sal   >  c.SAL_MIN
       ")

Results

   empno  ename      job  mgr   hiredate  sal comm deptno
1   7499  ALLEN SALESMAN 7698 1981-02-20 1600  300     30
2   7521   WARD SALESMAN 7698 1981-02-22 1250  500     30
3   7566  JONES  MANAGER 7839 1981-04-02 2975   NA     20
4   7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400     30
5   7698  BLAKE  MANAGER 7839 1981-03-01 2850   NA     30
6   7782  CLARK  MANAGER 7839 1981-01-09 2450   NA     10
7   7844 TURNER SALESMAN 7698 1981-09-08 1500    0     30
8   7876  ADAMS    CLERK 7788 1983-01-12 1100   NA     20
9   7900  JAMES    CLERK 7698 1981-12-03  950   NA     30
10  7934 MILLER    CLERK 7782 1982-01-23 1300   NA     10

6. Python pandasql Package

Python Programming

ps.sqldf("select *                                                \
          from emp e                                              \
          where sal<   (select sal from emp where empno = e.mgr)  \
            and sal>   (select min(sal) from emp where empno!=e.mgr);").head()

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
0	7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30
1	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30
2	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20
3	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30
4	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30

Python Programming

ps.sqldf("select *                                                 \
          from emp e                                               \
          where  sal<   ( select sal from emp where empno = e.mgr) \
            and  EXISTS ( select sal from emp where empno!=e.mgr AND e.sal > sal)").head()

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
0	7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30
1	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30
2	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20
3	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30
4	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30

Python Programming

ps.sqldf("SELECT a.*                                   \
          FROM   EMP A,                                \
                 EMP B,                                \
                 ( SELECT a.empno,min(b.sal) sal_min   \
                   FROM   emp a,                       \
                          emp b                        \
                   WHERE  a.empno <> b.mgr             \
                   group by a.empno ) c                \
          WHERE  A.mgr   = b.empno                     \
            and  a.sal   <  b.sal                      \
            and  A.empno = c.empno                     \
            and  A.sal   >  c.SAL_MIN").head()

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
0	7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30
1	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30
2	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20
3	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30
4	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30

7. R data.table Package

DT[DT, allow.cartesian=TRUE] 에서 현재 부정비교(!=) 지원안함. 상세 내용은 113번 참조
Cross Join(카테시안곱) 생성 : 105번 / 113번 예제 참조

1차로 emp 테이블을 self join 형식으로 내부조인을 수행하여서 담당 관리자의 급여보다 적은 급여를 수령하는 직원들을 선택하고, 2차로 emp 테이블을 self join 형식으로 카테시안 조인을 수행 후 담당 관리자를 제외한 다른 부서의 관리자들의 정보를 선택한 후 사원별로 다른 부서 관리자들의 최소 급여를 생성한m다. 앞 에서 생성한 2개의 결과를 결합하여서 1차로 선택한 직원의 급여가 2차로 선택한 다른 부서 관리자의 최소 급여보다 큰 급여를 수령하는 직원들을 선택한다.

R Programming

%%R

DT          <- data.table(emp)
dept_DT     <- data.table(dept)
salgrade_DT <- data.table(salgrade)

Dtalbe_1 = DT[DT, nomatch=NULL, on = .(mgr= empno), ][sal < i.sal]   # .( mgr= empno, sal<sal) 은 반환 값이 이상.

Dtalbe_2 = (merge(DT[,k:=1],DT[,k:=1], by="k",allow.cartesian=TRUE)[empno.x != mgr.y])[, min(sal.y), keyby = list(empno.x)]


withmooc = Dtalbe_1[Dtalbe_2, nomatch=NULL, on = .( empno = empno.x, sal > V1), ]

mycols   <- grep('^i.', colnames(withmooc), value=TRUE)  # 불필요한 변수명을 선정

withmooc[, !..mycols]  ## 불필요 변수 제외

Results

    empno  ename      job  mgr   hiredate sal comm deptno
 1:  7499  ALLEN SALESMAN 7698 1981-02-20 800  300     30
 2:  7521   WARD SALESMAN 7698 1981-02-22 800  500     30
 3:  7566  JONES  MANAGER 7839 1981-04-02 800   NA     20
 4:  7654 MARTIN SALESMAN 7698 1981-09-28 800 1400     30
 5:  7698  BLAKE  MANAGER 7839 1981-03-01 800   NA     30
 6:  7782  CLARK  MANAGER 7839 1981-01-09 800   NA     10
 7:  7844 TURNER SALESMAN 7698 1981-09-08 800    0     30
 8:  7876  ADAMS    CLERK 7788 1983-01-12 800   NA     20
 9:  7900  JAMES    CLERK 7698 1981-12-03 800   NA     30
10:  7934 MILLER    CLERK 7782 1982-01-23 800   NA     10

8. SAS Proc SQL

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    select * 
    from   emp e
    where  sal <    (select sal from emp where empno  = e.mgr) 
      and  sal > any(select sal from emp where empno ne e.mgr);;
QUIT;
PROC PRINT data=STATSAS_1(obs=5);RUN;

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno
1	7499	ALLEN	SALESMAN	7698	1981-02-20	1600	300	30
2	7521	WARD	SALESMAN	7698	1981-02-22	1250	500	30
3	7566	JONES	MANAGER	7839	1981-04-02	2975	.	20
4	7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30
5	7698	BLAKE	MANAGER	7839	1981-03-01	2850	.	30

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_2 AS
    select ename, sal, comm, sal+COALESCE(comm,0) AS netPay
    from   emp e
    where  sal <  ( select sal from emp where empno  = e.mgr)
      and  EXISTS ( select sal from emp where empno NE e.mgr AND e.sal > sal);
QUIT;
PROC PRINT data=STATSAS_2(obs=5);RUN;

Results

OBS	ename	sal	comm	netPay
1	ALLEN	1600	300	1900
2	WARD	1250	500	1750
3	JONES	2975	.	2975
4	MARTIN	1250	1400	2650
5	BLAKE	2850	.	2850

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_3 AS
    SELECT a.*,
           B.SAL AS MGR_SAL,
           C.SAL_MIN
    FROM   EMP A,
           EMP B,
           ( SELECT a.empno,min(b.sal) AS sal_min 
             FROM   emp a,
                    emp b
             WHERE  a.empno NE b.mgr
             group by a.empno ) c
    WHERE  A.mgr   = b.empno
      and  a.sal   <  b.sal
      and  A.empno = c.empno
      and  A.sal   >  c.SAL_MIN;
QUIT;
PROC PRINT data=STATSAS_3(obs=5 drop=hiredate job);RUN;

Results

OBS	empno	ename	mgr	sal	comm	deptno	MGR_SAL	sal_min
1	7499	ALLEN	7698	1600	300	30	2850	800
2	7521	WARD	7698	1250	500	30	2850	800
3	7566	JONES	7839	2975	.	20	5000	800
4	7654	MARTIN	7698	1250	1400	30	2850	800
5	7698	BLAKE	7839	2850	.	30	5000	800

9. SAS Data Step

DATA STEP(CROSS JOIN-카테시안 곱);

SAS Programming

%%SAS sas

DATA STATSAS_4; 
 SET EMP(RENAME=(SAL=SAL_MIN EMPNO=MIN_EMPNO) KEEP=EMPNO SAL);
     DO I=1 TO KOBS; 
        SET EMP NOBS=KOBS POINT=I;
        IF EMPNO NE MIN_EMPNO THEN OUTPUT;
     END;    
RUN;

PROC SUMMARY DATA=STATSAS_4 NWAY;
     CLASS EMPNO;
     VAR   SAL_MIN;
     OUTPUT OUT=SAL_MIN(DROP=_:) MIN=;
QUIT;

PROC SORT DATA=emp OUT=EMP_1;
     BY EMPNO;
RUN;

PROC SORT DATA=SAL_MIN;
     BY EMPNO;
RUN;

DATA STATSAS_5;
 MERGE emp(IN=A) SAL_MIN(IN=B);
     BY EMPNO;
     IF A AND B;
RUN;

PROC SORT DATA=STATSAS_5 OUT=STATSAS_51(RENAME=EMPNO=MGR_EMPNO);
     BY EMPNO;
RUN;

PROC SORT DATA=EMP OUT=EMP_1(RENAME=MGR=MGR_EMPNO);
     BY MGR;
RUN;

PROC SORT DATA=EMP OUT=EMP_2(RENAME=(EMPNO=MGR_EMPNO SAL=MGR_SAL) KEEP=EMPNO SAL);
     BY EMPNO;
RUN;

DATA EMP_4;
 MERGE STATSAS_51(IN=A) EMP_1(IN=B) EMP_2(IN=C);
     BY MGR_EMPNO;
     IF A AND B AND C;
     IF sal < MGR_sal and sal > SAL_MIN THEN OUTPUT;
     DROP MGR_EMPNO;
RUN;
PROC SORT;
     BY EMPNO;
RUN;
PROC PRINT data=EMP_4(obs=5 drop=hiredate job comm);RUN;

Results

OBS	ename	mgr	sal	deptno	SAL_MIN	empno	MGR_SAL
1	ALLEN	7839	1600	30	800	7499	2850
2	WARD	7839	1250	30	800	7521	2850
3	JONES	.	2975	20	800	7566	5000
4	MARTIN	7839	1250	30	800	7654	2850
5	BLAKE	.	2850	30	800	7698	5000

10. Python Dfply Package

Python Programming

@pipe
def inner_join_merge(df, other, left_on,right_on,suffixes=['_x','_y']):

    joined = df.merge(other, how='inner', left_on=left_on,
                      right_on=right_on , suffixes=suffixes)
    return joined


base_date = emp >> \
              inner_join_merge( emp, left_on=['mgr'], right_on=['empno'], suffixes = ['_x1', '_y1']) >> \
              filter_by(X.sal_x1 < X.sal_y1) >> \
              select(X.empno_x1, ends_with('_x1'))


empno_min = emp.assign(foo=1) >> \
              full_join(emp.assign(foo=1), by="foo") >> \
              filter_by(X.empno_x != X.mgr_y) >> \
              group_by('empno_x') >> \
              summarize(sal_min=X.sal_y.min())

final_data = base_date >> \
               inner_join_merge(empno_min, left_on = ['empno_x1'], right_on=['empno_x']) >> \
               filter_by(X.sal_x1 > X.sal_min) >> \
               select(~X.empno_x)

final_data >> select(~X.job_x1, ~X.hiredate_x1, ~X.comm_x1) >> head()

Results

	empno_x1	ename_x1	mgr_x1	sal_x1	deptno_x1	sal_min
1	7499	ALLEN	7698.0	1600	30	800
2	7521	WARD	7698.0	1250	30	800
3	7654	MARTIN	7698.0	1250	30	800
4	7844	TURNER	7698.0	1500	30	800
5	7900	JAMES	7698.0	950	30	800

변수명 일괄적으로 rename() 적용하기
- literal_eval() 과 eval() : 114번 , 154번

테이블의 변수 리스트를 사용하여서 변경하기 위한 코드를 작성 후 literal_eval() 함수를 사용하여서 문자열을 프로그램으로 인식하여 실행한다.

Python Programming

@pipe
def rename_fun(df, col_name):
    return df.rename(columns=col_name)


from ast import literal_eval
s =  '{'+','.join([ "'" + str(col) + "_x1'" + ": '" + str(col) + "'"   for col in final_data.columns ]) +'}'

print( literal_eval(s) )

final_data >> rename_fun( literal_eval(s) ) >> select(~X.job_x1, ~X.hiredate_x1) >> head()

{'empno_x1_x1': 'empno_x1', 'ename_x1_x1': 'ename_x1', 'job_x1_x1': 'job_x1', 'mgr_x1_x1': 'mgr_x1', 'hiredate_x1_x1': 'hiredate_x1', 'sal_x1_x1': 'sal_x1', 'comm_x1_x1': 'comm_x1', 'deptno_x1_x1': 'deptno_x1', 'sal_min_x1': 'sal_min'}

Results

	empno_x1	ename_x1	mgr_x1	sal_x1	comm_x1	deptno_x1	sal_min
1	7499	ALLEN	7698.0	1600	300.0	30	800
2	7521	WARD	7698.0	1250	500.0	30	800
3	7654	MARTIN	7698.0	1250	1400.0	30	800
4	7844	TURNER	7698.0	1500	0.0	30	800
5	7900	JAMES	7698.0	950	NaN	30	800

Pandas의 Rename 함수 사용

Python Programming

final_data.rename(columns=lambda x: re.sub('_x1','',x)).rename(columns = {"empno_x":"ss"}) >> head()

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno	sal_min
1	7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30	800
2	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30	800
3	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30	800
4	7844	TURNER	SALESMAN	7698.0	1981/09/08	1500	0.0	30	800
5	7900	JAMES	CLERK	7698.0	1981/12/03	950	NaN	30	800

[참고] dfply에서 rename자동 구현 - 미완성

Python Programming

withmooc.columns = withmooc.columns.str.replace('q','x')
withmooc

Python Programming

[ str(col) for col in emp.columns ]

Results

['empno', 'ename', 'job', 'mgr', 'hiredate', 'sal', 'comm', 'deptno']

Python Programming

list(emp.columns)

Results

['empno', 'ename', 'job', 'mgr', 'hiredate', 'sal', 'comm', 'deptno']

Python Programming

list(map(lambda i: i + '=' + 'X.' + i + '_x', emp.columns))

Results

['empno=X.empno_x',
 'ename=X.ename_x',
 'job=X.job_x',
 'mgr=X.mgr_x',
 'hiredate=X.hiredate_x',
 'sal=X.sal_x',
 'comm=X.comm_x',
 'deptno=X.deptno_x']

Python Programming

s =  ','.join([ str(col) + '=' + 'X.' + str(col) + '_x'  for col in emp.columns ])
s

Results

'empno=X.empno_x,ename=X.ename_x,job=X.job_x,mgr=X.mgr_x,hiredate=X.hiredate_x,sal=X.sal_x,comm=X.comm_x,deptno=X.deptno_x'

[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 전처리 방법 리스트

저작자표시 비영리 변경금지

'통계프로그램 비교 시리즈 > 프로그래밍비교(Oracle,Python,R,SAS)' 카테고리의 다른 글

[데이터 추출] 하위 Top 5 추출 - 윈도우 함수 - 116 (오라클 SQL, R, Python, SAS) (0)	2022.12.10
[데이터 추출] 전체 합계 추가하기 - 스칼라 서브쿼리와 Cross join - 115 (오라클 SQL, R, Python, SAS) (0)	2022.12.09
[데이터 추출] 결측치 대체, 카테시안 곱(Cartesian Product) - Full Join, Any 연산자 - 113 (0)	2022.12.07
[데이터 추출] 특정 조건을 만족하는 데이터 추출 - 상관 서브쿼리, 평균 - 112 (0)	2022.12.07
[데이터 추출] 특정 조건을 만족하는 데이터 추출 - 비상관 서브쿼리, 평균 - 111 (0)	2022.12.06

[데이터 추출] 상관 서브쿼리 & 카테시안 곱(Cartesian Product) - 114

114. Display those employees whose salary is less than his manager but more than salary of any other managers.

1. Oracle(오라클)

2. Python Pandas(파이썬)

3. R Programming (R Package)

4. R Dplyr Package

5. R sqldf Package

6. Python pandasql Package

7. R data.table Package

8. SAS Proc SQL

9. SAS Data Step

DATA STEP(CROSS JOIN-카테시안 곱);

10. Python Dfply Package

'통계프로그램 비교 시리즈 > 프로그래밍비교(Oracle,Python,R,SAS)' 카테고리의 다른 글

댓글

티스토리툴바

[데이터 추출] 상관 서브쿼리 & 카테시안 곱(Cartesian Product) - 114

114. Display those employees whose salary is less than his manager but more than salary of any other managers.

1. Oracle(오라클)

2. Python Pandas(파이썬)

3. R Programming (R Package)

4. R Dplyr Package

5. R sqldf Package

6. Python pandasql Package

7. R data.table Package

8. SAS Proc SQL

9. SAS Data Step

DATA STEP(CROSS JOIN-카테시안 곱);

10. Python Dfply Package

'통계프로그램 비교 시리즈 > 프로그래밍비교(Oracle,Python,R,SAS)' 카테고리의 다른 글

관련글

댓글

티스토리툴바