포스팅 목차
142. List out employees name and salary increased by 15% and expressed as whole number of dollars?
* 직원 이름과 15% 인상된 급여 계산 후 달러 표시와 함께 출력하시오.
- 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
- 서브쿼리 : 48 / 50
- [문자 함수와 수치형 함수] 문자 결합 함수와 반올림 함수 - 달러('$') 출력
- 데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql
|
1. Oracle(오라클)
15% 인상된 급여를 계산 후 concat 함수를 사용하여서 계산된 값에 달러(‘$’)를 추가하여 함께 출력한다.
| Oracle Programming |
select empno,ename,sal,
lpad(concat('$',round(sal*115/100)),7) salary
from emp;
2. Python Pandas(파이썬)
15% 인상된 급여를 계산 후 astype() 함수를 사용하여서 정수로 변환 후 출력형식을 지정하기 위하여 format() 함수를 사용하여 달러를 추가하여 함께 출력한다.
| Python Programming |
(emp['sal']*115/100).astype(int).apply(lambda x: "${:}".format((x))).head()
| Results |
0 $920
1 $1840
2 $1437
3 $3421
4 $1437
Name: sal, dtype: object
3. R Programming (R Package)
15% 인상된 급여를 계산 후 priceR::format_dollars() 함수를 사용하여서 달러표시와 함께 출력한다.
| R Programming |
%%R
library(priceR)
emp["salary"] = priceR::format_dollars((emp$sal*115/100))
emp[1:5, ]
| Results |
# A tibble: 5 x 9
empno ename job mgr hiredate sal comm deptno salary
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <chr>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 $920
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 $1,840
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 $1,438
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 $3,421
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 $1,438
4. R Dplyr Package
15% 인상된 급여를 계산 후 formattable::currency() 또는 scales::dollar() 함수를 사용하여서 달러 표시와 함께 출력한다.
| R Programming |
%%R
library(formattable)
library(scales)
withmooc <- emp
withmooc %>%
dplyr::mutate(salary = formattable::currency(as.integer(sal*115/100), digits = 0L) ,
salary1 = scales::dollar(round(sal*115/100))) %>%
head()
| Results |
# A tibble: 6 x 10
empno ename job mgr hiredate sal comm deptno salary salary1
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <formttbl> <chr>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 $920 $920
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 $1,840 $1,840
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 $1,437 $1,438
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 $3,421 $3,421
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 $1,437 $1,438
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 $3,277 $3,278
5. R sqldf Package
15% 인상된 급여를 계산 후 cast 함수를 사용하여서 계산 결과를 정수로 변환하고, 달러(‘$’)를 추가하여 함께 출력한다.
| R Programming |
%%R
sqldf(" select empno,ename,
'$'||cast(round(sal*115/100) as int) salary
from emp; ") %>% head()
| Results |
empno ename salary
1 7369 SMITH $920
2 7499 ALLEN $1840
3 7521 WARD $1438
4 7566 JONES $3421
5 7654 MARTIN $1438
6 7698 BLAKE $3278
6. Python pandasql Package
| Python Programming |
ps.sqldf(" select empno,ename,'$'||cast(round(sal*115/100) as int) salary \
from emp; ").head()
| Results |
| empno | ename | salary | |
| 0 | 7369 | SMITH | $920 |
| 1 | 7499 | ALLEN | $1840 |
| 2 | 7521 | WARD | $1437 |
| 3 | 7566 | JONES | $3421 |
| 4 | 7654 | MARTIN | $1437 |
7. R data.table Package
15% 인상된 급여를 계산 후 priceR::format_dollars() 함수를 사용하여서 달러표시와 함께 출력한다.
| R Programming |
%%R
DT <- data.table(emp)
DT[, salary := priceR::format_dollars(sal*115/100)][1:5, ]
| Results |
empno ename job mgr hiredate sal comm deptno salary
1: 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 $920
2: 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 $1,840
3: 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 $1,438
4: 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 $3,421
5: 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 $1,438
8. SAS Proc SQL
| SAS Programming |
%%SAS sas
PROC SQL;
create table STATSAS_1 as
select empno,ename,
sal,
put(sal*115/100,dollar.) as salary_1,
'$'||STRIP( put(int(sal*115/100),4.) ) as salary_2,
cats('$',put(int(sal*115/100),4.)) as salary_3
from emp;
QUIT;
PROC PRINT data=STATSAS_1(obs=3);RUN;
| Results |
| OBS | empno | ename | sal | salary_1 | salary_2 | salary_3 |
| 1 | 7369 | SMITH | 800 | $920 | $920 | $920 |
| 2 | 7499 | ALLEN | 1600 | $1,840 | $1840 | $1840 |
| 3 | 7521 | WARD | 1250 | $1,438 | $1437 | $1437 |
9. SAS Data Step
| SAS Programming |
%%SAS sas
data STATSAS_2;
set emp;
salary_1 = put(sal*115/100,dollar.);
salary_2 = '$'||STRIP( put(int(sal*115/100),4.) );
salary_3 = cats('$',put(int(sal*115/100),4.));
keep empno ename sal:;
run;
PROC PRINT data=STATSAS_2(obs=3);RUN;
| Results |
| OBS | empno | ename | sal | salary_1 | salary_2 | salary_3 |
| 1 | 7369 | SMITH | 800 | $920 | $920 | $920 |
| 2 | 7499 | ALLEN | 1600 | $1,840 | $1840 | $1840 |
| 3 | 7521 | WARD | 1250 | $1,438 | $1437 | $1437 |
10. Python Dfply Package
| Python Programming |
emp >> \
mutate(salary = ( (X.sal+115/100).astype(int).apply(lambda x: "${:}".format((x)) ) )) >> \
head
| Results |
| empno | ename | job | mgr | hiredate | sal | comm | deptno | salary | |
| 0 | 7369 | SMITH | CLERK | 7902.0 | 1980/12/17 | 800 | NaN | 20 | $801 |
| 1 | 7499 | ALLEN | SALESMAN | 7698.0 | 1981/02/20 | 1600 | 300.0 | 30 | $1601 |
| 2 | 7521 | WARD | SALESMAN | 7698.0 | 1981/02/22 | 1250 | 500.0 | 30 | $1251 |
| 3 | 7566 | JONES | MANAGER | 7839.0 | 1981/04/02 | 2975 | NaN | 20 | $2976 |
| 4 | 7654 | MARTIN | SALESMAN | 7698.0 | 1981/09/28 | 1250 | 1400.0 | 30 | $1251 |
[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 전처리 방법 리스트
반응형
댓글