Mutating multiple times on the basis of conditions.

[kuttan98]

I have a big dataset that has the scores of students of about 2500 students. There are about 100 questions also. But the raw data does not have the scores, only responses. The first data below shows the responses. 1 for correct answer, 0 for incorrect answer and -99 for No Response. My end result must be the one like shown in the second set. After each question, the score must appear. When 1 is the response, the corresponding score (which can vary according to each question) must appear and if it is 0 or -99, the corresponding score 0 must appear. I want the set of codes which can help me do it. Because otherwise, doing it manually every time is difficult. Can I use looping and do something about this?

library(tidyverse)
library(janitor)

tibble::tribble(
  ~Student.ID, ~Gender, ~School, ~l1c1, ~l1c2, ~l1c3, ~l1c4,
      "ST001",      1L,   "BVB",    1L,    1L,  -99L,    1L,
      "ST002",      1L,  "CSET",    1L,    2L,    1L,    1L,
      "ST003",      1L,   "BVB",    1L,    1L,    1L,    1L,
      "ST004",      2L, "CVCET",    0L,    1L,    1L,    1L,
      "ST005",      1L,   "IIT",    0L,    1L,    1L,    0L,
      "ST006",      1L,   "IIM",    0L,    1L,    1L,    0L,
      "ST007",      2L,   "IIM",    0L,    1L,    1L,    0L,
      "ST008",      2L,   "IIM",    1L,    1L,    1L,    0L,
      "ST009",      2L,   "IIM",    1L,    2L,    0L,    1L,
      "ST010",      2L,   "VIT",    1L,    2L,    0L,    1L,
      "ST011",      2L,   "VIT",    0L,    2L,    0L,    1L,
      "ST012",      2L,   "VIT",    0L,  -99L,    0L,    0L,
      "ST013",      2L,   "VIT",    1L,    0L,    0L,    0L,
      "ST014",      1L,   "VIT",    0L,    0L,    0L,    1L,
      "ST015",      1L,   "IIT",    0L,    0L,    1L,    1L,
      "ST016",      1L,   "IIT",    1L,    0L,    1L,    1L,
      "ST017",      1L,   "IIT",  -99L,  -99L,    1L,    1L,
      "ST018",      1L,   "IIT",  -99L,    1L,    1L,    1L,
      "ST019",      1L,   "IIT",  -99L,    1L,    0L,  -99L,
      "ST020",      1L,   "IIT",    1L,    1L,    0L,    1L
  )

tibble::tribble(
  ~Student.ID, ~Gender, ~School, ~l1c1, ~score, ~l1c2, ~score, ~l1c3, ~score, ~l1c4, ~score,
      "ST001",      1L,   "BVB",    1L,     2L,    1L,     2L,  -99L,     0L,    1L,     1L,
      "ST002",      1L,  "CSET",    1L,     2L,    2L,     0L,    1L,     1L,    1L,     1L,
      "ST003",      1L,   "BVB",    1L,     2L,    1L,     2L,    1L,     1L,    1L,     1L,
      "ST004",      2L, "CVCET",    0L,     0L,    1L,     2L,    1L,     1L,    1L,     1L,
      "ST005",      1L,   "IIT",    0L,     0L,    1L,     2L,    1L,     1L,    0L,     0L,
      "ST006",      1L,   "IIM",    0L,     0L,    1L,     2L,    1L,     1L,    0L,     0L,
      "ST007",      2L,   "IIM",    0L,     0L,    1L,     2L,    1L,     1L,    0L,     0L,
      "ST008",      2L,   "IIM",    1L,     2L,    1L,     2L,    1L,     1L,    0L,     0L,
      "ST009",      2L,   "IIM",    1L,     2L,    2L,     0L,    0L,     0L,    1L,     1L,
      "ST010",      2L,   "VIT",    1L,     2L,    2L,     0L,    0L,     0L,    1L,     1L,
      "ST011",      2L,   "VIT",    0L,     0L,    2L,     0L,    0L,     0L,    1L,     1L,
      "ST012",      2L,   "VIT",    0L,     0L,  -99L,     0L,    0L,     0L,    0L,     0L,
      "ST013",      2L,   "VIT",    1L,     2L,    0L,     0L,    0L,     0L,    0L,     0L,
      "ST014",      1L,   "VIT",    0L,     0L,    0L,     0L,    0L,     0L,    1L,     1L,
      "ST015",      1L,   "IIT",    0L,     0L,    0L,     0L,    1L,     1L,    1L,     1L,
      "ST016",      1L,   "IIT",    1L,     2L,    0L,     0L,    1L,     1L,    1L,     1L,
      "ST017",      1L,   "IIT",  -99L,     0L,  -99L,     0L,    1L,     1L,    1L,     1L,
      "ST018",      1L,   "IIT",  -99L,     0L,    1L,     2L,    1L,     1L,    1L,     1L,
      "ST019",      1L,   "IIT",  -99L,     0L,    1L,     2L,    0L,     0L,  -99L,     0L,
      "ST020",      1L,   "IIT",    1L,     2L,    1L,     2L,    0L,     0L,    1L,     1L
  )

