[결측치 처리 ] 특정 조건을 만족하는 데이터 추출- 154 (오라클 SQL, R, Python, SAS)

포스팅 목차

154. Use the variable in a statement which finds all employees who can earn 30,000 a year or more.

* 연간 총 30,000 이상 수익을 올릴 수 있는 직원들의 정보를 출력하시오. (153번에 생성한 신규 변수의 값이 30,000 이상)

유사문제 : 6 / 113
fn$sqldf : 147
[변수 생성] 신규 변수를 생성하여 출력 - 결측치 처리
데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql

Oracle : NVL 함수
파이썬 Pandas : replace(), np.nan, Exec() 파이썬 코드 동적 실행, compile() & eval() 프로그램 컴파일과 소스 실행
R 프로그래밍 : transform(), ifelse(), is.na(), parse() 와 eval() 프로그램 컴파일과 코드 실행, base::str2lang() 와 eval() 프로그램 컴파일과 코드 실행
R Dplyr Package : dplyr::mutate(), ifelse(), is.na(), parse() 와 eval() 프로그램 컴파일과 코드 실행
R sqldf Package : ifnull(), gsubfn 패키지(gsubfn::fn$sqldf) 외부 변수 참조 프로그램 실행
Python pandasql Package : ifnull(), format(), globals() 전역 변수 참조
R data.table Package : ifelse(), is.na(), parse() 와 eval() ,프로그램 컴파일과 코드 실행
SAS Proc SQL : Coalesce(), IFN() 함수
SAS Data Step : Coalesce(), IFN() 함수, KEEP 구문
Python Dfply Package : if_else(), isnull()
파이썬 Base 프로그래밍 :

1. Oracle(오라클)

수수료를 포함한 직원의 1년간 년봉을 계산 후 계산된 년봉이 30000을 초과하는 직원 정보를 출력한다.

Oracle Programming

select * 
from   emp 
where  12 * (sal+nvl(comm,0)) > 30000;

2. Python Pandas(파이썬)

수수료를 포함한 직원의 1년간 년봉을 계산 후 계산된 년봉이 30000을 초과하는 직원 정보를 출력한다.

eval(),exec() 함수 : [링크] eval(), exec(), compile() 내장함수 (EG공간)

Python Programming

emp[ 12 * (emp['sal'] + emp['comm'].replace(np.nan,0)) >30000 ]

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
3	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20
4	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30
5	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30
7	7788	SCOTT	ANALYST	7566.0	1982/12/09	3000	NaN	20
8	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10
12	7902	FORD	ANALYST	7566.0	1981/12/03	3000	NaN	20

exec를 사용하여서 괄호 안의 파이썬 코드를 동적으로 실행한다.

Python Programming

exec("print(emp[ 12 * (emp['sal'] + emp['comm'].replace(np.nan,0)) >30000 ].drop(['job','hiredate'], axis=1))")

Results

    empno   ename     mgr   sal    comm  deptno
3    7566   JONES  7839.0  2975     NaN      20
4    7654  MARTIN  7698.0  1250  1400.0      30
5    7698   BLAKE  7839.0  2850     NaN      30
7    7788   SCOTT  7566.0  3000     NaN      20
8    7839    KING     NaN  5000     NaN      10
12   7902    FORD  7566.0  3000     NaN      20

수수료를 포함한 직원의 1년간 년봉을 계산하는 식을 “emp_ann_sal” 변수에 할당한다.

Python Programming

emp_ann_sal = "12 * (emp['sal'] + emp['comm'].replace(np.nan,0))";

emp_ann_sal

Results

"12 * (emp['sal'] + emp['comm'].replace(np.nan,0))"

위에서 생성한 소스 코드를 compile() 함수로 먼저 컴파일하고, eval() 함수로 컴파일된 소스를 실행하여 수수료를 포함한 직원의 1년간 년봉을 계산하여서 계산된 년봉이 30000을 초과하는 직원 정보를 출력한다.

Python Programming

com1=compile(emp_ann_sal,"<string>",'eval')
print(com1)

emp[eval(com1) > 30000]

Results

<code object <module> at 0x000001DDE9A28870, file "<string>", line 1>

	empno	ename	job	mgr	hiredate	sal	comm	comm
