Insert value from multiple dataframes into another dataframe, based on column values of the other dataframe in R

user_12009013 0
Viewed 57

I have a large dataframe (called dftot) with multiple environmental variable columns, including depth, salinity and treatment locations. The same treatment locations are used multiple times. To simplify:

 Depth<-c(1,4,33,7,8,20,12,8)
 Treatment<- c("1.1", "1.2", "1.3", "2.1", "2.2", "2.3", "1.1", "1.2")
 dftot<- data.frame(Depth, Treatment)

And a (for now) empty column for salinity:

 dftot[, "Salinity"] <- NA

Furthermore, for each treatment location I have a dataframe containing Depth and Salinity. Depths go from 1-40 and I will give the salinity random numbers here. For treatment 1.1 it looks something like:

Depth11<- c(1:40)
Salinity11 <- c(data$newrow <- sample(40, size = nrow(data), replace = TRUE)
tr11<-  data.frame(Depth11, Salinity11)

What I need is a piece of code that for each treatment in dftot, selects the corresponding treatment dataframe and fills in the salinity value of that treatment dataframe into the empty salinity column in dftot, based on corresponding Depths.

As I have to do this for multiple treatments it would be ideal to have some sort of loop I think. But if this is not possible I could run the code for each treatment as well.

Greatly appreciated if someone can help me out!

insert r select loops
1 Answers

here is a possible apporach using some made up sample data for the trXX data.frames

# create some sample data
Depth<-c(1,4,33)
Treatment<- c("1.1", "1.2", "1.3")
dftot <- data.frame(Depth, Treatment)
set.seed(123)
tr11 <- data.frame(Depth = 1:40, Salinity = sample(1:100, 40))
tr12 <- data.frame(Depth = 1:40, Salinity = sample(1:100, 40))
tr13 <- data.frame(Depth = 1:40, Salinity = sample(1:100, 40))

## now we code
library(tidyverse)
# build list of all loaded tr.. dataframes
lookup <- mget(ls()[grepl("^tr", ls())]) %>%
  dplyr::bind_rows(.id = "Treatment")
# now operate
dftot %>%
  # get the treamtnet into dftot
  dplyr::mutate(Treatment2 = paste0("tr", gsub("\\.","", Treatment))) %>%
  # join in data from the df.lookup
  dplyr::left_join(lookup, by = c("Treatment2" = "Treatment", "Depth"))

#   Depth Treatment Treatment2 Salinity
# 1     1       1.1       tr11       31
# 2     4       1.2       tr12       13
# 3    33       1.3       tr13       80
user_63562780
Related