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

CAST Oracle Function [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table, DuckDB]

by 기서무나구물 2021. 11. 21.

포스팅 목차

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


    [ CAST 함수 ]

     


    CAST 함수는 데이터 형식이나 collection 형식을 다른 데이터 형식이나 collection 형식으로 변환한다.

     

     


    1. Oracle(오라클)

     

    Oracle Programming
    SELECT EMPNO,
           CAST(EMPNO AS VARCHAR(44)) EMPNO_CHAR,
           CAST(CAST(EMPNO AS VARCHAR(44)) AS NUMBER(4)) EMPNO_NUM
    FROM   EMP

     

     


    2. Python Pandas(파이썬)

     

    Python Programming
    withmooc = emp.astype({"empno":'object'})
    
    display(withmooc.dtypes)
    
    withmooc = withmooc.astype({"empno":'int64'})
    
    withmooc.dtypes

     

    Results
    empno        object
    ename        object
    job          object
    mgr         float64
    hiredate     object
    sal           int64
    comm        float64
    deptno        int64
    dtype: object
    
    empno         int64
    ename        object
    job          object
    mgr         float64
    hiredate     object
    sal           int64
    comm        float64
    deptno        int64
    dtype: object

     


    Python Programming
    withmooc = emp.copy()
    
    # withmooc = withmooc['empno'].to_string()                    # pandas.DataFrame.to_string
    withmooc['empno'] = withmooc['empno'].apply(str)
    display(withmooc.dtypes)
    
    withmooc['empno'] = pd.to_numeric(withmooc['empno'])   # pandas.to_numeric
    
    display(withmooc.dtypes)

     

    Results
    empno        object
    ename        object
    job          object
    mgr         float64
    hiredate     object
    sal           int64
    comm        float64
    deptno        int64
    dtype: object
    
    empno         int64
    ename        object
    job          object
    mgr         float64
    hiredate     object
    sal           int64
    comm        float64
    deptno        int64
    dtype: object

     


    [참고] Series 데이터 타입을 문자열(String)로 변경

    Python Programming
    display(emp['empno'].head())
    
    display(emp['empno'].astype("string").head() )
    
    display(pd.Series(emp['empno'], dtype="string").head() )

     

    Results
    0    7369
    1    7499
    2    7521
    3    7566
    4    7654
    Name: empno, dtype: int64
    
    0    7369
    1    7499
    2    7521
    3    7566
    4    7654
    Name: empno, dtype: string
    
    0    7369
    1    7499
    2    7521
    3    7566
    4    7654
    Name: empno, dtype: string

     


    3. R Programming (R Package)

     

    R Programming
    %%R
    
    print(str(emp))
    withmooc <- emp
    
    withmooc$empno = as.character(withmooc$empno)
    
    print(str(withmooc))
    
    withmooc$empno = as.numeric(withmooc$empno)
    
    print(str(withmooc))

     

    Results
    tibble [14 x 8] (S3: spec_tbl_df/tbl_df/tbl/data.frame)
     $ empno   : num [1:14] 7369 7499 7521 7566 7654 ...
     $ ename   : chr [1:14] "SMITH" "ALLEN" "WARD" "JONES" ...
     $ job     : chr [1:14] "CLERK" "SALESMAN" "SALESMAN" "MANAGER" ...
     $ mgr     : num [1:14] 7902 7698 7698 7839 7698 ...
     $ hiredate: Date[1:14], format: "1980-12-17" "1981-02-20" ...
     $ sal     : num [1:14] 800 1600 1250 2975 1250 ...
     $ comm    : num [1:14] NA 300 500 NA 1400 NA NA NA NA 0 ...
     $ deptno  : num [1:14] 20 30 30 20 30 30 10 20 10 30 ...
     - attr(*, "spec")=
      .. cols(
      ..   empno = col_double(),
      ..   ename = col_character(),
      ..   job = col_character(),
      ..   mgr = col_double(),
      ..   hiredate = col_date(format = ""),
      ..   sal = col_double(),
      ..   comm = col_double(),
      ..   deptno = col_double()
      .. )
    NULL
    tibble [14 x 8] (S3: spec_tbl_df/tbl_df/tbl/data.frame)
     $ empno   : chr [1:14] "7369" "7499" "7521" "7566" ...
     $ ename   : chr [1:14] "SMITH" "ALLEN" "WARD" "JONES" ...
     $ job     : chr [1:14] "CLERK" "SALESMAN" "SALESMAN" "MANAGER" ...
     $ mgr     : num [1:14] 7902 7698 7698 7839 7698 ...
     $ hiredate: Date[1:14], format: "1980-12-17" "1981-02-20" ...
     $ sal     : num [1:14] 800 1600 1250 2975 1250 ...
     $ comm    : num [1:14] NA 300 500 NA 1400 NA NA NA NA 0 ...
     $ deptno  : num [1:14] 20 30 30 20 30 30 10 20 10 30 ...
     - attr(*, "spec")=
      .. cols(
      ..   empno = col_double(),
      ..   ename = col_character(),
      ..   job = col_character(),
      ..   mgr = col_double(),
      ..   hiredate = col_date(format = ""),
      ..   sal = col_double(),
      ..   comm = col_double(),
      ..   deptno = col_double()
      .. )
    NULL
    tibble [14 x 8] (S3: spec_tbl_df/tbl_df/tbl/data.frame)
     $ empno   : num [1:14] 7369 7499 7521 7566 7654 ...
     $ ename   : chr [1:14] "SMITH" "ALLEN" "WARD" "JONES" ...
     $ job     : chr [1:14] "CLERK" "SALESMAN" "SALESMAN" "MANAGER" ...
     $ mgr     : num [1:14] 7902 7698 7698 7839 7698 ...
     $ hiredate: Date[1:14], format: "1980-12-17" "1981-02-20" ...
     $ sal     : num [1:14] 800 1600 1250 2975 1250 ...
     $ comm    : num [1:14] NA 300 500 NA 1400 NA NA NA NA 0 ...
     $ deptno  : num [1:14] 20 30 30 20 30 30 10 20 10 30 ...
     - attr(*, "spec")=
      .. cols(
      ..   empno = col_double(),
      ..   ename = col_character(),
      ..   job = col_character(),
      ..   mgr = col_double(),
      ..   hiredate = col_date(format = ""),
      ..   sal = col_double(),
      ..   comm = col_double(),
      ..   deptno = col_double()
      .. )
    NULL

     


    R Programming
    %%R
    
    withmooc <- emp
    
    print( sapply(withmooc, mode) )  
    
    print('************************************************************')
    
    withmooc <- base::transform(withmooc, empno = as.character(empno))
    
    print( sapply(withmooc, class) )
    
    print('************************************************************')
    
    withmooc <- base::transform(withmooc, empno = as.numeric(empno))
    
    print( sapply(withmooc, class) )

     

    Results
          empno       ename         job         mgr    hiredate         sal 
      "numeric" "character" "character"   "numeric"   "numeric"   "numeric" 
           comm      deptno 
      "numeric"   "numeric" 
    [1] "************************************************************"
          empno       ename         job         mgr    hiredate         sal 
    "character" "character" "character"   "numeric"      "Date"   "numeric" 
           comm      deptno 
      "numeric"   "numeric" 
    [1] "************************************************************"
          empno       ename         job         mgr    hiredate         sal 
      "numeric" "character" "character"   "numeric"      "Date"   "numeric" 
           comm      deptno 
      "numeric"   "numeric" 

     


    [참고] 구조 확인

    R Programming
    %%R
    
    print(str(emp))
    print('************************************************************')
    print( sapply(emp, mode) )
    print('************************************************************')
    print( sapply(emp, class) )

     

    Results
    tibble [14 x 8] (S3: spec_tbl_df/tbl_df/tbl/data.frame)
     $ empno   : num [1:14] 7369 7499 7521 7566 7654 ...
     $ ename   : chr [1:14] "SMITH" "ALLEN" "WARD" "JONES" ...
     $ job     : chr [1:14] "CLERK" "SALESMAN" "SALESMAN" "MANAGER" ...
     $ mgr     : num [1:14] 7902 7698 7698 7839 7698 ...
     $ hiredate: Date[1:14], format: "1980-12-17" "1981-02-20" ...
     $ sal     : num [1:14] 800 1600 1250 2975 1250 ...
     $ comm    : num [1:14] NA 300 500 NA 1400 NA NA NA NA 0 ...
     $ deptno  : num [1:14] 20 30 30 20 30 30 10 20 10 30 ...
     - attr(*, "spec")=
      .. cols(
      ..   empno = col_double(),
      ..   ename = col_character(),
      ..   job = col_character(),
      ..   mgr = col_double(),
      ..   hiredate = col_date(format = ""),
      ..   sal = col_double(),
      ..   comm = col_double(),
      ..   deptno = col_double()
      .. )
    NULL

     

     


    4. R Dplyr Package

     

    R Programming
    %%R
    
    withmooc <- emp
    
    print( sapply(withmooc, mode) )
    print('************************************************************')
    withmooc %>% 
      dplyr::mutate(empno = as.character(empno)) %>% 
      print() %>% 
      dplyr::mutate(empno = as.numeric(empno)) %>% 
      print()

     

    Results
          empno       ename         job         mgr    hiredate         sal 
      "numeric" "character" "character"   "numeric"   "numeric"   "numeric" 
           comm      deptno 
      "numeric"   "numeric" 
    [1] "************************************************************"
    # A tibble: 14 x 8
       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 7566  JONES  MANAGER    7839 1981-04-02  2975    NA     20
     5 7654  MARTIN SALESMAN   7698 1981-09-28  1250  1400     30
     6 7698  BLAKE  MANAGER    7839 1981-03-01  2850    NA     30
     7 7782  CLARK  MANAGER    7839 1981-01-09  2450    NA     10
     8 7788  SCOTT  ANALYST    7566 1982-12-09  3000    NA     20
     9 7839  KING   PRESIDENT    NA 1981-11-17  5000    NA     10
    10 7844  TURNER SALESMAN   7698 1981-09-08  1500     0     30
    11 7876  ADAMS  CLERK      7788 1983-01-12  1100    NA     20
    12 7900  JAMES  CLERK      7698 1981-12-03   950    NA     30
    13 7902  FORD   ANALYST    7566 1981-12-03  3000    NA     20
    14 7934  MILLER CLERK      7782 1982-01-23  1300    NA     10
    # A tibble: 14 x 8
       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  7566 JONES  MANAGER    7839 1981-04-02  2975    NA     20
     5  7654 MARTIN SALESMAN   7698 1981-09-28  1250  1400     30
     6  7698 BLAKE  MANAGER    7839 1981-03-01  2850    NA     30
     7  7782 CLARK  MANAGER    7839 1981-01-09  2450    NA     10
     8  7788 SCOTT  ANALYST    7566 1982-12-09  3000    NA     20
     9  7839 KING   PRESIDENT    NA 1981-11-17  5000    NA     10
    10  7844 TURNER SALESMAN   7698 1981-09-08  1500     0     30
    11  7876 ADAMS  CLERK      7788 1983-01-12  1100    NA     20
    12  7900 JAMES  CLERK      7698 1981-12-03   950    NA     30
    13  7902 FORD   ANALYST    7566 1981-12-03  3000    NA     20
    14  7934 MILLER CLERK      7782 1982-01-23  1300    NA     10

     


    5. R sqldf Package

     

    R Programming
    %%R
    
    sqldf(" SELECT CAST(EMPNO AS VARCHAR(44))                            EMPNO_CHAR,
                   typeof(CAST(EMPNO AS VARCHAR(44)))                    EMPNO_char_type,
                   CAST(CAST(EMPNO AS VARCHAR(44)) AS NUMBER(4))         EMPNO_NUM,
                   typeof(CAST(CAST(EMPNO AS VARCHAR(44)) AS NUMBER(4))) EMPNO_num_type
            FROM   emp ")[1:5,]

     

    Results
      EMPNO_CHAR EMPNO_char_type EMPNO_NUM EMPNO_num_type
    1     7369.0            text      7369        integer
    2     7499.0            text      7499        integer
    3     7521.0            text      7521        integer
    4     7566.0            text      7566        integer
    5     7654.0            text      7654        integer

     

     


    6. Python pandasql Package

     

    Python Programming
    ps.sqldf(" SELECT CAST(EMPNO AS VARCHAR(44))                            EMPNO_CHAR,          \
                      typeof(CAST(EMPNO AS VARCHAR(44)))                    EMPNO_char_type,     \
                      CAST(CAST(EMPNO AS VARCHAR(44)) AS NUMBER(4))         EMPNO_NUM,           \
                      typeof(CAST(CAST(EMPNO AS VARCHAR(44)) AS NUMBER(4))) EMPNO_num_type       \
                FROM   emp ").head()

     

    Results
    	EMPNO_CHAR	EMPNO_char_type	EMPNO_NUM	EMPNO_num_type
    0	7369		text		7369		integer
    1	7499		text		7499		integer
    2	7521		text		7521		integer
    3	7566		text		7566		integer
    4	7654		text		7654		integer

     


    7. Python DuckDB의 SQL

     

    Python Programming
    %%sql
      SELECT EMPNO,
             CAST(EMPNO AS VARCHAR(10)) EMPNO_CHAR,
             CAST(CAST(EMPNO AS VARCHAR(44)) AS INTEGER) EMPNO_NUM
      FROM   emp

     

    Python Programming
    duckdb.sql(" SELECT EMPNO,                                                   \
                        CAST(EMPNO AS VARCHAR(10)) EMPNO_CHAR,                   \
                        CAST(CAST(EMPNO AS VARCHAR(44)) AS INTEGER) EMPNO_NUM    \
                 FROM   emp ").df()

     

    Results
       empno EMPNO_CHAR  EMPNO_NUM
    0   7369       7369       7369
    1   7499       7499       7499
    2   7521       7521       7521
    3   7566       7566       7566
    4   7654       7654       7654

     


     

    [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 예제로 만나는 테이블 데이터 처리 방법 리스트 링크 링크
    반응형

    댓글