I am trying to add a column to a data frame (df1) from another data frame (df2), but only when the "depth range" from df1 lies within the "depth range" from df2. I'll explain below what the depth interval represent:
df1 <-data.frame(location_ID = c("Location_01", "Location_01","Location_01", "Location_02", "Location_02","Location_02"),
start_1 = c(0,5, 15, 0, 2.5, 5),
end_1 = c(5,15, 25, 2.5,5, 20),
value = c(3.00, 3.75, 3.30, 3.25, 4.15, 4.25))
df2 <-data.frame(location_ID = c("Location_01", "Location_01", "Location_02", "Location_02"),
start_2 = c(0, 10, 0, 5),
end_2 = c(10, 25, 5, 20),
text_description = c("First Description (Location 1)", "Second Description (Location 1)",
"First Description (Location 2)", "Second Description (Location 2)"))
In terms of the example data frames above, I would like to add the data from the "text_description" column in df2 to it's matching "location_ID" in df1, but only where the "start_1" and "end_1" columns are contained within the "start_2" and "end_2" columns.
For example, the first row of df1 should be assigned the text description in the first row of df2, since in this case the depth range from df2 associated with that description is 0-10, which includes the 0-5 range in the first row of df1
However, it gets more complicated in cases like the second row of df1, where the depth range (5-15) is not entirely included in one single depth range from df2. In these cases, I would like to assign the text value based on where the majority of that interval lies
Any advice as to how to go about this would be appreciated
I have tried this so far, based on a similiar post, but it does not work since the two data frames do not have the same number of rows (df2 is going to have less rows, since it has larger depth ranges than df1)
test_df <- df1 %>%
mutate(text = case_when(df2$start_2 <= start_1 & df2$end_2 >= end_1 ~ df2$text_description ))
