CODEWITHSUNDEEP
rdataframelapplyrbind
OnlyDean
OnlyDean

Reputation: 1039

R -- Cannot rbind In lapply function

I start off by building an empty data frame:

results <- data.frame(ID=numeric(0), StartDate=numeric(0), term_type=character(0), EndDate=numeric(0), stringsAsFactors = FALSE)

I then have a list of unique ID numbers: uniqueIds <- c(1234, 4566, 7838)

I have a function getDataForGivenId that produces a data frame in the format:

ID, StartDate, term_type, EndDate

I need the code to call the function getDataForGivenId for each ID and append the resulting data frame to the empty dataframe results.

I have tried:

library(dplyr)

results <- bind_rows(results, (lapply(uniqueIds, getDataForGivenId)))

and

do.call("rbind", lapply(uniqueIds, getDataForGivenId))

and

for (Id in uniqueIds) {
    Y <- getDataForGivenId(Id)
    results <- rbind(results, Y) 
}

Everytime I just end up with an empty results dataframe.

Note that if I take things out of the loop and simply execute the code:

Y <- getDataForGivenId(1234)
results <- rbind(results, Y)

I get the output I expect.

Does anyone know what I'm doing wrong?

EDIT -- My full script is below.

library(dplyr)
library(lubridate)

enVariables <- Sys.getenv()
username    <- enVariables[["DB_USERNAME"]]
password    <- enVariables[["DB_PASSWORD"]]

results <- data.frame(ID=numeric(0), StartDate=numeric(0), term_type=character(0), EndDate=numeric(0), stringsAsFactors = FALSE)

getConnection <- function(){
  require(RMySQL)
  username <- username
  password <- password 
  con <- dbConnect(
    MySQL(), user=username, password=password, 
    dbname='database', host='host', port=port
  )
  return(con)
}

queryuniqueIds <- "SELECT DISTINCT(id) FROM table LIMIT 5"
con                <- getConnection()
uniqueIds      <- dbGetQuery(con, queryuniqueIds)
dbDisconnect(con)

getDataForGivenID <- function(idNumber) {
  queryData <- paste0(
    "SELECT ",
    "Id, bill_date, bill_hour ",
    "FROM table ",
    "WHERE id = ", idNumber
  ) 
  con <- getConnection()
  Data <- dbGetQuery(con, queryData)
  dbDisconnect(con)
  X <- Data %>% 
    select(ID, bill_date, bill_hour) %>%
    mutate(
      bill_date_x = ymd_hms(bill_date)
    ) %>% 
    arrange(ID, bill_date, bill_hour)
  hour(X$bill_date_x) <- X$bill_hour
  X <- X %>% 
    mutate(
      lag_x     = lag(bill_date_x, 1),
      lag_diff  = difftime(bill_date_x,lag_x, units = "hours") %>% as.integer(),
      lead_x    = lead(bill_date_x, 1),
      lead_diff = difftime(lead_x, bill_date_x, units = "hours") %>% as.integer()
    )
  Y <- X %>% 
    filter(
      is.na(lag_diff) | 
        is.na(lead_diff) | 
        !(lag_diff == 1 & lead_diff == 1),

      is.na(lag_diff) | 
        is.na(lead_diff) | 
        !(lag_diff == 0 | lead_diff == 0)
    ) %>% 
    mutate(
      term_type = "N",
      term_type = replace(term_type, lead_diff == 1, "S"),
      term_type = replace(term_type, lag_diff  == 1, "E")
    )
  Y <- Y %>%
    select(ID, bill_date_x, term_type) %>% 
    mutate(
      lead_date = lead(bill_date_x, 1)
    )  %>% 
    filter(term_type == "S")
  colnames(Y) <- c("ID", "StartDate", "term_type", "EndDate")
  return(Y)
}

do.call("rbind", lapply(uniqueIds, getDataForGivenID))
View(results)

Upvotes: 0

Views: 220

Answers (1)

OnlyDean
OnlyDean

Reputation: 1039

I finally figured out my problem.

The list uniqueIds was of length 1. R was passing in the entire list as once, causing the SQL statement to only return the data for the first id.

I changed 

uniqueIds <- dbGetQuery(con, queryuniqueIds)

to

uniqueIds <- as.data.frame(dbGetQuery(con, queryuniqueIds))

and

do.call("rbind", lapply(uniqueIds, getDataForGivenId))

to

results <- do.call("rbind", lapply(uniqueIds$id, getDataForGivenId))

Things now work as expected. Thank you to those who helped.

Upvotes: 1

Related Questions