[집계 함수] 그룹별 평균과 합계 계산 - 결측치 처리

포스팅 목차

156. Find out the avg sal and avg total remuneration for each job type remember salesman earn commission.

* 개별 직무별 직원들의 수수료(commission)를 포함한 급여 평균과 합계를 집계하시오.

파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
유사문제 : 6 / 27
group by : 42 / 46
assign / transform : 41
[집계 함수] 그룹별 평균과 합계 계산 - 결측치 처리
데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql

Oracle : avg(), sum(), NVL()
파이썬 Pandas : assign(), replace(), groupby(), agg()의 ['mean','sum']
R 프로그래밍 : transform(), ifelse(), is.na(), aggregate()의 function(x) 사용자 정의 함수, Mean(), Sum(), do.call(), rbind, tapply(), with()
R Dplyr Package : ifelse(), is.na(), dplyr::summarise()의 mean()과 sum() 함수
R sqldf Package : avg(), sum(), NVL()
Python pandasql Package : avg(), sum(), NVL()
R data.table Package : ifelse(), is.na(), mean(), sum()
SAS Proc SQL : avg(), sum(), NVL()
SAS Data Step : COALESCE(), Proc Summary의 MEAN과 SUM 인수
Python Dfply Package : if_else(), isnull(), Mean(), Sum()
파이썬 Base 프로그래밍 :

1. Oracle(오라클)

직무별 직원들의 수수료를 고려한 평균 급여와 합계를 집계한다.

[오라클 SQL 함수] NVL 함수 : http://statwith.com/nvl-oracle-function-comparision/

Oracle Programming

select job,
       avg(sal+nvl(comm,0)) sal_avg,
       sum(sal+nvl(comm,0)) sal_sum
from   emp 
group 
   by  job;

2. Python Pandas(파이썬)

직무별 직원들의 수수료를 고려한 급여를 계산하여서 assign() 함수를 사용하여서 신규 변수(‘tot_sal’)로 생성한다.

Python Programming

emp.assign(tot_sal = (emp["sal"]+emp["comm"].replace(np.nan,0))).head()

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno	tot_sal
0	7369	SMITH	CLERK	7902.0	1980/12/17	800	NaN	20	800.0
1	7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30	1900.0
2	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30	1750.0
3	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20	2975.0
4	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30	2650.0

agg() 함수를 사용하여서 직무별 직원들의 수수료를 고려한 평균 급여와 합계를 집계한다.

Python Programming

emp.assign(tot_sal = (emp["sal"]+emp["comm"].replace(np.nan,0))).groupby(['job']).agg({'tot_sal':['mean','sum']}).reset_index()

Results

	job	tot_sal
		mean	sum
0	ANALYST	3000.000000	6000.0
1	CLERK	1037.500000	4150.0
2	MANAGER	2758.333333	8275.0
3	PRESIDENT	5000.000000	5000.0
4	SALESMAN	1950.000000	7800.0

3. R Programming (R Package)

직무별 직원들의 수수료를 고려한 급여를 계산하여서 transform() 함수를 사용하여서 신규 변수(‘tot_sal’)로 생성한다.

R Programming

%%R

transform(emp, tot_sal = (sal + ifelse(is.na(comm),0,comm) )) %>% head()

Results

  empno  ename      job  mgr   hiredate  sal comm deptno tot_sal
1  7369  SMITH    CLERK 7902 1980-12-17  800   NA     20     800
2  7499  ALLEN SALESMAN 7698 1981-02-20 1600  300     30    1900
3  7521   WARD SALESMAN 7698 1981-02-22 1250  500     30    1750
4  7566  JONES  MANAGER 7839 1981-04-02 2975   NA     20    2975
5  7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400     30    2650
6  7698  BLAKE  MANAGER 7839 1981-03-01 2850   NA     30    2850

transform() 함수를 통하여 수수료를 고려한 급여를 사전에 계산 후 aggregate() 함수를 사용하여서 직무별 직원들의 수수료를 고려한 평균 급여와 합계를 집계한다.

R Programming

%%R

emp1 <- transform(emp, tot_sal = (sal + ifelse(is.na(comm),0,comm) ))

aggregate(sal~ job, data = emp1, FUN = function(x) c(mean = mean(x), sum = sum(x) ) )

Results

        job sal.mean  sal.sum
1   ANALYST 3000.000 6000.000
2     CLERK 1037.500 4150.000
3   MANAGER 2758.333 8275.000
4 PRESIDENT 5000.000 5000.000
5  SALESMAN 1400.000 5600.000

transform() 함수를 통하여 수수료를 고려한 급여를 사전에 계산 후 aggregate() 함수에 by= 인수에 직무(‘job’)를 지정하여서 직무별 직원들의 수수료를 고려한 평균 급여와 합계를 집계한다.

R Programming

%%R
emp1 <- transform(emp, tot_sal = (sal + ifelse(is.na(comm),0,comm) ))

