[데이터 추출] 하위 Top 5 추출 - 윈도우 함수

포스팅 목차

116. Find out the last 5(least) earner of the company?

* 최소 급여 수령자 하위 5명의 정보를 출력하시오.

파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
Cross Join 생성(dummy) : 97번
fuzzyjoin::fuzzy_left_join : 116번
유사문제 : 113번
상위 5명 : 106번
[데이터 추출] 하위 Top 5 추출 - 윈도우 함수
데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql

Oracle : ROW_NUMBER(), RANK()
파이썬 Pandas : pd.merge()의 Cross Join(카테시안곱), nlargest()
R 프로그래밍 : aggregate(), function(x) 사용자 정의 함수, fuzzyjoin::fuzzy_left_join(), subset(), order(), row_number()
R Dplyr Package : fuzzyjoin::fuzzy_left_join(), dplyr::full_join(), dplyr::summarise(), n_distinct(), dplyr::inner_join()
R sqldf Package : ROW_NUMBER(), RANK()
Python pandasql Package : ROW_NUMBER(), RANK()
R data.table Package : DT Syntax 방식 데이터 결합의 Cross Join(카테시안곱), fuzzyjoin::fuzzy_left_join(), ifelse()
SAS Proc SQL : monotonic()
SAS Data Step : PROC RANK, N
Python Dfply Package : apply 함수, lambda 함수, make_symbolic(), if_else(), np.where, case_when(), replace()
파이썬 Base 프로그래밍 :

1. Oracle(오라클)

상관서브쿼리(Correlated Subquery)를 사용하여서 본인의 급여보다 적은 급여를 수령하는 직원의 수를 카운트하여서 순위가 5 미만인 직원 정보를 출력한다. 최하위 급여 수령자는 본인보다 더 적은 급여를 받는 직원이 없으므로 0이 반환되어서 5 미만을 지정하면 최하위 급여 수령자(0)를 포함하여 5명이 선택된다.

Oracle Programming

select e.*,
       (select count(*) from emp where sal<e.sal) rank
from   emp e 
where  5 > (select count(*) from emp where sal<e.sal) 
order 
   by  sal

WINDOWS 함수 Row_number()를 사용하여서 급여(‘SAL’)를 기준으로 순번을 부여 후 최하위 급여 수령자 5명을 선택한다.

Oracle Programming

SELECT *
FROM   ( select ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY 1) ROW_NUM,
                RANK() OVER (ORDER BY SAL) ROW_RANK,
                a.*
         from   emp a
       )
WHERE ROW_NUM <= 5

2. Python Pandas(파이썬)

Cross Join을 수행 후 본인의 급여보다 적은 급여를 수령하는 직원의 수(순위)를 카운트(‘sal_y’)하고, 이 결과를 Emp 테이블과 조인하여서 순위가 5 미만인 직원 정보를 출력한다. 최하위 급여 수령자는 본인보다 더 적은 급여를 받는 직원이 없으므로 NaN 값이 반환되고, 이를 0으로 치환 후 5 미만을 지정하여서 최하위 급여 수령자(0)를 포함하여 총 5명의 정보가 선택된다.

Python Programming

pd.merge( emp ,
          (pd.merge(emp.assign(foo=1), emp.assign(foo=1), on ='foo').query('sal_x > sal_y').groupby('empno_x')['sal_y'].count().reset_index()),
          "left", 
          left_on=['empno'], right_on=['empno_x']
        ).replace({'sal_y':np.nan},{'sal_y':0}).query('sal_y < 5')

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno	empno_x	sal_y
0	7369	SMITH	CLERK	7902.0	1980/12/17	800	NaN	20	NaN	0.0
2	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30	7521.0	3.0
4	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30	7654.0	3.0
10	7876	ADAMS	CLERK	7788.0	1983/01/12	1100	NaN	20	7876.0	2.0
11	7900	JAMES	CLERK	7698.0	1981/12/03	950	NaN	30	7900.0	1.0

3. R Programming (R Package)

