<commit_before># If no RSQLite
install.packages("RSQLite")
library(dplyr)
library(RSQLite)

# Make connection to the datbase
mammals <- src_sqlite("portal_mammals.sqlite")

tbl(mammals, sql("SELECT year, species_id FROM surveys")) %>%
  collect()

surveys <- tbl(mammals, "surveys")

## Challenge:
# Pull the other two tables into R.  Name them plots and species

plots <- tbl(mammals,"plots")
species <- tbl(mammals, "species")

yr_species <- surveys %>%
  select(year, species_id)

# What is happening behind the curtain
explain(surveys %>%
          select(year, species_id))

## Challenge
# SELECT species_id, year, weight 
# FROM surveys 
# ORDER BY year;

surveys_syw <- surveys %>%
  select(species_id, year, weight) %>%
  arrange(desc(year))

tbl1 <- data.frame(id = c(1,2,3,4), number = c(37,28,65,2))
tbl2 <- data.frame(id = c(3,4,5,6), letters = c("a","r","q","j"))


#Join plots to surveys
surveys_pl1 <- left_join(surveys, plots, by = "plot_id") %>%
  filter(plot_id == 1) %>%
  select(month, day, year, species_id, plot_type) %>%
  collect()
<commit_msg>update script<commit_after># If no RSQLite
install.packages("RSQLite")
library(dplyr)
library(RSQLite)

# Make connection to the datbase
mammals <- src_sqlite("portal_mammals.sqlite")

tbl(mammals, sql("SELECT year, species_id FROM surveys")) %>%
  collect()

surveys <- tbl(mammals, "surveys")

## Challenge:
# Pull the other two tables into R.  Name them plots and species

plots <- tbl(mammals,"plots")
species <- tbl(mammals, "species")

yr_species <- surveys %>%
  select(year, species_id)

# What is happening behind the curtain
explain(surveys %>%
          select(year, species_id))

## Challenge
# SELECT species_id, year, weight 
# FROM surveys 
# ORDER BY year;

surveys_syw <- surveys %>%
  select(species_id, year, weight) %>%
  arrange(desc(year))

tbl1 <- data.frame(id = c(1,2,3,4), number = c(37,28,65,2))
tbl2 <- data.frame(id = c(3,4,5,6), letters = c("a","r","q","j"))


#Join plots to surveys
surveys_pl1 <- left_join(surveys, plots, by = "plot_id") %>%
  filter(plot_id == 1) %>%
  select(month, day, year, species_id, plot_type) %>%
  collect()
surveys_pl1

## Challenge
# Use dplyr and query our database to return the number of rodents 
# observed in each plot in each year.


{'fraction_non_alphanumeric': 0.15733803437637725, 'fraction_numerical': 0.017188188629352136, 'mean_word_length': 4.761421319796955, 'pattern_counts': {'":': 0, '<': 21, '<?xml version=': 0, '>': 19, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6759041', 'n_tokens_mistral': 890, 'n_tokens_neox': 817, 'n_words': 218}