limpar_dados_metro_sp.R

# baixar dados
library(tidyverse)
library(foreign)
library(readxl)
library(janitor)
library(fuzzyjoin)

# download.file("http://www.metro.sp.gov.br/pesquisa-od/arquivos/Pesquisa-Origem-Destino-2017-Banco-Dados.zip",
#               destfile = "od.zip")
# 
# dir.create("data/")
# 
# unzip("od.zip", exdir = "data")

# limpar dados

#### pasta banco de dados ####

df_geral <- foreign::read.dbf("data/banco de dados/Banco de dados/OD_2017.dbf") %>%
  as_tibble() %>%
  mutate_if(is.factor, as.character)

attr(df_geral, "NOME_TABELA") <- "Dados gerais"

df_zonas <- read_excel("data/banco de dados/Banco de dados/Corresp2007_2017.xlsx",
                       sheet = "Correspondência",
                       skip = 5) %>%
  select(COD_ZONA_2007 = 1,
         COD_ZONA = 2,
         NOME_ZONA = 3,
         COD_MUNICIPIO = 4,
         NOME_MUNICIPIO = 5,
         COD_DISTRITO = 6,
         NOME_DISTRITO = 7)

df_sub_regiao <- read_excel("data/banco de dados/Banco de dados/Corresp2007_2017.xlsx",
                            sheet = "Resumo",
                            skip = 6,
                            col_names = FALSE) %>%
  select(SUB_REGIAO = 1,
         #NOME_MUNICIPIO = 2,
         NOME_AREA_SP_CAPITAL = 3,
         ZONA_LIM_INF = 4,
         ZONA_LIM_SUP = 6) %>%
  fill(SUB_REGIAO, .direction = "down") %>%
  filter(!is.na(ZONA_LIM_INF))




df_zonas <- df_zonas %>%
  fuzzyjoin::fuzzy_left_join(df_sub_regiao,
                             by = c("COD_ZONA" = "ZONA_LIM_INF",
                                    "COD_ZONA" = "ZONA_LIM_SUP"),
                             match_fun = list(`>=`, `<=`)) %>%
  select(-c(ZONA_LIM_INF, ZONA_LIM_SUP))


attr(df_zonas, "NOME_TABELA") <- "Dados geográficos das zonas"


rm(df_sub_regiao)


df_dicionario_raw <- read_excel("data/banco de dados/Banco de dados/LAYOUT OD2017.xlsx",
                                sheet = "Layout",
                                skip = 3,
                                col_names = FALSE) %>%
  select(VAR_NOME = 2,
         VAR_CONTEUDO = 3,
         VAR_DE_PARA  = 7) %>%
  fill(VAR_NOME, VAR_CONTEUDO, .direction = "down")

df_dicionario_raw$VAR_DE_PARA[df_dicionario_raw$VAR_DE_PARA == "4 -Comércio"] <- "4 - Comércio"


df_dicionario_vars_com_codigo <- df_dicionario_raw  %>%
  mutate(VAR_DE_PARA = stringi::stri_trans_general(VAR_DE_PARA, "Latin-ASCII")) %>%
  filter(str_detect(VAR_DE_PARA, " - ")) %>%
  separate(VAR_DE_PARA, into = c("COD_RESPOSTA", "DESCRICAO_RESPOSTA"),
           sep = " - ", extra = "merge") %>%
  mutate(COD_RESPOSTA = as.integer(str_squish(COD_RESPOSTA)),
         DESCRICAO_RESPOSTA = str_squish(DESCRICAO_RESPOSTA))

df_dicionario <- df_dicionario_raw %>%
  distinct(VAR_NOME, VAR_CONTEUDO)

rm(df_dicionario_raw)

attr(df_dicionario, "NOME_TABELA") <- "Dicionário das variáveis da tabela Dados gerais"
attr(df_dicionario_vars_com_codigo, "NOME_TABELA") <- "Dicionários de variáveis da tabela de Dados Gerais encodificadas"


