---
title: "Demo - use case 2: Querying data on species and sample level"
author: Moritz Mainka
date: 09 April 2025
format: pdf
editor: visual
---

## Introduction

The goal of this script is to show how to query data from sample and species level at the same time. Further, we will have a look at how to aggregate, visualize, and store this query.

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



```

## Load plot-info table

The first step is to load the plot-level information table. This table is central as it functions as linking table between sample and species level. As a good practice, we start by assessing the table.

```{r}
#| echo: true

# load plot-level information table
# this table is central as it connects site data down to the sample or species level
plot_info <- read.csv("../data/plot_level_info.csv")

head(plot_info)
# str(plot_info)
# summary(plot_info)


```

## Aggregate species-level data on plot-level

We will now aggregate species-level data on plot-level. For this we will use the SLA (specific leaf area) data for *Saxifraga cespitosa* and calculate the mean SLA. Then we will do the same for soil chemistry (pH and CEC) and combine both datasets.

```{r}
#| echo: true

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


# aggregate mean sla for Saxifraga cespitosa
spec_sla <- aggregate(sla ~ plot_id, data = veg[veg$species == "Saxifraga cespitosa", ], FUN = mean)


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

# repeat process for sample information
soil <- read.csv("../data/soil_chem.csv")

# aggregate TC
soil_agg <- aggregate(cbind(pH_CaCl2, CECeff_BaCl2) ~ plot_id, data = soil, FUN = mean)

# merge plot-level information with plot-level data using the plot_id column
soil_veg <- merge(plot_data, soil_agg, by = "plot_id", all.x = T)

```

The resulting dataframe has `{r} nrow(soil_veg)` rows.

## Create plot of aggregated data

Let's create a simple scatterplot to visualize our query:

```{r}
#| echo: true

# plot of SLA with pH
library(ggplot2)
library(viridis)

ggplot(soil_veg) +
  geom_point(aes(x = pH_CaCl2, y = sla, color = as.factor(geology))) +
  geom_smooth(aes(x = pH_CaCl2, y = sla), method = "loess", se = FALSE) +
  labs(title = "Relation of specific leaf area (SLA) of Saxifraga cespitosa\nand soil pH",
       x = "pH (-)",
       y = "Specific Leaf Area (SLA)") +
  scale_color_manual(name = "", values = viridis(length(levels(as.factor(soil_veg$geology))))) +
  theme_minimal()


```

## Save table as csv

Finally, let's save our query as a csv file.

```{r}
#| echo: true

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

 
```
