从SQLite数据库中读取多个表的For循环开发者社区

文章/答案/技术大牛

发布

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()
}

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
  #....
  #....

问 从SQLite数据库中读取多个表的For循环 EN

回答 1

Stack Overflow用户