DAY15. R 정형데이터

정형VS비정형

비정형 ex) text > 전처리 (불용어 정제 등) > R통계 패키지 > 추론통계, 패턴, 규칙

반정형 ex) HTML > 전처리> R통계 패키지 > 추론통계, 패턴, 규칙

정형 ex) DB> R통계 패키지 > 추론통계, 패턴, 규칙

 

 

 

 

 

정형데이터 처리

1. JDK & JRE 설치 : RJDBC 패키지를 사용하기 위해서는 java 설치
C:\ITWILL\2_Rwork\tools\jdk-8u151-windows-x64.exe 설치파일 이용 

2. 패키지 설치

install.packages("rJava") # R에서 JAVA 사용 지원 
install.packages("DBI") #DB Interface : SELECT문
install.packages("RJDBC") #R+DB연동 (rJava의존성)

3. 패키지 로딩 : 순서를 지켜 in memory시켜야 한다

library(DBI)
Sys.setenv(JAVA_HOME='C:\\Program Files\\Java\\jdk1.8.0_151') # JAVA 설치 경로
library(rJava) 
library(RJDBC) # rJava에 의존적

4. Oracle 연동 (Oracle 11g)
단계1: driver 객체 (프로그램ex.R,Pyton..과 DB를 연결)

drv<-JDBC(driverClass="oracle.jdbc.driver.OracleDriver", 
          classPath="C:\\oraclexe\\app\\oracle\\product\\11.2.0\\server\\jdbc\\lib\\ojdbc6.jar")

단계2 : db연동(driver, url, user, password)
1단계에서 연동한 drv를 연결

conn <- dbConnect(drv=drv, 
                  url="jdbc:oracle:thin:@//127.0.0.1:1521/xe", #127~ 포트번호/DB이름
                  user="scott", 
                  password="tiger")

1.전체 테이블 조회

query = 'select*from tab'
dbGetQuery(conn, query) #DBI소속 패키지

2. table 생성 : DB(XE)에 저장됨

query = "create table db_test(sid int, pwd char(4), name varchar(25), age int)"
mode(query) #"character":문자열

dbSendUpdate(conn, query) #RJDBC소속 패키지. table생성 함수
dbGetQuery(conn, "select*from tab") #table조회
dbGetQuery(conn, "select*from emp") #table의 구조 확인

3. DB내용 수정

: dbSendUpdate함수(conn, query(=create/insert/update/delete))

1) INSERT문 (레코드 추가)

query = "INSERT INTO DB_TEST values(1001, '1234', '홍길동', 35)" #''와 ""모두 사용 가능하지만 ''의 경우 '문자열'과 혼용될 수 있음.
dbSendUpdate(conn, query) #db함수를 써야 반영됨
dbGetQuery(conn, "select*from DB_TEST") #추가한 레코드 확인

2) UPDATE문

query = "UPDATE DB_TEST set name='김길동' WHERE sid = 1001"
dbSendUpdate(conn, query)
dbGetQuery(conn, "select*from DB_TEST")

3) DELETE문

query = "DELETE FROM DB_TEST where sid=1001"
dbSendUpdate(conn, query)
dbGetQuery(conn, "select*from DB_TEST")

4) table삭제

dbSendUpdate(conn, "DROP TABLE DB_TEST PURGE")
dbGetQuery(conn, "select*from tab")

5. table불러오기

emp = dbGetQuery(conn, "select*from emp") #R의 객체로 저장
str(emp) #data.frame으로 생성. num숫자형 chr문자형

 

 

1) 통계함수 : 비율/등간 척도

mean(emp$SAL) #2073.214
mean(emp$COMM) #NA
mean(emp$COMM, na.rm=T) #결측치를 제외한 값만 구하기. 550
summary(emp)

2) 조건 조회

query = "select * from emp where sal>=2500 and job='MANAGER'" #논리연산자 and 사용. '문자열'은 대소문자 구분
manager_2500 = dbGetQuery(conn, query)
manager_2500

