Demo20161126

作業二

library(rvest)
ltn <- 'http://news.ltn.com.tw/list/BreakingNews'
lipic <- read_html(ltn) %>% html_nodes('.lipic')
title    <- lipic %>% html_nodes('a.picword') %>% html_text()
url      <- lipic %>% html_nodes('a.picword') %>% html_attr('href')
dt       <- lipic %>% html_nodes('span') %>% html_text()
ltn_news <- data.frame('datetime' = dt, 'title' = title, 'url' = url)
ltn_news

#write.csv(x= ltn_news, file='ltnnews.csv')

資料庫語句

## Create Table
CREATE TABLE customer(
id int AUTO_INCREMENT PRIMARY KEY,
name varchar(50),
gender varchar(1),
address varchar(200)
) ENGINE=InnoDB;

## Alter Table
ALTER TABLE customer CHANGE COLUMN name cname VARCHAR(50) NOT NULL,ADD COLUMN phone VARCHAR(10);

## Describe Schema
DESCRIBE customer;


## Data Insert
INSERT INTO customer(cname,gender,address)
VALUES('John','M','Chiayi');


SELECT * FROM customer;

INSERT INTO customer(cname,gender,address)
VALUES('John1','UNKNOWN','Chiayi');

INSERT INTO customer(cname,gender,address)
VALUES('Mary','F','Tainan');

INSERT INTO customer(cname,gender,address)
VALUES('Brad','M','Chiayi');

## Data Update
UPDATE customer SET cname = 'Johnny' WHERE id = 1;

## Data Select
SELECT * FROM customer;
SELECT cname, gender FROM customer;
SELECT cname, gender FROM customer WHERE gender ='M';

## Data Aggregation with GROUP BY and HAVING
SELECT COUNT(*) FROM customer;
SELECT COUNT(*) FROM customer WHERE gender = 'M';
SELECT COUNT(*) FROM customer WHERE gender = 'F';

SELECT gender, COUNT(*) FROM customer GROUP BY gender;

SELECT gender, COUNT(*) FROM customer 
GROUP BY gender HAVING COUNT(*) >= 2;

進階議題

show engines
SHOW DATABASES;

## Information Schema
USE information_schema;
SHOW TABLES;

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA

SELECT * FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'orders';

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'orders';

## Create Index
create index idx_customer on customer(cname);
create index idx_customer2 on customer(cname,gender);
create index idx_customer3 on customer(cname) using BTREE;

## Delete, Truncate, Drop
DELETE FROM customer WHERE id =1;
DELETE FROM customer;

truncate customer;
select * from customer;
drop table customer;

使用R 連結MySQL

if(dbExistsTable(conn,'iris')){
  dbRemoveTable(conn, 'iris')
  }

logical(0)

讀取蘋果新聞資料

download.file('https://raw.githubusercontent.com/ywchiu/rtibame/master/data/applenews.RData', 'applenews.RData')

trying URL 'https://raw.githubusercontent.com/ywchiu/rtibame/master/data/applenews.RData'
Content type 'application/octet-stream' length 1044040 bytes (1019 KB)
downloaded 1019 KB

load('applenews.RData')
library(RJDBC)
jar.loc<-'C:\\Program Files (x86)\\MySQL\\Connector.J 5.1\\mysql-connector-java-5.1.39-bin.jar'
drv<-JDBC("com.mysql.jdbc.Driver",jar.loc,identifier.quote="`")
conn <-dbConnect(drv, "jdbc:mysql://localhost/appledaily", "root", "test")
if(dbExistsTable(conn, 'news_main')){
  dbRemoveTable(conn, 'news_main')
}

logical(0)

dbSendUpdate(conn, 'CREATE TABLE news_main (
id       int(11) NOT NULL AUTO_INCREMENT,
content  text,
title    varchar(1000)DEFAULT NULL,
dt       datetime DEFAULT NULL,
category varchar(1000)DEFAULT NULL,
view_cnt int(11)DEFAULT NULL,
PRIMARY KEY(id)
)ENGINE=InnoDB;')
applenews <- cbind(id=1:nrow(applenews), applenews)
dbWriteTable(conn, 'news_main', applenews, append=TRUE,row.names=TRUE,overwrite=FALSE)

[1] TRUE