3	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20
4	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30
5	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30
7	7788	SCOTT	ANALYST	7566.0	1982/12/09	3000	NaN	20
8	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10
12	7902	FORD	ANALYST	7566.0	1981/12/03	3000	NaN	20

3. R Programming (R Package)

수수료를 포함한 직원의 1년간 년봉을 계산 후 계산된 년봉이 30000을 초과하는 직원 정보를 출력한다.

R Programming

%%R

emp[ 12 * (emp['sal'] + ifelse(is.na(emp$comm),0,emp$comm)) > 30000, ]

Results

# A tibble: 6 x 8
  empno ename  job         mgr hiredate     sal  comm deptno
  <dbl> <chr>  <chr>     <dbl> <date>     <dbl> <dbl>  <dbl>
1  7566 JONES  MANAGER    7839 1981-04-02  2975    NA     20
2  7654 MARTIN SALESMAN   7698 1981-09-28  1250  1400     30
3  7698 BLAKE  MANAGER    7839 1981-03-01  2850    NA     30
4  7788 SCOTT  ANALYST    7566 1982-12-09  3000    NA     20
5  7839 KING   PRESIDENT    NA 1981-11-17  5000    NA     10
6  7902 FORD   ANALYST    7566 1981-12-03  3000    NA     20

수수료를 포함한 직원의 1년간 년봉을 계산하는 식을 “emp_ann_sal” 변수에 할당한다.

R Programming

%%R

emp_ann_sal <- "12 * (emp['sal'] + ifelse(is.na(emp$comm),0,emp$comm))"

emp_ann_sal

Results

[1] "12 * (emp['sal'] + ifelse(is.na(emp$comm),0,emp$comm))"

위에서 생성한 emp_ann_sal 변수의 텍스트 구문을 parse() 함수로 해석한 후 eval() 함수를 사용하여 해석된 소스를 실행한다.

R Programming

%%R

eval(parse(text=emp_ann_sal))

Results

위에서 생성한 emp_ann_sal 변수의 텍스트 구문을 parse() 함수로 해석한 후 eval() 함수를 사용하여 해석된 소스를 실행하여 수수료를 포함한 직원의 1년간 년봉을 계산하여서 계산된 년봉이 30000을 초과하는 직원 정보를 출력한다.

%%R

emp[ eval(parse(text=emp_ann_sal)) > 30000, ]

Results

# A tibble: 6 x 8
  empno ename  job         mgr hiredate     sal  comm deptno
  <dbl> <chr>  <chr>     <dbl> <date>     <dbl> <dbl>  <dbl>
1  7566 JONES  MANAGER    7839 1981-04-02  2975    NA     20
2  7654 MARTIN SALESMAN   7698 1981-09-28  1250  1400     30
3  7698 BLAKE  MANAGER    7839 1981-03-01  2850    NA     30
4  7788 SCOTT  ANALYST    7566 1982-12-09  3000    NA     20
5  7839 KING   PRESIDENT    NA 1981-11-17  5000    NA     10
6  7902 FORD   ANALYST    7566 1981-12-03  3000    NA     20

수수료를 포함한 직원의 1년간 년봉을 계산하는 식을 “emp_ann_sal” 변수에 할당한 후 emp_ann_sal 변수에 할당된 텍스트 구문을 str2lang() 함수로 해석한 후 eval() 함수를 사용하여 해석된 소스를 실행하여 수수료를 포함한 직원의 1년간 년봉을 계산하여서 계산된 년봉이 30000을 초과하는 직원 정보를 출력한다.

R Programming

%%R
emp_ann_sal <- "12 * (emp['sal'] + ifelse(is.na(emp$comm),0,emp$comm))"

emp[ eval(base::str2lang(emp_ann_sal)) > 30000, ]

Results

# A tibble: 6 x 8
  empno ename  job         mgr hiredate     sal  comm deptno
  <dbl> <chr>  <chr>     <dbl> <date>     <dbl> <dbl>  <dbl>