#### pasta tabelas ####

importar_tabela <- function(nome_aba,
                            nome_variavel_gather,
                            nrows_skip = 6,
                            arquivo_planilha = "data/banco de dados/Tabelas Gerais/Dados Gerais OD2017.xlsx"){
  
  nome_tabela_attr <- read_excel(arquivo_planilha,
                                 sheet = nome_aba,
                                 range = "A1",
                                 col_names = FALSE) %>%
    pull(1)
  
  dframe <-  read_excel(arquivo_planilha,
                        sheet = nome_aba,
                        skip = nrows_skip) %>%
    # renomar primeira coluna
    rename(COD_ZONA = 1) %>%
    # remover coluna de Total
    #select(-Total) %>%
    # excluir linha vazia
    #filter(row_number() > 1) %>%
    # formatar colnames
    mutate(COD_ZONA = as.integer(COD_ZONA)) %>%
    filter(!is.na(COD_ZONA)) %>%
    gather(!!enquo(nome_variavel_gather), QTD, -COD_ZONA) %>%
    filter(!str_detect(!!enquo(nome_variavel_gather), "Total")) %>%
    arrange(COD_ZONA)
  
  attr(dframe, "NOME_TABELA") <- nome_tabela_attr
  
  #rint(dframe)
  print(dframe %>% count(!!enquo(nome_variavel_gather)))
  dframe
  
  
}



df_tab_01 <- read_excel("data/banco de dados/Tabelas Gerais/Dados Gerais OD2017.xlsx",
                        sheet = "Tabela 1",
                        skip = 6) %>%
  rename(COD_ZONA = Zona) %>%
  filter(row_number() > 1) %>%
  rename_all(~ str_to_upper(janitor::make_clean_names(.))) %>%
  rename_at(vars(PRODUZIDAS, ATRAIDAS), ~ str_c("VIAGENS_", .)) %>%
  mutate(COD_ZONA = as.integer(COD_ZONA)) %>%
  filter(!is.na(COD_ZONA)) %>%
  arrange(COD_ZONA)

attr(df_tab_01, "NOME_TABELA") <- "Dados Gerais por Zona de Pesquisa – 2017"



df_tab_02 <- importar_tabela("Tabela 2", FAIXA_ETARIA)
df_tab_03 <- importar_tabela("Tabela 3", GRAU_INSTRUCAO)
df_tab_04 <- importar_tabela("Tabela 4", GENERO)

df_tab_05 <- importar_tabela("Tabela 5", FAIXA_RENDA)

df_tab_06 <- importar_tabela("Tabela 6", TIPO_RENDA)

df_tab_07 <- importar_tabela("Tabela 7", NUMERO_AUTOMOVEIS)

df_tab_08 <- importar_tabela("Tabela 8", VINCULO_EMPREGATICIO)

df_tab_09 <- importar_tabela("Tabela 9", CONDICAO_ATIVIDADE, 7)
x <- attr(df_tab_09, "NOME_TABELA")

df_tab_09 <- df_tab_09 %>%
  mutate(CONDICAO_ATIVIDADE = case_when(
    CONDICAO_ATIVIDADE == "Médica" ~ "Em Licença Médica",
    CONDICAO_ATIVIDADE == "Trabalho" ~ "Sem Trabalho",
    CONDICAO_ATIVIDADE == "Trabalhou" ~ "Nunca Trabalhou",
    CONDICAO_ATIVIDADE == "Casa" ~ "Dona de Casa",
    TRUE ~ CONDICAO_ATIVIDADE
  ))
attr(df_tab_09, "NOME_TABELA") <- x


df_tab_10 <- importar_tabela("Tabela 10", TIPO_ESCOLA)

df_tab_11 <- importar_tabela("Tabela 11", EMPREGO_POR_SETOR_ATIVIDADE) %>%
  filter(EMPREGO_POR_SETOR_ATIVIDADE != "...5")

