R / 문자함수(gsub), 날짜함수, 변환함수, 일반함수, 그룹함수

8 R 에서의 문자함수

오라클	R
upper	toupper
lower	tolower
substr	substr
replace	gsub

ㅇ gsub

오라클에서 replace가 R에서 gsub와 같은 함수

-- SQL
select ename, replace(sal, 0, '*')
 from emp;

# 판다스
import pandas as pd

emp = pd.read_csv("c:\\data\\emp2.csv")
emp['sal_star'] = emp.sal.apply(lambda x : str(x).replace('0','*'))
emp

# R
library(data.table)
data.table( 이름 = emp$ename, 월급 = gsub(0,'*', emp$sal) )

gsub(변경전 데이터, 변경후 데이터, 컬럼)

 

문제28. 아래의 SQL을 이름과 월급을 출력하는데 월급을 출력할 때에 숫자 0,1,2를 *로 출력하시오.

-- SQL
select ename, regexp_replace(sal, '[0-2]', '*' )
 from emp;

# R
library(data.table)
data.table(이름 = emp$ename, 월급 = gsub('[0-2]','*',emp$sal) )

# 판다스
import pandas as pd
import re

emp = pd.read_csv("c:\\data\\emp2.csv")
emp['sal_star'] = emp.sal.apply(lambda x : re.sub('[012]','*',str(x)))
emp

파이썬 > 데이터 정제모듈인 re를 임포트해서 re의 함수를 이용해 replace해줌.

re.sub(변경전 데이터, 변경후 데이터, 컬럼)

 

오라클	R	python
upper	toupper	문자열.upper()
lower	tolower	문자열.lower()
substr	substr	emp['ename'].apply(lambda x:x[0] == 'S' )
replace	gsub	문자열.replace() 또는 re.sub
instr	instr

 

ㅇinstr

문자열에서 특정 철자가 몇번째 있는지(인덱스) 출력하는 함수

-- SQL
select instr('smith','m')
 from dual;   # 숫자 2 출력

# R
install.packages("survPen")  # 패키지 설치
library(survPen)             # 호출
instr('smith','m')			# 2 출력

# python
'smith'.find('m')   # 숫자1 출력 < 파이썬은 0부터 시작함

 

문제29. 우리반 테이블에서 이름, 이메일, 이메일에서 @이 몇번째에 있는지 출력하시오.

-- SQL
select ename, email, instr(email, '@')
 from emp14;

# 판다스
import pandas as pd

emp14 = pd.read_csv("c:\\data\\emp14.csv")
emp14['arobase'] = emp14.email.str.find('@')
emp14[['ename','email','arobase']]

# R
gregexpr('@', emp14$email)

gregexpr함수로 emp14의 이메일에서 @의 위치가 출력할 수 있음. 그런데 결과가 리스트 자료로 출력됨 
> unlist함수를 사용해서 리스트로 출력하지 않겠다고 해주면 됨

( 리스트로 변경하고 싶다면 > list() , 리스트가 아닌 일반 벡터로 변경하고 싶다면 > unlist() ) 

unlist(gregexpr('@', emp14$email))

emp14$email_loc <- unlist(gregexpr('@', emp14$email))

emp14[ , c("ename", "email","email_loc")]

gregexpr( 검색할 문자 , 검색할 문자 또는 컬럼) 

gregexpr("apple", "It is a apple ans apple is tasty")   # apple의 시작 인덱스가 출력됨

 

 

9 R 에서의 숫자함수

오라클	R	파이썬
round	round	round
trunc	trunc	trunc
mod	%%	%
power	2^3	2**3

예제. 이름과 월급을 출력하는데 월급을 출력할때에 10의 자리에서 반올림되게 하시오.

ex) 1260 ----> 1300

# R
data.table(이름 = emp$ename, 월급 = emp$sal, 월급반올림 = round(emp$sal,-2))

 8:  JAMES  950       1000
 9:   WARD 1250       1200

= 950은 10의 자리에서 반올림해서 1000이 출력되었고 1250은 그냥 1300이 되지 않고 1250이 나옴. 

= R과 파이썬은 짝수를 좋아함

= round(122.5) > 122, round(123.5) > 124

예제. 이름과 월급을 출력하는데 월급을 출력할때에 십의 자리에서 끝까지 다 버리고 출력되게 하시오.

