How I can create a yes/no table from given data in R?

[sharmachetan]

Hello, I have a data in the format of as below:

squads <- tibble::tribble(
            ~`1`,  ~Black.pepper,     ~Chili.lime,  ~Nashville.hot,
              2L,          "BBQ", "Honey mustard",      "Teriyaki",
              3L,      "Buffalo",    "Korean BBQ",              NA,
              4L,          "BBQ",    "Chili lime",    "Korean BBQ",
              5L,          "BBQ",  "Black pepper",    "Chili lime",
              6L,          "BBQ",       "Buffalo", "Nashville hot",
              7L,          "BBQ",       "Buffalo", "Honey mustard",
              8L, "Black pepper",     "Gochujang",    "Korean BBQ",
              9L, "Black pepper",    "Chili lime",     "Gochujang",
             10L,   "Chili lime",     "Gochujang",  "Indian curry",
             11L,     "Kung pao",     "Peri Peri",    "Sweet heat",
             12L,             NA,              NA,              NA,
             13L,      "Buffalo",     "Gochujang",    "Korean BBQ",
             14L,             NA,              NA,              NA,
             15L,          "BBQ",  "Black pepper",    "Chili lime"
            )
head(squads)
#> # A tibble: 6 x 4
#>     `1` Black.pepper Chili.lime    Nashville.hot
#>   <int> <chr>        <chr>         <chr>        
#> 1     2 BBQ          Honey mustard Teriyaki     
#> 2     3 Buffalo      Korean BBQ    <NA>         
#> 3     4 BBQ          Chili lime    Korean BBQ   
#> 4     5 BBQ          Black pepper  Chili lime   
#> 5     6 BBQ          Buffalo       Nashville hot
#> 6     7 BBQ          Buffalo       Honey mustard

And I want it to be in this form - image below attached:

^{Created on 2022-04-17 by the reprex package (v2.0.1)}

[M_AcostaCH]

You could use library(dpyrl) and case_when fuction. Maybe ifelse for make this.

For better help you, please put the conditions for Yes and No.

[yifanliu]

I guess the column name of squads should not be the one in your tribble function. So I changed it a little.

and is that what you want?

squads <- tibble::tribble(
  ~ID, ~pepper1, ~pepper2, ~pepper3,
  1L,  'Black pepper',     'Chili lime',  'Nashville hot',
  2L,          "BBQ", "Honey mustard",      "Teriyaki",
  3L,      "Buffalo",    "Korean BBQ",              NA,
  4L,          "BBQ",    "Chili lime",    "Korean BBQ",
  5L,          "BBQ",  "Black pepper",    "Chili lime",
  6L,          "BBQ",       "Buffalo", "Nashville hot",
  7L,          "BBQ",       "Buffalo", "Honey mustard",
  8L, "Black pepper",     "Gochujang",    "Korean BBQ",
  9L, "Black pepper",    "Chili lime",     "Gochujang",
  10L,   "Chili lime",     "Gochujang",  "Indian curry",
  11L,     "Kung pao",     "Peri Peri",    "Sweet heat",
  12L,             NA,              NA,              NA,
  13L,      "Buffalo",     "Gochujang",    "Korean BBQ",
  14L,             NA,              NA,              NA,
  15L,          "BBQ",  "Black pepper",    "Chili lime"
)

squads %>% pivot_longer(cols = -ID,names_to = 'order',values_to = 'pepper.choosed') %>% 
  filter(!is.na(pepper.choosed)) %>% mutate(exist = TRUE) %>% select(-order) %>% 
  pivot_wider(names_from = 'pepper.choosed', values_from = 'exist') %>% 
  mutate(across(-ID,~replace_na(.x,FALSE)))

owing to the information given is very limited, I'm not sure if I've done it right. Could you describe what you want to achieve?

[mdzorn]

I don't know what the different columns represent. But for one column you can use pivot_wider:

I added a generic column v to represent the Yes value, then pivot_wider with a generic fill in for missing values as "No":

> squads |> mutate(v = "Yes") |> pivot_wider(names_from = Black.pepper, values_from = v, values_fill = "No")

# A tibble: 14 × 9
     `1` Chili.lime    Nashville.hot BBQ   Buffalo `Black pepper` `Chili lime` `Kung pao` `NA` 
   <int> <chr>         <chr>         <chr> <chr>   <chr>          <chr>        <chr>      <chr>
 1     2 Honey mustard Teriyaki      Yes   No      No             No           No         No   
 2     3 Korean BBQ    NA            No    Yes     No             No           No         No   
 3     4 Chili lime    Korean BBQ    Yes   No      No             No           No         No   
 4     5 Black pepper  Chili lime    Yes   No      No             No           No         No   
 5     6 Buffalo       Nashville hot Yes   No      No             No           No         No   
 6     7 Buffalo       Honey mustard Yes   No      No             No           No         No   
 7     8 Gochujang     Korean BBQ    No    No      Yes            No           No         No   
 8     9 Chili lime    Gochujang     No    No      Yes            No           No         No   
 9    10 Gochujang     Indian curry  No    No      No             Yes          No         No   
10    11 Peri Peri     Sweet heat    No    No      No             No           Yes        No   
11    12 NA            NA            No    No      No             No           No         Yes  
12    13 Gochujang     Korean BBQ    No    Yes     No             No           No         No   
13    14 NA            NA            No    No      No             No           No         Yes  
14    15 Black pepper  Chili lime    Yes   No      No             No           No         No

You can either combine the columns into one column before the pivot_wider as yifanliu suggested or build 3 separate tables with Black.pepper, Chili.lime, Nashville.hot and then combine by rows (cbind) and reconcile the columns.

squads <- tibble::tribble(
  ~ID,      ~Choice1,        ~Choice2,        ~Choice3,
  1L, "Black pepper",    "Chili lime", "Nashville hot",
  2L,          "BBQ", "Honey mustard",      "Teriyaki",
.....


squads |> 
  pivot_longer(2:4, names_to = "Choice", values_to = "Sauce") |> 
  filter(!is.na(Sauce)) |> 
  select(-Choice) |> 
  mutate(v = "Yes") |> 
  pivot_wider(names_from = Sauce, values_from = v, values_fill = "No")


# A tibble: 13 × 14
      ID `Black pepper` `Chili lime` `Nashville hot` BBQ   `Honey mustard` Teriyaki Buffalo `Korean BBQ`
   <int> <chr>          <chr>        <chr>           <chr> <chr>           <chr>    <chr>   <chr>       
 1     1 Yes            Yes          Yes             No    No              No       No      No          
 2     2 No             No           No              Yes   Yes             Yes      No      No          
 3     3 No             No           No              No    No              No       Yes     Yes         
 4     4 No             Yes          No              Yes   No              No       No      Yes         
 5     5 Yes            Yes          No              Yes   No              No       No      No          
 6     6 No             No           Yes             Yes   No              No       Yes     No          
 7     7 No             No           No              Yes   Yes             No       Yes     No          
 8     8 Yes            No           No              No    No              No       No      Yes         
 9     9 Yes            Yes          No              No    No              No       No      No          
10    10 No             Yes          No              No    No              No       No      No          
11    11 No             No           No              No    No              No       No      No          
12    13 No             No           No              No    No              No       Yes     Yes         
13    15 Yes            Yes          No              Yes   No              No       No      No          
# … with 5 more variables: Gochujang <chr>, `Indian curry` <chr>, `Kung pao` <chr>, `Peri Peri` <chr>,
#   `Sweet heat` <chr>
>