---
title: "Demo - use case 1: Querying tables across levels, visualize data and store query"
author: Moritz Mainka
date: 08 April 2025
format: pdf
editor: visual
---

## Introduction

The goal of this use case is to show how to query data across different levels (plot, sample, species) and visualize the data. We will use the `ggplot2` package for visualization and the `dplyr` package for data manipulation. This is just one way of handling data, the R language provides a myriad of other ways to do the same operations.

```{r}
#| echo: false
 
rm(list = ls())
cat("\014")


 library(knitr)
 opts_chunk$set(size="scriptsize")


```

## Load plot-info table

The first step is to load the plot-level information table. This table is central as it provides general information of plots. As a good practice, we start by assessing the table.

```{r}
#| echo: true

# load plot-level information table
plot_info <- read.csv("../data/plot_level_info.csv")


# as a first step, lets have a look at the table:
head(plot_info)

```

Next to plot_id, this table provides information on coordinates of the plots (lat-long), geology, environment (tundra (T), bird cliff (B), settlement (S)), etc. In total `{r} nrow(plot_info)` rows = unique plots have been sampled.

## Merge with other table on plot level

Let's do our first merge combining plot-level information with plot-level data. For this we will use the biomass data per plant functional type. Both tables are on the same level and therefore connected through one-to-one relationships. This means that each row in one table corresponds to one row in the other table.

```{r}
#| echo: true

# load data with plant cover per plant functional type
veg <- read.csv("../data/veg_pft_cover.csv")

# merge plot-level information with plot-level data using the plot_id column
plot_data <- merge(plot_info, veg, by = "plot_id", all.y = T)


```

Our merge was successful. Out of all `{r} nrow(plot_info)` existing plots, `{r} nrow(veg)` have recorded vegetation cover data. In addition, species richness and Shannon diversity indices are provided.

## Visualize plot-level data

```{r}
#| echo: true
#| warning: false

library(ggplot2)

# plot species richness against the share of bare_ground
ggplot(plot_data, aes(x = as.factor(geology), y = species_richness)) +
  geom_boxplot() +
  labs(title = "Plant species richness across geological parent material",
       x = "",
       y = "Species richness") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 70, hjust = 1))

```

## Combine plot with sample-level data

Now we want to combine the plot-level data with sample-level data. For this we will use the qPCR and soil chemistry data on sample-level.

```{r}
#| message: false
#| warning: false
#| echo: true

# let's first remove what we don't need anymore from the environment
rm(veg, plot_data)

library(dplyr)

# load qpcr data
qpcr <- read.csv("../data/microbio_qpcr.csv")

# merge plot-level data with site-level data using the site_id column
plot_qpcr <- merge(plot_info, qpcr, by = "plot_id", all.y = T)


# let's now add the sample-level data

# load sample level soil chemistry data and select relevant columns
soil <- read.csv("../data/soil_chem.csv") %>% 
  select(plot_id, sample_id, pH_CaCl2, CECeff_BaCl2)

# merge plot_data with soil sample data
query <- merge(plot_qpcr, soil, by = c("sample_id", "plot_id"), all.x = T)


```

## Plot data

Now, let's plot the queried data. Let's generate a plot showing the relationship of qPCR results and soil pH.

```{r}
#| echo: false
#| warning: false
#| message: false

library(viridis)

# filter for NAs
query <- query %>% filter(!is.na(geology))

# plot SOC against species richness
ggplot(query) +
  geom_point(aes(x = pH_CaCl2, y = X16s, color = factor(geology))) +
  geom_smooth(aes(x = pH_CaCl2, y = X16s), method = "loess", se = T) +
  labs(title = "Soil pH and 16S copies",
       x = "pH (-)",
       y = "Number of 16S copies") +
  theme_minimal() +
  scale_color_manual(name = "", values = viridis(length(levels(as.factor(query$geology))))) +
  theme(axis.text.x = element_text(angle = 70, hjust = 1))
```

It can be seen that there is an pH optimum for the number of 16S copies.

## Save table as csv

To finally export a query of the data, we can use the `write.csv` function.

```{r}
#| echo: true

# save table as csv
write.csv(query, file = "../demo/query_demo1.csv", row.names = F)


```
