[데이터 정렬 - 조건절 기준 정렬] 신규 변수를 기준으로 데이터 내림차순 정렬

포스팅 목차

**40. Display the name of the employee along with their annual salary (Sal * 12). The name of the employee earning highest annual salary should appear first.**

* emp테이블에서 근무하는 직원의 연봉(급여*12)를 기준으로 내림차순(연봉이 큰 값 우선)으로 정렬하고 해당 직원의 이름을 출력하시오.

파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
[데이터 정렬] 신규로 생성된 정렬 키를 기준으로 데이터 내림차순 정렬

Oracle : order by, nvl(), desc
파이썬 Pandas : sort_values(), replace(), .fillna(), ascending=
R 프로그래밍 : order(), ifelse(), is.na(), transform(), descending(-)
R Dplyr Package : dplyr::arrange(), desc(), dplyr::mutate, ifelse
R sqldf Package : order by, ifnull(), desc
Python pandasql Package : order by, ifnull(), desc
R data.table Package : order(), ifelse(), is.na(), desc()
SAS Proc SQL : order by, desc
SAS Data Step : proc sort, DESCENDING
Python Dfply Package : arrange(), sort_values(), ascending=, .replace()
파이썬 Base 프로그래밍 :

1. 오라클(Oracle)

Order by 구문

Oracle Programming

select ename, 12*(sal+nvl(comm,0)) Annual 
from   emp 
order 
   by  12*(sal+nvl(comm,0)) desc;

2. 파이썬(Pandas)

Sort_values() 함수

Python Programming

emp["Annual"] = 12 * emp["sal"] + emp["comm"].replace(np.nan,0)

emp[["ename","Annual"]].sort_values(by=["Annual"], ascending=[False])

Results

	ename	Annual
8	KING	60000.0
7	SCOTT	36000.0
12	FORD	36000.0
3	JONES	35700.0
5	BLAKE	34200.0
6	CLARK	29400.0
1	ALLEN	19500.0
9	TURNER	18000.0
4	MARTIN	16400.0
13	MILLER	15600.0
2	WARD	15500.0
10	ADAMS	13200.0
11	JAMES	11400.0
0	SMITH	9600.0

Sort_values() 함수

Python Programming

emp["Annual"] =  ( 12 * emp["sal"] + emp.fillna({'comm': 0})['comm'] )

emp[["ename","Annual"]].sort_values(by=["Annual"], ascending=[False])

Results

	ename	Annual
8	KING	60000.0
7	SCOTT	36000.0
12	FORD	36000.0
3	JONES	35700.0
5	BLAKE	34200.0
6	CLARK	29400.0
1	ALLEN	19500.0
9	TURNER	18000.0
4	MARTIN	16400.0
13	MILLER	15600.0
2	WARD	15500.0
10	ADAMS	13200.0
11	JAMES	11400.0
0	SMITH	9600.0

3. R Programming (R Package)

ifelse() 함수와 Order 함수

R Programming

%%R

emp$Annual <- 12 * emp["sal"] + ifelse(is.na(emp$comm),0,emp$comm)

emp[order(-emp$Annual) , c("empno","Annual")]

Results

# A tibble: 14 x 2
   empno Annual$sal
   <dbl>      <dbl>
 1  7839      60000
 2  7788      36000
 3  7902      36000
 4  7566      35700
 5  7698      34200
 6  7782      29400
 7  7499      19500
 8  7844      18000
 9  7654      16400
10  7934      15600
11  7521      15500
12  7876      13200
13  7900      11400
14  7369       9600

Transform() 함수와 Order 함수

R Programming

%%R

emp_chg <- transform(emp,Annual = 12*(emp$sal+replace_na(emp$comm,0)) )

emp_chg[order(-emp$Annual) , c("empno","Annual")]

Results

   empno Annual
9   7839  60000
8   7788  36000
13  7902  36000
4   7566  35700
6   7698  34200
7   7782  29400
2   7499  22800
10  7844  18000
5   7654  31800
14  7934  15600
3   7521  21000
11  7876  13200
12  7900  11400
1   7369   9600

4. R Dplyr Package

Mutate() 함수와 Arrange 함수

R Programming

%%R
emp %>% dplyr::mutate(Annual = 12 * sal + ifelse(is.na(comm),0,comm)) %>% dplyr::select(ename, Annual) %>% dplyr::arrange(desc(Annual))

Results

# A tibble: 14 x 2
   ename  Annual
   <chr>   <dbl>
 1 KING    60000
 2 SCOTT   36000
 3 FORD    36000
 4 JONES   35700
 5 BLAKE   34200
 6 CLARK   29400
 7 ALLEN   19500
 8 TURNER  18000
 9 MARTIN  16400
10 MILLER  15600
11 WARD    15500
12 ADAMS   13200
13 JAMES   11400
14 SMITH    9600

5. R sqldf Package

Order by 구문

R Programming

%%R

sqldf("select ename, 12*(sal+ifnull(comm,0)) Annual from emp order by 12*(sal+ifnull(comm,0)) desc")

Results

    ename Annual
1    KING  60000
2   SCOTT  36000
3    FORD  36000
4   JONES  35700
5   BLAKE  34200
6  MARTIN  31800
7   CLARK  29400
8   ALLEN  22800
9    WARD  21000
10 TURNER  18000
11 MILLER  15600
12  ADAMS  13200
13  JAMES  11400
14  SMITH   9600

6. Python pandasql Package

Order by 구문

Python Programming

ps.sqldf("select ename, 12*(sal+ifnull(comm,0)) Annual from emp order by 12*(sal+ifnull(comm,0)) desc")

