포스팅 목차
130. Display those employees whose 10% of salary is equal to the year of joining?
입사일자의 첫 2 숫자(19)가 급여의 1%보다 큰 직원들의 정보를 출력하시오.
- [링크] 파이썬 & R 패키지 호출 및 예제 데이터 생성
- [데이터 추출] 특정 조건을 만족하는 데이터 추출 - 문자 추출, 문자형 수치를 수치형으로 변환
- 데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql
|
1. Oracle(오라클)
입사일자의 첫 2개의 숫자(19)가 급여의 1% 보다 더 큰 직원들의 정보를 출력한다.
| Oracle Programming |
select * from emp where to_char(hiredate,'yy') > sal*10/1000;
2. Python Pandas(파이썬)
Slice 함수를 사용하여서 입사일자의 첫 2개의 숫자(19)를 추출 후 이 값이 급여의 1% 보다 더 큰 직원들의 정보를 출력한다.
| Python Programming |
emp [ pd.to_numeric(emp['hiredate'].str.slice(0,2)) > emp['sal']*10/1000 ].head()| Results |
| empno | ename | job | mgr | hiredate | sal | comm | deptno | |
| 0 | 7369 | SMITH | CLERK | 7902.0 | 1980/12/17 | 800 | NaN | 20 |
| 1 | 7499 | ALLEN | SALESMAN | 7698.0 | 1981/02/20 | 1600 | 300.0 | 30 |
| 2 | 7521 | WARD | SALESMAN | 7698.0 | 1981/02/22 | 1250 | 500.0 | 30 |
| 4 | 7654 | MARTIN | SALESMAN | 7698.0 | 1981/09/28 | 1250 | 1400.0 | 30 |
| 9 | 7844 | TURNER | SALESMAN | 7698.0 | 1981/09/08 | 1500 | 0.0 | 30 |
3. R Programming (R Package)
base::substr() 함수를 사용하여서 입사일자의 첫 2개의 숫자(19)를 추출 후 이 값이 급여의 1% 보다 더 큰 직원들의 정보를 출력한다.
| R Programming |
%%R
withmooc <- emp
emp[ ( as.numeric(base::substr(emp$hiredate,1,2)) ) > (emp$sal*10/1000) , ]
| Results |
# A tibble: 8 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 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 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
5 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
6 7876 ADAMS CLERK 7788 1983-01-12 1100 NA 20
7 7900 JAMES CLERK 7698 1981-12-03 950 NA 30
8 7934 MILLER CLERK 7782 1982-01-23 1300 NA 10
4. R Dplyr Package
stringr::str_sub() 함수를 사용하여서 입사일자의 첫 2개의 숫자(19)를 추출 후 이 값이 급여의 1% 보다 더 큰 직원들의 정보를 출력한다. 여기서 2개의 값을 수치형으로 비교를 위하여 as.numeric() 함수를 사용하여 문자형 값을 수치형 값으로 변환하여 처리한다.
| R Programming |
%%R
library(stringi)
emp %>%
# dplyr::mutate(temp1 = as.numeric(stringr::str_sub(hiredate,1,2)) ) # 입사일자(문자형)의 첫 두글자를 추출 후 수치형으로 변경
dplyr::filter( as.numeric(stringr::str_sub(hiredate,1,2)) > (sal*10/1000))
| Results |
# A tibble: 6 x 8
empno ename job mgr hiredate sal comm deptno
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl>
1 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
2 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
3 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
4 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
5 7876 ADAMS CLERK 7788 1983-01-12 1100 NA 20
6 7934 MILLER CLERK 7782 1982-01-23 1300 NA 10
5. R sqldf Package
입사일자의 첫 2개의 숫자(19)가 급여의 1% 보다 더 큰 직원들의 정보를 출력한다.
- 현재 hiredate가 “real” 형태로 저장되어 있어서 datetime 함수를 사용하여서 문자형을 전환 후 substr() 함수를 사용하여서 2글자를 추출한다.
- R의 sqlite에는 날짜형을 지원 안 함 (129번 sqldf)
- unixepoch : 유닉스(UNIX) timestamp로 1970년 1월 1일을 기준으로 초 단위로 시간을 계산하여 반환한다.
| R Programming |
%%R
sqldf(" select empno,ename,
typeof(hiredate) hire_real,
typeof(datetime(hiredate * 3600 * 24,'unixepoch')) hire_text,
datetime(hiredate * 3600 * 24,'unixepoch') hire_unixepoch,
substr(datetime(hiredate * 3600 * 24,'unixepoch'),1,2) year_str,
sal*10/1000 sal_1p
from emp
where cast(substr(datetime(hiredate * 3600 * 24,'unixepoch'),1,2) as int) > sal*10/1000")
| Results |
empno ename hire_real hire_text hire_unixepoch year_str sal_1p
1 7369 SMITH real text 1980-12-17 00:00:00 19 8.0
2 7499 ALLEN real text 1981-02-20 00:00:00 19 16.0
3 7521 WARD real text 1981-02-22 00:00:00 19 12.5
4 7654 MARTIN real text 1981-09-28 00:00:00 19 12.5
5 7844 TURNER real text 1981-09-08 00:00:00 19 15.0
6 7876 ADAMS real text 1983-01-12 00:00:00 19 11.0
7 7900 JAMES real text 1981-12-03 00:00:00 19 9.5
8 7934 MILLER real text 1982-01-23 00:00:00 19 13.0
6. Python pandasql Package
| Python Programming |
ps.sqldf(" select empno,ename, \
substr(datetime(hiredate * 3600 * 24,'unixepoch'),1,2) year_str, \
sal*10/1000 sal_1p \
from emp \
where cast(substr(datetime(hiredate * 3600 * 24,'unixepoch'),1,2) as int) > sal*10/1000 ").head()
| Results |
| empno | ename | year_str | sal_1p | |
| 0 | 7369 | SMITH | 19 | 8 |
| 1 | 7499 | ALLEN | 19 | 16 |
| 2 | 7521 | WARD | 19 | 12 |
| 3 | 7654 | MARTIN | 19 | 12 |
| 4 | 7844 | TURNER | 19 | 15 |
7. R data.table Package
stringr::str_sub() 함수를 사용하여서 입사일자의 첫 2개의 숫자(19)를 추출 후 이 값이 급여의 1% 보다 더 큰 직원들의 정보를 출력한다. 여기서 2개의 값을 수치형으로 비교를 위하여 as.numeric() 함수를 사용하여 문자형 값을 수치형 값으로 변환하여 처리한다.
| R Programming |
%%R
DT <- data.table(emp)
DT[ as.numeric(stringr::str_sub(hiredate,1,2)) > (sal*10/1000) ]
| Results |
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: 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
5: 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
6: 7876 ADAMS CLERK 7788 1983-01-12 1100 NA 20
7: 7900 JAMES CLERK 7698 1981-12-03 950 NA 30
8: 7934 MILLER CLERK 7782 1982-01-23 1300 NA 10
8. SAS Proc SQL
| SAS Programming |
%%SAS sas
PROC SQL;
CREATE TABLE STATSAS_1 AS
select A.*,
SUBSTR(put(hiredate,year4.),1,2) AS YEAR_YY_CHAR,
INPUT(SUBSTR(put(hiredate,year4.),1,2),8.) AS YEAR_YY_NUM,
SUBSTR(PUT(YEAR(hiredate),4.),1,2) AS YEAR_CHAR,
INPUT(SUBSTR(PUT(YEAR(hiredate),4.),1,2),8.) AS YEAR_NUM
from emp A
WHERE INPUT(SUBSTR(put(hiredate,year4.),1,2),8.) > sal*10/1000;
QUIT;
PROC PRINT Data=STATSAS_1(Drop=job mgr sal comm deptno);RUN;
| Results |
| OBS | empno | ename | hiredate | YEAR_YY_CHAR | YEAR_YY_NUM | YEAR_CHAR | YEAR_NUM |
| 1 | 7369 | SMITH | 1980-12-17 | 19 | 19 | 19 | 19 |
| 2 | 7499 | ALLEN | 1981-02-20 | 19 | 19 | 19 | 19 |
| 3 | 7521 | WARD | 1981-02-22 | 19 | 19 | 19 | 19 |
| 4 | 7654 | MARTIN | 1981-09-28 | 19 | 19 | 19 | 19 |
| 5 | 7844 | TURNER | 1981-09-08 | 19 | 19 | 19 | 19 |
| 6 | 7876 | ADAMS | 1983-01-12 | 19 | 19 | 19 | 19 |
| 7 | 7900 | JAMES | 1981-12-03 | 19 | 19 | 19 | 19 |
| 8 | 7934 | MILLER | 1982-01-23 | 19 | 19 | 19 | 19 |
9. SAS Data Step
| SAS Programming |
%%SAS sas
DATA STATSAS_2;
SET emp;
YEAR_YY_CHAR = SUBSTR(put(hiredate,year4.),1,2);
YEAR_YY_NUM = INPUT(SUBSTR(put(hiredate,year4.),1,2),8.);
YEAR_CHAR = SUBSTR(PUT(YEAR(hiredate),4.),1,2);
YEAR_NUM = INPUT(SUBSTR(PUT(YEAR(hiredate),4.),1,2),8.);
Drop job mgr sal comm deptno;
where INPUT(SUBSTR(put(hiredate,year4.),1,2),8.) > sal*10/1000;
RUN;
PROC PRINT;RUN;
| Results |
| empno | ename | hiredate | YEAR_YY_CHAR | YEAR_YY_NUM | YEAR_CHAR | YEAR_NUM | |
| 1 | 7369 | SMITH | 1980-12-17 | 19 | 19 | 19 | 19 |
| 2 | 7499 | ALLEN | 1981-02-20 | 19 | 19 | 19 | 19 |
| 3 | 7521 | WARD | 1981-02-22 | 19 | 19 | 19 | 19 |
| 4 | 7654 | MARTIN | 1981-09-28 | 19 | 19 | 19 | 19 |
| 5 | 7844 | TURNER | 1981-09-08 | 19 | 19 | 19 | 19 |
| 6 | 7876 | ADAMS | 1983-01-12 | 19 | 19 | 19 | 19 |
| 7 | 7900 | JAMES | 1981-12-03 | 19 | 19 | 19 | 19 |
| 8 | 7934 | MILLER | 1982-01-23 | 19 | 19 | 19 | 19 |
10. Python Dfply Package
| Python Programming |
emp >> \
filter_by( make_symbolic(pd.to_numeric)(X.hiredate.str.slice(0,2)) > X.sal*10/1000 ) >> \
head()
| Results |
| empno | ename | job | mgr | hiredate | sal | comm | deptno | |
| 0 | 7369 | SMITH | CLERK | 7902.0 | 1980/12/17 | 800 | NaN | 20 |
| 1 | 7499 | ALLEN | SALESMAN | 7698.0 | 1981/02/20 | 1600 | 300.0 | 30 |
| 2 | 7521 | WARD | SALESMAN | 7698.0 | 1981/02/22 | 1250 | 500.0 | 30 |
| 4 | 7654 | MARTIN | SALESMAN | 7698.0 | 1981/09/28 | 1250 | 1400.0 | 30 |
| 9 | 7844 | TURNER | SALESMAN | 7698.0 | 1981/09/08 | 1500 | 0.0 | 30 |
[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 전처리 방법 리스트
반응형
댓글