fuzzy_left_join함수를 사용하여서 비등가 cross Join을 수행하여 본인의 급여보다 적은 급여를 수령하는 직원을 선택한 후 aggregate 함수를 사용하여 본인 급여보다 적은 급여를 수령하는 직원의 수(순위)를 카운트(‘sal.y’)한다. 이 결과를 Emp 테이블과 조인하여서 순위가 5 미만인 직원 정보를 출력한다. 최하위 급여 수령자는 본인보다 더 적은 급여를 받는 직원이 없으므로 NaN 값이 반환되고, 이를 0으로 치환 후 5 미만을 지정하여서 최하위 급여 수령자(0)를 포함하여 총 5명의 정보가 선택된다.

비등가조인에 대한 cross join (fuzzy_left_join)
기존 cross join으로 처리시 최소 급여자(7369 / SMITH)가 a.sal > b.sal 에서 누락 됨.
length(COUNT(*)) 계산 시 에도 null값에 대한 처리가 필요하여 function 처리

R Programming

%%R

library(fuzzyjoin)

Cross_table <- aggregate( sal.y~empno.x,
                          FUN=function(x) if(is.na(x)) 0 else length(x),  # count 시 null 칼럼에 대하여 누락 방지(0으로 출력 되도록)
                          na.action=NULL,
                          data= (fuzzyjoin::fuzzy_left_join( emp, emp,
                                                             by = c("sal" = "sal"),
                                                             match_fun = list(`>`) )))


subset( merge(emp,Cross_table,by.x=c("empno"),by.y=c("empno.x"),all=F) , 
        sal.y <5)

Results

   empno  ename      job  mgr   hiredate  sal comm deptno sal.y
1   7369  SMITH    CLERK 7902 1980-12-17  800   NA     20     0
3   7521   WARD SALESMAN 7698 1981-02-22 1250  500     30     3
5   7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400     30     3
11  7876  ADAMS    CLERK 7788 1983-01-12 1100   NA     20     2
12  7900  JAMES    CLERK 7698 1981-12-03  950   NA     30     1

4. R Dplyr Package

fuzzy_left_join함수를 사용하여서 비등가 cross Join을 수행하여 본인의 급여보다 적은 급여를 수령하는 직원을 선택한 후 summarise 함수를 사용하여 본인 급여보다 적은 급여를 수령하는 직원의 수(순위)를 카운트(‘sal_count’)한다. 이 결과를 Emp 테이블과 조인하여서 순위가 5 미만인 직원 정보를 출력한다.

최하위 급여 수령자는 본인보다 더 적은 급여를 받는 직원이 없으므로 0 값이 반환되어서 5 미만을 지정하면 최하위 급여 수령자(0)를 포함하여 총 5명의 정보가 선택된다.

[참고] summarise_all(funs(sum(. > 0, na.rm = TRUE)))
cross Join 후 filter를 사용하는 경우 최소값(SMITH) 자료가 누락 됨에 따라서 fuzzy_left_join 사용(Cross Join(Full_join) 후 emp 테이블에 Left Join으로 구현 가능)

R Programming

%%R

emp %>%
  fuzzyjoin::fuzzy_left_join( emp, by = c("sal" = "sal"),match_fun = list(`>`) ) %>%
  dplyr::group_by(empno.x) %>%
  dplyr::summarise(sal_count = n_distinct(empno.y,na.rm = TRUE)) %>%
  dplyr::inner_join(emp, by = c("empno.x" = "empno")) %>%
  dplyr::filter(sal_count<5)

Results

`summarise()` ungrouping output (override with `.groups` argument)
# A tibble: 5 x 9
  empno.x sal_count ename  job        mgr hiredate     sal  comm deptno
    <dbl>     <int> <chr>  <chr>    <dbl> <date>     <dbl> <dbl>  <dbl>
1    7369         0 SMITH  CLERK     7902 1980-12-17   800    NA     20
2    7521         3 WARD   SALESMAN  7698 1981-02-22  1250   500     30
3    7654         3 MARTIN SALESMAN  7698 1981-09-28  1250  1400     30
4    7876         2 ADAMS  CLERK     7788 1983-01-12  1100    NA     20
5    7900         1 JAMES  CLERK     7698 1981-12-03   950    NA     30

full Join 함수에서 Cross join을 수행 후 filter를 사용하는 경우 본인의 급여보다 적은 급여 수령자가 없는 최하위 급여 수령자 ‘SMITH’ 자료가 누락 됨에 따라서 emp 테이블에 Left Join 을 다시 하여서 smith 데이터를 포함하여 처리

R Programming

%%R