df_tab_12 <- importar_tabela("Tabela 12", EMPREGOS_POR_CLASSE_ATIVIDADE, 7)
x <- attr(df_tab_12, "NOME_TABELA")


df_tab_12 <- df_tab_12 %>%
  mutate(EMPREGOS_POR_CLASSE_ATIVIDADE = case_when(
    EMPREGOS_POR_CLASSE_ATIVIDADE == "...2" ~ "Agrícola",
    EMPREGOS_POR_CLASSE_ATIVIDADE == "...3" ~ "Construção Civil",
    EMPREGOS_POR_CLASSE_ATIVIDADE == "...4" ~ "Indústria",
    EMPREGOS_POR_CLASSE_ATIVIDADE == "...5" ~ "Comércio",
    EMPREGOS_POR_CLASSE_ATIVIDADE == "...16" ~ NA_character_,
    TRUE ~ EMPREGOS_POR_CLASSE_ATIVIDADE
  )) %>%
  filter(!is.na(EMPREGOS_POR_CLASSE_ATIVIDADE))
attr(df_tab_12, "NOME_TABELA") <- x



df_tab_13 <- importar_tabela("Tabela 13", EMPREGOS_POR_VINCULO_EMPREGATICIO)

df_tab_14 <- importar_tabela("Tabela 14", ENDERECO_EMPREGOS, 5)

df_tab_15 <- importar_tabela("Tabela 15", EMPREGO_EXTERNO_INTERNO)

df_tab_16 <- importar_tabela("Tabela 16", MODO_PRINCIPAL) %>%
  filter(MODO_PRINCIPAL != "...16")

df_tab_17 <- importar_tabela("Tabela 17", TIPO_VIAGEM, 7)
x <- attr(df_tab_17, "NOME_TABELA")


df_tab_17 <- df_tab_17%>%
  mutate(TIPO_VIAGEM = case_when(
    TIPO_VIAGEM == "Motorizado...4" ~ "Modo Motorizado",
    TIPO_VIAGEM == "Motorizado...5" ~ "Modo Não Motorizado",
    TRUE ~ TIPO_VIAGEM
  ))
attr(df_tab_17, "NOME_TABELA") <- x


df_tab_18 <- importar_tabela("Tabela 18", MOTIVO_VIAGEM, 7)
x <- attr(df_tab_18, "NOME_TABELA")

df_tab_18 <- df_tab_18 %>%
  mutate(MOTIVO_VIAGEM = case_when(
    MOTIVO_VIAGEM %in% c("Indústria", "Comércio", "Serviços") ~ str_c("Trabalho ", MOTIVO_VIAGEM),
    MOTIVO_VIAGEM == "Emprego" ~ "Procurar Emprego",
    MOTIVO_VIAGEM == "Pessoais" ~ "Assuntos Pessoais",
    TRUE ~ MOTIVO_VIAGEM
  ))
attr(df_tab_18, "NOME_TABELA") <- x


df_tab_19 <- importar_tabela("Tabela 19", RAZAO_VIAGEM_A_PE)

df_tab_20 <- importar_tabela("Tabela 20", TEMPO_MEDIO_VIAGEM)

df_tab_21 <- importar_tabela("Tabela 21", VIAGENS_ATRAIDAS_MODO_PRINCIPAL) %>%
  filter(VIAGENS_ATRAIDAS_MODO_PRINCIPAL != "...16")

df_tab_22 <- importar_tabela("Tabela 22", VIAGENS_ATRAIDAS_POR_TIPO) %>%
  filter(VIAGENS_ATRAIDAS_POR_TIPO != "...6")

df_tab_23 <- importar_tabela("Tabela 23", VIAGENS_ATRAIDAS_POR_MOTIVO, 7)
x <- attr(df_tab_23, "NOME_TABELA")