=> R의 trunc함수는 소수점 이하면 버릴 수 있는데 소수점 이전은 버릴 수 없어서 수행되지 않음. 

 

= trunc(122.57,-1) > 예상되기는 120인데 그렇지 않고 122가 출력됨

= trunc(122.57,0) > 122

 

예제. 10을 2로 나눈 나머지값을 출력하시오.

-- SQL
select mod(10,2)
 from dual;

# R
10%%2

# 파이썬
10%2

 

예제. 2의 3승을 출력하시오

-- SQL
select power(2,3)
 from dual;

# R
2^3

# 파이썬
2**3

 

 

10 R 에서의 날짜함수

오라클	R
sysdate	Sys.Date()
add_months	difftime
months_between	lubridate 패키지 사용
last_day	lubridate 패키지 사용
next_day	lubridate 패키지 사용

예제. R에서의 오늘날짜를 출력하시오

Sys.Date()

문제30. 이름, 입사한 날짜부터 오늘까지 총 몇일 근무했는지 출력하시오.

-- SQL
select ename, sysdate-hiredate
 from emp;

# R
emp$hire_date = as.Date(emp$hiredate)  #문자를 날짜로 형변환
data.table(이름 = emp$ename, 근무일 = Sys.Date()-emp$hire_date)

R에서 hiredate는 chr로 문자형식으로 되어있음. Sys.Date()는 날짜인데 여기서 문자인 hiredate를 뺄 수 없음. => 날짜로 변환해줘야함.

또는 difftime 함수를 사용하면 형변환을 하지 않아도 됨.

difftime(날짜2, 날짜1) : 날짜1 ~ 날짜2 까지의 일수를 출력

data.table(이름 = emp$ename, 근무일 = difftime(Sys.Date(), emp$hiredate))

# 변수로 넣어줘서 출력해도 됨
emp$hiredate2 = difftime(Sys.Date(), emp$hiredate)
emp[ , c('ename','hiredate2')]

 

python에서 오늘날짜 불러오는 방법

# python
import datetime

datetime.datetime.now() # 오늘날짜

emp테이블의 hiredate컬럼은 object로 문자로 되어 있음 > 형변환 필요

 5   hiredate    14 non-null     object

emp['hiredate3'] = pd.to_datetime(emp['hiredate'])  #hiredate를 날짜로 변환해서 hiredate3 파생변수를 생성함
emp['hire_day'] = datetime.datetime.now() - emp['hiredate3']
emp[['ename','hire_day']]

문제32. 우리반 테이블에서 이름, 태어난 날짜부터 오늘까지 총 몇일 살았는지 출력하시오.

# R
data.table(이름 = emp14$ename, 일수 = trunc(difftime(Sys.Date(), emp14$birth)))

# python 
emp14['birth_day'] = pd.to_datetime(emp14['birth'])
emp14['birth_day2'] = datetime.datetime.now() - emp14['birth_day']
emp14[['ename', 'birth_day2']]

문제33. 오늘날짜의 달의 마지막 날짜를 출력하시오.

-- SQL 
select last_day(sysdate)   # 이번달의 마지막 날짜
 from dual;

# R
install.packages("lubridate")
library(lubridate)
Sys.Date()  # 오늘날짜 출력  "2022-01-18"
floor_date(Sys.Date(), "months")   # "2022-01-01" 이번달의 첫번째 날짜
ceiling_date(Sys.Date(), "months")  # "2022-02-01"  다음달의 1일 날짜
ceiling_date(Sys.Date(), "months") - days(1) #  "2022-01-31" 이번달의 마지막 날짜

 

문제34. 아래의 SQL을 R로 구현하시오.

-- SQL
select ename, last_day(hiredate)
 from emp;

# R
emp$hire_date = as.Date(emp$hiredate)  #문자를 날짜로 형변환
data.table(이름 = emp$ename, 마지막날짜 = ceiling_date(emp$hire_date, "months")-days(1))

 

 

11 R 에서의 변환함수

오라클	R	파이썬
to_char	as.character	str()
to_number	as.integer	int()
to_date	as.Date	(판다스) pd.to_datetime()

예제. 이름과 입사한 요일을 출력하시오.

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

# R
data.table(emp$ename, format(as.Date(emp$hiredate), '%A'))

format(특정날짜, 옵션)

옵션의 종류 : %A(요일), %Y(연도 4자리), %y(연도 2자리), %m(월), %d(일)

 

