-1

I have a dataframe...

df <- tibble(
  id = 1:10, 
  family = c("a","a","b","b","c", "d", "e", "f", "g", "h"),
  col1_a = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10),
  col1_b = c(1, 2, 3, 4, NA, NA, NA, NA, NA, NA),
  col2_a = c(11, 12, 13, 14, 15, 16, 17, 18, 19, 20),
  col2_b = c(11, 12, 13, 14, NA, NA, NA, NA, NA, NA),
  )

Families will only contain 2 members at most (so they're either individuals or pairs).

For individuals (families with only one row, i.e. id = 5:10), I want to randomly move 50% of the data from columns ending in 'a' to columns ending in 'b'.

By the end, the data should look like the following (depending on which 50% of rows are used)...

df <- tibble(
  id = 1:10, 
  family = c("a","a","b","b","c", "d", "e", "f", "g", "h"),
  col1_a = c(1, 2, 3, 4, 5, NA, 7, NA, 9, NA),
  col1_b = c(1, 2, 3, 4, NA, 6, NA, 8, NA, 10),
  col2_a = c(11, 12, 13, 14, NA, NA, 17, 18, NA, 20),
  col2_b = c(11, 12, 13, 14, 15, 16, NA, NA, 19, NA),
  )

I would like to be able to do this with a combination of group_by and mutate since I am mostly using Tidyverse.

Update: I forgot to mention that values in columns ending 'a' should be replaced with NA if they are moved across to 'b'.

Tom
  • 279
  • 1
  • 12

1 Answers1

0

I would do this in two primary steps, first create the fam_count column to determine which families only have 1 person. Then, create two rand columns, to determine whether or not we use the values in the b columns. 

library(tidyverse)
set.seed(1)

df %>% group_by(family) %>% 
  mutate(fam_count = n()) %>% 
  ungroup() %>% 
  mutate(
    rand1 = sample(c(NA, 1), nrow(.), replace = TRUE),
    rand2 = sample(c(NA, 1), nrow(.), replace = TRUE),
    col1_b = ifelse(fam_count == 1, rand1 * col1_a, col1_b),
    col2_b = ifelse(fam_count == 1, rand2 * col2_a, col2_b)
  ) %>%
  mutate(
    col1_a = ifelse(fam_count == 1 & !is.na(col1_b), NA, col1_a),
    col2_a = ifelse(fam_count == 1 & !is.na(col2_b), NA, col2_a)
  ) %>%
  select(-rand1, -rand2, - fam_count)

# A tibble: 10 x 6
      id family col1_a col1_b col2_a col2_b
   <int> <chr>   <int>  <dbl>  <int>  <dbl>
 1     1 a           1      1     11     11
 2     2 a           2      2     12     12
 3     3 b           3      3     13     13
 4     4 b           4      4     14     14
 5     5 c           5     NA     NA     15
 6     6 d           6     NA     NA     16
 7     7 e          NA      7     17     NA
 8     8 f           8     NA     NA     18
 9     9 g          NA      9     19     NA
10    10 h          10     NA     20     NA
Mako212
  • 6,787
  • 1
  • 18
  • 37