df_tab_23 <- df_tab_23 %>%
  mutate(VIAGENS_ATRAIDAS_POR_MOTIVO = case_when(
    VIAGENS_ATRAIDAS_POR_MOTIVO %in% c("Indústria", "Comércio", "Serviços") ~ str_c("Trabalho ", VIAGENS_ATRAIDAS_POR_MOTIVO),
    VIAGENS_ATRAIDAS_POR_MOTIVO == "Emprego" ~ "Procurar Emprego",
    VIAGENS_ATRAIDAS_POR_MOTIVO == "Pessoais" ~ "Assuntos Pessoais",
    TRUE ~ VIAGENS_ATRAIDAS_POR_MOTIVO
  )) %>%
  filter(VIAGENS_ATRAIDAS_POR_MOTIVO != "...12")
attr(df_tab_23, "NOME_TABELA") <- x


#### tabelas origem-destino ####
importar_tab_orig_dest <- function(arquivo_planilha){
  
  nome_tabela_attr <- read_excel(arquivo_planilha,
                                 range = "A2",
                                 col_names = FALSE) %>%
    pull(1)
  
  dframe <- read_excel(arquivo_planilha, skip = 7) %>%
    rename(COD_ZONA_ORIGEM = 1) %>%
    gather(COD_ZONA_DESTINO, QTD, -COD_ZONA_ORIGEM) %>%
    mutate_at(vars(COD_ZONA_ORIGEM, COD_ZONA_DESTINO), as.integer) %>%
    filter(!is.na(COD_ZONA_ORIGEM) & !is.na(COD_ZONA_DESTINO))
  
  
  attr(dframe, "NOME_TABELA") <- nome_tabela_attr
  dframe
  
}


# df_tab_24 <- read_excel("data/banco de dados/Tabelas Gerais/Tab24_OD2017.xlsx",
#                         skip = 7) %>%
#   rename(COD_ZONA_ORIGEM = 1) %>%
#   gather(COD_ZONA_DEST, QTD, -COD_ZONA_ORIGEM) %>%
#   mutate_at(vars(COD_ZONA_ORIGEM, COD_ZONA_DEST), as.integer) %>%
#   filter(!is.na(COD_ZONA_ORIGEM) & !is.na(COD_ZONA_DEST))

df_tab_24 <- importar_tab_orig_dest("data/banco de dados/Tabelas Gerais/Tab24_OD2017.xlsx")
df_tab_25 <- importar_tab_orig_dest("data/banco de dados/Tabelas Gerais/Tab25_OD2017.xlsx")
df_tab_26 <- importar_tab_orig_dest("data/banco de dados/Tabelas Gerais/Tab26_OD2017.xlsx")
df_tab_27 <- importar_tab_orig_dest("data/banco de dados/Tabelas Gerais/Tab27_OD2017.xlsx")
df_tab_28 <- importar_tab_orig_dest("data/banco de dados/Tabelas Gerais/Tab28_OD2017.xlsx")
df_tab_29 <- importar_tab_orig_dest("data/banco de dados/Tabelas Gerais/Tab29_OD2017.xlsx")
df_tab_30 <- importar_tab_orig_dest("data/banco de dados/Tabelas Gerais/Tab30_OD2017.xlsx")

# save every dataframe into a list
names_of_dataframes <- str_subset(ls(), "df_")

list_of_dfs <- names_of_dataframes %>% map(get)


bug <- list_of_dfs %>% map(~ attr(., "NOME_TABELA")) %>% map_dbl(is.null)

#### create a sqlite file with each dataframe as a table ---
library(RSQLite)
library(janitor)
# create SQLITE file
con <- dbConnect(RSQLite::SQLite(), dbname = "DB_ORIGEM_DESTINO_SP")
# write dataframe to table, using dataframe attribute as table name
dir.create("data/output")

for (i in seq_along(list_of_dfs)){
  x <- list_of_dfs[[i]]
  tb_name <- make_clean_names(attr(x, "NOME_TABELA"))
  
  dbWriteTable(
    con,
    name = tb_name,
    value = x
  )
  write_csv(x, path = str_c("data/output/", tb_name, ".csv"))
  
}

dbListTables(con)
dbDisconnect(con)