emp %>%
  dplyr::left_join(  ( emp %>%
                         dplyr::full_join( emp, by = character() ) %>%
                         dplyr::group_by(empno.x) %>%
                         dplyr::filter(sal.x > sal.y ) %>%
                         dplyr::tally()
                      ) ,
                      by = c("empno" = "empno.x")  ) %>%
  dplyr::filter(ifelse(is.na(n),0,n)<5)

Results

# A tibble: 5 x 9
  empno ename  job        mgr hiredate     sal  comm deptno     n
  <dbl> <chr>  <chr>    <dbl> <date>     <dbl> <dbl>  <dbl> <int>
1  7369 SMITH  CLERK     7902 1980-12-17   800    NA     20    NA
2  7521 WARD   SALESMAN  7698 1981-02-22  1250   500     30     3
3  7654 MARTIN SALESMAN  7698 1981-09-28  1250  1400     30     3
4  7876 ADAMS  CLERK     7788 1983-01-12  1100    NA     20     2
5  7900 JAMES  CLERK     7698 1981-12-03   950    NA     30     1

[참고] NA 포함 데이터 전체 제외

R Programming

%%R

emp %>% filter(complete.cases(.))

5. R sqldf Package

R Programming

%%R

sqldf(" select e.*,
               (select count(*) from emp where sal<e.sal) rank
        from   emp e 
        where  5 > (select count(*) from emp where sal<e.sal) 
        order 
           by  sal;")

Results

  empno  ename      job  mgr   hiredate  sal comm deptno rank
1  7369  SMITH    CLERK 7902 1980-12-17  800   NA     20    0
2  7900  JAMES    CLERK 7698 1981-12-03  950   NA     30    1
3  7876  ADAMS    CLERK 7788 1983-01-12 1100   NA     20    2
4  7521   WARD SALESMAN 7698 1981-02-22 1250  500     30    3
5  7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400     30    3

비등가 Left join을 수행하여서 본인의 급여보다 적은 급여를 수령하는 직원의 수(등수)를 카운트하여 5 미만을 지정하여서 최하위 급여수령자 5명의 정보를 추출한다.

R Programming

%%R

sqldf("select *
       from   emp a,
              ( SELECT a.empno,
                       count(b.empno) sal_count
                from   emp a 
                       left join emp b
                            on   a.sal > b.sal
                group by a.empno ) b
       where a.empno = b.empno
         and b.sal_count < 5
     ")

Results

  empno  ename      job  mgr   hiredate  sal comm deptno empno sal_count
1  7369  SMITH    CLERK 7902 1980-12-17  800   NA     20  7369         0
2  7521   WARD SALESMAN 7698 1981-02-22 1250  500     30  7521         3
3  7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400     30  7654         3
4  7876  ADAMS    CLERK 7788 1983-01-12 1100   NA     20  7876         2
5  7900  JAMES    CLERK 7698 1981-12-03  950   NA     30  7900         1

쉽게 WINDOWS 함수를 사용하여서 급여(‘SAL’)를 기준으로 최하위 급여 수령자 5명을 선택한다.

R Programming

%%R

sqldf("SELECT *
       FROM   ( select RANK() OVER (ORDER BY SAL) ROW_RANK,a.*
                from   emp a
              )
       WHERE ROW_RANK < 5
     ")

Results

  ROW_RANK empno  ename      job  mgr   hiredate  sal comm deptno
1        1  7369  SMITH    CLERK 7902 1980-12-17  800   NA     20
2        2  7900  JAMES    CLERK 7698 1981-12-03  950   NA     30
3        3  7876  ADAMS    CLERK 7788 1983-01-12 1100   NA     20
4        4  7521   WARD SALESMAN 7698 1981-02-22 1250  500     30
5        4  7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400     30

6. Python pandasql Package

서브쿼리(Sub-query)

Python Programming

ps.sqldf("select * from emp e where 5 > (select count(*) from emp where sal<e.sal) order by sal;")

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
0	7369	SMITH	CLERK	7902.0	1980/12/17	800	NaN	20
1	7900	JAMES	CLERK	7698.0	1981/12/03	950	NaN	30
2	7876	ADAMS	CLERK	7788.0	1983/01/12	1100	NaN	20
3	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

테이블 조인

Python Programming

ps.sqldf("select *                                   \
          from   emp a,                              \
                 ( SELECT a.empno,                   \
                          count(b.empno) sal_count   \
                   from   emp a                      \
                          left join emp b            \
                               on   a.sal > b.sal    \
                   group by a.empno ) b              \
          where a.empno = b.empno                    \
            and b.sal_count < 5")

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno	empno	sal_count
0	7369	SMITH	CLERK	7902.0	1980/12/17	800	NaN	20	7369	0
1	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30	7521	3
2	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30	7654	3
3	7876	ADAMS	CLERK	7788.0	1983/01/12	1100	NaN	20	7876	2
4	7900	JAMES	CLERK	7698.0	1981/12/03	950	NaN	30	7900	1

SQLite Windows Functions (윈도우 함수)

Python Programming

ps.sqldf("SELECT *                                                     \
          FROM   ( select RANK() OVER (ORDER BY SAL) ROW_RANK,a.*      \
                   from   emp a                                        \
                 )                                                     \
          WHERE ROW_RANK < 5")

Results

	ROW_RANK	empno	ename	job	mgr	hiredate	sal	comm	deptno
0	1	7369	SMITH	CLERK	7902.0	1980/12/17	800	NaN	20
1	2	7900	JAMES	CLERK	7698.0	1981/12/03	950	NaN	30
2	3	7876	ADAMS	CLERK	7788.0	1983/01/12	1100	NaN	20
3	4	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30
4	4	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30

7. R data.table Package

DT Syntax 방식으로 비등가 cross Join을 수행하여 본인의 급여보다 적은 급여를 수령하는 직원을 선택한 후에 본인 급여보다 적은 급여를 수령하는 직원의 수(순위)를 카운트(‘sal_count’)한다. 이 결과를 Emp 테이블에 조인하여서 순위가 5 미만인 직원 정보를 출력한다.

R Programming

%%R

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

Dtable_1 = DT[DT[,sal_temp:=sal],on = .(sal>sal),nomatch=NULL,allow.cartesian=TRUE,.(empno,sal,sal_temp,i.empno,i.sal_temp)][, .(`sal_count` = sum(!is.na(sal))), keyby = list(empno)]

Dtable_1[DT, on = "empno" ][ifelse(is.na(sal_count),0, sal_count)<5]

Results

   empno sal_count  ename      job  mgr   hiredate  sal comm deptno sal_temp
1:  7369        NA  SMITH    CLERK 7902 1980-12-17  800   NA     20      800
2:  7521         3   WARD SALESMAN 7698 1981-02-22 1250  500     30     1250
3:  7654         3 MARTIN SALESMAN 7698 1981-09-28 1250 1400     30     1250
4:  7876         2  ADAMS    CLERK 7788 1983-01-12 1100   NA     20     1100
5:  7900         1  JAMES    CLERK 7698 1981-12-03  950   NA     30      950

fuzzy_left_join함수를 사용하여서 비등가 cross Join을 수행하여 본인의 급여보다 적은 급여를 수령하는 직원을 선택한 후에 본인 급여보다 적은 급여를 수령하는 직원의 수(순위)를 카운트(‘sal_count’)한다. 이 결과를 Emp 테이블에 추가하여서 순위가 5 미만인 직원 정보를 출력한다.

최하위 급여 수령자는 본인보다 더 적은 급여를 받는 직원이 없으므로 0 값이 반환되어서 5 미만을 지정하면 최하위 급여 수령자(0)를 포함하여 총 5명의 정보가 선택된다.* fuzzyjoin::fuzzy_left_join 테이블 결합 후 데이터 테이블로 재 변경

R Programming

%%R

Dtable_1 = data.table(fuzzyjoin::fuzzy_left_join( DT,DT, by = c("sal" = "sal"),match_fun = list(`>`) ))[, .(`sal_count` = sum(!is.na(sal.y))), keyby = list(empno.x)]

Dtable_1[DT, nomatch=NULL, on = .( empno.x = empno), ][sal_count<5]

Results

   empno.x sal_count  ename      job  mgr   hiredate  sal comm deptno sal_temp
1:    7369         0  SMITH    CLERK 7902 1980-12-17  800   NA     20      800
2:    7521         3   WARD SALESMAN 7698 1981-02-22 1250  500     30     1250
3:    7654         3 MARTIN SALESMAN 7698 1981-09-28 1250 1400     30     1250
4:    7876         2  ADAMS    CLERK 7788 1983-01-12 1100   NA     20     1100
5:    7900         1  JAMES    CLERK 7698 1981-12-03  950   NA     30      950

8. SAS Proc SQL

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    select e.*,
           (select count(*) from emp where sal<e.sal) AS rank
    from   emp e 
    where  5 > (select count(*) from emp where sal<e.sal) 
    order 
       by  sal;
QUIT;
PROC PRINT;RUN;

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno	rank
1	7369	SMITH	CLERK	7902	1980-12-17	800	.	20	0
2	7900	JAMES	CLERK	7698	1981-12-03	950	.	30	1
3	7876	ADAMS	CLERK	7788	1983-01-12	1100	.	20	2
4	7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30	3
5	7521	WARD	SALESMAN	7698	1981-02-22	1250	500	30	3

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_2 AS
    select *
    from   emp a,
           ( SELECT a.empno,
                    count(b.empno) as sal_count
             from   emp a 
                    left join emp b
                         on   a.sal > b.sal
             group by a.empno ) b
    where a.empno = b.empno
      and b.sal_count < 5;
QUIT;
PROC PRINT;RUN;

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno	sal_count
1	7369	SMITH	CLERK	7902	1980-12-17	800	.	20	0
2	7521	WARD	SALESMAN	7698	1981-02-22	1250	500	30	3
3	7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30	3
4	7876	ADAMS	CLERK	7788	1983-01-12	1100	.	20	2
5	7900	JAMES	CLERK	7698	1981-12-03	950	.	30	1

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_2 AS
    select *
    from   emp a,
           ( SELECT a.empno,
                    count(b.empno) as sal_count
             from   emp a 
                    left join emp b
                         on   a.sal > b.sal
             group by a.empno ) b
    where a.empno = b.empno
      and b.sal_count < 5;
QUIT;
PROC PRINT;RUN;

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno	sal_count
1	7369	SMITH	CLERK	7902	1980-12-17	800	.	20	0
2	7521	WARD	SALESMAN	7698	1981-02-22	1250	500	30	3
3	7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30	3
4	7876	ADAMS	CLERK	7788	1983-01-12	1100	.	20	2
5	7900	JAMES	CLERK	7698	1981-12-03	950	.	30	1

SAS에서는 인라인뷰에서 ORDER BY를 사용하지 못하는 단점이 있음;
사전 정렬 후 monotonic() 사용;
통계분석연구회 카페;
PROC SQL에서 중복제거는 일반적으로 SUBQUERY나 JOIN 으로 일반적으로 처리하지만, MONOTONIC() 함수로 처리해보았습니다;
MONOTONIC은 메모리에서 처리 순서에 따라서 씨퀀스 방식이 달라지므로 권해드리는 방식은 아님을 알려드립니다;
PROC SQL에서 MONOTONIC은 정식 지원 옵션은 아님;
http://support.sas.com/kb/15/138.html;
Usage Note 15138: Support for the MONOTONIC() function in PROC SQL;
[ 참고 ]
- 데이터 중복제거(first. / last대안) - MAX , MIN : [링크]
- [SQL] PROC SQL에서 nodupkye 사용(monotonic) : [링크]
- [MAX] 변수중에 2번째 큰값 찾기 [Link]

SAS Programming

%%SAS sas

* 사전 소팅 필수;
PROC SQL;
  CREATE TABLE STATSAS_3 AS
    select e.*
    from   emp e 
    order 
       by  sal;

  create table STATSAS_4 as 
    select a.*, 
           monotonic() as sal_rank
    from   STATSAS_3 a
    HAVING sal_rank<=5;  * HAVING CALCULATED sal_rank<=5;
QUIT;
PROC PRINT;RUN;

proc sql;
  create table STATSAS_4(where=(monotonic()<=5)) as
    select e.*
    from   emp e 
    order 
       by  sal;
quit;
PROC PRINT;RUN;

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno	sal_rank
1	7369	SMITH	CLERK	7902	1980-12-17	800	.	20	1
2	7900	JAMES	CLERK	7698	1981-12-03	950	.	30	2
3	7876	ADAMS	CLERK	7788	1983-01-12	1100	.	20	3
4	7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30	4
5	7521	WARD	SALESMAN	7698	1981-02-22	1250	500	30	5

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno
1	7369	SMITH	CLERK	7902	1980-12-17	800	.	20
2	7900	JAMES	CLERK	7698	1981-12-03	950	.	30
3	7876	ADAMS	CLERK	7788	1983-01-12	1100	.	20
4	7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30
5	7521	WARD	SALESMAN	7698	1981-02-22	1250	500	30

[참고] 올림차순;

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_5 AS
    SELECT A.empno,
           A.ename,
           A.sal,
           COUNT(B.empno)+1 AS RANK
    FROM   EMP A
           LEFT JOIN EMP B
                ON  A.SAL  > B.sal
    GROUP BY
           A.empno,
           A.ename,
           A.sal
    HAVING RANK <= 5;
QUIT;
PROC PRINT;RUN;

Results

OBS	empno	ename	sal	RANK
1	7369	SMITH	800	1
2	7521	WARD	1250	4
3	7654	MARTIN	1250	4
4	7876	ADAMS	1100	3
5	7900	JAMES	950	2

9. SAS Data Step

- RANK 프로시져 사용;

SAS Programming

%%SAS sas

PROC RANK DATA=EMP OUT=STATSAS_6(WHERE=(RANK<=5));
     RANKS RANK;
     VAR SAL ;
RUN;
PROC PRINT;RUN;

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno	RANK
1	7369	SMITH	CLERK	7902	1980-12-17	800	.	20	1.0
2	7521	WARD	SALESMAN	7698	1981-02-22	1250	500	30	4.5
3	7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30	4.5
4	7876	ADAMS	CLERK	7788	1983-01-12	1100	.	20	3.0
5	7900	JAMES	CLERK	7698	1981-12-03	950	.	30	2.0

SAS Programming

%%SAS sas

PROC SORT DATA=EMP OUT=EMP_1;
     BY SAL;
RUN;
DATA STATSAS_7;
 SET EMP_1;
     BY SAL;
     IF _N_ <=5;
RUN;
PROC PRINT;RUN;

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno
1	7369	SMITH	CLERK	7902	1980-12-17	800	.	20
2	7900	JAMES	CLERK	7698	1981-12-03	950	.	30
3	7876	ADAMS	CLERK	7788	1983-01-12	1100	.	20
4	7521	WARD	SALESMAN	7698	1981-02-22	1250	500	30
5	7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30

SAS Programming

%%SAS sas

PROC SORT DATA=EMP OUT=EMP_1;
     BY SAL;
RUN;
DATA STATSAS_8;
 SET EMP_1;
     BY SAL;
     RANK + 1;
     IF RANK <=5;
RUN;

PROC PRINT;RUN;

Results

OBS	empno	ename	job	mgr	hiredate	sal	comm	deptno	RANK
1	7369	SMITH	CLERK	7902	1980-12-17	800	.	20	1
2	7900	JAMES	CLERK	7698	1981-12-03	950	.	30	2
3	7876	ADAMS	CLERK	7788	1983-01-12	1100	.	20	3
4	7521	WARD	SALESMAN	7698	1981-02-22	1250	500	30	4
5	7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30	5

10. Python Dfply Package

조건문 (관련 내용 Link)

Python Programming

emp >>  \
  mutate(var1 = X.sal.apply(lambda var : 'Y' if var > 1000 else 'N'),
         var2 = X.apply(lambda x : 'Y' if x['sal'] > 1000 else 'N' ,axis=1),
         var3 = make_symbolic(lambda series: ['Y' if x > 1000 else 'N' for x in series])(X.sal),
         var4 = if_else( X.sal > 1000,'Y', 'N' ),
         var5 = make_symbolic(np.where)(X.sal > 1000, 'Y', 'N'),
         var6 = case_when([X.sal > 1000, 'Y'], [True,'N']),
         ref7 = (X.sal > 1000).replace({True: 'Y', False: 'N'} )
        ) >> \
  select(~X.job, ~X.hiredate, ~X.comm, ~X.mgr, ~X.ename, ~X.deptno) >> head()

Results

	empno	sal	var1	var2	var3	var4	var5	var6	var7
0	7369	800	N	N	N	N	N	N	N
1	7499	1600	Y	Y	Y	Y	Y	Y	Y
2	7521	1250	Y	Y	Y	Y	Y	Y	Y
3	7566	2975	Y	Y	Y	Y	Y	Y	Y
4	7654	1250	Y	Y	Y	Y	Y	Y	Y

조건에 만족하는 칼럼 찾기

Python Programming

cb = lambda x: 'BLAKE' in str(x)

display( emp >> mutate(mf=[cb(x) for x in emp.ename]) >> filter_by(X.mf) )
display( emp >> mutate(mf= make_symbolic(lambda series: [cb(x) for x in series])(X.ename)) >> filter_by(X.mf) )
emp >> filter_by(make_symbolic(lambda series: pd.Series([cb(x) for x in series]))(X.ename))

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno	mf
5	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30	True

	empno	ename	job	mgr	hiredate	sal	comm	deptno	mf
5	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30	True

	empno	ename	job	mgr	hiredate	sal	comm	deptno
5	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30

Python Programming

def get_join_parameters(join_kwargs):
    """
    Convenience function to determine the columns to join the right and
    left DataFrames on, as well as any suffixes for the columns.
    """

    by = join_kwargs.get('by', None)
    suffixes = join_kwargs.get('suffixes', ('_x', '_y'))
    if by is None:
        left_on, right_on = None, None
    else:
        if isinstance(by, str):
            left_on, right_on = by, by
        else:
            if not isinstance(by[0], str):
                left_on  = by[0]
                right_on = by[1]                         # 소스에는 right_in으로 되어 있음.
            else:
                left_on, right_on = by, by
    return left_on, right_on, suffixes

@pipe
def left_join(df, other, **kwargs):
    left_on, right_on, suffixes = get_join_parameters(kwargs)
    joined = df.merge(other, how='left', left_on=left_on,
                      right_on=right_on, suffixes=suffixes)
    return joined

apply / Lambda 함수로 조건문 사용

Python Programming

emp >> \
  left_join ( ( emp.assign(foo=1)                          >> \
                  full_join( emp.assign(foo=1), by="foo" ) >> \
                  group_by('empno_x')                      >> \
                  filter_by(X.sal_x > X.sal_y)             >> \
                  summarize( emp_cnt = X.empno_x.count()) ),
              by=[ ["empno"],["empno_x"] ] )                                        >> \
  mutate( emp_cnt_1 = X.emp_cnt.apply(lambda x : x if (pd.notnull(x)) else 0 )    ) >> \
  filter_by( X.emp_cnt_1 < 5 )                                                      >> \
  select(~X.job, ~X.hiredate, ~X.deptno)

Results

	empno	ename	mgr	sal	comm	empno_x	emp_cnt	emp_cnt_1
0	7369	SMITH	7902.0	800	NaN	NaN	NaN	0.0
2	7521	WARD	7698.0	1250	500.0	7521.0	3.0	3.0
4	7654	MARTIN	7698.0	1250	1400.0	7654.0	3.0	3.0
10	7876	ADAMS	7788.0	1100	NaN	7876.0	2.0	2.0
11	7900	JAMES	7698.0	950	NaN	7900.0	1.0	1.0

if_else() 조건문 함수

Python Programming

emp >> \
  left_join ( ( emp.assign(foo=1)                          >> \
                  full_join( emp.assign(foo=1), by="foo" ) >> \
                  group_by('empno_x')                      >> \
                  filter_by(X.sal_x > X.sal_y)             >> \
                  summarize( emp_cnt = X.empno_x.count()) ),
              by=[ ["empno"],["empno_x"] ] )                                   >> \
  mutate( emp_cnt_1 = if_else( ( X.emp_cnt.notnull() ), X.emp_cnt, 0 ) )       >> \
  filter_by( X.emp_cnt_1 < 5 )                                                 >> \
  select(~X.job, ~X.hiredate, ~X.deptno)

Results

	empno	ename	mgr	sal	comm	empno_x	emp_cnt	emp_cnt_1
0	7369	SMITH	7902.0	800	NaN	NaN	NaN	0.0
2	7521	WARD	7698.0	1250	500.0	7521.0	3.0	3.0
4	7654	MARTIN	7698.0	1250	1400.0	7654.0	3.0	3.0
10	7876	ADAMS	7788.0	1100	NaN	7876.0	2.0	2.0
11	7900	JAMES	7698.0	950	NaN	7900.0	1.0	1.0

np.where() 함수 사용
- 현재 조건절은 filter_by()에서 바로 적용 방식 구현 못함.
- replace() 함수에 변수의 값은 반환 못함.

Python Programming

emp >> \
  left_join ( ( emp.assign(foo=1)                          >> \
                  full_join( emp.assign(foo=1), by="foo" ) >> \
                  group_by('empno_x')                      >> \
                  filter_by(X.sal_x > X.sal_y)             >> \
                  summarize( emp_cnt = X.empno_x.count()) ),
              by=[ ["empno"],["empno_x"] ] )                                            >> \
  mutate( emp_cnt_1 = make_symbolic(np.where)(X.emp_cnt.isna(), 0, X.emp_cnt) ,
          ref_cnt   = (X.emp_cnt.isna()).replace({True: 1, False: 0} ) )                >> \
  filter_by( X.emp_cnt_1 < 5 )                                                          >> \
  select(~X.job, ~X.hiredate, ~X.deptno)

Results

	empno	ename	mgr	sal	comm	empno_x	emp_cnt	emp_cnt_1	ref_cnt
0	7369	SMITH	7902.0	800	NaN	NaN	NaN	0.0	1
2	7521	WARD	7698.0	1250	500.0	7521.0	3.0	3.0	0
4	7654	MARTIN	7698.0	1250	1400.0	7654.0	3.0	3.0	0
10	7876	ADAMS	7788.0	1100	NaN	7876.0	2.0	2.0	0
11	7900	JAMES	7698.0	950	NaN	7900.0	1.0	1.0	0

case_when() 함수
- 현재 조건절을 filter_by()에서 바로 적용 방식 구현 못함.

Python Programming

emp >> \
  left_join ( ( emp.assign(foo=1)                          >> \
                  full_join( emp.assign(foo=1), by="foo" ) >> \
                  group_by('empno_x')                      >> \
                  filter_by(X.sal_x > X.sal_y)             >> \
                  summarize( emp_cnt = X.empno_x.count()) ),
              by=[ ["empno"],["empno_x"] ] )                                             >> \
  mutate( emp_cnt_1 = case_when([( X.emp_cnt.notnull() ), X.emp_cnt], [True,0]) ,
          ref_cnt   = (X.emp_cnt.isna()).replace({True: 1, False: 0} )  )                >> \
  filter_by( X.emp_cnt_1 < 5 )                                                           >> \
  select(~X.job, ~X.hiredate, ~X.deptno)

C:\Users\BACK\anaconda3\lib\site-packages\dfply\vector.py:186: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  outcome[~logical] = np.nan

Results

	empno	ename	mgr	sal	comm	empno_x	emp_cnt	emp_cnt_1	ref_cnt
0	7369	SMITH	7902.0	800	NaN	NaN	NaN	0.0	1
2	7521	WARD	7698.0	1250	500.0	7521.0	3.0	3.0	0
4	7654	MARTIN	7698.0	1250	1400.0	7654.0	3.0	3.0	0
10	7876	ADAMS	7788.0	1100	NaN	7876.0	2.0	2.0	0
11	7900	JAMES	7698.0	950	NaN	7900.0	1.0	1.0	0

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

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

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

[비상관 서브쿼리(IN 연산자)] 특정 조건을 만족하는 데이터 추출 - 118 (오라클 SQL, R, Python, SAS) (0)	2022.12.11
[데이터 전처리] 특정 조건을 만족하는 데이터 추출 - 상관 서브쿼리 - 117 (오라클 SQL, R, Python, SAS) (0)	2022.12.10
[데이터 추출] 전체 합계 추가하기 - 스칼라 서브쿼리와 Cross join - 115 (오라클 SQL, R, Python, SAS) (0)	2022.12.09
[데이터 추출] 상관 서브쿼리 & 카테시안 곱(Cartesian Product) - 114 (0)	2022.12.07
[데이터 추출] 결측치 대체, 카테시안 곱(Cartesian Product) - Full Join, Any 연산자 - 113 (0)	2022.12.07

[데이터 추출] 하위 Top 5 추출 - 윈도우 함수 - 116 (오라클 SQL, R, Python, SAS)

116. Find out the last 5(least) earner of the company?

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

10. Python Dfply Package

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

댓글

티스토리툴바

[데이터 추출] 하위 Top 5 추출 - 윈도우 함수 - 116 (오라클 SQL, R, Python, SAS)

116. Find out the last 5(least) earner of the company?

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

10. Python Dfply Package

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

관련글

댓글

티스토리툴바