# 파이썬
import pandas as pd
emp = pd.read_csv("c:\\data\\emp2.csv")
emp['hiredate2'] = pd.to_datetime(emp['hiredate'])  #hiredate를 날짜로 변환해서 hiredate2 파생변수를 생성함
emp['day'] = emp['hiredate2'].dt.day_name()    # 날짜형 내장함수 dt
emp[['ename', 'day']]

 

문제35. (점심시간 문제) 11월에 입사한 사원들의 이름과 입사일을 출력하시오.

# R
emp$month = format(as.Date(emp$hiredate), '%m')
emp[emp$month == 11, c('ename', 'hiredate')]

-- SQL
select ename, hiredate
 from emp
 where to_char(hiredate, 'mm') = 11;

 

문제36. 오늘부터 100 달 뒤에 돌아오는 날짜를 출력하시오.

-- SQL
select add_months(sysdate, 100)
 from dual;

# R
library(lubridate)

Sys.Date() + months(100)

문제37. 위의 결과를 판다스로 수행하시오.

# python 
import datetime

now = datetime.datetime.now()
print( now + pd.DateOffset(months = 100) )

문제38. 아래의 SQL을 R로 구현하시오.

-- SQL
select to_char(add_months(sysdate, 100), 'day')
 from dual;

> 오늘부터 100달 뒤에 돌아오는 날짜의 요일을 출력

 

힌트 : format(특정날짜, 옵션)

# R
format(as.Date(Sys.Date() + months(100)), '%A')

# python 
import datetime
import pandas as pd

now = datetime.datetime.now()
a = now + pd.DateOffset(months = 100)   # type = pandas._libs.tslibs.timestamps.Timestamp
a2 = pd.Series(a)
a2.dt.day_name()  # 판다스 내장함수 dt.day_name()

판다스의 문법이 기억나지 않을 때 다음의 help를 이용하면 됨.

import pandas as pd

help( pd.Series.dt.day_name )

문제40. 내가 무슨 요일에 태어났는지 출력하시오.

-- SQL
select to_char( to_date('1999/11/17','RRRR/MM/DD'), 'day')
 from dual;

# R
format(as.Date('1995/01/30'), '%A')

문제41. 위의 결과를 판다스로 수행하시오.

import pandas as pd

a = pd.to_datetime('1995/01/30')
a.day_name()  # 판다스 내장함수

# a2 = pd.Series(birth)  
# a2.dt.day_name() # 판다스 시리즈 내장함수

판다스 날짜함수 참고 블로그)

https://truman.tistory.com/97

 

문제42. 1981년도에 입사한 사원들의 이름과 입사일을 출력하시오.

-- SQL
select ename, hiredate
 from emp
 where to_char(hiredate, 'RRRR') = '1981';

# R
emp[format(as.Date(emp$hiredate), '%Y') == 1981, c("ename","hiredate")]

문제43. 위의 결과를 판다스로 수행하세요.

# python
import pandas as pd

emp = pd.read_csv("c:\\data\\emp2.csv")
a = pd.to_datetime(emp.hiredate)
emp[['ename','hiredate']][pd.to_datetime(emp['hiredate']).dt.year == 1981 ]

 

 

12 R 에서의 일반함수

오라클	R
nvl	ifelse
decode	ifelse
case	ifelse

문제44. 이름, 커미션을 출력하는데 커미션이 null이면 0으로 출력하시오.

-- SQL
select ename, nvl(comm, 0)
 from emp;

# R
emp$comm_zero <- ifelse(is.na(emp$comm), 0, emp$comm)
emp[ , c("ename","comm_zero") ]

ifelse(조건, 조건에 대한 출력값, 기본값)

is.na(emp$comm) 은 커미션이 null이면 True아니면 False

 

# 판다스
emp['comm_zero'] = emp['comm'].fillna(0)
emp

# 넘파이 사용
import numpy as np
emp['zero'] = emp['comm'].replace(np.nan, 0)
emp

fillna의 옵션중에 inplace = True는 결과가 반영된 상태로 실제 데이터가 변경되는 것이고 inplace = False는 결과가 반영된 상태로 출력되는 것.

 

문제46. 이름, 월급, 부서번호, 보너스를 출력하는데 부서번호가 10번이면 보너스를 5000으로 하고 아니면 0으로 하시오.