1  7566 JONES  MANAGER    7839 1981-04-02  2975    NA     20
2  7654 MARTIN SALESMAN   7698 1981-09-28  1250  1400     30
3  7698 BLAKE  MANAGER    7839 1981-03-01  2850    NA     30
4  7788 SCOTT  ANALYST    7566 1982-12-09  3000    NA     20
5  7839 KING   PRESIDENT    NA 1981-11-17  5000    NA     10
6  7902 FORD   ANALYST    7566 1981-12-03  3000    NA     20

4. R Dplyr Package

수수료를 포함한 직원의 1년간 년봉을 계산 후 계산된 년봉이 30000을 초과하는 직원 정보를 출력한다.

R Programming

%%R

emp %>% dplyr::filter( 12 * (sal + ifelse(is.na(comm),0,comm)) > 30000 )

Results

# A tibble: 6 x 8
  empno ename  job         mgr hiredate     sal  comm deptno
  <dbl> <chr>  <chr>     <dbl> <date>     <dbl> <dbl>  <dbl>
1  7566 JONES  MANAGER    7839 1981-04-02  2975    NA     20
2  7654 MARTIN SALESMAN   7698 1981-09-28  1250  1400     30
3  7698 BLAKE  MANAGER    7839 1981-03-01  2850    NA     30
4  7788 SCOTT  ANALYST    7566 1982-12-09  3000    NA     20
5  7839 KING   PRESIDENT    NA 1981-11-17  5000    NA     10
6  7902 FORD   ANALYST    7566 1981-12-03  3000    NA     20

R Programming

%%R
emp_ann_sal <- "12 * (emp['sal'] + ifelse(is.na(emp$comm),0,emp$comm))"

emp %>% dplyr::filter( eval(parse(text=emp_ann_sal)) > 30000 )

Results

# A tibble: 6 x 8
  empno ename  job         mgr hiredate     sal  comm deptno
  <dbl> <chr>  <chr>     <dbl> <date>     <dbl> <dbl>  <dbl>
1  7566 JONES  MANAGER    7839 1981-04-02  2975    NA     20
2  7654 MARTIN SALESMAN   7698 1981-09-28  1250  1400     30
3  7698 BLAKE  MANAGER    7839 1981-03-01  2850    NA     30
4  7788 SCOTT  ANALYST    7566 1982-12-09  3000    NA     20
5  7839 KING   PRESIDENT    NA 1981-11-17  5000    NA     10
6  7902 FORD   ANALYST    7566 1981-12-03  3000    NA     20

5. R sqldf Package

수수료를 포함한 직원의 1년간 년봉을 계산 후 계산된 년봉이 30000을 초과하는 직원 정보를 출력한다.

R Programming

%%R

