본문 바로가기
통계프로그램 비교 시리즈/오라클함수 비교(R & Python)

NULLIF 오라클 함수 [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table, DuckDB]

by 기서무나구물 2021. 12. 8.

포스팅 목차

    * 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크

    [ NULLIF Oracle Function ]

     

    NULLIF함수는 expr1과 expr2를 비교하여서 만약 expr1과 expr2가 같으면 NULL 값을 반환하고, expr1과 expr2가 같지 않으면 expr1을 반환한다. expr1 인수에는 NULL을 지정할 수 없다.

     

    • 특정값을 NULL로 변경하는 경우 주로 사용.
    • 다음 조건문으로 대체할 수 있다. CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END
    • 함수 설명 : NULLIF 오라클 함수 링크

     

     

    1. Oracle(오라클)

     

    Nullif() 함수

    부서 번호가 20인 경우 null을 반환하고, 20이 아닌 경우 부서 번호를 반환한다.

     

    Oracle Programming 
    select ename,deptno,
       nullif(deptno,20) nullif,
       case when deptno = 20 then null else deptno end nullif_1
from   emp

     

    Results 
    ENAME	DEPTNO	NULLIF	NULLIF_1
-------------------------------------
KING	10	10	10
BLAKE	30	30	30
CLARK	10	10	10
JONES	20	 - 	 - 
SCOTT	20	 - 	 - 
FORD	20	 - 	 - 
SMITH	20	 - 	 - 
ALLEN	30	30	30
WARD	30	30	30
MARTIN	30	30	30
TURNER	30	30	30
ADAMS	20	 - 	 - 
JAMES	30	30	30
MILLER	10	10	10

     

    2. Python Pandas(파이썬)

     

    Python Programming 
    import copy
withmooc =copy.copy(emp)

withmooc.loc[withmooc['deptno'] != 20,'nullif'] = withmooc['deptno']

withmooc.head()

     

    Results 
    	empno	ename	job		mgr	hiredate	sal	comm	deptno	nullif
0	7369	SMITH	CLERK		7902.0	1980/12/17	800	NaN	20	NaN
1	7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30	30.0
2	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30	30.0
3	7566	JONES	MANAGER		7839.0	1981/04/02	2975	NaN	20	NaN
4	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30	30.0

     

    np.where() 

     

    Python Programming
     
    withmooc = copy.copy(emp)

withmooc['nullif'] = np.where(withmooc['deptno'] == 20, 'NaN', withmooc['deptno'])
withmooc.head()

     

    Results 
    	empno	ename	job		mgr	hiredate	sal	comm	deptno	nullif
0	7369	SMITH	CLERK		7902.0	1980/12/17	800	NaN	20	NaN
1	7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30	30
2	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30	30
3	7566	JONES	MANAGER		7839.0	1981/04/02	2975	NaN	20	NaN
4	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30	30

     

    if 구문

     

    Python Programming
     
    withmooc = copy.copy(emp)

# (1) IF condition – Set of numbers
withmooc.loc[withmooc['deptno'] == 20, 'nullif'] = 'NaN'
withmooc.loc[withmooc['deptno'] != 20, 'nullif'] = withmooc['deptno']

# (2) IF condition – set of numbers and lambda
withmooc['nullif_1'] = withmooc['deptno'].apply(lambda x: 'NaN' if x == 20 else x)

withmooc.head()

     

    Results 
    	empno	ename	job		mgr	hiredate	sal	comm	deptno	nullif	nullif_1
0	7369	SMITH	CLERK		7902.0	1980/12/17	800	NaN	20	NaN	NaN
1	7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30	30	30
2	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30	30	30
3	7566	JONES	MANAGER		7839.0	1981/04/02	2975	NaN	20	NaN	NaN
4	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30	30	30

     

    if 구문

     

    Python Programming
     
    withmooc =copy.copy(emp)

withmooc['nullif'] = ['NaN'
                       if   x == 20 
                       else x
                       for x in withmooc['deptno']]

