python 讀取 MySQL 資料

sqlalchemy連結MySQL時，必須先安裝另一模組 pymysql

pip install pymysql

接下來利用 python 來讀取先前我們用R寫入的股價資料庫吧

%matplotlib inline

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://stockuser:password@127.0.0.1:3306/stockdb")

sql ="select * from tw1101;"

df = pd.read_sql_query(sql, engine)

print(df)

df.close.plot()

執行結果：

  row_names        date       open       high        low      close  \
0            1  2009-01-02  24.193001  24.193001  24.193001  24.193001   
1            2  2009-01-05  25.135599  25.225300  24.597000  24.821400   
2            3  2009-01-06  24.955999  25.135599  24.372499  24.641800   
3            4  2009-01-07  25.090700  25.449800  24.417400  25.225300   
4            5  2009-01-08  24.372499  24.731600  23.789000  24.417400   
5            6  2009-01-09  24.552099  25.090700  23.789000  23.789000   
...
          volume   adjusted  
0            0.0  14.254436  
1     22712510.0  14.624688  
2     20298561.0  14.518868  
3     26370751.0  14.862664  
4     23748492.0  14.386651
...
2536  30751985.0  41.000000  
2537  23165336.0  41.599998  
2538  16390191.0  41.549999  
2539  13459252.0  41.799999  
2540  11300709.0  41.750000  

[2541 rows x 8 columns]

R tidyquant 抓取股價資料寫入 MySQL 資料庫

MySQL上先建立一個新使用者 stockuser ：

mysql -u root -p

出現 mysql > 後，開始建立新用者資料並設定密碼：

mysql >
mysql > GRANT ALL PRIVILEGES ON *.* TO 'stockuser'@'%' IDENTIFIED BY 'password';
mysql > FLUSH PRIVILEGES;
mysql > quit;

若MySQL伺服器要開放遠端連結，記得防火牆允入3306埠，另外 MySQL的設定檔也要檢查：

vi /etc/mysql/my.cnf

記得 bind-address = 127.0.0.1 這行要註解

#bind-address           = 127.0.0.1

也記得 MySQL 要重新啟動

service mysql restart

資料庫部分沒問題的話，就可以準備來使用R抓取股價資料~R 如果還沒有安裝過 RMySQL package, 也記得先安裝：

install.packages("RMySQL")

確認安裝完成後，就開始吧...

library(RMySQL)

library(DBI)
library(tidyquant)
library(dplyr)

cn <- dbConnect(
 drv = RMySQL::MySQL(),
 username='stockuser',
 password='password',
 host='127.0.0.1',
 port = 3306,
 dbname = "stockdb"
 )

#  利用 tidyquant 取得 Yahoo 上的台灣五十股價資料, 寫入 MySQL stockdb > tw{股票代碼}
tw50 <- c("1101","1102","1216") #只有示範三檔股票
for (i in tw50) {
 stockid <- paste(i,"TW",sep=".")
 tblname <- paste("tw",i,sep="")
 print(stockid)
 sdata <- tq_get(stockid)
 dbWriteTable(cn, name=tblname, value=sdata, overwrite=TRUE)
 Sys.sleep(10) # 每次 Loop 先休息 10 秒
}

# 利用 tidyquant 取得 Yahoo 上的IBM股價資料, 寫入 MySQL stockdb > ibm
ibm <- tq_get("IBM")
dbWriteTable(cn, name="ibm", value=ibm, overwrite=TRUE)

dbDisconnect(cn) #記得斷開資料庫連線

資料都抓取完成後，MySQL資料庫裡面 stockdb > 就會多個股 table ：

mysql -u stockuser -p
輸入密碼，登入後...
mysql> use stockdb;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_stockdb |
+-------------------+
| ibm               |
| tw1101            |
| tw1102            |
| tw1216            |
+-------------------+
4 rows in set (0.00 sec)

以後就可以直接從 MySQL 中讀取股價資料來運用：

# 從 MySQL 取出 IBM 股價資料
cn <- src_mysql(
 user='stockuser',
 password='password',
 host='127.0.0.1',
 port = 3306,
 dbname = "stockdb"
 )

ibm_table <- tbl(cn, "ibm")
ibm_table

dbDisconnect(cn)

延伸閱讀資料：

Amazon RDS + R (其實我是租不起, 所以自建 MySQL...)

R學習筆記 - 資料工程篇(四) Database

---

取得台北火車站附近最熱門的打卡點 (留給自己參考)

* 參考來源：苗栗最熱門的打卡點？ Andrew Tang

• 修改：
• gcode將"台北車站"字串以iconv從big5編碼為utf8
• 取得個地點打卡次數時，順便將location寫入fields取回lat, lng等地理座標資訊

install.packages("ggmap")
install.packages("leaflet")
install.packages("DT")
install.packages("jsonlite")
install.packages("curl")
install.packages("dplyr")

library(ggmap)
library(leaflet)
library(DT)
options(stringsAsFactors = F)

(myplace <- geocode(iconv("台北車站", "big5", "utf8")))

顯示台北火車站座標結果

      lon      lat
1 121.517 25.04774

繪製出台北火車站方圓5公里(radius=5000)的範圍

leaflet() %>% addTiles() %>% 
  fitBounds(0, 40, 10, 50) %>% 
  setView(myplace$lon, myplace$lat, zoom = 12) %>% 
  addCircles(myplace$lon, myplace$lat, radius = 5000)

