I have some data on areal coverage that is stratified by depth and substrate type. I need to convert the areas into biomass for a species of interest. The area data looks like this:

library(tidyverse)
dat <- tibble::tribble(
                  ~model_seg, ~start_depth_m, ~substrate, ~area_m2,
                           1,              0,     "hard",        0,
                           1,              0,     "sand",   482500,
                           1,              0,     "soft",        0,
                           1,              1,     "hard",        0,
                           1,              1,     "sand",   870000,
                           1,              1,     "soft",        0,
                           1,              2,     "hard",        0,
                           1,              2,     "sand",   700000,
                           1,              2,     "soft",     5000
                  )
dat

I then have biomass estimates for each depth/substrate pairing that looks like this:

sub.dat <- tibble::tribble(
                         ~rnd_depth, ~substrate,      ~biomass_gm2,
                                  0,     "hard",  11.6378195672474,
                                  0,     "sand", 0.480829306398132,
                                  0,     "soft",  2.16687001205995,
                                  1,     "hard",  23.6843572281917,
                                  1,     "sand", 0.674134215616002,
                                  1,     "soft",  3.35885439288727,
                                  2,     "hard",  41.0456045259747,
                                  2,     "sand",  1.75564256550627,
                                  2,     "soft",  5.34840185637371
                         )
sub.dat

I need to convert the area value to a biomass value for each substrate/depth pair. The actual data contains substrate/depth pairs to 20 meters but for the example data the result should look like the variable answer in this dataset:

ans <- tibble::tribble(
  ~model_seg, ~start_depth_m, ~substrate, ~area_m2,  ~answer,
           1,              0,     "hard",        0,        0,
           1,              0,     "sand",   482500, 232082.5,
           1,              0,     "soft",        0,        0,
           1,              1,     "hard",        0,        0,
           1,              1,     "sand",   870000,   586380,
           1,              1,     "soft",        0,        0,
           1,              2,     "hard",        0,        0,
           1,              2,     "sand",   700000,  1232000,
           1,              2,     "soft",     5000,    26750
  )
# EDIT: Perhaps it would be helpful to know that I created the answer column manually like this: 
a <- c(0,482500*0.481,0,
       0,870000*0.674,0,
       0,700000*1.76,5000*5.35)
# this vector was simply added to the 'ans' data object but it shows the calulation (area * biomass)

I tried to use a previous question I posted about lookup tables: R- How do I use a lookup table containing threshold values that vary for different variables (columns) to replace values below those thresholds? But I am not competent enough with the replace function to adapt it. TIA

CodePudding user response：

If I understand you correctly...

You have two dataframes:

1. dat a table with "depth", "substrate type", and "area".
2. sub.dat a table with "depth", "substrate type", and "biomass_gm2" (the conversion factor from area to biomass for a particular species).

You want to use both "depth" and "substrate type" as a compound key to first look up the conversion factor (biomass_gm2) from sub.dat, then take the product of the conversion factor and area (biomass_gm2 * area) to calculate the final biomass.

require(dplyr)

left_join(dat, sub.dat, by = c("start_depth_m" = "rnd_depth", "substrate" = "substrate")) %>%
mutate(answer = area_m2 * biomass_m2)