parse irregular data

# (library後面註解是該套件會用到的函數)
library(data.table) # fread, data.table, setnames
library(pipeR)      # %>>%
library(plyr)       # rlply
library(dplyr)      # mutate
library(stringr)    # str_replace_all, str_c

readLines(textConnection('sam 18; boy
paul; 20 boy
charlotte; 21; girl
christy 17 girl')) %>>%
  str_replace_all("([a-zA-Z0-9]);? ", "\\1,") %>>%
  # str_replace_all比gsub適合用在pipeR
  str_c(collapse = "\n") %>>% fread %>>% # str_c = paste
  setnames(c("name", "age", "sex")) %>>%
  mutate(age = as.integer(age))
#        name age  sex
# 1       sam  18  boy
# 2      paul  20  boy
# 3 charlotte  21 girl
# 4   christy  17 girl

# 用兩百萬個row測試看看：
# data generation
textData <- raply(5e5, 'sam 18; boy
paul; 20 boy
charlotte; 21; girl
christy 17 girl') %>>% str_c(collapse = "\n")

library(microbenchmark)
microbenchmark(test = textData %>>% str_replace_all("([a-zA-Z0-9]);? ", "\\1,") %>>%
                 # str_replace_all比gsub適合用在pipeR
                 str_c(collapse = "\n") %>>% fread %>>% # str_c = paste
                 setnames(c("name", "age", "sex")) %>>%
                 mutate(age = as.integer(age)), times = 10L)
# Unit: seconds
# expr      min       lq     mean   median       uq      max neval
# test 4.489137 4.520529 4.528179 4.529275 4.543055 4.557189    10

# 平均一次4.5秒左右，全距也很小，所以兩百萬列不是問題



pattern <- str_c("([a-z]*);?\\s*", "(\\d*)?;?\\s*", "(boy|girl)?;?\\s*",
                 "(\\d{4}:\\d{2}:\\d{2}:\\d{2})?")
# 第一個是第一欄位，第二個是第二欄位，依此類推
readLines(textConnection('sam;16;boy; 2015:00:09:59
green;18;girl;
paul;20; 2015:00:09:59
charlotte; 21;')) %>>% str_replace_all(pattern, "\\1, \\2, \\3, \\4") %>>%
  str_replace_all(", , , ", "") %>>% str_c(collapse = "\n") %>>%
  fread(na.string="") %>>% setnames(c("name", "age", "sex", "time")) %>>%
  mutate(age = as.integer(age))
# name age  sex          time
# 1       sam  16  boy 2015:00:09:59
# 2     green  18 girl          <NA>
# 3      paul  20 <NA> 2015:00:09:59
# 4 charlotte  21 <NA>          <NA>

# 這裡要為什麼要取代", , , "，我暫時想不出來為什麼
# 這裡不知道為什麼經過regex後，每一列會多出三個逗號，讓我匪夷所思QQ

# 用兩百萬個row測試看看：
# data generation
textData <- raply(5e5, 'sam;16;boy; 2015:00:09:59
green;18;girl;
paul;20; 2015:00:09:59
charlotte; 21;') %>>% str_c(collapse = "\n")

library(microbenchmark)
microbenchmark(test = readLines(textConnection(textData)) %>>%
                 str_replace_all(pattern, "\\1, \\2, \\3, \\4") %>>%
                 str_replace_all(", , , ", "") %>>% str_c(collapse = "\n") %>>%
                 fread(na.string="") %>>% setnames(c("name", "age", "sex", "time")) %>>%
                 mutate(age = as.integer(age)), times = 10L)
# Unit: seconds
# expr      min      lq    mean   median       uq      max neval
# test 10.93214 10.9493 10.9683 10.96072 10.98294 11.04314    10

# 平均一次11秒左右，全距也很小，所以兩百萬列不是問題