Database operations with dplyr

After you have read in your data from Google Sheets in R, you often want to do further database operations. Typical database operations that you may want to perform are combining multiple tables (read from different links) into one, filtering particular sets of records, only specific variables or calculating new variables form existing variables. This can all be done well using functionsl from the dplyr package in R

In the example database, there are the following tables:
FactSectionCount: observations of animal counts on multiple dates in different sections along transects
DimSection: fixed properties of each section, such as their geographic location, and to which transect each section belongs
DimTransect: fixed properties of the transects, such as the study area where they are located, and the coordinates of start and end points
These data can be read and combined into a single file as follows:

###########R script

library(tidyverse)

# read the different tables from their published csv links
DimTransect<-readr::read_csv("https://docs.google.com/spreadsheets/d/e/2PACX-1vTtMDeI9k2M2xsukcZm-kE_gbYi3kYJDfbp1vEGKtpajM84GSFYrpd2U0P3kqgnbLQUKhhHciDZpV-j/pub?gid=801890265&single=true&output=csv")

DimSection<-readr::read_csv("https://docs.google.com/spreadsheets/d/e/2PACX-1vTtMDeI9k2M2xsukcZm-kE_gbYi3kYJDfbp1vEGKtpajM84GSFYrpd2U0P3kqgnbLQUKhhHciDZpV-j/pub?gid=1134090811&single=true&output=csv")

FactSectionAnimals<-readr::read_csv("https://docs.google.com/spreadsheets/d/e/2PACX-1vTtMDeI9k2M2xsukcZm-kE_gbYi3kYJDfbp1vEGKtpajM84GSFYrpd2U0P3kqgnbLQUKhhHciDZpV-j/pub?gid=1286117256&single=true&output=csv")

# join the data through a query using dplyr in a pipe and filter for a specific study area, and calculate the total count
AllData<-dplyr::left_join(FactSectionAnimals, DimSection, by="Section_ID") |>
dplyr::left_join(DimTransect, by="Transect_ID") |>
dplyr::filter(studyarea=="Loliondo Plains") |>
dplyr::mutate(TotalCount=CountLeft+ CountRight)

Page updated

Report abuse