ETL on R

還記得這個新聞吧？ 就從這裡開始

從上面新聞所述，所以我們想要:

從上面新聞所述，所以我們想要:

從上面新聞所述，所以我們想要:

• dplyr 可用類似SQL方法操作data frome
• xts 處理時間格式好用的套件
• gdata 可以處理Excel 2007以上的文件
• quantmod 可以處理股市資料
• stringr 字串相關處理

• DSC2014Tutorial R社群為了這次Tutorial製作的套件，所有的教材都在這了

deps <- available.packages("http://taiwanrusergroup.github.io/R/src/contrib")[1,"Imports"]
pkgs <- strsplit(gsub("\\s", "", deps), ",")[[1]]
for(i in seq_along(pkgs)) {
  # You can change your favorite repository
  if (require(pkgs[i], character.only = TRUE)) next
  install.packages(pkgs[i], repo = "http://cran.csie.ntu.edu.tw")
}
install.packages('DSC2014Tutorial', 
    repo = 'http://taiwanrusergroup.github.io/R', type = 'source')
library(DSC2014Tutorial)

安裝之後, 輸入以下指令就可以打開投影片:

slides("ETL")

學會extraction, cleaning, transformation

• 用R整理結構化資料 STEP1 房貸餘額1 , STEP2 GDP
• 用R整理非結構化資料 STEP3新聞分析

學會 load

• 整併全部的資料 STEP4

邁向 其他有意思的主題

請連線到 https://survey.banking.gov.tw/statis/stmain.jsp?sys=100&funid=r100

房貸餘額,直接下載現成的csv檔案

• 直接到https://raw.githubusercontent.com/ntuaha/TWFS/master/db/cl_info_other.csv下載檔案
• 應該會看見cl_info_other.csv
• 讀入它!

或是

library(DSC2014Tutorial)
ETL_file("cl_info_other.csv")

至少要記得的 read.table

Cl_info = read.table(file=ETL_file("cl_info_other.csv"),sep=",",stringsAsFactors=F,header=T)

• 輸出形態為Data Frame
• file 就是指讀入的檔案路徑
• sep 指的是欄位分割用的符號,通常csv檔案格式是透過,做分割
• stringsAsFactors 預設是True, 會讓讀入的字串都用Factor形態儲存，那麼資料就會轉為整數儲存與額外的對照表
• header 預設是False，表示第一行是不是表格標頭，作為輸出的dataframe欄位名的colnames

View(Cl_info)
str(Cl_info)

• etl_dt data_dt 是文字但應該是時間
• bank_code 也是文字但應該是factor

將資料讀入

library(DSC2014Tutorial)
library(dplyr)
Cl_info = read.table(file=ETL_file('cl_info_other.csv'),header=T,sep=",",stringsAsFactors=F)
Cl_info_part = mutate(Cl_info,data_dt = as.POSIXct(data_dt),
                 bank_code = as.factor(bank_code),etl_dt = as.POSIXct(etl_dt))
View(Cl_info_part)
str(Cl_info_part)

• 到這裡已經完成第一次的資料Extraction與Transformation了！

dplyr select 函式，用來挑選欄位

Cl_demo1 = select(資料表,欄位1,欄位2,欄位3)

select data_dt,bank_nm,mortgage_bal from Cl_info;

dplyr select 函式，用來挑選欄位

Cl_demo1 = select(Cl_info,data_dt,bank_nm,mortgage_bal)

select data_dt,bank_nm,mortgage_bal from Cl_info;

dplyr filter 函式，用來保留要留下的資料列

Cl_demo2 = filter(Cl_info,mortgage_bal>1000000)

select * from Cl_info where mortgage>1000000;

dplyr mutate 用來增加非彙總計算欄位

Cl_demo3 = mutate(資料表,新欄位名 = 運算式)

select mortgage_bal/1000000 as mortage from Cl_info;

dplyr mutate 用來增加非彙總計算欄位

Cl_demo3 = mutate(Cl_info,mortage = mortgage_bal/1000000)

select mmortgage_bal/1000000 as mortage from Cl_info;

dplyr arrange 用來重新排序

Cl_demo4 = arrange(資料表,欄位1,desc(欄位2)))

select * from Cl_info order by mortage,data_dt desc ;

dplyr arrange 用來重新排序

Cl_demo4 = arrange(Cl_info,mortgage_bal,desc(data_dt))

select * from Cl_info order by mortage,data_dt desc ;

1. 請幫忙從Cl_info_part找出data_dt,bank_nm,mortgage_bal
2. 請幫忙從Cl_info_part挑選出mortgage＿bal大於1千萬的銀行資料
3. 請幫忙排序Cl_info_part出mortgage＿bal由小到大，但資料時間data_dt從大到小
4. 請執行下面程式碼，我們後續會利用Cl_info_part2

Cl_info_part2 = mutate(Cl_info_part,time= as.POSIXct(data_dt))

請連線到 http://ebas1.ebas.gov.tw/pxweb/Dialog/NI.asp