withmooc.head()

     

    Results 
    	empno	ename	job		mgr	hiredate	sal	comm	deptno	nullif
0	7369	SMITH	CLERK		7902.0	1980/12/17	800	NaN	20	NaN
1	7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30	30
2	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30	30
3	7566	JONES	MANAGER		7839.0	1981/04/02	2975	NaN	20	NaN
4	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30	30
     

     

    3. R Programming (R Package)

     

    ifelse

     

    R Programming 
    %%R
withmooc <- emp

withmooc$nullif <- ifelse( withmooc$deptno == 20, NA, withmooc$deptno )

withmooc[1:10, ]

     

    Results 
    # A tibble: 10 x 9
   empno ename  job         mgr hiredate     sal  comm deptno nullif
   <dbl> <chr>  <chr>     <dbl> <date>     <dbl> <dbl>  <dbl>  <dbl>
 1  7369 SMITH  CLERK      7902 1980-12-17   800    NA     20     NA
 2  7499 ALLEN  SALESMAN   7698 1981-02-20  1600   300     30     30
 3  7521 WARD   SALESMAN   7698 1981-02-22  1250   500     30     30
 4  7566 JONES  MANAGER    7839 1981-04-02  2975    NA     20     NA
 5  7654 MARTIN SALESMAN   7698 1981-09-28  1250  1400     30     30
 6  7698 BLAKE  MANAGER    7839 1981-03-01  2850    NA     30     30
 7  7782 CLARK  MANAGER    7839 1981-01-09  2450    NA     10     10
 8  7788 SCOTT  ANALYST    7566 1982-12-09  3000    NA     20     NA
 9  7839 KING   PRESIDENT    NA 1981-11-17  5000    NA     10     10
10  7844 TURNER SALESMAN   7698 1981-09-08  1500     0     30     30

     

    case_when()

     

    R Programming 
    %%R
withmooc <- emp

withmooc$nullif <- case_when(withmooc$deptno == 20 ~ NA_real_, TRUE ~ withmooc$deptno)

withmooc[1:10, ]

     

    Results 
    # A tibble: 10 x 9
   empno ename  job         mgr hiredate     sal  comm deptno nullif
   <dbl> <chr>  <chr>     <dbl> <date>     <dbl> <dbl>  <dbl>  <dbl>
 1  7369 SMITH  CLERK      7902 1980-12-17   800    NA     20     NA
 2  7499 ALLEN  SALESMAN   7698 1981-02-20  1600   300     30     30
 3  7521 WARD   SALESMAN   7698 1981-02-22  1250   500     30     30
 4  7566 JONES  MANAGER    7839 1981-04-02  2975    NA     20     NA
 5  7654 MARTIN SALESMAN   7698 1981-09-28  1250  1400     30     30
 6  7698 BLAKE  MANAGER    7839 1981-03-01  2850    NA     30     30
 7  7782 CLARK  MANAGER    7839 1981-01-09  2450    NA     10     10
 8  7788 SCOTT  ANALYST    7566 1982-12-09  3000    NA     20     NA
 9  7839 KING   PRESIDENT    NA 1981-11-17  5000    NA     10     10
10  7844 TURNER SALESMAN   7698 1981-09-08  1500     0     30     30

     

    case_when()

     

    R Programming 
    %%R

withmooc <- emp
withmooc['nullif'] = lapply(withmooc['deptno'], function(x) case_when( x == 20 ~ NA_real_,
                                                                       x != 20 ~ x ,) ) 

withmooc[1:10, ]

     

    Results 
    # A tibble: 10 x 9
   empno ename  job         mgr hiredate     sal  comm deptno nullif
   <dbl> <chr>  <chr>     <dbl> <date>     <dbl> <dbl>  <dbl>  <dbl>
 1  7369 SMITH  CLERK      7902 1980-12-17   800    NA     20     NA
 2  7499 ALLEN  SALESMAN   7698 1981-02-20  1600   300     30     30
 3  7521 WARD   SALESMAN   7698 1981-02-22  1250   500     30     30
 4  7566 JONES  MANAGER    7839 1981-04-02  2975    NA     20     NA
 5  7654 MARTIN SALESMAN   7698 1981-09-28  1250  1400     30     30
 6  7698 BLAKE  MANAGER    7839 1981-03-01  2850    NA     30     30
 7  7782 CLARK  MANAGER    7839 1981-01-09  2450    NA     10     10
 8  7788 SCOTT  ANALYST    7566 1982-12-09  3000    NA     20     NA
 9  7839 KING   PRESIDENT    NA 1981-11-17  5000    NA     10     10
