Data Analysis

Source of Data

Native R Files Database System Files Web Page Web API Data Streams

Native R Files

a <- list(a = 1, b = 2)
save(a, file = "a.Rdata")
rm("a")
a

## Error: object 'a' not found

load("a.Rdata")
a

## $a
## [1] 1
## 
## $b
## [1] 2

Database

DBI - A Generalized Database Interface for R

RSQLite - DBI Interface

suppressPackageStartupMessages(library(RSQLite))
db <- dbConnect("SQLite", "iris.db")
dbReadTable(db, "iris")
dbDisconnect(db)

RSQLite - Transaction and Other Features

• Transaction
• SQL Preparation

library(RSQLite)
db <- dbConnect("SQLite", "cars.db")
if (dbExistsTable(db, "cars")) dbRemoveTable(db, "cars")
dbBeginTransaction(db)
sql.create <- "create table cars (speed INTEGER, dist INTEGER)"
sql.insert <- "insert into cars values (:speed, :dist)"
dbGetPreparedQuery(db, sql.create, bind.data = cars)
dbGetPreparedQuery(db, sql.insert, bind.data = cars)
dbCommit(db)
dbReadTable(db, "cars", row.names = FALSE)
dbDisconnect(db)

RSQLite - Batch Loading

• For loading Big Data
• Performance Issue:
  • Don't exhaust the memory
  • Allocate the result and modify in place. data.table: a package for in-memory modification and indexing of data.frame

# This is a script to load 10^7 rows into R in several minutes
library(data.table)
library(bit64)  # A package for 64bit integer in R.
library(RSQLite)

db.pv <- try(dbConnect("SQLite", normalizePath(sprintf("CountPV/%s/pv.db", param.date), 
    mustWork = TRUE)), silent = TRUE)
if (class(db.pv) == "try-error") {
    # Error Handling
}

res <- dbSendQuery(db.pv, "SELECT COUNT(*) FROM ExampleTableName")
n <- fetch(res)
dbClearResult(res)
pv <- data.table(example_column1 = rep(as.integer64(0), n), example_column2 = rep("", 
    n), example_column3 = rep(0L, n))
gc()

res <- dbSendQuery(db.pv, "SELECT example_column1, example_column2, example_column3 FROM ExampleTableName")
pv.part <- fetch(res, m)
i <- 1
pb <- txtProgressBar(max = n)
while (nrow(pv.part) > 0) {
    pv.part$example_column1 <- as.integer64(pv.part$example_column1)
    pv.part$example_column3 <- as.integer(pv.part$example_column3)
    pv.part
    pv[seq(i, by = 1, length = nrow(pv.part)), `:=`(example_column1, pv.part$example_column1)]
    pv[seq(i, by = 1, length = nrow(pv.part)), `:=`(example_column2, pv.part$example_column2)]
    pv[seq(i, by = 1, length = nrow(pv.part)), `:=`(example_column3, pv.part$example_column3)]
    i <- i + nrow(pv.part)
    setTxtProgressBar(pb, i - 1)
    pv.part <- fetch(res, m)
}
close(pb)
dbClearResult(res)
dbDisconnect(db.pv)

External Client:

library(RembedPy)

