Fill column of dataframe using a list

user_2395692 0
Viewed 205

I have the following dataframe:

tibble(
  people = rep(c("person1", "person2", "person3"), each = 4), 
  things = rep(c("thing1", "thing2", "thing3", "thing4"), times = 3), 
  vals = 0) %>% 
  group_by(people) %>% 
  mutate(order = seq_along(vals))

|people  |things | vals| order|
|:-------|:------|----:|-----:|
|person1 |thing1 |    0|     1|
|person1 |thing2 |    0|     2|
|person1 |thing3 |    0|     3|
|person1 |thing4 |    0|     4|
|person2 |thing1 |    0|     1|
|person2 |thing2 |    0|     2|
|person2 |thing3 |    0|     3|
|person2 |thing4 |    0|     4|
|person3 |thing1 |    0|     1|
|person3 |thing2 |    0|     2|
|person3 |thing3 |    0|     3|
|person3 |thing4 |    0|     4|

I also have list with the "things" that the "people" have done.

# What they did
list(
  person1 = c(1, 3, 4), 
  person2 = c(2, 3), 
  person3 = NA 
  )

The values in the list refer to the order column of the original dataframe. So person1 did things 1, 3, and 4, which correspond to the numbers 1, 3, and 4 of the order order column. I would like to use the list to fill in the column vals based on what each person has done. The desired output from this example should look like this:

|people  |things | vals| order|
|:-------|:------|----:|-----:|
|person1 |thing1 |    1|     1|
|person1 |thing2 |    0|     2|
|person1 |thing3 |    1|     3|
|person1 |thing4 |    1|     4|
|person2 |thing1 |    0|     1|
|person2 |thing2 |    1|     2|
|person2 |thing3 |    1|     3|
|person2 |thing4 |    0|     4|
|person3 |thing1 |    0|     1|
|person3 |thing2 |    0|     2|
|person3 |thing3 |    0|     3|
|person3 |thing4 |    0|     4|

I have tried using base R as well case_when but I can't seem to get my head around how to do this.

data-wrangling tidyverse r
2 Answers

Here is an option. We change the list to a two column dataset with enframe, then do a right_join with the original dataset, grouped by 'people', check whether the 'order' values are %in% the first unlisted 'value' column, coerce to binary with +

library(dplyr)
library(tibble)
enframe(outlst, name = 'people') %>% 
    right_join(df1) %>% 
    group_by(people) %>%
    mutate(vals = +(order %in% unlist(value[[1]]))) %>%
    ungroup

-output

# A tibble: 12 x 5
#   people  value     things  vals order
#   <chr>   <list>    <chr>  <int> <int>
# 1 person1 <dbl [3]> thing1     1     1
# 2 person1 <dbl [3]> thing2     0     2
# 3 person1 <dbl [3]> thing3     1     3
# 4 person1 <dbl [3]> thing4     1     4
# 5 person2 <dbl [2]> thing1     0     1
# 6 person2 <dbl [2]> thing2     1     2
# 7 person2 <dbl [2]> thing3     1     3
# 8 person2 <dbl [2]> thing4     0     4
# 9 person3 <lgl [1]> thing1     0     1
#10 person3 <lgl [1]> thing2     0     2
#11 person3 <lgl [1]> thing3     0     3
#12 person3 <lgl [1]> thing4     0     4
user_37322710

A base R option

transform(
  merge(
    subset(df, select = -vals),
    cbind(subset(stack(lst), !is.na(values)), vals = 1),
    by.x = c("people", "order"),
    by.y = c("ind", "values"),
    all = TRUE
  ),
  vals = replace(vals, is.na(vals), 0)
)[names(df)]

which gives

    people things vals order
1  person1 thing1    1     1
2  person1 thing2    0     2
3  person1 thing3    1     3
4  person1 thing4    1     4
5  person2 thing1    0     1
6  person2 thing2    1     2
7  person2 thing3    1     3
8  person2 thing4    0     4
9  person3 thing1    0     1
10 person3 thing2    0     2
11 person3 thing3    0     3
12 person3 thing4    0     4

Data

> dput(df)
structure(list(people = c("person1", "person1", "person1", "person1", 
"person2", "person2", "person2", "person2", "person3", "person3", 
"person3", "person3"), things = c("thing1", "thing2", "thing3",
"thing4", "thing1", "thing2", "thing3", "thing4", "thing1", "thing2", 
"thing3", "thing4"), vals = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0), order = c(1L, 2L, 3L, 4L, 1L, 2L, 3L, 4L, 1L, 2L, 3L, 4L)), row.names = c(NA,
-12L), groups = structure(list(people = c("person1", "person2", 
"person3"), .rows = structure(list(1:4, 5:8, 9:12), ptype = integer(0), class = c("vctrs_list_of",
"vctrs_vctr", "list"))), row.names = c(NA, 3L), class = c("tbl_df", 
"tbl", "data.frame"), .drop = TRUE), class = c("grouped_df",
"tbl_df", "tbl", "data.frame"))

> dput(lst)
list(person1 = c(1, 3, 4), person2 = c(2, 3), person3 = NA)
user_121587570
Related