For loop to read in multiple tables from SQLite database

Question

I would like to create a for loop that reads in multiple tables from a SQLite database. I would like it to either read the first 300 tables, but ideally I would like to get it to read 300 random tables from my database into R.

For each table read in, I would like it to go through the written code, save the graph at the end then start over with a new table. If possible I would like the all of the tables to be on the same graph. I have written the code for a single table, but I am unsure as to how I could proceed from here.

for (i in 1:300){
# Reads the selected table in database
ind1 <- dbReadTable(mydb, i)

# Formats the SQL data to appropriate R data structure
cols <- c("Mortality", "AnimalID", "Species", "Sex", "CurrentCohort", 
          "BirthYear", "CaptureUnit","CaptureSubunit",
          "CaptureArea", "ProjectName")
ind[cols] <- lapply(ind[cols], factor)  ## as.factor() could also be used
ind$DateAndTime <- as.POSIXct(ind$DateAndTime, tz = "UTC",
                               origin = '1970-01-01')

# Converts the Longitude and Latitude to UTMs
ind <- convert_utm(ind1)

ind_steps <- ind %>% 
  # It's always a good idea to *double check* that your data are sorted
  # properly before using lag() or lead() to get the previous/next value.
  arrange(AnimalID, DateAndTime) %>% 
  # If we group_by() AnimalID, lead() will insert NAs in the proper
  # places when we get to the end of one individual's data and the beginning
  # of the next
  group_by(AnimalID) %>% 
  # Now rename our base columns to reflect that they are the step's start point
  rename(x1 = utm_x, 
         y1 = utm_y, 
         t1 = DateAndTime) %>% 
  # Attach the step's end point
  mutate(x2 = lead(x1),
         y2 = lead(y1),
         t2 = lead(t1)) %>% 
  # Calculate differences in space and time
  mutate(dx = x2 - x1,
         dy = y2 - y1,
         DateAndTime = as.numeric(difftime(t2, t1, units = "hours"))) %>% 
  # Calculate step length
  mutate(sl = sqrt(dx^2 + dy^2)) %>% 
  # Calculate absolute angle
  mutate(abs_angle = (pi/2 - atan2(dy, dx)) %% (2*pi)) %>% 
  # Calculate relative angle
  mutate(rel_diff = (abs_angle - lag(abs_angle)) %% (2*pi),
         rel_angle = ifelse(rel_diff > pi, rel_diff - 2*pi, rel_diff)) %>% 
  # Drop this uneccesary column
  select(-rel_diff) %>% 
  # Drop incomplete final step
  filter(!is.na(x2))

ind_steps <- ind_steps %>% 
  mutate(NSD = (x2 - x1[1])^2 + (y2 - y1[1])^2)

# Plot NSD
ind_steps %>% 
  ggplot(aes(x = t2, y = NSD)) +
  geom_line() +
  theme_bw()
}

Any help would be greatly appreciated!

Ronak Shah · Accepted Answer · 2021-04-10 02:20:42Z

2

If there are 1000 tables you can use sample to get random 300 from them, create a list with length 300 to store the plots and if you want to plot them together you can use cowplot::plot_grid.

random_tables <- sample(1000, 300, replace = TRUE)
plot_list <- vector('list', 300)

for (i in seq_along(random_tables)){
  # Reads the selected table in database
  ind1 <- dbReadTable(mydb, random_tables[i])
  
  #...Rest of the code
  #....
  #....
  # Plot NSD
  plot_list[[i]] <- ggplot(ind_steps, aes(x = t2, y = NSD)) + 
                    geom_line() + theme_bw()
}

cowplot::plot_grid(plotlist = plot_list, nrow = 30, ncol = 10)

answered Apr 10, 2021 at 2:20

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

For loop to read in multiple tables from SQLite database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related