How do I use group_by and case_when to impute a new value in a column?

user_13007959 0
Viewed 117

I'm trying to wrap my head around this data wrangling problem. My conjoint study output df looks similar to this:

   id set_number card_number att1 att2 att3 att4 score
1 932          1           1    1    1    1    3     0
2 932          1           2    2    2    4    4   100
3 932          1           3    8    8    8    8     0
4 932          2           1    3    3    3    1     0
5 932          2           2    4    2    2    4     0
6 932          2           3    8    8    8    8   100
7 933          1           1    1    1    1    3     0
8 933          1           2    2    2    4    4   100
9 933          1           3    8    8    8    8     0
...

Where id refers to a person and score is a dependent variable. I need to reformat the df in order to run an analysis using ChoiceModelR package.

I am trying to figure out how to write a code (I am guessing using group_by(id and card_number) and case_when/if else statements) that would impute the card_number in the top row corresponding to each set_number, if a score is 100 for that card number. However, the score needs to be "card_number + 1" if all att1 to att4 are 8s.

The desired df needs to look like so:

   id set_number card_number att1 att2 att3 att4 score
1 932          1           1    1    1    1    3     2
2 932          1           2    2    2    4    4     0
3 932          1           3    8    8    8    8     0
4 932          2           1    3    3    3    1     4
5 932          2           2    4    2    2    4     0
6 932          2           3    8    8    8    8     0
7 933          2           1    3    3    3    1     2
8 933          2           2    4    2    2    4     0
9 933          2           3    8    8    8    8     0

...

I would really appreciate any help.

My complete dataset in csv. format is here

Dput output

structure(list(id = c(932L, 932L, 932L, 932L, 932L, 932L, 932L, 
932L, 932L, 932L), set_number = c(1L, 1L, 1L, 2L, 2L, 2L, 3L, 
3L, 3L, 4L), card_number = c(1L, 2L, 3L, 1L, 2L, 3L, 1L, 2L, 
3L, 1L), att1 = c(1L, 2L, 8L, 3L, 4L, 8L, 5L, 6L, 8L, 3L), att2 = c(1L, 
2L, 8L, 3L, 2L, 8L, 4L, 3L, 8L, 1L), att3 = c(1L, 4L, 8L, 3L, 
2L, 8L, 1L, 3L, 8L, 2L), att4 = c(3L, 4L, 8L, 1L, 4L, 8L, 3L, 
2L, 8L, 2L), score = c(0L, 100L, 0L, 0L, 100L, 0L, 0L, 100L, 
0L, 0L)), class = "data.frame", row.names = c(NA, -10L))
tidyr dplyr r
2 Answers

A solution based in the tidyverse can look as follows.

library(dplyr)
library(purrr)

as_tibble(df) %>%
  group_by(id, set_number) %>%
  mutate(scoreX = card_number[which(score == 100)][1],
         scoreX = pmap_dbl(list(att1, att2, att3, att4, score, scoreX),
                           ~ if_else(sum(..1, ..2, ..3, ..4) == 32 & ..5 == 100, 
                                     ..6 + 1, as.double(..6))),
         scoreX = max(scoreX),
         scoreX = if_else(row_number() == min(row.names(.)), scoreX, 0))


#       id set_number card_number  att1  att2  att3  att4 score scoreX
#    <int>      <int>       <int> <int> <int> <int> <int> <int>  <dbl>
#  1   932          1           1     1     1     1     3     0      2
#  2   932          1           2     2     2     4     4   100      0
#  3   932          1           3     8     8     8     8     0      0
#  4   932          2           1     3     3     3     1     0      2
#  5   932          2           2     4     2     2     4   100      0
#  6   932          2           3     8     8     8     8     0      0
#  7   932          3           1     5     4     1     3     0      2
#  8   932          3           2     6     3     3     2   100      0
#  9   932          3           3     8     8     8     8     0      0
# 10   932          4           1     3     1     2     2     0     NA
user_128202050

This is probably not the most efficient way of solving this, but here it goes (I would also welcome any other way of achieving the same thing):

df$dv = 0

for (i in seq(1, nrow(df),by = 3)){
  if(df$score[i] == 100)
  {df$dv[i] = 1}
  if(df$score[i+1] == 100)
  {df$dv[i] = 2}
  if(df$score[i+2] == 100)
  {df$dv[i] = 4}
}

dv is a new column that stores updated scores. I then just removed score column with a subset function.

user_130079590
Related