[nirgrahamuk]

Since you are an active forum participant, it might be helpful for you to know how to properly format code and console output that you post here. Using proper code formatting makes the site easier to read, prevents confusion (unformatted code can get garbled by the forum software ), and is generally considered the polite thing to do. Check out this FAQ to find out how — it's as easy as the click of a button! :

FAQ: How to format your code

Formatting Code: the Basics All code or console output you include in your posts should be formatted properly. Luckily, this is very easy to do! Just use the code formatting button at the top of the post editing box: [image] Select some code Click the </> button! The code formatting button automatically adds special Markdown formatting symbols to whatever you selected. If you check the preview pane to the right of your post, you'll see that the forum software now outputs nicely formatted code . [04] The code formatting button is pretty smart! It can tell the difference between snippets of code that are part of a sentence and blocks of code that stand alone. …

[kuttan98]

Oh thanks, as there was no code in this I did not do it. Will do it from next time.

[nirgrahamuk]

The library statement, the tribble function call, are all code and best formatted as such.
I have taken the liberty of formatting it for you on this occasion.

[kuttan98]

Thanks a lot.. Will make sure to do it properly from next time.

[nirgrahamuk]

I think for your ask to be solved you would need to provide the mapping between responses to scores

[kuttan98]

ok, I will explain. The scores is largely uniform, but for certain questions, it changes. In the present case, the questions, l1c1, l1c2- score is 2 marks for correct answer, 0 for wrong answer and no response. For l1c3, l1c4- score is 1 marks for correct answer, 0 for wrong answer and no response.

[nirgrahamuk]

library(tidyverse)

in1 <- tibble::tribble(
  ~Student.ID, ~Gender, ~School, ~l1c1, ~l1c2, ~l1c3, ~l1c4,
  "ST001",      1L,   "BVB",    1L,    1L,  -99L,    1L,
  "ST002",      1L,  "CSET",    1L,    2L,    1L,    1L,
  "ST003",      1L,   "BVB",    1L,    1L,    1L,    1L,
  "ST004",      2L, "CVCET",    0L,    1L,    1L,    1L,
  "ST005",      1L,   "IIT",    0L,    1L,    1L,    0L,
  "ST006",      1L,   "IIM",    0L,    1L,    1L,    0L,
  "ST007",      2L,   "IIM",    0L,    1L,    1L,    0L,
  "ST008",      2L,   "IIM",    1L,    1L,    1L,    0L,
  "ST009",      2L,   "IIM",    1L,    2L,    0L,    1L,
  "ST010",      2L,   "VIT",    1L,    2L,    0L,    1L,
  "ST011",      2L,   "VIT",    0L,    2L,    0L,    1L,
  "ST012",      2L,   "VIT",    0L,  -99L,    0L,    0L,
  "ST013",      2L,   "VIT",    1L,    0L,    0L,    0L,
  "ST014",      1L,   "VIT",    0L,    0L,    0L,    1L,
  "ST015",      1L,   "IIT",    0L,    0L,    1L,    1L,
  "ST016",      1L,   "IIT",    1L,    0L,    1L,    1L,
  "ST017",      1L,   "IIT",  -99L,  -99L,    1L,    1L,
  "ST018",      1L,   "IIT",  -99L,    1L,    1L,    1L,
  "ST019",      1L,   "IIT",  -99L,    1L,    0L,  -99L,
  "ST020",      1L,   "IIT",    1L,    1L,    0L,    1L
)

step2 <- mutate(in1,
                across(.cols = c(l1c1,l1c2),
                       .fns = ~case_when(.x==1~2,
                                         TRUE ~ 0),
                       .names="score_{.col}"),
                across(.cols = c(l1c3,l1c4),
                       .fns = ~case_when(.x==1~1,
                                         TRUE ~ 0),
                       .names="score_{.col}")
                )
(norder <- map(names(in1),
    ~{if(str_detect(.x,"l1c")){
      c(.x,paste0("score_",.x))
    } else {
      .x
    }}) %>% unlist)

step3 <- step2 %>% select(all_of(norder))

# its likely not helpful to have all the score variables with the same column name, 
# but I did it for you anyway
s3n <- startsWith(names(step3),"score")
names(step3)[s3n] <- rep("score",length(which(s3n)))

[kuttan98]

Thanks for this. But can you just explain the codes from norder. I did not get it.

[nirgrahamuk]

