본문 바로가기
통계프로그램 비교 시리즈/오라클함수 비교(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 예제로 만나는 테이블 데이터 처리 방법 리스트 링크 링크
반응형
저작자표시 비영리 변경금지

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

CHR Oracle Function [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table]  (0) 2021.11.21
CEIL Oracle Function [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table, DuckDB]  (0) 2021.11.21
BITAND Oracle Function [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table, DuckDB]  (0) 2021.11.21
BIN_TO_NUM Oracle Function [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table]  (0) 2021.11.20
AVG Oracle Function [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table, DuckDB]  (0) 2021.11.20

관련글

댓글

티스토리툴바