rsqlite table and lubridate

Question

I tried to deal with the table in r database. The code I wrote is as follows.

con <- dbConnect(RSQLite::SQLite(), "flights.sqlite")
airports1 <- dplyr::tbl(con, "airports")
flights1 <- dplyr::tbl(con, "flights")
airportcounts <- flights1 %>% filter(Dest %in% c("SNA", "SJC", "SMF")) %>% group_by(Dest, Year, Month) %>% summarise(count = n())

Untill this line, there were no problems. The table "flights" is already written and I used dplyr functions to summarise the counts. After doing this, I tried to make a new column, 'Date' with 'Year' and 'Month' column, the code I wrote is as follows.

airportcounts %>% mutate(Date = lubridate::make_date(Year, Month))

However, I got an error message:

'Error in vapply(list(year, month, day), length, 1, USE.NAMES = FALSE) : 
  object 'Year' not found'

I have to do this job in the table airportcounts, not in the form of tibble or data.frame. How can I solve this problem?

(The code I wrote worked well for tibble data)

Answer 1

SQLite doesn't know how to deal with dates. You need to load the data into R for working with dates:

airportcounts %>% collect() %>% mutate(...)

Also, summarize() retains a part of the grouping, I recommend to ungroup() right after summarize().