professor : 정교수
name_pay = dbGetQuery(conn, "select name, pay from professor
                      where position='정교수'")
str(name_pay)
name_pay

barplot(name_pay$PAY, col='green', names.arg = name_pay$NAME) #names.arg:x축 눈금마다 교수명 붙이기

3) subquery 이용해서 특정 부서의 사원정보 출력 (SQL에서와 동일하게 사용)
* sub : dept, main : emp

dbGetQuery(conn, 'select * from dept')

query = "select ename, sal, job from emp
where deptno= 
(select deptno from dept where dname='SALES')"
sales = dbGetQuery(conn, query)
sales

4) join : inner join

query = "select e.ename, e.job, d.*
from emp e, dept d
where e.deptno=d.deptno and e.ename like'%M%'"

 

* 양쪽 테이블에서 부서번호가 같은 (조인조건)
* and이하 : 이름에 M이 포함되어 있는 사원 (일반조건건)

join_df = dbGetQuery(conn, query)
join_df #R의 객체

5) GROUP BY : 각 부서별 급여 평균, 합계 구하기

query = "select deptno, avg(sal) avg_sal, sum(sal) tot_sal
from emp
group by deptno
order by deptno"

dept_group = dbGetQuery(conn, query)
dept_group

* select절에서 별칭 설정 가능


DB 연결 종료

dbDisconnect(conn) #TRUE 종료

 

 

 

 

 

 

연습문제

01. 다음과 같은 단계를 통해서 테이블을 호출하고, SQL문을 이용하여 레코드를 조회하시오.
[단계 1] 사원테이블(EMP)을 검색하여 결과를 EMP_DF 변수로 불러오기
1) 전체 레코드 조회

EMP_DF <- dbGetQuery(conn, "select * from EMP")  
EMP_DF

2) 또는 부서별 조회 

EMP_DF2 <- dbGetQuery(conn, "select deptno, avg(sal) avg_sal 
                             from EMP  
                             group by deptno
                             order by deptno")
                             
EMP_DF2
#    DEPTNO  AVG_SAL
#1     10 2916.667
#2     20 2175.000
#3     30 1566.667

단계 2] EMP_DF 변수를 대상으로 부서별 급여의 평균을 막대차트로 시각화
1) 전체 레코드 조회
각 부서별 SUBSET 

dept10 <- subset(EMP_DF, DEPTNO == 10)
dept20 <- subset(EMP_DF, DEPTNO == 20)
dept30 <- subset(EMP_DF, DEPTNO == 30)

각 부서별 급여 평균 

dept10_sal <- mean(dept10$SAL)
dept20_sal <- mean(dept20$SAL)
dept30_sal <- mean(dept30$SAL)

[단계 3] 막대차트를 대상으로 X축의 축눈금을 부서명으로 표시하기
1) 전체 레코드 조회

dept_sal_avg <- c(dept10_sal, dept20_sal, dept30_sal)

X축 눈금 : 부서 이름 표시 

bp <- barplot(dept_sal_avg, col = rainbow(3),
        names.arg = c('10', '20', '30'), ylim = c(0, 3500))

text(x=bp, y=dept_sal_avg+80, round(dept_sal_avg, 3))

2) 부서별 조회 경우

DEPT <- dbGetQuery(conn, "select * from DEPT")

table join : 부서명으로 표시 

library(plyr)
join_df <- join(EMP_DF2, DEPT, by='DEPTNO')
join_df

bp <- barplot(join_df$AVG_SAL, col = rainbow(3), 
              names.arg = join_df$DNAME, ylim = c(0, 3500))

text(x=bp, y=EMP_DF2$AVG_SAL+80, round(EMP_DF2$AVG_SAL, 3))

 

02. 'WARD' 사원의 상사(MGR)와 동일한 사원이름, 직책, 연봉을 출력하시오.
<조건1> 서브쿼리 이용

query <- "select ename, job, sal from EMP
          where MGR = (select MGR from EMP where ename = 'WARD')"
result <- dbGetQuery(conn, query)

<조건2> 차트 결과 참고 

result
pie(result$SAL, labels = result$ENAME, col = rainbow(5),
    main = "WARD 사원의 상사와 같은 사원들의 급여")