get the names of in1 in the order that they are
if they contain l1c then also create a score_ name to use. so it will be next door.
make step3 by choosing those names in that order.

[kuttan98]

The last line of code does not seem work when I do it in my original dataset. So I am sending the snippet of the original data so that you can understand my difficulty. I am unable to figure out what the error is. It says, "Can't subset columns that don't exist."

library(tidyverse)

tibble::tribble(
  ~student_id, ~Age, ~l1c1_identify_pic1, ~l1c1_identify_pic2, ~l1c2_identify_col1, ~l1c2_identify_col2, ~l1c3_identify_shape1, ~l1c3_identify_shape2, ~l1c4_card_order, ~l1c5_dots,
     "04BMEZ",   7L,                 "1",                 "1",                 "1",                  1L,                   "1",                   "0",              "1",         1L,
     "XWB3O0",   7L,                 "1",                 "1",                 "1",                  1L,                   "1",         "No response",              "1",         1L,
     "9AHCMX",   7L,                 "1",                 "1",                 "1",                  1L,                   "1",                   "0",              "1",         1L,
     "9LURRC",   7L,                 "1",                 "1",                 "1",                  1L,                   "1",                   "0",              "1",         1L,
     "PA9BXA",   7L,                 "1",                 "1",                 "1",                  1L,                   "1",                   "0",              "1",         1L,
     "75J239",   7L,                 "1",                 "1",       "No response",                  1L,                   "1",         "No response",              "1",         1L,
     "V6MGK4",   7L,                 "1",                 "1",                 "1",                  1L,                   "1",         "No response",              "1",         1L,
     "Z8ME14",   7L,       "No response",                 "1",       "No response",                  1L,         "No response",         "No response",              "1",         0L,
     "54TC0J",   7L,                 "0",                 "1",                 "0",                  0L,                   "0",                   "0",              "0",         0L,
     "2GCC0O",   7L,                 "1",                 "1",                 "1",                  1L,                   "1",                   "0",              "1",         1L,
     "48MKZY",   7L,                 "1",                 "1",                 "1",                  1L,                   "1",                   "1",              "1",         1L,
     "W7W6U4",   7L,                 "1",                 "1",                 "1",                  1L,                   "1",                   "1",              "1",         1L,
     "R0UQTS",   7L,                 "1",                 "1",                 "0",                  1L,                   "1",                   "0",              "0",         0L,
     "7RKE3P",   7L,                 "1",                 "1",                 "1",                  1L,                   "1",                   "1",              "1",         0L,
     "KB96JH",   7L,                 "1",                 "1",                 "1",                  1L,                   "1",                   "1",              "1",         1L,
     "QXTG2W",   7L,                 "1",                 "1",                 "1",                  1L,                   "1",                   "1",              "1",         1L,
     "ZLR493",   7L,       "No response",       "No response",                 "0",                  0L,         "No response",         "No response",              "1",         1L,
     "WX6U84",   7L,                 "1",                 "1",                 "1",                  1L,                   "1",         "No response",    "No response",         1L,
     "PAUD8I",   7L,                 "1",                 "1",                 "0",                  1L,                   "1",                   "1",              "1",         1L
  )
#> # A tibble: 19 x 10
#>    student_id   Age l1c1_identify_pic1 l1c1_identify_pic2 l1c2_identify_col1
#>    <chr>      <int> <chr>              <chr>              <chr>             
#>  1 04BMEZ         7 1                  1                  1                 
#>  2 XWB3O0         7 1                  1                  1                 
#>  3 9AHCMX         7 1                  1                  1                 
#>  4 9LURRC         7 1                  1                  1                 
#>  5 PA9BXA         7 1                  1                  1                 
#>  6 75J239         7 1                  1                  No response       
#>  7 V6MGK4         7 1                  1                  1                 
#>  8 Z8ME14         7 No response        1                  No response       
#>  9 54TC0J         7 0                  1                  0                 
#> 10 2GCC0O         7 1                  1                  1                 
#> 11 48MKZY         7 1                  1                  1                 
#> 12 W7W6U4         7 1                  1                  1                 
#> 13 R0UQTS         7 1                  1                  0                 
#> 14 7RKE3P         7 1                  1                  1                 
#> 15 KB96JH         7 1                  1                  1                 
#> 16 QXTG2W         7 1                  1                  1                 
#> 17 ZLR493         7 No response        No response        0                 
#> 18 WX6U84         7 1                  1                  1                 
#> 19 PAUD8I         7 1                  1                  0                 
#> # ... with 5 more variables: l1c2_identify_col2 <int>,
#> #   l1c3_identify_shape1 <chr>, l1c3_identify_shape2 <chr>,
#> #   l1c4_card_order <chr>, l1c5_dots <int>