10  7844 TURNER SALESMAN   7698 1981-09-08  1500     0     30     30

     

     

    4. R Dplyr Package

     

    ifelse()

     

    R Programming 
    %%R

emp %>%
  dplyr::mutate( nullif = ifelse(deptno == 20, NA, deptno)) %>%
  head()

     

    Results 
    # A tibble: 6 x 9
  empno ename  job        mgr hiredate     sal  comm deptno nullif
  <dbl> <chr>  <chr>    <dbl> <date>     <dbl> <dbl>  <dbl>  <dbl>
1  7369 SMITH  CLERK     7902 1980-12-17   800    NA     20     NA
2  7499 ALLEN  SALESMAN  7698 1981-02-20  1600   300     30     30
3  7521 WARD   SALESMAN  7698 1981-02-22  1250   500     30     30
4  7566 JONES  MANAGER   7839 1981-04-02  2975    NA     20     NA
5  7654 MARTIN SALESMAN  7698 1981-09-28  1250  1400     30     30
6  7698 BLAKE  MANAGER   7839 1981-03-01  2850    NA     30     30

     

    case_when()

     

    R Programming 
    %%R

# NA_character_ / NA_real_
withmooc <- emp

withmooc %>% 
  dplyr::mutate( sal = case_when(deptno == 20 ~ NA_real_, TRUE ~ deptno) ) %>%
  head()

     

    Results 
    # A tibble: 6 x 8
  empno ename  job        mgr hiredate     sal  comm deptno
  <dbl> <chr>  <chr>    <dbl> <date>     <dbl> <dbl>  <dbl>
1  7369 SMITH  CLERK     7902 1980-12-17    NA    NA     20
2  7499 ALLEN  SALESMAN  7698 1981-02-20    30   300     30
3  7521 WARD   SALESMAN  7698 1981-02-22    30   500     30
4  7566 JONES  MANAGER   7839 1981-04-02    NA    NA     20
5  7654 MARTIN SALESMAN  7698 1981-09-28    30  1400     30
6  7698 BLAKE  MANAGER   7839 1981-03-01    30    NA     30

     

     

    5. R sqldf Package

     

    nullif와 case when

     

    R Programming 
    %%R