pyscript("import cql")
cql <- pyobj("cql")
con <- cql$connect("localhost", cql_version="3.0.0") 
# cql_version 3.0.0 is unavailable in R
cursor <- con$cursor()
cursor$execute("use keyspace_name")
cursor$execute("SELECT * FROM column_family_name LIMIT 2")
cursor["description"]
pyscript("
def test_print(src):
    print src
")
pycall("test_print", cursor["description"])
result <- cursor$fetchone()
as.list(result)

NoSQL

library(rredis)
redisConnect()
redisSet("a", list(a = 1, b = 2))

## [1] "OK"

redisGet("a")

## $a
## [1] 1
## 
## $b
## [1] 2

redisClose()

System File: General Issue

$ file utf8.txt 
utf8.txt: UTF-8 Unicode text
$ file utf8-BOM.txt 
utf8-BOM.txt: UTF-8 Unicode (with BOM) text
$ file big5.txt
big5.txt: ISO-8859 text, with CRLF line terminators

readLines("utf8.txt")

## [1] "中文編碼測試" ""

readLines("utf8-BOM.txt")

## [1] "中文編碼測試" ""

readLines("big5.txt")

## [1] "\xa4\xa4\xa4\xe5\xbds\xbdX\xb4\xfa\xb8\xd5"

iconv(readLines("big5.txt"), from = "big5", to = "utf8")

## [1] "中文編碼測試"

Common Format:

Only list some packages which I used before:

• csv: read.csv, write.csv. Check quote and sep
• xls: read.xls(in package xlsReadWrite or gdata), write.xls(in package xlsReadWrite or dataframes2xls)
• xlsx: read.xlsx, write.xlsx in package xlsx
• ods: read.ods in package ROpenOffice
• foreign: Read Data Stored by Minitab, S, SAS, SPSS, Stata, Systat, dBase, ...

Always check the file before importing

Web Related

Example: XML

How many package does CRAN have?

suppressPackageStartupMessages(library(XML))
r_pkg <- "http://cran.rstudio.com/web/packages/available_packages_by_date.html"
tb <- readHTMLTable(r_pkg)[[1]]
nrow(tb)

## [1] 4528

Distribution of last updated date

tb.date <- as.Date(as.character(tb[[" Date "]]))
range(tb.date)

## [1] "2005-10-29" "2013-05-18"

pie(table(findInterval(as.integer(tb.date), vec = as.integer(seq.Date(from = as.Date("2005-01-01"), 
    to = as.Date("2014-01-01"), by = "years")))), labels = format(seq.Date(from = as.Date("2005-01-01"), 
    to = as.Date("2013-01-01"), by = "years"), "%Y"))

Web API:

Only list some resources which I awared before:

• Maps: OpenStreetMap, ggmap, osmar
• GoogleDocs: RGoogleDocs
• Twitter: twitteR

Map

http://stackoverflow.com/questions/11056738/plotting-points-from-a-data-frame-using-openstreetmap

Add hotspot of wifi (from http://data.taipei.gov.tw/opendata/apply/NewDataContent?oid=094E4C4E-2E35-4B76-BDBB-F842DC0A865B) to the map:

library(ggmap)
wifi <- read.csv("wifi-utf8.csv")  # I manually convert the encoding before importing!!
lng <- c(121.5138, 121.5785)
lat <- c(25.0233, 25.069)
index.lng <- wifi$lng > lng[1] & wifi$lng < lng[2]
index.lat <- wifi$lat > lat[1] & wifi$lat < lat[2]
wifi <- wifi[index.lng & index.lat, ]
if (file.exists("map.RDS")) {
    map <- readRDS("map.RDS")
} else {
    map <- get_map(c(min(lng), min(lat), max(lng), max(lat)), source = "osm", 
        maptype = "roadmap")
}
ggmap(map)

ggmap(map) + geom_point(data = wifi, aes(x = lng, y = lat), size = 1)

Google Doc

suppressPackageStartupMessages(library(RGoogleDocs))
# options(GoogleDocsPassword = c('login id' = 'password')
con <- getGoogleDocsConnection(service = "wise")
sheets <- getWorksheets("TW.R COSCUP 講題投票 (回覆內容)", con)
result <- sheetAsMatrix(sheets[[1]], header = TRUE)[, 2:31]
m <- gvisTable(result, options = gvis.opt)
cat(m$html$chart)

Twitter

An example from http://cran.r-project.org/web/packages/twitteR/twitteR.pdf

Comparing the tweets from Taipei and Maynila relating to fisherman:

suppressPackageStartupMessages(library(twitteR))
suppressPackageStartupMessages(library(xtable))
source("searchTwitter.R")
cred <- readRDS("cred.Rds")
invisible(registerTwitterOAuth(cred))

tw_Tweets <- searchTwitter("fisherman", n = 300, lang = "en", since = "2013-05-16", 
    geocode = "25.0233,121.5784,300km")  #, 25.0233,121.5784,300km)

## Warning: 300 tweets were requested but the API can only return 79

tw_sources <- sapply(tw_Tweets, function(x) x$text)
length(tw_sources) <- 300
ph_Tweets <- searchTwitter("fisherman", n = 300, lang = "en", since = "2013-05-16", 
    geocode = "14.3459,120.5800,300km")
ph_sources <- sapply(ph_Tweets, function(x) x$text)
result <- data.frame(tw = tw_sources, ph = ph_sources)
result$tw <- iconv(result$tw, from = "utf-8", to = "ascii")
result$ph <- iconv(result$ph, from = "utf-8", to = "ascii")
print(xtable(result), type = "html")

Data Streams

Check our incoming talk at 6/24 by Philips: about collecting stock data stream with rJava. It should be a great example of importing data streams.

Call for Proposal

• July: Visualization
• August: Algorithm of Machine Learning

Please send your proposal to

Q&A