aggregate(emp1$tot_sal, by =list(emp1$job), function(x) { c(sal_mean=mean(x), sal_sum=sum(x) )})

Results

    Group.1 x.sal_mean x.sal_sum
1   ANALYST   3000.000  6000.000
2     CLERK   1037.500  4150.000
3   MANAGER   2758.333  8275.000
4 PRESIDENT   5000.000  5000.000
5  SALESMAN   1950.000  7800.000

tapply() 함수를 사용하여서 직무별로 개별적으로 직원들의 수수료를 고려한 평균 급여와 급여 합계를 집계 후 rbind 함수로 리스트로 계산된 결과를 데이터프레임으로 변환하여 출력한다.

R Programming

%%R

do.call(rbind, tapply(emp1$tot_sal, emp$job, FUN =  function(x) c( sal_mean = mean(x), sal_sum= sum(x) )) )

Results

          sal_mean sal_sum
ANALYST   3000.000    6000
CLERK     1037.500    4150
MANAGER   2758.333    8275
PRESIDENT 5000.000    5000
SALESMAN  1950.000    7800

tapply() 함수를 사용하여서 직무별로 개별적으로 직원들의 수수료를 고려한 평균 급여와 급여 합계를 집계 후 rbind 함수로 리스트로 계산된 결과를 데이터프레임으로 변환하여 출력한다. with() 함수는 데이터프레임의 변수를 변수 이름만 사용하여 처리하도록 지원한다.

R Programming

%%R

do.call(rbind, with(emp1, tapply(tot_sal, list(job), function(x) { c(mean(x) , sum(x) )} )) )

Results

              [,1] [,2]
ANALYST   3000.000 6000
CLERK     1037.500 4150
MANAGER   2758.333 8275
PRESIDENT 5000.000 5000
SALESMAN  1950.000 7800

함수 내에 %>% (체인-chain, 파이프 연산자, 파이프라인) 연산자를 사용하여서 계산된 직원 평균 급여의 소수점 자리수를 변경 할 수 있다.

R Programming

%%R

aggregate(emp1$tot_sal ~ emp$job, data = emp1, FUN = . %>% mean %>% round(1))

Results

    emp$job emp1$tot_sal
1   ANALYST       3000.0
2     CLERK       1037.5
3   MANAGER       2758.3
4 PRESIDENT       5000.0
5  SALESMAN       1950.0

4. R Dplyr Package

mutate() 함수를 통하여 수수료를 고려한 급여를 사전에 계산 후 summarise() 함수를 사용하여서 직무별 직원들의 수수료를 고려한 평균 급여와 합계를 집계한다.

R Programming

%%R
emp %>% 
  dplyr::mutate( tot_sal = sal+ifelse(is.na(comm),0,comm)) %>% 
  group_by(job) %>% 
  dplyr::summarise( sal_mean = mean(tot_sal), sal_sum = sum(tot_sal) )

Results

`summarise()` ungrouping output (override with `.groups` argument)
# A tibble: 5 x 3
  job       sal_mean sal_sum
  <chr>        <dbl>   <dbl>
1 ANALYST      3000     6000
2 CLERK        1038.    4150
3 MANAGER      2758.    8275
4 PRESIDENT    5000     5000
5 SALESMAN     1950     7800

5. R sqldf Package

직무별 직원들의 수수료를 고려한 평균 급여와 합계를 집계한다.

sqlite에서 nvl함수는 지원하지 않아서, ifnull 함수를 사용하여서 null값을 0 으로 변경 할 수 있다.
NVL Oracle Function [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table] : http://statwith.com/nvl-oracle-function-comparision/

R Programming

%%R