-- SQL
select ename, sal, deptno, decode(deptno, 10, 5000, 0) as 보너스
 from emp;

# R
emp$bonus <- ifelse(emp$deptno == 10, 5000, 0)
emp[ , c("ename", "sal", "deptno", "bonus")]

# 판다스
emp['bonus'] = emp['deptno'].apply(lambda x: 5000 if x == 10 else 0)
emp[['ename','bonus']]

# 함수를 따로 생성해서 넣어도 됨
def aa(num) :
	if num == 10:
    	return 5000
    else:
    	return 0
        
emp['bonus'] = emp.deptno.apply(aa)
emp[['ename','bonus']]

문제47. 이름, 부서번호, 보너스를 출력하는데 부서번호가 10번이면 보너스를 5000을 출력하고 20번이면 보너스를 4000, 나머지 부서는 0을 출력하시오.

-- SQL
select ename, deptno, decode( deptno, 10, 5000, 
                                     20, 4000, 0 ) as 보너스
 from emp;

# R
emp$bonus <- ifelse(emp$deptno == 10, 5000, ifelse(emp$deptno == 20, 4000, 0))
emp[ , c("ename", "sal", "deptno", "bonus")]

문제48. 위의 결과를 판다스로 구현하시오.

# 판다스
emp['bonus'] = emp['deptno'].apply(lambda x: 5000 if x == 10 else ( 4000 if x == 20 else 0 ) )
emp[['ename','deptno','bonus']]

# 함수를 따로 만들어서 넣어도 가능함
def aa(num):
    if num == 10:
        return 5000
    elif num == 20:
        return 4000
    else:
        return 0

emp['bonus2'] = emp['deptno'].apply(aa)
emp[['ename','deptno','bonus2']]

 

문제49. 이름, 월급, 등급을 출력하는데 월급이 3000이상이면 A등급, 2000이상이고 3000보다 작으면 B로 출력하고 나머지는 C로 출력하세요

-- SQL
select enae, sal, case when sal >= 3000 then 'A'
                       when sal >= 2000 then 'B'
                       else 'C' end 등급
 from emp;

# R
emp$grade <- ifelse(emp$sal >= 3000, 'A', ifelse(emp$sal >= 2000, 'B', 'C') )
emp[ , c("ename", "sal", "grade")]

문제50. 위의 결과를 판다스로 수행하시오.

# 판다스
emp['grade'] = emp['sal'].apply(lambda x: 'A' if x >= 3000 else ('B' if x >= 2000 else 'C') )
emp[['ename', 'sal', 'grade']]

 

 

13 R에서의 그룹함수

SQL	R	파이썬
max	max	emp['sal'].max()
min	min	emp['sal'].min()
sum	sum	emp['sal'].sum()
avg	mean	emp['sal'].mean()
count	length(세로)	emp['sal'].count()
	table(가로)

-- SQL
select max(sal)
 from emp;

# R
max(emp$sal)

# 판다스
emp['sal'].max()

문제52. 직업이 SALESMAN인 사원들의 최대월급을 출력하시오.

-- SQL
select max(sal)
 from emp
 where job == 'SALESMAN';
 
 -- 이름이랑 같이 출력하고 싶을 때
 select ename, sal
  from emp
  where sal = (select max(sal)
                 from emp
                 where job = 'SALESMAN') and job = 'SALESMAN';

# R
a <- emp[emp$job == 'SALESMAN', c("sal")]
max(a)

# 이름이랑 같이 출력하고 싶을 때
a <- emp[emp$job == 'SALESMAN', c("sal")]
emp[emp$sal == max(a) & emp$job == 'SALESMAN', c("ename","sal")]

# 판다스
emp[['sal']][emp['job'] == 'SALESMAN'].max()


#이름이랑 같이 출력하고 싶을 때
a = emp[['sal']][emp['job'] == 'SALESMAN' ].max()
emp[['ename', 'sal']][(emp['sal'] == a.values[0]) & (emp['job'] == 'SALESMAN')]

문제53. (오늘의 마지막 문제) 부서번호가 20번인 사원들중에서의 최소월급을 출력하세요.

-- SQL
select min(sal)
 from emp
 where deptno == 20;

# R
a <- emp[emp$deptno == 20, c("sal")]
min(a)

# 판다스
emp[['sal']][emp['deptno'] == 20 ].min()