R/query fdic db.R

In william-rinauto/fdicDB: Build and Query FDIC Data

fdicQuery <- function(dbName, vars = c(), yearStart, quarterStart, yearStop, quarterStop){

  db <- dbConnect(RSQLite::SQLite(), dbName)

  #browser()
  #Correspondence of var names to table
  matching <- dbGetQuery(db,
                         "SELECT * FROM matchingTable")

  matching <- matching %>%
    mutate(varNames = tolower(varNames)) %>%
    #Remove fields that are going to be queried anyway
    filter(!(varNames %in% c('cert','repdte','name','stalp')))


  matching <- matching %>%
    filter(varNames %in% vars) %>%
    mutate(yearQuarter = as.numeric(paste0(year,quarter)))


  quarterConvert <- function(x){

    case_when(x == 1 ~ '0331',
              x == 2 ~ '0630',
              x == 3 ~ '0930',
              x == 4 ~ '1231')
  }

  quarterStart <- quarterConvert(quarterStart)
  quarterStop <- quarterConvert(quarterStop)

  dateRange <- as.numeric(paste0(yearStart,quarterStart)):as.numeric(paste0(yearStop,quarterStop))


  matching <- filter(matching, yearQuarter %in% dateRange)

  datalist <- list()
  t <- 1
  for(i in unique(matching$yearQuarter)){

    data <- matching %>%
      dplyr::filter(yearQuarter == i)

    datalist1 <- list()
    v <- 1
    for(j in unique(data$tableName)){

      innerVars <- data %>%
        filter(tableName == j)


      data1 <- dplyr::tbl(db, j) %>%
        select(cert, repdte, name, stalp, innerVars$varNames) %>%
        as.data.frame()

      datalist1[[v]] <- data1
      v <- v + 1
    }

    quarterCrunch <- reduce(datalist1, dplyr::left_join)

    datalist[[t]] <- quarterCrunch
    t <- t+1
  }

  outData <- dplyr::bind_rows(datalist)

  return(outData)
}