sqldf(" select * 
        from   emp 
        where  12*(sal+ifnull(comm,0)) > 30000")

Results

  empno  ename       job  mgr   hiredate  sal comm deptno
1  7566  JONES   MANAGER 7839 1981-04-02 2975   NA     20
2  7654 MARTIN  SALESMAN 7698 1981-09-28 1250 1400     30
3  7698  BLAKE   MANAGER 7839 1981-03-01 2850   NA     30
4  7788  SCOTT   ANALYST 7566 1982-12-09 3000   NA     20
5  7839   KING PRESIDENT   NA 1981-11-17 5000   NA     10
6  7902   FORD   ANALYST 7566 1981-12-03 3000   NA     20

수수료를 포함한 직원의 1년간 년봉을 계산하는 식을 “emp_ann_sal” 변수에 할당한 후 fn$sqldf() 함수를 사용하여서 emp_ann_sal 변수에 할당된 텍스트 구문을 sqldf에 전달하여 실행한다.

fn$sqldf() 함수는 R에서 할당된 변수를 외부 변수 참조 방식으로 sqldf() 함수 내에 전달하여 실행한다.
관련예제 : 147번
gsubfn 패키지 fn$ 참고(Insert Variables) : [링크] Sqldf

R Programming

%%R

emp_ann_sal <- "12*(sal+ifnull(comm,0)) > 30000"

print(emp_ann_sal)

fn$sqldf("select * from emp where `emp_ann_sal`")

Results

[1] "12*(sal+ifnull(comm,0)) > 30000"
  empno  ename       job  mgr   hiredate  sal comm deptno
1  7566  JONES   MANAGER 7839 1981-04-02 2975   NA     20
2  7654 MARTIN  SALESMAN 7698 1981-09-28 1250 1400     30
3  7698  BLAKE   MANAGER 7839 1981-03-01 2850   NA     30
4  7788  SCOTT   ANALYST 7566 1982-12-09 3000   NA     20
5  7839   KING PRESIDENT   NA 1981-11-17 5000   NA     10
6  7902   FORD   ANALYST 7566 1981-12-03 3000   NA     20

6. Python pandasql Package

수수료를 포함한 직원의 1년간 년봉을 계산 후 계산된 년봉이 30000을 초과하는 직원 정보를 출력한다.

Python Programming

ps.sqldf(" select *  from emp                  \
           where 12*(sal+ifnull(comm,0)) > 30000")

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
0	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20
1	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30
2	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30
3	7788	SCOTT	ANALYST	7566.0	1982/12/09	3000	NaN	20
4	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10
5	7902	FORD	ANALYST	7566.0	1981/12/03	3000	NaN	20

년봉 계산식을 format() 함수를 통하여 query 변수 내부에 전달하여서 조회 쿼리 구문을 생성하고, 생성된 조회 쿼리 구문을 ps.sqldf() 내부에서 실행한다.

관련예제 : http://statwith.com/emp-example-sql-r-python-12/

emp_ann_sal = "12*(sal+ifnull(comm,0))";

query = "SELECT * FROM emp where {} > 30000".format(emp_ann_sal)
print(query)
ps.sqldf(query, globals())

Results

SELECT * FROM emp where 12*(sal+ifnull(comm,0)) > 30000

	empno	ename	job	mgr	hiredate	sal	comm	deptno
0	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20
1	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30
2	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30
3	7788	SCOTT	ANALYST	7566.0	1982/12/09	3000	NaN	20
4	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10
5	7902	FORD	ANALYST	7566.0	1981/12/03	3000	NaN	20

7. R data.table Package

수수료를 포함한 직원의 1년간 년봉을 계산 후 계산된 년봉이 30000을 초과하는 직원 정보를 출력한다.

R Programming

%%R

DT <- data.table(emp)

DT[12 * (sal + ifelse(is.na(comm),0,comm)) > 30000, ]

Results

   empno  ename       job  mgr   hiredate  sal comm deptno
1:  7566  JONES   MANAGER 7839 1981-04-02 2975   NA     20
2:  7654 MARTIN  SALESMAN 7698 1981-09-28 1250 1400     30
3:  7698  BLAKE   MANAGER 7839 1981-03-01 2850   NA     30
4:  7788  SCOTT   ANALYST 7566 1982-12-09 3000   NA     20
5:  7839   KING PRESIDENT   NA 1981-11-17 5000   NA     10
6:  7902   FORD   ANALYST 7566 1981-12-03 3000   NA     20

수수료를 포함한 직원의 1년간 년봉을 계산하는 식을 “emp_ann_sal” 변수에 할당한 후 emp_ann_sal 변수에 할당된 텍스트 구문을 parse() 함수로 해석한 후 eval() 함수를 사용하여 해석된 소스를 실행하여 수수료를 포함한 직원의 1년간 년봉을 계산하여서 계산된 년봉이 30000을 초과하는 직원 정보를 출력한다.

R Programming

%%R

emp_ann_sal <- "12 * (sal + ifelse(is.na(comm),0,comm))"

DT[ eval(parse(text=emp_ann_sal)) > 30000, ]

Results

   empno  ename       job  mgr   hiredate  sal comm deptno
1:  7566  JONES   MANAGER 7839 1981-04-02 2975   NA     20
2:  7654 MARTIN  SALESMAN 7698 1981-09-28 1250 1400     30
3:  7698  BLAKE   MANAGER 7839 1981-03-01 2850   NA     30
4:  7788  SCOTT   ANALYST 7566 1982-12-09 3000   NA     20
5:  7839   KING PRESIDENT   NA 1981-11-17 5000   NA     10
6:  7902   FORD   ANALYST 7566 1981-12-03 3000   NA     20

8. SAS Proc SQL

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    select empno,
           ename,
           sal,
           comm,
           12 * (sal+COALESCE(comm,0)) as salary_1,
           12 * (sal+ifn(comm=.,0,comm)) as salary_2
    from   emp 
    where  12 * (sal+COALESCE(comm,0)) > 30000;
QUIT;
PROC PRINT;RUN;

Results

OBS	empno	ename	sal	comm	salary_1	salary_2
1	7566	JONES	2975	.	35700	35700
2	7654	MARTIN	1250	1400	31800	31800
3	7698	BLAKE	2850	.	34200	34200
4	7788	SCOTT	3000	.	36000	36000
5	7839	KING	5000	.	60000	60000
6	7902	FORD	3000	.	36000	36000

9. SAS Data Step

SAS Programming

%%SAS sas

DATA STATSAS_2;
 SET EMP;
     emp_ann_sal   = (sal+COALESCE(comm,0))*12;
     emp_ann_sal_1 = (sal+IFN(comm=.,0,comm))*12;
     keep empno ename emp_:;
     where  12 * (sal+COALESCE(comm,0)) > 30000;
RUN;
PROC PRINT;RUN;

Results

OBS	empno	ename	emp_ann_sal	emp_ann_sal_1
1	7566	JONES	35700	35700
2	7654	MARTIN	31800	31800
3	7698	BLAKE	34200	34200
4	7788	SCOTT	36000	36000
5	7839	KING	60000	60000
6	7902	FORD	36000	36000

10. Python Dfply Package

Python Programming

emp >> \
  filter_by( 12 * (X.sal+if_else(X.comm.isnull(),0,X.comm)) > 30000 )

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
3	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20
4	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30
5	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30
6	7788	SCOTT	ANALYST	7566.0	1982/12/09	3000	NaN	20
8	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10
12	7902	FORD	ANALYST	7566.0	1981/12/03	3000	NaN	20

literal_eval() 과 eval() : 114번 , 154번

Python Programming

emp_ann_sal = "12 * (emp['sal'] + emp['comm'].replace(np.nan,0))";

print( emp_ann_sal )

com1=compile(emp_ann_sal,"<string>",'eval')
print(com1)

emp >> filter_by( eval(com1) > 30000 )

Results

12 * (emp['sal'] + emp['comm'].replace(np.nan,0))
<code object <module> at 0x0000025141543A80, file "<string>", line 1>

	empno	ename	job	mgr	hiredate	sal	comm	deptno
3	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20
4	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30
5	7698	BLAKE	MANAGER	7839.0	1981/03/01	2850	NaN	30
6	7788	SCOTT	ANALYST	7566.0	1982/12/09	3000	NaN	20
8	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10
12	7902	FORD	ANALYST	7566.0	1981/12/03	3000	NaN	20

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

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

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

[집계 함수] 그룹별 평균과 합계 계산 - 결측치 처리 - 156 (오라클 SQL, R, Python, SAS) (0)	2023.01.09
[총건수 카운트] 특정 조건을 만족하는 데이터 집계 - 155 (오라클 SQL, R, Python, SAS) (1)	2023.01.09
[변수 생성] 신규 변수를 생성하여 출력- 153 (오라클 SQL, R, Python, SAS) (0)	2023.01.06
[데이터 상하 결합(UNION)] 특정 조건을 만족하는 데이터 추출 - 비상관 서브쿼리 - 152 (오라클 SQL, R, Python, SAS) (0)	2023.01.04
[중복 제거(Distinct)] 특정 조건을 만족하는 데이터 추출 - 비상관 서브쿼리(In) - 151 (오라클 SQL, R, Python, SAS) (0)	2023.01.04

[결측치 처리 ] 특정 조건을 만족하는 데이터 추출- 154 (오라클 SQL, R, Python, SAS)

154. Use the variable in a statement which finds all employees who can earn 30,000 a year or more.

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)' 카테고리의 다른 글

댓글

티스토리툴바

[결측치 처리 ] 특정 조건을 만족하는 데이터 추출- 154 (오라클 SQL, R, Python, SAS)

154. Use the variable in a statement which finds all employees who can earn 30,000 a year or more.

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)' 카테고리의 다른 글

관련글

댓글

티스토리툴바