ifelse sqlite

library(DBI)
library(openxlsx)
library(tidyverse)
library(magrittr)
library(hablar)
con <- dbConnect(RSQLite::SQLite(), "~/Downloads/phone.db")
dbListTables(con)
phones <- dbGetQuery(con, "select * from phones")
#phones <- tbl(con,sql("select * from phones")) %>% collect()
regions <- dbGetQuery(con, "select * from regions")
phones %<>%
  left_join(regions,by=c('region_id'='id')) %>%
  select(number,province,city,zip_code,area_code)
dbDisconnect(con)

df2015 <- read.xlsx('./Downloads/甘肃全国12320数据至-20200223.xlsx',sheet = 1) %>%
  mutate(year=2015)
df2016 <- read.xlsx('./Downloads/甘肃全国12320数据至-20200223.xlsx',sheet = 2) %>%
  mutate(year=2016)
df2017 <- read.xlsx('./Downloads/甘肃全国12320数据至-20200223.xlsx',sheet = 3) %>%
  mutate(year=2017)
df2018 <- read.xlsx('./Downloads/甘肃全国12320数据至-20200223.xlsx',sheet = 4) %>%
  mutate(year=2018)
df2019 <- read.xlsx('./Downloads/甘肃全国12320数据至-20200223.xlsx',sheet = 5) %>%
  mutate(year=2019)
df2020 <- read.xlsx('./Downloads/甘肃全国12320数据至-20200223.xlsx',sheet = 6) %>%
  mutate(year=2020)


# df12320 <- rbind(df2015,df2016,df2017,df2018,df2019,df2020) %>%
#   mutate(来电号码=if_else(str_detect(来电号码,'^1[0-9]{10}'),来电号码,'NA')) %>%
#   mutate(来电号码=if_else(is.na(来电号码),'NA',来电号码)) %>%
#   mutate(sj=str_sub(来电号码,1,7)) %>%
#   convert(int(sj))

df12320 <- rbind(df2015,df2016,df2017,df2018,df2019,df2020) %>%
  mutate(号码=ifelse(str_detect(来电号码,'^1[0-9]{10}'),来电号码,NA)) %>%
  mutate(sj=str_sub(来电号码,1,7)) %>%
  convert(int(sj)) %>%
  left_join(phones,by=c('sj'='number'))

# str_detect(df12320$来电号码,'^1\\d{10}')

# df12320 %<>% left_join(phones,by=c('sj'='number'))
write.xlsx(df12320,'./Downloads/df12320.xlsx')