Convert checkboxes from wide to long by specific variable(s)

[darthpathos76]

Hi -

I'd like to be able to do something like this:
Age Group | Ice Cream Flavour | Toppings___1 | Toppings___2 | Toppings___3 | Toppings___4
15-24 | Chocolate | Checked | Checkec | Checked | Unchecked

and convert it to
Age Group | Ice Cream Flavour | Toppings
15-24 | Chocolate | Sprinkles
15-24 | Chocolate | Peanuts
15-24 | Chocolate | Whipped Cream

I've done this using the code below (extremely inelegant but I'm a SQL guy and this worked):

geniden5 <- as.data.frame(sqldf("select hours, GenIdentity from geniden4 where GenIdentity<>'NA'"))
geniden6 <- bind_rows(geniden5,
sqldf("select hours, male as GenIdentity from geniden4 where male<>'NA'"),
sqldf("select hours, transwoman as GenIdentity from geniden4 where transwoman<>'NA'"),
sqldf("select hours, transman as GenIdentity from geniden4 where transman<>'NA'"),
sqldf("select hours, genderqueer as GenIdentity from geniden4 where genderqueer<>'NA'"),
sqldf("select hours, nonbinary as GenIdentity from geniden4 where nonbinary<>'NA'"),
sqldf("select hours, twospirit as GenIdentity from geniden4 where twospirit<>'NA'"),
sqldf("select hours, questioning as GenIdentity from geniden4 where questioning<>'NA'"),
sqldf("select hours, another as GenIdentity from geniden4 where another<>'NA'"),
sqldf("select hours, dontknow as GenIdentity from geniden4 where dontknow<>'NA'"),
sqldf("select hours, noanswer as GenIdentity from geniden4 where noanswer<>'NA'")
)

I'd love a "make_choose_long" option so that I could do something like
make_choose_long(data, age_group, icecream_flavour, by=toppings)

or something similar.
Thanks
Chris

[RaymondBalise]

Hi Chris,
I start out with a table like this in REDCap:

It imports like this when I use import_instruments():

> ice_cream_questionnaire
  record_id age_group  flavor toppings___1 toppings___2 toppings___3 toppings___4 toppings___5 ice_cream_questionnaire_complete
1         1      5-10 Vanilla      Checked      Checked      Checked      Checked    Unchecked                         Complete
2         2      5-10 Vanilla      Checked    Unchecked      Checked      Checked    Unchecked                         Complete
3         3       21+ Vanilla    Unchecked    Unchecked    Unchecked    Unchecked      Checked                         Complete

I am playing with a new process_boxes() function.

ice_cream_questionnaire |> 
  process_boxes("toppings")

It returns this:

# A tibble: 8 × 5
  record_id age_group flavor  ice_cream_questionnaire_complete check_boxes  
      <dbl> <chr>     <chr>   <chr>                            <chr>        
1         1 5-10      Vanilla Complete                         Sprinkles    
2         1 5-10      Vanilla Complete                         Peanuts      
3         1 5-10      Vanilla Complete                         Whipped Cream
4         1 5-10      Vanilla Complete                         Hot Fudge    
5         2 5-10      Vanilla Complete                         Sprinkles    
6         2 5-10      Vanilla Complete                         Whipped Cream
7         2 5-10      Vanilla Complete                         Hot Fudge    
8         3 21+       Vanilla Complete                         Whiskey  

For subgroup totals you can use the usual tidyverse or base R stuff:

totals <-
  ice_cream_questionnaire |> 
  process_boxes("toppings")

totals |> 
  group_by(flavor, age_group) |> 
  count(check_boxes)

That returns:

# A tibble: 5 × 4
# Groups:   flavor, age_group [2]
  flavor  age_group check_boxes       n
  <chr>   <chr>     <chr>         <int>
1 Vanilla 21+       Whiskey           1
2 Vanilla 5-10      Hot Fudge         2
3 Vanilla 5-10      Peanuts           1
4 Vanilla 5-10      Sprinkles         2
5 Vanilla 5-10      Whipped Cream     2

Is this what you have in mind?

Ray