1. GDP從直接下載國民生產毛額之處分
2. 季(1980之後）
3. 選全部的日期
4. 選全部的計價方式
5. 項目選GDP
6. 總類選原始值
7. 按繼續後，選從螢幕顯示Excel檔
8. 開啟後，另存新檔成csv檔
9. 開回RStudio 開始處理資料

或是ETL_file("GDP.txt")

GDP_part = GDP[5:136,]

別忘了改上欄位名稱

colnames(GDP_part) = c("time","GDP","GDP_yoy","GDP_2006","GDP_2006_yoy",
                        "GDP_minus","GDP_minus_yoy")

rownames(GDP_part) = NULL
View(GDP_part)

另外一個簡單的例子

iris
iris_part <- iris[4:6,]
rownames(iris_part) <- c('a','c','d')
View(iris_part)

# 去除中間不合理的,在數字欄位上與補上百萬
GDP_part2= mutate(GDP_part,GDP = as.numeric(gsub(",", "",GDP))*1000000)

1. 我們利用了 gsub, 替換字元，將原先有問題的,去除
2. 而後利用了as.numeric 將原來的文字形態改成數字

GDP_part3 = mutate(GDP_part2,year=as.numeric(substr(time,0,4)),
                            season=as.numeric(substr(time,6,6)))
GDP_part4 = select(GDP_part3,year,season,GDP)

1. 我們利用了 substr, 取出特定位置的資料
2. 再次利用as.numeric, 將文字轉成數字

1. 去除頭尾不合理的資料列
2. 去除GDP的 , , 然後將它轉成數字
3. 將year,season抽離出來並轉成數字，同時最後資料只保留GDP,year,season

要處理的標的物有

1. 將房貸餘額匯總到每個月的資料
2. 將GDP匯總到每年的資料
3. 透過年份將房貸餘額與GDP的表結合起來

將介紹 group_by, summarize

1. 將房貸餘額每個月的值算出來
2. 將GDP每年的值算出來

GDP_part5 = filter(summarise(group_by(GDP_part4,year),GDP=sum(GDP)),is.na(year)==F)        

可以使用的函數如下所列

• mean 平均值
• sum 加總
• n 計算個數 例如: A B B C 輸出4
• n_distinct 計算不同物件的個數 例: A B B C 輸出3
• max, min 最大或最小值
• median 中位數

請計算每月全體銀行餘額平均值(eg1)

請計算每個月有多少家銀行有房貸餘額(eg2)

請計算每年有多少家銀行有房貸餘額(eg3)

請計算每月單一銀行擁有的最多房貸餘額(eg4)

請計算每月房貸餘額排名第ㄧ的銀行(eg5)

• first 該群體第一個，可配合order_by 使用; first(x,order_by=y)
• last 該群體最後一個，可配合order_by 使用; last(x,order_by=y)
• nth 該群體的第n個，可配合order_by 使用; nth(x,10))

請計算每月房貸餘額排名第2的銀行 (eg6)

eg1 =  summarise(group_by(Cl_info_part2,time) , 
         mortage_mean_bal = mean(mortgage_bal, na.rm = TRUE))

eg2_1 = filter(Cl_info_part2,mortgage_bal >0)
eg2_2 = summarise(group_by(eg2_1,time),count = n())

eg3_1 = filter(Cl_info_part2,mortgage_bal >0)
eg3_2 = mutate(eg3_1,year = format(time,"%Y"))
eg3_3 = summarise(group_by(eg3_2,year),count = n_distinct(bank_nm))

eg4 = summarise(group_by(Cl_info_part2,time),
       val = max(mortgage_bal))



eg5_1 = arrange(Cl_info_part2,time,desc(mortgage_bal))
eg5_2 =summarise(group_by(eg5_1,time),val = first(bank_nm))


eg6_1 = arrange(Cl_info_part2,time,desc(mortgage_bal))
eg6_2 = summarise(group_by(eg6_1,time),
          val = nth(bank_nm,2))

1. cbind 用來做 1-1 水平結合
2. rbind 用來做 垂直結合
3. left_join 用來做多對多 水平結合
4. inner_join 用來做多對多 水平結合

看得懂在幹嘛嗎?

參數by 指出以何欄位作為對照鍵值

透過先前創建的x與y，以及交集的鍵值c1

ljxy = left_join(x,y,by="c1") #以X為主
ljyx = left_join(y,x,by="c1") #以Y為主

請問，這兩張表該怎麼結合比較好?

1. 將GDP與房貸餘額，透過1月的資訊整理起來

GDP_part6 = select(mutate(GDP_part5 ,                    
                   time = as.POSIXct(paste(year,'1','1',sep='-'))),time,GDP)
t1 = left_join(GDP_part6,Cl_info_part4,by="time")
t2 = filter(t1,is.na(mortage_total_bal)==FALSE)

看一下資料 View(t2)

see1 = filter(mutate(t2,ratio =mortage_total_bal/GDP),is.na(ratio)==FALSE)

library(ggplot2)
ggplot(see1, aes(time, ratio))+geom_smooth(method="loess") +
  scale_size_area() +geom_point(aes(size = 20), alpha = 1/2) 

利用解釋將圖想要表達的意思更清楚地傳達給觀眾

將所有的解釋與圖表包裝成一個故事，展現出來

• 建立R Script

iris
iris_part <- iris[4:6,]
rownames(iris_part) <- c('a','c','d')
write.csv(iris_part,file="[想要的目錄]/GG.csv")

• 儲存到特定位置下 假定叫做Script.R
• 在terminal執行

R CMD BATCH [該檔案儲存位置]/Script.R

• 可透過crontab定期執行

• 安裝套件

install.packages("RCurl")
library(RCurl)

• 讀檔案 (非Windows)

DF = read.table(sep=",", header=TRUE,            
file= textConnection(
getURL("https://raw.githubusercontent.com/ntuaha/TWFS/master/db/cl_info_other.csv")
))

• 讀檔案 (Windows)

DF = read.csv(sep=",", header=TRUE, 
file="https://raw.githubusercontent.com/ntuaha/TWFS/master/db/cl_info_other.csv",
fileEncoding="UTF-8")

1. 自動化排程
2. 與資料庫溝通
3. 其他的資料源
   • 結構化資料
   • 非結構化資料
4. 培養對資料的品味

1. http://cran.rstudio.com/web/packages/dplyr/vignettes/introduction.html
2. http://cran.r-project.org/web/packages/dplyr/dplyr.pdf