Replace specific values in dataframe with a random row's values

user_372526 0
Viewed 65

Let's say I have the following df

df <- read.table(text = "team id result result_number
A 1 999 999
A 2 cat 45
A 3 dog 50
B 4 999 999
B 5 three 60
B 6 four 30
C 7 999 999
C 8 rabbit 45
C 9 monkey 11
D 10 dog 12
D 11 999 999
D 12 basket 10", header = T)

For every row that has a 999 in the result category, I'd like to replace that value (and the corresponding result_number value) with the values from a random row within the same team (e.g. random within A, B, C, or D) that is not 999.

So here's an example output:

df <- read.table(text = "team id result result_number
A 1 cat 45
A 2 cat 45
A 3 dog 50
B 4 four 30
B 5 three 60
B 6 four 30
C 7 rabbit 45
C 8 rabbit 45
C 9 monkey 11
D 10 dog 12
D 11 basket 10
D 12 basket 10", header = T)

I can't figure out an elegant way of doing this in dplyr without a really clumsy external function

dplyr r
2 Answers

Grouped by 'team', loop across the 'result', 'result_number', columns, replace, where the value is 999 with a sample of elements that are not equal (!=) to 999, and return a single value

library(dplyr)
df %>% 
   group_by(team) %>% 
   mutate(across(result:result_number,
      ~ replace(., as.character(.)==  "999", 
          sample(.[as.character(.) != "999"], 1)))) %>%
   ungroup

-output

# A tibble: 12 × 4
   team     id result result_number
   <chr> <int> <chr>          <int>
 1 A         1 dog               45
 2 A         2 cat               45
 3 A         3 dog               50
 4 B         4 four              30
 5 B         5 three             60
 6 B         6 four              30
 7 C         7 monkey            45
 8 C         8 rabbit            45
 9 C         9 monkey            11
10 D        10 dog               12
11 D        11 dog               10
12 D        12 basket            10
user_37322710

This result was created with the help of akrun! Many thanks!

library(dplyr)
df %>% 
  group_by(team) %>% 
  mutate(across(contains("result"), ~ifelse(.==999, sample(.[. != 999], 1, replace = TRUE),.)))

team     id result result_number
   <chr> <int> <chr>          <int>
 1 A         1 cat               50
 2 A         2 cat               45
 3 A         3 dog               50
 4 B         4 four              60
 5 B         5 three             60
 6 B         6 four              30
 7 C         7 monkey            45
 8 C         8 rabbit            45
 9 C         9 monkey            11
10 D        10 dog               12
11 D        11 dog               12
12 D        12 basket            10
user_133216470
Related