R / 그룹함수, aggregate, tapply, table, format

문제54. 부서번호, 부서번호별 최대월급을 출력해주세요.

-- SQL
select deptno, max(sal)
 from emp
 group by deptno;

# R
emp <- read.csv("c:\\data\\emp3.csv")

x <- aggregate(sal~job, emp, max)
names(x) <- c("job", "sumsal")
x

aggregate(값을 뽑을 컬럼명 ~ 그룹핑할 컬럼명, 테이블명, 그룹함수)

 

# python
x = emp.groupby('job')['sal'].max().reset_index()
x.columns = ['job','maxsal']
x

df.groupby('그룹핑할 컬럼명')['값 컬럼명'].그룹함수.reset_index()   

# reset_index() : 데이터 프레임으로 변환하는 함수

 

문제55. 부서번호, 부서번호별 토탈월급을 출력하시오.

-- SQL
select deptno, sum(sal)
 from emp
 group by deptno;

# R
emp <- read.csv("c:\\data\\emp3.csv")

x <- aggregate(sal~deptno, emp, sum)
names(x) <- c("deptno", "sumsal")  # names()로 컬럼명변경
x

# python
result = emp.groupby('deptno')['sal'].sum().reset_index()
result.columns = ['deptno', 'sumsal']
result

문제56. 위의 결과를 다시 출력하는데 토탈월급이 높은것부터 출력하시오.

-- SQL
select deptno, sum(sal)
 from emp
 group by deptno
 order by 2 desc;

# R
emp <- read.csv("c:\\data\\emp3.csv")

x <- aggregate(sal~deptno, emp, sum)
names(x) <- c("deptno", "sumsal")

library(doBy)  # doBy 패키지 라이브러리
orderBy(~ -sumsal, x)   # desc로 정렬

# python
result = emp.groupby('deptno')['sal'].sum().reset_index()
result.columns = ['deptno', 'sumsal']
result.sort_values(by= 'sumsal', ascending = False)

문제57. 직업, 직업별 인원수를 출력하시오.

# R
x <- aggregate(ename~job, emp, length)
names(x) <- c("job", "count")
x

# python
result = emp.groupby('job')['ename'].count().reset_index()
result.columns = ['job', 'count']
result

-- SQL
select job, count(job)
 from emp
 group by job;

문제58. 위의 결과를 다시 출력하는데 인원수가 높은 것부터 출력하시오.

-- SQL
select job, count(job)
 from emp
 group by job
 order by 2 desc;

# R
emp <- read.csv("c:\\data\\emp3.csv")

x <- aggregate(ename~job, emp, length)
names(x) <- c("job", "count")

library(doBy)
orderBy(~ -count, x)

# python
result = emp.groupby('job')['ename'].count().reset_index()
result.columns = ['job', 'count']
result.sort_values('count', ascending = False)

문제59. 직업, 직업별 토탈월급을 출력하는데, 직업별 토탈월급이 5000 이상인 것만 출력하시오.

-- SQL
select job, sum(sal)
 from emp
 group by job
 having sum(sal) >= 5000;

# R
emp <- read.csv("c:\\data\\emp3.csv")

x <- aggregate(sal~job, emp, sum)
names(x) <- c("job", "sumsal")
x[x$sumsal >= 5000, c("job", "sumsal")]

# python
a = emp.groupby('job')['sal'].sum().reset_index()
a.columns = ['job', 'sumsal']
a[:][a.sumsal >= 5000]

문제60. 직업과 직업별 토탈월급을 출력하는데 직업별 토탈월급이 5000 이상인 것만 출력하고 직업별 토탈월급을 높은 것부터 출력하시오.

-- SQL
select job, sum(sal)
 from emp
 group by job
 having sum(sal) >= 5000
 order by 2 desc;

# R
emp <- read.csv("c:\\data\\emp3.csv")

x <- aggregate(sal~job, emp, sum)
names(x) <- c("job", "sumsal")
x_sum = x[x$sumsal >= 5000, c("job", "sumsal")]

library(doBy)
orderBy(~ -sumsal, x_sum)

# python 
a = emp.groupby('job')['sal'].sum().reset_index()
a.columns = ['job', 'sumsal']
a[:][a.sumsal >= 5000].sort_values('sumsal', ascending = False)

문제61. 직업과 직업별 토탈월급을 출력하는데 직업이 SALESMAN은 제외하고 출력하고 직업별 토탈월급이 5000 이상인 것만, 직업별 토탈월급이 높은 것부터 출력하시오.

-- SQL
select job, sum(sal)
 from emp
 where job != 'SALESMAN'
 group by job
 having sum(sal) >= 5000
 order by 2 desc;

# R
emp <- read.csv("c:\\data\\emp3.csv")

x <- aggregate(sal~job, emp, sum)
names(x) <- c("job", "sumsal")
x2 = x[(x$sumsal >= 5000) & (x$job != 'SALESMAN'), c("job", "sumsal")]

library(doBy)
orderBy(~ -sumsal, x2)

# python
a = emp.groupby('job')['sal'].sum().reset_index()
a.columns = ['job', 'sumsal']
a[:][(a.sumsal >= 5000) & (a.job != 'SALESMAN')].sort_values('sumsal', ascending = False)

문제62. 아래의 SQL을 R과 파이썬으로 구현하시오.