news <- dbReadTable(conn, 'news_main')
res <-dbGetQuery(conn, 
"SELECT category, count(*) AS cnt FROM news_main
GROUP BY category ORDER BY COUNT(*) DESC")
pie(res$cnt, labels = res$category)

barplot(res$cnt, names.arg = res$category, col=as.factor(res$category))

#?barplot
dbSendUpdate(conn, 'drop table news_main;')
dbDisconnect(conn)

[1] TRUE

USE appledaily;
DESCRIBE applenews;


CREATE TABLE news_main (
id       int(11) NOT NULL AUTO_INCREMENT,
content  text,
title    varchar(1000)DEFAULT NULL,
dt       datetime DEFAULT NULL,
category varchar(1000)DEFAULT NULL,
view_cnt int(11)DEFAULT NULL,
PRIMARY KEY(id)
)ENGINE=InnoDB;

使用dplyr

# R Style
applenews[applenews$category == '3C'  ,   ]

Warning message:
In strsplit(code, "\n", fixed = TRUE) :
  input string 1 is invalid in this locale

library(dplyr)
# dplyr Style
filter(applenews, category=="3C")

# R Style
applenews[applenews$category == '3C' & applenews$view_cnt > 1000  ,   ]

# dplyr Style
filter(applenews, category=="3C"&view_cnt>1000)

filter(applenews, category%in%c("3C"))

# R Style
applenews[, c("category","view_cnt")]

# dplyr Style
select(applenews, category, view_cnt)

# R Style
applenews[applenews$category=="3C", c('category', 'view_cnt')]

# dplyr Style
applenews %>% 
  select(category,view_cnt) %>%
  filter(category=="3C")

## SELECT category, view_cnt 
## FROM applenews
## WHERE category = <e2>€<9c>3C<e2>€<9d>
## ORDER BY view_cnt
# R Style
applenews$view_cnt <- as.numeric(applenews$view_cnt)
a <- applenews[applenews$category=='3C',c('category', 'view_cnt')]
a[order(a$view_cnt, decreasing = TRUE),]

# dplyr Style
applenews %>% select(category, view_cnt) %>%
  filter(category=='3C') %>% arrange(desc(view_cnt))

# mutate
freqsum <- applenews %>% select(view_cnt) %>% sum()
freqsum

[1] 21574014

applenews %>% 
  select(title,category,view_cnt) %>%    
  mutate(portion=view_cnt/freqsum)

applenews <- applenews %>% mutate(portion=view_cnt/freqsum)
head(applenews)

# Group By 
## R Style
tapply(applenews$view_cnt, applenews$category, sum)

                      3C 
                  146308 
                    甇<a3>憒<b9> 
                  672949 
                    <e7><94>暑 
                 3417804 
                    <e5><e7> 
                  220812 
                    蝷暹<9c><83> 
                 5721750 
                    <e6>瘝<bb> 
                 1701980 
                    憡<a8><82> 
                 3571005 
                    <e6><99><b0><9a> 
                  260499 
                    鞎∠<b6><93> 
                  618243 
                    <e5><8b> 
                  123287 
                    <e5><9c><9a><9b> 
                 2485621 
<e5><9c><9a><9b>","LA","SF","NY","US 
                   43627 
          <e5><9c><9a><9b>","SF","US 
                   11163 
                    <e6><90><a5><87> 
                  668307 
                    隢<a3><87> 
                  312592 
                    擃 
                 1598067 

applenews %>% select(view_cnt, category) %>%
  group_by(category) %>% summarise(view_sum = sum(view_cnt)) %>% arrange(desc(view_sum))

applenews %>% select(view_cnt, category) %>%
  group_by(category) %>% summarise(view_mean = mean(view_cnt)) %>% arrange(desc(view_mean)) %>% filter(view_mean > 20000)

applenews %>% group_by(category) %>% summarise_each(funs(sum), view_cnt, portion) %>% arrange(desc(portion))

applenews %>% group_by(category) %>% summarise_each(funs(min(., na.rm=TRUE), max(., na.rm=TRUE)), matches("view_cnt"))

## Count Data
## select count(category) from applenews
applenews%>%select(category)%>%summarise_each(funs(n()))

## select count(distinct(category)) from applenews
applenews%>%select(category)%>%summarise_each(funs(n_distinct(category)))

cat_stat <- applenews %>% group_by(category) %>% summarise(view_sum=sum(view_cnt)) %>% arrange(desc(view_sum))
barplot(cat_stat$view_sum, names.arg = cat_stat$category, col = factor(cat_stat$category))

pie(cat_stat$view_sum, label =cat_stat$category)

Homework 3

download.file('https://github.com/ywchiu/rtibame/raw/master/Data/purchase.csv', 'purchase.csv')

trying URL 'https://github.com/ywchiu/rtibame/raw/master/Data/purchase.csv'
Content type 'text/plain; charset=utf-8' length 3497968 bytes (3.3 MB)
downloaded 3.3 MB

purchase <- read.csv('purchase.csv', header=TRUE, stringsAsFactors = FALSE)
View(purchase)
str(purchase)

'data.frame':   54772 obs. of  7 variables:
 $ X       : int  0 1 2 3 4 5 6 7 8 9 ...
 $ Time    : chr  "2015-07-01 00:00:01" "2015-07-01 00:00:03" "2015-07-01 00:00:19" "2015-07-01 00:01:10" ...
 $ Action  : chr  "order" "order" "order" "order" ...
 $ User    : chr  "U312622727" "U239012343" "U10007697373" "U296328517" ...
 $ Product : chr  "P0006944501" "P0006018073" "P0002267974" "P0016144236" ...
 $ Quantity: int  1 1 1 1 1 1 1 1 1 1 ...
 $ Price   : num  1069 1680 285 550 249 ...

purchase$Time <- as.POSIXct(purchase$Time)
#strftime(purchase$Time, '%H:%M')