Calculate the count of yes/no in multiple columns

Question

Col1 has ID, Col2, Col3, Col4 has different questions with Yes and No answer. I am trying to output number of yes and no in each column. This is for spotfire(r) table. I tried using pivot_wider function, but it didnt work for more than one column.

Thanks for looking into this.

ID	Q1	Q2
101	Yes	No
104	Yes	Yes
104	No	No

Q1 - Yes	Q1 - No	Q2-Yes	Q2 - No
2	1	1	2

Answer 1

A base R option

with(
    as.data.frame(
        table(paste0("Q", col(df[-1])), unlist(df[-1]))
    ),
    setNames(Freq, paste(Var1, Var2, sep = "-"))
)

gives

 Q1-No  Q2-No Q1-Yes Q2-Yes
     1      2      2      1

data

> dput(df)
structure(list(ID = c(101L, 104L, 104L), Q1 = c("Yes", "Yes", 
"No"), Q2 = c("No", "Yes", "No")), class = "data.frame", row.names = c(NA,
-3L))

Answer 2

Your data is already kind of wide. It would be easier to reshape to long first, do the transformation, then go back to wide. For example

library(dplyr)
library(tidyr)
dd |>
  pivot_longer(cols=Q1:Q2) |>
  count(name, value) |>
  mutate(outcol = paste(name,"-", value)) |>
  select(outcol, n) |> 
  pivot_wider(names_from=outcol, values_from=n, values_fill=0)
# # A tibble: 1 × 4
#   `Q1 - No` `Q1 - Yes` `Q2 - No` `Q2 - Yes`
#       <int>      <int>     <int>      <int>
# 1         1          2         2          1