How to sample from smaller data frame with multiple conditions to a larger data frame?

user_6820344 0
Viewed 57

I have a main dataset df.main with 3 sites and each site has 3 subsites, that were samlpled over three months. I have a separate dataset with some abiotic variables ONLY for a single month df.sample. But for each site, I have three values from the sub-sites. In my original dataset, I need to add the abitoic column. However, for every month, for each sub-site I only want to SAMPLE with replacement from one of the three samples from the sub.site.

set.seed(111)

##Main Data Set
month <- rep(c("Jan","Feb","Mar"), each =9 )
site <- rep(c("1","2","3","1","2","3","1","2","3"), each = 3)
sub.site <- rep(c(1,2,3,1,2,3,1,2,3), time = 3 )

df.main <- data.frame(month, site, sub.site)
  month site sub.site
Jan    1        1
Jan    1        2
Jan    1        3
Jan    2        1
Jan    2        2
...     ..      ..
Mar    3        3

##Sampler Data Set
site <- rep(c(1,2,3), time = 9)
sub.site <- rep(c(1,1,1,2,2,2,3,3,3), each = 3)
abiotic <- rnorm(27,7,1)
df.sample <- data.frame(site, sub.site, abiotic)

  site sub.site  abiotic
 1        1      7.175096
 1        1      8.805868
 1        1      6.783571
 1        2      7.910917
 1        2      7.202307
 1        2      8.404883
 ...
 2        1      7.122915
 2        1      6.152732
 ...
 3        1      7.978232
 3        1      6.870228

##Desired Output in df.main
month site sub.site abiotic
  Jan  1     1      8.805868
  Jan  1     2      7.910917
dplyr sampling r random
1 Answers

You can do a full join of the two tables using site and sub.site, and then just sample one row from each month, site and sub.site combination.

If you are unsure about table joining (full join, left join, etc.), you may want to look that up online. It is very simple and standard in, say querying database.

In your case, after the full joining, because you have 9 unique combination of site and sub.site, you will have 81 rows:

joining.output <- df.main %>%
  full_join(df.sample, by = c("site", "sub.site"))

> joining.output
   month site sub.site  abiotic
1    Jan    1        1 7.235221
2    Jan    1        1 4.697654
3    Jan    1        1 5.502573
...
28   Feb    1        1 7.235221
29   Feb    1        1 4.697654
30   Feb    1        1 5.502573
...
55   Mar    1        1 7.235221
56   Mar    1        1 4.697654
57   Mar    1        1 5.502573

Then to sample 1 row for each site and sub.site combination for each month, just group by the 3 variables and sample.

Here is the code that puts everything together:

output <-  df.main %>%
  full_join(df.sample, by = c("site", "sub.site")) %>%
  group_by(month, site, sub.site) %>%
  slice_sample(n=1)

p.s. in your example, df.main$sub.site is a character array but df.sample$sub.site is a numeric array. You may want to convert the character array to numeric using as.double() function before joining.

user_79229280
Related