sqldf(" select job,
               avg(sal+ifnull(comm,0)) as sal_avg,
               sum(sal+ifnull(comm,0)) as sal_sum
        from   emp
        group 
           by  job;")

Results

        job  sal_avg sal_sum
1   ANALYST 3000.000    6000
2     CLERK 1037.500    4150
3   MANAGER 2758.333    8275
4 PRESIDENT 5000.000    5000
5  SALESMAN 1950.000    7800

6. Python pandasql Package

직무별 직원들의 수수료를 고려한 평균 급여와 합계를 집계한다.

Python Programming

ps.sqldf(" select job,                                   \
                  avg(sal+ifnull(comm,0)) as sal_avg,    \
                  sum(sal+ifnull(comm,0)) as sal_sum     \
           from emp group by job;")

Results

	job	sal_avg	sal_sum
0	ANALYST	3000.000000	6000.0
1	CLERK	1037.500000	4150.0
2	MANAGER	2758.333333	8275.0
3	PRESIDENT	5000.000000	5000.0
4	SALESMAN	1950.000000	7800.0

7. R data.table Package

ifelse() 함수를 사용하여서 수수료(‘comm’)의 값이 na 인 경우 0으로 변경 후 수수료를 고려한 급여를 사전에 계산 후 직무별 직원들의 수수료를 고려한 평균 급여와 합계를 집계한다.

R Programming

%%R
DT <- data.table(emp)

DT[,tot_sal := (sal+ifelse(is.na(comm),0,comm))][ , .(mean(tot_sal), sum(tot_sal)), by = .(job)]

Results

         job       V1   V2
1:     CLERK 1037.500 4150
2:  SALESMAN 1950.000 7800
3:   MANAGER 2758.333 8275
4:   ANALYST 3000.000 6000
5: PRESIDENT 5000.000 5000

8. SAS Proc SQL

직무별 직원들의 수수료를 고려한 평균 급여와 합계를 집계한다.

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    select job,
           avg(sal+COALESCE(comm,0)) AS sal_avg,
           sum(sal+COALESCE(comm,0)) AS sal_sum
    from   emp 
    group 
       by  job;
QUIT;
PROC PRINT;RUN;

Results

OBS	job	sal_avg	sal_sum
1	ANALYST	3000.00	6000
2	CLERK	1037.50	4150
3	MANAGER	2758.33	8275
4	PRESIDEN	5000.00	5000
5	SALESMAN	1950.00	7800

9. SAS Data Step

SAS Programming

%%SAS sas

DATA STATSAS_2;
 SET EMP;
     SAL_COMM = (sal+COALESCE(comm,0));
RUN;

PROC SUMMARY DATA=STATSAS_2 NWAY;
     CLASS JOB;
     VAR   SAL_COMM;
     OUTPUT OUT=STATSAS_3(DROP=_:) MEAN=sal_avg SUM=sal_sum;
QUIT;
PROC PRINT;RUN;

Results

OBS	job	sal_avg	sal_sum
1	ANALYST	3000.00	6000
2	CLERK	1037.50	4150
3	MANAGER	2758.33	8275
4	PRESIDEN	5000.00	5000
5	SALESMAN	1950.00	7800

10. Python Dfply Package

Python Programming

emp >> \
  mutate( tot_sal = X.sal + if_else(X.comm.isnull(),0,X.comm)) >> \
  group_by('job')  >> \
  summarize( sal_mean = mean(X.tot_sal), sal_sum = X.tot_sal.sum() )

Results

	job	sal_mean	sal_sum
0	ANALYST	3000.000000	6000.0
1	CLERK	1037.500000	4150.0
2	MANAGER	2758.333333	8275.0
3	PRESIDENT	5000.000000	5000.0
4	SALESMAN	1950.000000	7800.0

[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 전처리 방법 리스트

저작자표시 비영리 변경금지

'통계프로그램 비교 시리즈 > 프로그래밍비교(Oracle,Python,R,SAS)' 카테고리의 다른 글

[비상관 서브쿼리(In)] 특정 조건을 만족하는 데이터 추출 - 최소값(Min) - 158 (오라클 SQL, R, Python, SAS) (0)	2023.01.14
[집계 함수] 데이터 유일성 체크 - 데이터 중복 제거 - 157 (오라클 SQL, R, Python, SAS) (0)	2023.01.10
[총건수 카운트] 특정 조건을 만족하는 데이터 집계 - 155 (오라클 SQL, R, Python, SAS) (1)	2023.01.09
[결측치 처리 ] 특정 조건을 만족하는 데이터 추출- 154 (오라클 SQL, R, Python, SAS) (0)	2023.01.08
[변수 생성] 신규 변수를 생성하여 출력- 153 (오라클 SQL, R, Python, SAS) (0)	2023.01.06

[집계 함수] 그룹별 평균과 합계 계산 - 결측치 처리 - 156 (오라클 SQL, R, Python, SAS)

156. Find out the avg sal and avg total remuneration for each job type remember salesman earn commission.

1. Oracle(오라클)

2. Python Pandas(파이썬)

3. R Programming (R Package)

4. R Dplyr Package

5. R sqldf Package

6. Python pandasql Package

7. R data.table Package

8. SAS Proc SQL

9. SAS Data Step

10. Python Dfply Package

'통계프로그램 비교 시리즈 > 프로그래밍비교(Oracle,Python,R,SAS)' 카테고리의 다른 글

댓글

티스토리툴바

[집계 함수] 그룹별 평균과 합계 계산 - 결측치 처리 - 156 (오라클 SQL, R, Python, SAS)

156. Find out the avg sal and avg total remuneration for each job type remember salesman earn commission.

1. Oracle(오라클)

2. Python Pandas(파이썬)

3. R Programming (R Package)

4. R Dplyr Package

5. R sqldf Package

6. Python pandasql Package

7. R data.table Package

8. SAS Proc SQL

9. SAS Data Step

10. Python Dfply Package

'통계프로그램 비교 시리즈 > 프로그래밍비교(Oracle,Python,R,SAS)' 카테고리의 다른 글

관련글

댓글

티스토리툴바