sqldf(" select ename,deptno,nullif(deptno,20) nullif,
               case when deptno = 20 then null else deptno end nullif_1
        from   emp ")

     

    Results 
        ename deptno nullif nullif_1
1   SMITH     20     NA       NA
2   ALLEN     30     30       30
3    WARD     30     30       30
4   JONES     20     NA       NA
5  MARTIN     30     30       30
6   BLAKE     30     30       30
7   CLARK     10     10       10
8   SCOTT     20     NA       NA
9    KING     10     10       10
10 TURNER     30     30       30
11  ADAMS     20     NA       NA
12  JAMES     30     30       30
13   FORD     20     NA       NA
14 MILLER     10     10       10

     

     

    6. Python pandasql Package

     

    nullif와 case when

     

    Python Programming 
    ps.sqldf(" select ename,deptno,nullif(deptno,20) nullif,                       \
                  case when deptno = 20 then null else deptno end nullif_1     \
           from emp ").head()

     

    Results 
    	ename	deptno	nullif	nullif_1
0	SMITH	20	NaN	NaN
1	ALLEN	30	30.0	30.0
2	WARD	30	30.0	30.0
3	JONES	20	NaN	NaN
4	MARTIN	30	30.0	30.0

     

     

    7. R data.table Package

     

     

    R Programming 
    %%R

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

DT[deptno != 20 , nullif := deptno ][1:10, ]

     

    Results 
        empno  ename       job  mgr   hiredate  sal comm deptno nullif
 1:  7369  SMITH     CLERK 7902 1980-12-17  800   NA     20     NA
 2:  7499  ALLEN  SALESMAN 7698 1981-02-20 1600  300     30     30
 3:  7521   WARD  SALESMAN 7698 1981-02-22 1250  500     30     30
 4:  7566  JONES   MANAGER 7839 1981-04-02 2975   NA     20     NA
 5:  7654 MARTIN  SALESMAN 7698 1981-09-28 1250 1400     30     30
 6:  7698  BLAKE   MANAGER 7839 1981-03-01 2850   NA     30     30
 7:  7782  CLARK   MANAGER 7839 1981-01-09 2450   NA     10     10
 8:  7788  SCOTT   ANALYST 7566 1982-12-09 3000   NA     20     NA
 9:  7839   KING PRESIDENT   NA 1981-11-17 5000   NA     10     10
10:  7844 TURNER  SALESMAN 7698 1981-09-08 1500    0     30     30

     

    조건문 : dplyr::if_else 구문

     

    R Programming 
    %%R

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

DT[ , nullif := ifelse(deptno == 20, NA , deptno ) ][1:10, ]

     

    Results 
        empno  ename       job  mgr   hiredate  sal comm deptno nullif
 1:  7369  SMITH     CLERK 7902 1980-12-17  800   NA     20     NA
 2:  7499  ALLEN  SALESMAN 7698 1981-02-20 1600  300     30     30
 3:  7521   WARD  SALESMAN 7698 1981-02-22 1250  500     30     30
 4:  7566  JONES   MANAGER 7839 1981-04-02 2975   NA     20     NA
 5:  7654 MARTIN  SALESMAN 7698 1981-09-28 1250 1400     30     30
 6:  7698  BLAKE   MANAGER 7839 1981-03-01 2850   NA     30     30
 7:  7782  CLARK   MANAGER 7839 1981-01-09 2450   NA     10     10
 8:  7788  SCOTT   ANALYST 7566 1982-12-09 3000   NA     20     NA
 9:  7839   KING PRESIDENT   NA 1981-11-17 5000   NA     10     10
10:  7844 TURNER  SALESMAN 7698 1981-09-08 1500    0     30     30

     

    dplyr::case_when() 구문

     

    R Programming 
    %%R

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

DT[, nullif := dplyr::case_when(deptno == 20 ~ NA_real_, TRUE ~ deptno) ][1:10, ]

     

    Results 
        empno  ename       job  mgr   hiredate  sal comm deptno nullif
 1:  7369  SMITH     CLERK 7902 1980-12-17  800   NA     20     NA
 2:  7499  ALLEN  SALESMAN 7698 1981-02-20 1600  300     30     30
 3:  7521   WARD  SALESMAN 7698 1981-02-22 1250  500     30     30
 4:  7566  JONES   MANAGER 7839 1981-04-02 2975   NA     20     NA
 5:  7654 MARTIN  SALESMAN 7698 1981-09-28 1250 1400     30     30
 6:  7698  BLAKE   MANAGER 7839 1981-03-01 2850   NA     30     30
 7:  7782  CLARK   MANAGER 7839 1981-01-09 2450   NA     10     10
 8:  7788  SCOTT   ANALYST 7566 1982-12-09 3000   NA     20     NA
 9:  7839   KING PRESIDENT   NA 1981-11-17 5000   NA     10     10
10:  7844 TURNER  SALESMAN 7698 1981-09-08 1500    0     30     30

     

    조건문 : data.table::fifelse 구문

     

    R Programming 
    %%R

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

DT[, nullif := fifelse(deptno == 20, NA_real_, deptno)][1:10, ]

     

    Results 
        empno  ename       job  mgr   hiredate  sal comm deptno nullif
 1:  7369  SMITH     CLERK 7902 1980-12-17  800   NA     20     NA
 2:  7499  ALLEN  SALESMAN 7698 1981-02-20 1600  300     30     30
 3:  7521   WARD  SALESMAN 7698 1981-02-22 1250  500     30     30
 4:  7566  JONES   MANAGER 7839 1981-04-02 2975   NA     20     NA
 5:  7654 MARTIN  SALESMAN 7698 1981-09-28 1250 1400     30     30
 6:  7698  BLAKE   MANAGER 7839 1981-03-01 2850   NA     30     30
 7:  7782  CLARK   MANAGER 7839 1981-01-09 2450   NA     10     10
 8:  7788  SCOTT   ANALYST 7566 1982-12-09 3000   NA     20     NA
 9:  7839   KING PRESIDENT   NA 1981-11-17 5000   NA     10     10
10:  7844 TURNER  SALESMAN 7698 1981-09-08 1500    0     30     30

     

    조건문 : data.table::fcase 구문

     

    R Programming 
    %%R

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

DT[, nullif := fcase(deptno == 20, NA_real_,
                     deptno != 20, deptno)][1:10, ]

     

    Results 
        empno  ename       job  mgr   hiredate  sal comm deptno nullif
 1:  7369  SMITH     CLERK 7902 1980-12-17  800   NA     20     NA
 2:  7499  ALLEN  SALESMAN 7698 1981-02-20 1600  300     30     30
 3:  7521   WARD  SALESMAN 7698 1981-02-22 1250  500     30     30
 4:  7566  JONES   MANAGER 7839 1981-04-02 2975   NA     20     NA
 5:  7654 MARTIN  SALESMAN 7698 1981-09-28 1250 1400     30     30
 6:  7698  BLAKE   MANAGER 7839 1981-03-01 2850   NA     30     30
 7:  7782  CLARK   MANAGER 7839 1981-01-09 2450   NA     10     10
 8:  7788  SCOTT   ANALYST 7566 1982-12-09 3000   NA     20     NA
 9:  7839   KING PRESIDENT   NA 1981-11-17 5000   NA     10     10
10:  7844 TURNER  SALESMAN 7698 1981-09-08 1500    0     30     30

     

    8. Python Duckdb의 SQL

     

    Python Programming 
    %%sql
  select ename,deptno,
         nullif(deptno,20) nullif_0,
         case when deptno = 20 then null else deptno end nullif_1
  from   emp
  LIMIT  6

     

    Python Programming 
    duckdb.sql(" select ename,deptno,                                               \
                    nullif(deptno,20) nullif_0,                                 \
                    case when deptno = 20 then null else deptno end nullif_1    \
             from   emp                                                         \
             LIMIT  6 ").df()

     

    Results 
        ename  deptno  nullif_0  nullif_1
0   SMITH      20       NaN       NaN
1   ALLEN      30      30.0      30.0
2    WARD      30      30.0      30.0
3   JONES      20       NaN       NaN
4  MARTIN      30      30.0      30.0
5   BLAKE      30      30.0      30.0

     

    Beef bibimbap (https://unsplash.com/photos/fU5_WBTXa_Y)

      --------------------------------------------  

     

    [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table] 오라클 함수와 R & Python 비교 사전 목록 링크

     

    오라클 SQL 함수(Oracle SQL Function) 목록 리스트 링크

     

    [SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트 링크 링크
    반응형
    저작자표시 비영리 변경금지

    '통계프로그램 비교 시리즈 > 오라클함수 비교(R & Python)' 카테고리의 다른 글

    NUMTOYMINTERVAL 오라클 함수 [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table,DuckDB]  (0) 2021.12.09
    NUMTODSINTERVAL 오라클 함수 [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table, DuckDB]  (0) 2021.12.08
    NTILE 오라클 함수 [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table, DuckDB]  (0) 2021.12.06
    NEXT_DAY 오라클 함수 [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table, DuckDB]  (0) 2021.12.06
    NEW_TIME 오라클 함수 [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table]  (0) 2021.12.06

    관련글

    댓글

    티스토리툴바