}
hashtags <- hashtags[-1, ]
hashtags <- aggregate(freq ~ tag, hashtags, FUN = sum)
hashtags <- hashtags[order(-hashtags$freq), ]
hashtags.original <- hashtags
# hashtags <- hashtags.original
### filter 1 :: before the disaster --------------------------------------------
hashtags.filter.1 <- dbGetQuery(db.con,
sprintf(sql.filter.1,
disasters[disNum, 'sql'],
disasters[disNum, 'start_date'],
sql.places.cnt
))
hashtags.filter.1 <- hashtags.filter.1$hashtag
hashtags <- hashtags[ !(hashtags$tag %in% hashtags.filter.1), ]
### filter 3 :: common disaster hashtags ---------------------------------------
# it's placed before the filter #2 for the purpose of optimization
hashtags.filter.3 <- scan(file = "./13_filter_3.txt", what = "character", comment.char = "#")
hashtags <- hashtags[ !(hashtags$tag %in% hashtags.filter.3), ]
# hashtags.filter.3 <- scan(file = "./13_filter_3.txt", what = "character", sep = "\n")
# write(sort(hashtags.filter.3), file = "./13_filter_3.txt")
### filter 2 :: classify hashtags ----------------------------------------------
hashtags.filter.2 <- sapply(hashtags$tag, function(tag) {
# tag <- 'txflood'
# rm(tag)
data <- dbGetQuery(db.con, sprintf(sql.filter.2,
tag,
disasters[disNum, 'start_date'],
disasters[disNum, 'end_date']
))
predict <- data.classify(data)
predict <- subset(predict, is.na(predict) == FALSE)
return( length(predict[predict == TRUE]) / length(predict) )
})
hashtags$rate <- hashtags.filter.2
hashtags <- hashtags[ hashtags$rate > 0.5, ]
print(hashtags)
View(hashtags)
library(RPostgreSQL)
source('./11_classify.R')
source('./12_sql.R')
### PostgreSQL connect ---------------------------------------------------------
db.con <- dbConnect(PostgreSQL(),
dbname = "twitter", host = "localhost", port = 5432,
user = "twitter", password = "111"
)
### retrieve first data --------------------------------------------------------
# the maximum number of places when the index is still in use
for (sql.places.cnt in 20:1) {
db.res <- dbGetQuery(db.con,
sprintf(paste('EXPLAIN (FORMAT TEXT)', sql.data, ' '),
disasters[disNum, 'sql'],
disasters[disNum, 'start_date'],
disasters[disNum, 'end_date'],
sql.places.cnt
))
if (length(db.res[grepl('message', db.res$`QUERY PLAN`), ]) == 2) break
}
# send query
db.query <- dbSendQuery(db.con, sprintf(sql.data,
disasters[disNum, 'sql'],
disasters[disNum, 'start_date'],
disasters[disNum, 'end_date'],
sql.places.cnt
))
# collector for hashtags
hashtags <- data.frame('tag'  = '', 'freq' = 0, stringsAsFactors = FALSE)
# read by 1000 records and classify
while (dbHasCompleted(db.query) == FALSE) {
data <- dbFetch(db.query, n = 1000)
if (nrow(data) == 0) {
cat("\nquery returned empty result.")
break
}
predict <- data.classify(data)
# ---- extract hashtags
for(i in which(predict == TRUE)) {
tmp.tags <- unlist( strsplit(data$hashtags[i], split = ",", fixed = TRUE) )
hashtags <- rbind(hashtags, data.frame(
"tag"  = tmp.tags,
"freq" = rep(1, times = length(tmp.tags))
))
}
# ---- clear intermediate variables
rm(data, predict)
}
db.res <- dbClearResult(db.query)
# hashtag frequencies
if (nrow(hashtags) == 1) {
cat("\nWarning: The number of tweets is not enought to study the disaster!",
sprintf("\n         Skip disaster #%d '%s'.", disNum, disasters$comment[disNum])
)
db.res <- dbDisconnect(db.con)
next # outer disaster-wide for-loop (see `00_start.R`)
}
hashtags <- hashtags[-1, ]
hashtags <- aggregate(freq ~ tag, hashtags, FUN = sum)
hashtags <- hashtags[order(-hashtags$freq), ]
hashtags.original <- hashtags
# hashtags <- hashtags.original
### filter 1 :: before the disaster --------------------------------------------
hashtags.filter.1 <- dbGetQuery(db.con,
sprintf(sql.filter.1,
disasters[disNum, 'sql'],
disasters[disNum, 'start_date'],
sql.places.cnt
))
hashtags.filter.1 <- hashtags.filter.1$hashtag
hashtags <- hashtags[ !(hashtags$tag %in% hashtags.filter.1), ]
### filter 3 :: common disaster hashtags ---------------------------------------
# it's placed before the filter #2 for the purpose of optimization
hashtags.filter.3 <- scan(file = "./13_filter_3.txt", what = "character", comment.char = "#")
hashtags <- hashtags[ !(hashtags$tag %in% hashtags.filter.3), ]
# hashtags.filter.3 <- scan(file = "./13_filter_3.txt", what = "character", sep = "\n")
# write(sort(hashtags.filter.3), file = "./13_filter_3.txt")
### filter 2 :: classify hashtags ----------------------------------------------
hashtags.filter.2 <- sapply(hashtags$tag, function(tag) {
# tag <- 'txflood'
# rm(tag)
data <- dbGetQuery(db.con, sprintf(sql.filter.2,
tag,
disasters[disNum, 'start_date'],
disasters[disNum, 'end_date']
))
predict <- data.classify(data)
predict <- subset(predict, is.na(predict) == FALSE)
return( length(predict[predict == TRUE]) / length(predict) )
})
hashtags$rate <- hashtags.filter.2
View(hashtags)
hashtags[ hashtags$rate > 0.5, ]
View(hashtags.original)
disNum
disasters$comment[disNum]
write(hashtags$tag, file =
sprintf("./result/%02d-%s.txt", disNum,
sub("([a-z]+).*", "\\1", disasters$comment[disNum], ignore.case = TRUE)
)
)
sprintf("./result/%02d-%s.txt", disNum,
sub("([a-z]+).*", "\\1", disasters$comment[disNum], ignore.case = TRUE)
)
View(hashtags)
View(hashtags)
db.user <- "twitter"
db.pass <- "111"
disasters <- read.csv('./02_disasters.csv', header = TRUE, stringsAsFactors = FALSE)
library(RPostgreSQL)
library(tm)
library(RTextTools)
library(cldr)
library(slam)
disNum <- 4
library(RPostgreSQL)
source('./11_classify.R')
source('./12_sql.R')
db.con <- dbConnect(PostgreSQL(),
dbname = "twitter", host = "localhost", port = 5432,
user = db.user, password = db.pass
)
sql.places.cnt <- 20
hashtags <- dbGetQuery(db.con, sprintf(sql.data,
disasters[disNum, 'sql'],
disasters[disNum, 'start_date'],
disasters[disNum, 'end_date'],
sql.places.cnt
))
hashtags.original <- hashtags
View(hashtags)
hashtags[ hashtags$freq > 1, ]
nrow(hashtags[ hashtags$freq > 1, ])
nrow(hashtags)
hashtags <- hashtags[ hashtags$freq > 1, ]
View(hashtags)
hashtags.filter.1 <- dbGetQuery(db.con,
sprintf(sql.filter.1,
disasters[disNum, 'sql'],
disasters[disNum, 'start_date'],
sql.places.cnt
))
hashtags.filter.2 <- hashtags.filter.1
rm(hashtags.filter.1)
View(hashtags.filter.2)
hashtags.filter.2 <- hashtags.filter.2$hashtag
hashtags <- hashtags[ !(hashtags$tag %in% hashtags.filter.2), ]
View(hashtags)
hashtags.filter.4 <- scan(file = "./13_filter_3.txt", what = "character", comment.char = "#")
hashtags <- hashtags[ !(hashtags$tag %in% hashtags.filter.4), ]
hashtags.filter.3 <- sapply(hashtags$tag, function(tag) {
# tag <- 'bagyo'
# rm(tag)
# data <- dbGetQuery(db.con, sprintf(sql.filter.2, tag))
data <- dbGetQuery(db.con, sprintf(sql.filter.2,
tag,
disasters[disNum, 'start_date'],
disasters[disNum, 'end_date']
))
predict <- data.classify(data)
predict <- subset(predict, is.na(predict) == FALSE)
return( length(predict[predict == TRUE]) / length(predict) )
})
hashtags$rate <- hashtags.filter.3
View(hashtags)
hashtags[hashtags$rate > 0.5, ]
hashtags <- hashtags[hashtags$rate > 0.5, ]
### finish ---------------------------------------------------------------------
db.res <- dbDisconnect(db.con)
rm(model, vocabulary, hashtags.original, hashtags.filter.1, hashtags.filter.2,
sql.data, sql.filter.1, sql.filter.2,
db.con, db.query, db.res, sql.places.cnt, data.classify,
regex.url, regex.html, regex.user, removeRegEx)
rm(model, vocabulary, hashtags.original, hashtags.filter.3, hashtags.filter.4,
sql.data, sql.filter.1, sql.filter.2,
db.con, db.res, sql.places.cnt, data.classify,
regex.url, regex.html, regex.user, removeRegEx)
write(hashtags$tag, file =
sprintf("./result/%02d-%s.txt", disNum,
sub("([a-z]+).*", "\\1", disasters$comment[disNum], ignore.case = TRUE)
)
)
write(hashtags$tag, file =
sprintf("./result/%02d-%s.txt", disNum,
sub("([a-z]+).*", "\\1", disasters$comment[disNum], ignore.case = TRUE)
)
)
for (disNum in c(5:7)) {
source('./10_hashtags_2.R')
write(hashtags$tag, file =
sprintf("./result/%02d-%s.txt", disNum,
sub("([a-z]+).*", "\\1", disasters$comment[disNum], ignore.case = TRUE)
)
)
}
for (disNum in c(5:7)) {
source('./10_hashtags.R')
write(hashtags$tag, file =
sprintf("./result/%02d-%s.txt", disNum,
sub("([a-z]+).*", "\\1", disasters$comment[disNum], ignore.case = TRUE)
)
)
}
sub("([a-z]+).*", "\\1", disasters$comment[disNum], ignore.case=TRUE)
dis.name <- sub("([a-z]+).*", "\\1", disasters$comment[disNum], ignore.case=TRUE)
cat(sprintf("Hashtags for `%s`\n", dis.name)
)
cat(sprintf("Hashtags for `%s`:\n", dis.name))
print(hashtags$tag)
cat(hashtags$tag)
print(hashtags$tag)
cat(sprintf("Hashtags for `%s`:\n", dis.name))
print(hashtags$tag)
disNum <- 6
library(RPostgreSQL)
source('./11_classify.R')
source('./12_sql.R')
### PostgreSQL connect ---------------------------------------------------------
db.con <- dbConnect(PostgreSQL(),
dbname = "twitter", host = "localhost", port = 5432,
user = db.user, password = db.pass
)
for (sql.places.cnt in 20:1) {
db.res <- dbGetQuery(db.con,
sprintf(paste('EXPLAIN (FORMAT TEXT)', sql.data, ' '),
disasters[disNum, 'sql'],
disasters[disNum, 'start_date'],
disasters[disNum, 'end_date'],
sql.places.cnt
))
if (length(db.res[grepl('message', db.res$`QUERY PLAN`), ]) == 2) break
}
hashtags <- dbGetQuery(db.con, sprintf(sql.data,
disasters[disNum, 'sql'],
disasters[disNum, 'start_date'],
disasters[disNum, 'end_date'],
sql.places.cnt
))
hashtags.original <- hashtags
View(hashtags)
hashtags.original
hashtags <- hashtags[ hashtags$freq > 1, ]
grep("flood", hashtags$tag, ignore.case=TRUE)
hashtags[grep("flood", hashtags$tag, ignore.case=TRUE), ]
hashtags[grep("flood", hashtags.original$tag, ignore.case=TRUE), ]
hashtags.original[grep("flood", hashtags.original$tag, ignore.case=TRUE), ]
hashtags[grep("flood", hashtags$tag, ignore.case=TRUE), ]
hashtags.filter.2 <- dbGetQuery(db.con,
sprintf(sql.filter.1,
disasters[disNum, 'sql'],
disasters[disNum, 'start_date'],
sql.places.cnt
))
hashtags.filter.2 <- hashtags.filter.2$hashtag
hashtags <- hashtags[ !(hashtags$tag %in% hashtags.filter.2), ]
hashtags[grep("flood", hashtags$tag, ignore.case=TRUE), ]
hashtags <- hashtags[ !(hashtags$tag %in% hashtags.filter.2), ]
hashtags[grep("flood", hashtags$tag, ignore.case=TRUE), ]
hashtags.filter.4 <- scan(file = "./13_filter_3.txt", what = "character", comment.char = "#")
hashtags <- hashtags[ !(hashtags$tag %in% hashtags.filter.4), ]
hashtags[grep("flood", hashtags$tag, ignore.case=TRUE), ]
hashtags.filter.3 <- sapply(hashtags$tag, function(tag) {
data <- dbGetQuery(db.con, sprintf(sql.filter.2,
tag,
disasters[disNum, 'start_date'],
disasters[disNum, 'end_date']
))
predict <- data.classify(data)
predict <- subset(predict, is.na(predict) == FALSE)
return( length(predict[predict == TRUE]) / length(predict) )
})
hashtags$rate <- hashtags.filter.3
hashtags[grep("flood", hashtags$tag, ignore.case=TRUE), ]
tag <- "txflood"
sprintf(sql.filter.2,
tag,
disasters[disNum, 'start_date'],
disasters[disNum, 'end_date']
)
cat(sprintf(sql.filter.2,
tag,
disasters[disNum, 'start_date'],
disasters[disNum, 'end_date']
))
rm(tag)
hashtags <- hashtags[hashtags$rate > 0.5, ]
View(hashtags)
tag <- "wendydavis"
data <- dbGetQuery(db.con, sprintf(sql.filter.2,
tag,
disasters[disNum, 'start_date'],
disasters[disNum, 'end_date']
))
predict <- data.classify(data)
predict <- subset(predict, is.na(predict) == FALSE)
length(predict[predict == TRUE])
length(predict)
length(predict[predict == TRUE]) / length(predict)
sql.places.cnt <- 20
hashtags <- dbGetQuery(db.con, sprintf(sql.data,
disasters[disNum, 'sql'],
disasters[disNum, 'start_date'],
disasters[disNum, 'end_date'],
sql.places.cnt
))
hashtags.original <- hashtags
hashtags.original[grep("flood", hashtags.original$tag, ignore.case=TRUE), ]
hashtags[grep("flood", hashtags$tag, ignore.case=TRUE), ]
### filter 1 :: low-frequent hashtags ------------------------------------------
hashtags <- hashtags[ hashtags$freq > 1, ]
### filter 2 :: foretime hashtags ----------------------------------------------
hashtags.filter.2 <- dbGetQuery(db.con,
sprintf(sql.filter.1,
disasters[disNum, 'sql'],
disasters[disNum, 'start_date'],
sql.places.cnt
))
hashtags.filter.2 <- hashtags.filter.2$hashtag
hashtags <- hashtags[ !(hashtags$tag %in% hashtags.filter.2), ]
### filter 4 :: ommon hashtags -------------------------------------------------
# it's placed before the filter #3 for the purpose of optimization
hashtags.filter.4 <- scan(file = "./13_filter_3.txt", what = "character", comment.char = "#")
hashtags <- hashtags[ !(hashtags$tag %in% hashtags.filter.4), ]
### filter 3 :: classification -------------------------------------------------
hashtags.filter.3 <- sapply(hashtags$tag, function(tag) {
data <- dbGetQuery(db.con, sprintf(sql.filter.2,
tag,
disasters[disNum, 'start_date'],
disasters[disNum, 'end_date']
))
predict <- data.classify(data)
predict <- subset(predict, is.na(predict) == FALSE)
return( length(predict[predict == TRUE]) / length(predict) )
})
hashtags$rate <- hashtags.filter.3
View(hashtags)
hashtags.original[grep("flood", hashtags.original$tag, ignore.case=TRUE), ]
hashtags[grep("flood", hashtags$tag, ignore.case=TRUE), ]
hashtags[hashtags$rate > 0.5, ]
hashtags[grep("txflood", hashtags$tag, ignore.case=TRUE), ]
hashtags[grep("remodelingkilleen", hashtags$tag, ignore.case=TRUE), ]
hashtags[hashtags$rate > 0.5, ]
hashtags <- hashtags[hashtags$rate > 0.5, ]
### finish ---------------------------------------------------------------------
db.res <- dbDisconnect(db.con)
rm(model, vocabulary, hashtags.original, hashtags.filter.3, hashtags.filter.4,
sql.data, sql.filter.1, sql.filter.2,
db.con, db.res, sql.places.cnt, data.classify,
regex.url, regex.html, regex.user, removeRegEx)
rm(model, vocabulary, hashtags.original, hashtags.filter.2, hashtags.filter.3,
hashtags.filter.4, sql.data, sql.filter.1, sql.filter.2, db.con, db.res,
sql.places.cnt, data.classify, regex.url, regex.html, regex.user, removeRegEx)
rm(predict)
rm(tag)
cat(sprintf("Hashtags for `%s`:\n", dis.name))
dis.name <- sub("([a-z]+).*", "\\1", disasters$comment[disNum], ignore.case=TRUE)
cat(sprintf("Hashtags for `%s`:\n", dis.name))
print(hashtags$tag)
disasters
disasters <- read.csv('./02_disasters.csv', header=TRUE, stringsAsFactors=FALSE)
disasters
disasters$comment[disNum]
disNum <- 1
disasters$comment[disNum]
library(RPostgreSQL)
library(tm)
library(RTextTools)
library(cldr)
library(slam)
### Prerequisite ---------------------------------------------------------------
#
# It is expected that tweets are stored in the PostgreSQL database:
#
# CREATE TABLE tweets (
#   tweet_id   bigint NOT NULL,
#   created_at timestamp with time zone,
#   geom       geography(Point,4326),
#   hashtags   text[],
#   message    text,
#   CONSTRAINT tweets_pkey PRIMARY KEY (tweet_id)
# )
# list of disasters
disasters <- read.csv('./02_disasters.csv', header=TRUE, stringsAsFactors=FALSE)
# database connect credentials
db.user <- "twitter"
db.pass <- "111"
for (disNum in c(1:3)) {
source('./10_hashtags.R')
dis.name <- sub("([a-z]+).*", "\\1", disasters$comment[disNum], ignore.case=TRUE)
cat(sprintf("Hashtags for `%s`:\n", dis.name))
print(hashtags$tag)
write(hashtags$tag, file=sprintf("./result/%02d-%s.txt", disNum, dis.name))
}
library(RPostgreSQL)
library(tm)
library(RTextTools)
library(cldr)
library(slam)
### Prerequisite ---------------------------------------------------------------
# list of disasters
disasters <- read.csv('./02_disasters.csv', header=TRUE, stringsAsFactors=FALSE)
View(disasters)
db.user <- "twitter"
db.pass <- "111"
disNum <- 1
library(RPostgreSQL)
source('./11_classify.R')
source('./12_sql.R')
### PostgreSQL connect ---------------------------------------------------------
db.con <- dbConnect(PostgreSQL(),
dbname = "twitter", host = "localhost", port = 5432,
user = db.user, password = db.pass
)
sql.places.cnt <- 20
cat(sprintf(sql.data,
disasters[disNum, 'sql'],
disasters[disNum, 'start_date'],
disasters[disNum, 'end_date'],
sql.places.cnt
))
hashtags <- dbGetQuery(db.con, sprintf(sql.data,
disasters[disNum, 'sql'],
disasters[disNum, 'start_date'],
disasters[disNum, 'end_date'],
sql.places.cnt
))
hashtags.original <- hashtags
### filter 1 :: low-frequent hashtags ------------------------------------------
hashtags <- hashtags[ hashtags$freq > 1, ]
### filter 2 :: foretime hashtags ----------------------------------------------
hashtags.filter.2 <- dbGetQuery(db.con,
sprintf(sql.filter.1,
disasters[disNum, 'sql'],
disasters[disNum, 'start_date'],
sql.places.cnt
))
hashtags.filter.2 <- hashtags.filter.2$hashtag
hashtags <- hashtags[ !(hashtags$tag %in% hashtags.filter.2), ]
### filter 4 :: ommon hashtags -------------------------------------------------
# it's placed before the filter #3 for the purpose of optimization
hashtags.filter.4 <- scan(file = "./13_filter_3.txt", what = "character", comment.char = "#")
hashtags <- hashtags[ !(hashtags$tag %in% hashtags.filter.4), ]
### filter 3 :: classification -------------------------------------------------
hashtags.filter.3 <- sapply(hashtags$tag, function(tag) {
data <- dbGetQuery(db.con, sprintf(sql.filter.2,
tag,
disasters[disNum, 'start_date'],
disasters[disNum, 'end_date']
))
predict <- data.classify(data)
predict <- subset(predict, is.na(predict) == FALSE)
return( length(predict[predict == TRUE]) / length(predict) )
})
hashtags$rate <- hashtags.filter.3
hashtags.filter.2$hashtag
hashtags[hashtags$rate > 0.5, ]
hashtags <- hashtags[hashtags$rate > 0.5, ]
### finish ---------------------------------------------------------------------
db.res <- dbDisconnect(db.con)
rm(model, vocabulary, hashtags.original, hashtags.filter.2, hashtags.filter.3,
hashtags.filter.4, sql.data, sql.filter.1, sql.filter.2, db.con, db.res,
sql.places.cnt, data.classify, regex.url, regex.html, regex.user, removeRegEx)
dis.name <- disasters$comment[disNum]
cat(sprintf("Hashtags for `%s`:\n", dis.name))
print(hashtags$tag)
write(hashtags$tag, file=sprintf("./result/%02d-%s.txt", disNum, dis.name))
View(disasters)
for (disNum in c(2:4)) {
source('./10_hashtags.R')
dis.name <- disasters$comment[disNum]
cat(sprintf("Hashtags for `%s`:\n", dis.name))
print(hashtags$tag)
write(hashtags$tag, file=sprintf("./result/%02d-%s.txt", disNum, dis.name))
}