-- SQL
select deptno, job, sum(sal)
 from emp
 group by deptno, job
 order by deptno, job;

# R
emp <- read.csv("c:\\data\\emp3.csv")

x <- aggregate(sal~ deptno+job, emp, sum)
names(x) <- c("deptno","job", "sumsal")

library(doBy)
orderBy(~ deptno, x)

# python
a = emp.groupby(['deptno', 'job'])['sal'].sum().reset_index()
a

문제63. 사원 테이블에서 입사한 년도를 4자리로 출력하시오.

-- SQL
select to_char(hiredate, 'RRRR')
 from emp;

# R
library(data.table)
data.table( 입사연도 = format(as.Date(emp$hiredate),'%Y') )

# python
emp['hiredate'] = pd.to_datetime(emp['hiredate'])  # object을 날짜형으로 변경
emp.info()  # 컬럼이 날짜형으로 변경되었는지 확인

5   hiredate    14 non-null     datetime64[ns]     <- 날짜형으로 바뀜

emp['hiredate'].dt.year  # 시리즈의 dt함수를 써서 연도 4자리 출력

문제64. 아래의 SQL을 R과 판다스로 구현하시오

-- SQL
select to_char(hiredate, 'RRRR'), deptno, sum(sal)
 from emp
 group by to_char(hiredate, 'RRRR'), deptno;

# R
library(data.table)
emp$hire_year = format(as.Date(emp$hiredate),'%Y')
x <- aggregate(sal ~ hire_year+deptno, emp, sum)
library(doBy)
orderBy(~ hire_year, x)

# python
emp['hiredate'] = pd.to_datetime(emp['hiredate'])
emp['hire_year'] = emp['hiredate'].dt.year
a = emp.groupby(['hire_year','deptno'])['sal'].sum().reset_index()
a.sort_values('hire_year', ascending = True)

문제65. 아래의 SQL을 R과 파이썬으로 수행하세요. (pivot)

-- SQL
select *
 from ( select deptno, sal from emp )
 pivot ( sum(sal) for deptno in (10,20,30) );

# R
tapply(emp$sal, emp$deptno, sum)

tapply( 계산할 값 컬럼, 그룹핑할 컬럼, 적용 함수)

 

# python
x = emp.pivot_table(columns = 'deptno', aggfunc = 'sum')
x[5:] # 슬라이싱 할 수도 있고
# x.iloc[[5], ]  iloc[[선택할 행번호],열번호] 가져와도 됨

문제66. (점심시간 문제) 직업, 직업별 인원수를 아래와 같이 가로로 출력하시오.

-- SQL
select *
 from ( select job, empno from emp)
 pivot( count(empno) for job in ('ANALYST','CLERK','MANAGER','SALESMAN','PRESIDENT') );

# R
tapply(emp$empno,emp$job, length)
	# 또는
table(emp$job)

table( count하고 싶은 컬럼 )

 

# python
x = emp.pivot_table(columns = 'job', aggfunc = 'count')   # 가로로 애초에 돌려서 검색함
x2 = x.iloc[[0], ]
x2.index = ['cnt']
x2

	# 또는
    
x = emp.groupby('job')['empno'].count().reset_index()  # 데이터 검색을 먼저 한 다음에
x.columns = ['job','cnt']   
x.pivot_table(columns = 'job')  # 세로로 출력된 것을 가로로 돌림

문제67. 판다스에서 부서번호, 부서번호별 토탈월급을 가로로 출력하시오.

x = emp.groupby('deptno')['sal'].sum().reset_index()
x.columns = ['deptno', 'sumsal']
x.pivot_table(columns = 'deptno')

 문제68. 입사한 연도, 입사한 연도별 토탈월급을 출력하시오.(세로로)

-- SQL
select to_char(hiredate, 'RRRR') as year, sum(sal)
 from emp
 group by to_char(hiredate,'RRRR');

# R
emp$hire_year = format(as.Date(emp$hiredate),'%Y')
aggregate( sal ~ hire_year, emp, sum)    # 세로

# python 
x = emp.groupby('hire_year')['sal'].sum().reset_index()
x.columns = ['hire_year', 'sumsal']
x

문제69. 입사년도, 입사년도별 토탈월급을 출력하는데 가로로 출력하시오.

-- SQL
select *
 from ( 
        select to_char(hiredate, 'RRRR') as year, sal
         from emp )
 pivot ( sum(sal) for year in ( 1980,1981,1982,1983 ) );
 
 	-- 또는 sum+decode 이용해서 할 수도 있음.

select sum(decode(to_char(hiredate,'RRRR'), '1980',sal,0)) as "1980",
       sum(decode(to_char(hiredate,'RRRR'), '1981',sal,0)) as "1981",
       sum(decode(to_char(hiredate,'RRRR'), '1982',sal,0)) as "1982",
       sum(decode(to_char(hiredate,'RRRR'), '1983',sal,0)) as "1983"
 from emp;

# R
emp$hire_year = format(as.Date(emp$hiredate),'%Y')
tapply(emp$sal, emp$hire_year, sum)

# python 
emp['hire_year'] = pd.to_datetime(emp['hiredate']).dt.year
x = emp.groupby('hire_year')['sal'].sum().reset_index()
x.columns = ['hire_year', 'sumsal']
x.pivot_table(columns = 'hire_year')