Results

	ename	Annual
0	KING	60000.0
1	SCOTT	36000.0
2	FORD	36000.0
3	JONES	35700.0
4	BLAKE	34200.0
5	MARTIN	31800.0
6	CLARK	29400.0
7	ALLEN	22800.0
8	WARD	21000.0
9	TURNER	18000.0
10	MILLER	15600.0
11	ADAMS	13200.0
12	JAMES	11400.0
13	SMITH	9600.0

7. R data.table Package

Order 함수

R Programming

%%R

DT          <- data.table(emp)
dept_DT     <- data.table(dept)

DT[, `:=`(Annual = 12 * sal + ifelse(is.na(comm), 0, comm))][, .(ename, Annual)][order(desc(Annual))]

Results

     ename Annual
 1:   KING  60000
 2:  SCOTT  36000
 3:   FORD  36000
 4:  JONES  35700
 5:  BLAKE  34200
 6:  CLARK  29400
 7:  ALLEN  19500
 8: TURNER  18000
 9: MARTIN  16400
10: MILLER  15600
11:   WARD  15500
12:  ADAMS  13200
13:  JAMES  11400
14:  SMITH   9600

8. SAS Proc SQL

Order by 구문

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    select ename, 12*(sal+coalesce(comm,0)) AS Annual 
    from   emp 
    order 
       by  2 desc;
QUIT;
PROC PRINT data=STATSAS_1(obs=5);RUN;

Results

OBS	ename	Annual
1	KING	60000
2	SCOTT	36000
3	FORD	36000
4	JONES	35700
5	BLAKE	34200

9. SAS Data Step

Proc sort 프로시져

SAS Programming

%%SAS sas

DATA STATSAS_2;
 SET EMP;
     Annual = 12*(sal+coalesce(comm,0));
     KEEP ENAME ANNUAL;
RUN;
PROC SORT DATA = STATSAS_2 OUT=STATSAS_3;
     BY DESCENDING Annual;
RUN;
PROC PRINT data=STATSAS_3(obs=5);RUN;

Results

OBS	ename	Annual
1	KING	60000
2	SCOTT	36000
3	FORD	36000
4	JONES	35700
5	BLAKE	34200

10. Python Dfply Package

Sort_values() 함수

Python Programming

emp["Annual"] = 12 * emp["sal"] + emp["comm"].replace(np.nan,0)

emp[["ename","Annual"]].sort_values(by=["Annual"], ascending=[False])

Arrange() 함수

Python Programming

emp >> mutate( Annual = 12* X.sal + X.comm.replace(np.nan,0) ) >> arrange(X.Annual, ascending=[False]) >> select(X.empno, X.Annual)

Results

	empno	Annual
8	7839	60000.0
7	7788	36000.0
12	7902	36000.0
3	7566	35700.0
5	7698	34200.0
6	7782	29400.0
1	7499	19500.0
9	7844	18000.0
4	7654	16400.0
13	7934	15600.0
2	7521	15500.0
10	7876	13200.0
11	7900	11400.0
0	7369	9600.0

Mutate() 함수와 Arrange 함수

Python Programming

emp >>                                                               \
  mutate( Annual = 12* X.sal + emp.fillna({'comm': 0})['comm'] ) >>  \
  arrange(X.Annual, ascending=[False])                           >>  \
  select(X.empno, X.Annual)

Results

	empno	Annual
8	7839	60000.0
7	7788	36000.0
12	7902	36000.0
3	7566	35700.0
5	7698	34200.0
6	7782	29400.0
1	7499	19500.0
9	7844	18000.0
4	7654	16400.0
13	7934	15600.0
2	7521	15500.0
10	7876	13200.0
11	7900	11400.0
0	7369	9600.0

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

저작자표시

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

[기초 통계량 - 총건수(Count) 계산] 그룹별 건수 집계 - 42 (오라클 SQL, R, Python, SAS) (0)	2021.08.10
[기초 통계량 - 총합계(Total) 계산] 연산작업을 통한 신규변수 생성 - 41 (오라클 SQL, R, Python, SAS) (0)	2021.08.10
[데이터 정렬 - 다중 변수 기준 정렬] 여러 개의 정렬 키를 기준으로 데이터 정렬 - 39 (오라클 SQL, R, Python, SAS) (0)	2021.08.09
[데이터 정렬 - 문자변수] 문자형 데이터를 기준으로 오름차순 정렬 - 38 (오라클 SQL, R, Python, SAS) (0)	2021.08.09
[데이터 정렬 - 오름차순 정렬] 수치형 데이터 정렬 - 36 (오라클 SQL, R, Python, SAS) (0)	2021.08.09

[데이터 정렬 - 조건절 기준 정렬] 신규 변수를 기준으로 데이터 내림차순 정렬 - 40 (오라클 SQL, R, Python, SAS)

**40. Display the name of the employee along with their annual salary (Sal * 12). The name of the employee earning highest annual salary should appear first.**

1. 오라클(Oracle)

2. 파이썬(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)' 카테고리의 다른 글

댓글

티스토리툴바

[데이터 정렬 - 조건절 기준 정렬] 신규 변수를 기준으로 데이터 내림차순 정렬 - 40 (오라클 SQL, R, Python, SAS)

40. Display the name of the employee along with their annual salary (Sal * 12). The name of the employee earning highest annual salary should appear first.

1. 오라클(Oracle)

2. 파이썬(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)' 카테고리의 다른 글

관련글

댓글

티스토리툴바

**40. Display the name of the employee along with their annual salary (Sal * 12). The name of the employee earning highest annual salary should appear first.**