r_sql.R.txt

# 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.
# Plot Id, Year, Count of Rodents
rodent_count <- inner_join(surveys,species, by = "species_id") %>%
  filter(taxa == "Rodent") %>%
  group_by(plot_id, year) %>%
  summarize(num_rodents = n()) %>%
  arrange(desc(num_rodents))
rodent_count
  
  
# Create a new sqlite database from some dataframes
surveys_csv <- read.csv("surveys.csv")
species_csv <- read.csv("species.csv")

new_portal_db <- src_sqlite("dplyr_sqlite_portal.sqlite", 
                            create = TRUE)
copy_to(new_portal_db, surveys_csv, temporary = FALSE)