Combining Reformated Likert Categories-CodePudding

I am currently using the Likert package and it requires my Likert Matrix tables to be in a different format. To do this I used the code:

LikertQ9_1 <- SurveyClean2 |> 
  dplyr::select(Q9_1) |> 
  mutate(Question = "Grazing or Forage Production") |> 
  group_by(Question, Q9_1) |> 
  count() |> 
  ungroup() |> 
  pivot_wider(names_from = Q9_1, values_from = n)

However, I also have Q9_2 through Q9_10 that need to be reformatted as well. Is there a way to do this in bulk or to combine them in the end? I've tried to merge and left join them, but it messes up the formatting.

The original format is shown below, just for reference:

New format shown below, just for reference. It is just for Q9_1, I need to add the other rows for 2-10:

Dataframe sample:

SurveyClean2 <- dplyr::tibble(
      Q9_1 = c("Not Interested", "Not Interested", "NA", "Slightly Interested", "NA"),
      Q9_2 = c("Extremely Interested", "Not Interested", "Somewhat Interested", "NA", "NA"),
      Q9_3 = c("Not Interested", "Extremely Interested", "Slightly Interested", "Somewhat Interested", "Not Interested"),
      Q9_4 = c("Not Interested", "Extremely Interested", "Slightly Interested", "Somewhat Interested", "Not Interested"), 
      Q9_5 = c("Slightly Interested", "Extremely Interested", "Slightly Interested", "Somewhat Interested", "Not Interested"),
      Q9_6 = c("Not Interested", "Extremely Interested", "Slightly Interested", "Somewhat Interested", "NA"),
      Q9_7 = c("Not Interested", "Extremely Interested", "Slightly Interested", "Extremely Interested", "NA"),
      Q9_8 = c("Not Interested", "Extremely Interested", "Somewhat Interested", "Somewhat Interested", "NA"),
      Q9_9 = c("NA", "Extremely Interested", "Slightly Interested", "Somewhat Interested", "NA"),
      Q9_10 = c("Not Interested", "Slightly Interested", "Slightly Interested", "Somewhat Interested", "NA"))

CodePudding user response：

Here's a solution using the tidyverse. I made some assumptions about your data that might be incorrect - it would be helpful if you're able to provide some sample data to show how your survey data is structured:

library(dplyr)
library(tidyr)
library(purrr)

# your survey data
SurveyClean2 <- dplyr::tibble(
  Q9_1 = c("Agree", "Disagree", "Neutral", "Agree"),
  Q9_2 = rep("Agree", 4),
  Q9_3 = c("Disagree", "Disagree", "Neutral", "Agree")
)

# function to clean data
clean_survey <- function(data, column, question) {
  data %>% 
    dplyr::select(all_of({{column}})) %>% 
    dplyr::mutate(Question = question) %>% 
    dplyr::group_by(Question, across(1)) %>% 
    dplyr::count() %>% 
    dplyr::ungroup() %>% 
    tidyr::pivot_wider(names_from = 2, values_from = n)
}

# table that contains survey questions/columns and the question name
survey_table <- dplyr::tibble(
  column = c("Q9_1", "Q9_2", "Q9_3"),
  question = c("Grazing or Forage Production", "Coffee or Tea", "Something else")
)


# loop through your data and clean it, then bind as dataframe
purrr::map2_df(survey_table$column, survey_table$question, function(x, y){
  clean_survey(SurveyClean2, x, y)
})
#> # A tibble: 3 × 4
#>   Question                     Agree Disagree Neutral
#>   <chr>                        <int>    <int>   <int>
#> 1 Grazing or Forage Production     2        1       1
#> 2 Coffee or Tea                    4       NA      NA
#> 3 Something else                   1        2       1

And if you want to replace NA with 0:

purrr::map2_df(survey_table$column, survey_table$question, function(x, y){
  clean_survey(SurveyClean2, x, y)
}) %>% 
  mutate(across(everything(), ~ifelse(is.na(.), 0, .)))