포스팅 목차
* 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
[ CAST 함수 ]
CAST 함수는 데이터 형식이나 collection 형식을 다른 데이터 형식이나 collection 형식으로 변환한다.
- 함수 설명 : CAST 오라클 함수 링크
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 예제로 만나는 테이블 데이터 처리 방법 리스트 링크 링크 |
반응형
댓글