台北火車站方圓5公里

至Facebook 圖形API測試工具 取得程式權杖(token)

library(jsonlite)
經緯度 <- paste(myplace$lat, myplace$lon, sep=",")
方圓幾公尺 <- 5 * 1000
token <- "EAACEdEose0(略)ppeaL"
url <- sprintf("https://graph.facebook.com/search?type=place¢er=%s&distance=%s&limit=50000&access_token=%s", 經緯度, 方圓幾公尺, token)

tmp <- fromJSON(url)
result <- tmp$data[c("category","name","id")]
while(!is.null(tmp$paging$`next`)){
  tmp <- fromJSON(tmp$paging$`next`)
  result <- rbind(result, tmp$data[c("category","name","id")])
}

datatable(result, 
          extensions = 'Scroller', options = list(
          deferRender = TRUE,
          scrollY = 500,
          scroller = TRUE
        ))

##執行結果

---

取得每個地點的 名稱(name)、打卡數(checkins)、地點類別(category)、和座標資料(location)

library(dplyr)
fields <- "name,checkins,category,location"
checkin <- sapply(result$id, function(id){
  url <- sprintf("https://graph.facebook.com/%s?fields=%s&access_token=%s", id, fields, token)
  #unlist(fromJSON(url))
  fromJSON(url)
})

mycheckin <- as.data.frame(t(checkin))
mycheckin$checkins <- as.integer(mycheckin$checkins)
mycheckin <- mycheckin[order(mycheckin$checkins, decreasing = T),]
row.names(mycheckin) <- NULL
datatable(mycheckin, 
          extensions = 'Scroller', options = list(
          deferRender = TRUE,
          scrollY = 200,
          scroller = TRUE
        ))

##執行結果

#

R 以地址取得經緯座標值

假如data.frame data中的lat值為0時，則利用google maps api geocode 查詢地址的經緯座標，並存回 data$lat 與 data$lng 中：

library(ggmap)

for (i in 1:nrow(data)) {
     if (data$lat[i] == 0) {
         addr <- data$service_addr[i]
         url = paste('http://maps.google.com/maps/api/geocode/xml?address=', addr,'&sensor=false',sep='')
         doc = xmlTreeParse(url) 
         root = xmlRoot(doc) 
         data$lat[i] = xmlValue(root[['result']][['geometry']][['location']][['lat']]) 
         data$lng[i] = xmlValue(root[['result']][['geometry']][['location']][['lng']])
     }
}

# 將 data 內容寫入檔案 mymap.csv
write.table(data, file = "mymap.csv", sep = "," )

#

取得政府資料開放平台資料資源，將資料分類統計以R語言繪製成圓餅圖

library(data.table)

# 取得政府資料開放平台資料資源 (file format = csv)
data <- fread("http://search.data.gov.tw/wise/query?q=%2A%3A%2A&export=true&format=csv&rows=2147483647&d=1", header="auto", encoding = "UTF-8")

# 統計資料分類及分類數量, 統計結果以 data.frame 儲存至 categories
categories <- as.data.frame(table(data$服務分類))

# 計算每個分類百分比，取小數點第2位，將結果存於 pct
pct <- round(categories$Freq/sum(categories$Freq)*100,2)

# 把原來的分類"名稱 百分比 %" 存回原來的名類名稱中 
categories$Var1 <- paste(categories$Var1, pct, "%", sep=" ")

#繪製圓餅圖 Piechart
pie(categories$Freq, labels=categories$Var1, main="政府資料開放平台資料資源", col=rainbow(length(categories$Var1)))

執行結果:

R - 簡單洗牌函數

> # 洗牌 to shuffle cards
> to_shuffle_cards <- function() {
+ rep_times = 1
+ cards <- matrix(nrow=4, sample(rep(1:52,times=rep_times), 52, replace=FALSE))
+ rownames(cards) <- c("player1","player2","player3","player4")
+ cards <- as.table(cards)
+ return(cards)
+ }

> x <- to_shuffle_cards()
> y <- to_shuffle_cards()
> x
         A  B  C  D  E  F  G  H  I  J  K  L  M
player1  4 34 19 45  3 42 27 24 37 40 51 22  5
player2 35 16 17 38  1 30 13 47 33 26 15 44 52
player3  7 39 41 10 36 23 48 14 20 31 25  6 46
player4 12 49  8 21 18 43  2 50 28  9 32 29 11

> y
         A  B  C  D  E  F  G  H  I  J  K  L  M
player1 31 17 15 37  5 18 43  3 51 13  9 38 19
player2 20 27 10 45 30 32  7 48 47 11  2 25 42
player3 41 34 29 50  8  4 28 35 23 44  1 12 52
player4  6 49 16 24 40 14 36 21 26 33 46 22 39

#

Windows 7 下幫 R 設定 HTTP Proxy 參數

將 RGui.exe ICON 上按下滑鼠右鍵，選擇內容修改程式執行捷徑，加上 http_proxy 設定即可：

原始設定："D:\R\R-3.2\bin\x64\Rgui.exe"

設定Proxy："D:\R\R-3.2\bin\x64\Rgui.exe" http_proxy=http://userId:password@your.proxy.domain:8080/

#