I'm using a multi-answer ethnicity question from a Qualtrics survey in my dataset and am looking to collapse multiple columns into one.

My data looks like this:

I'm trying to get it to look like this:

Is there a way to do this in R? I have been working on this for hours!

CodePudding user response：

We can write a custom function to do that -

return_col <- function(x) {
  inds <- x == 1
  if(sum(inds) > 1) "Multiple" else names(df)[inds]
}

This can be used in base R -

df$Race <- apply(df, 1, return_col)

Or in dplyr

library(dplyr)

df <- df %>%
  rowwise() %>%
  mutate(Race = return_col(c_across())) %>%
  ungroup

df %>% select(Race)

# A tibble: 4 × 1
#  Race                            
#  <chr>                           
#1 Multiple                        
#2 White/Caucasian                 
#3 American Indian/Alaskan Native  
#4 Pacific Islander/Native Hawaiian

data

It is easier to help if you provide data in a reproducible format

df <- structure(list(`White/Caucasian` = c("1", "1", "-", "-"), `Black/African American` = c("-", 
"-", "-", "-"), Hispanic = c("1", "-", "-", "-"), `Pacific Islander/Native Hawaiian` = c("-", 
"-", "-", "1"), `American Indian/Alaskan Native` = c("-", "-", 
"1", "-")), row.names = c(NA, -4L), class = "data.frame")

CodePudding user response：

Another tidyverse option:

library(tidyverse)

df %>%
  mutate(id = row_number(),
         across(everything(), ~ na_if(.x, "-"))) %>%
  pivot_longer(-id, names_to = "Race", values_drop_na = TRUE) %>%
  group_by(id) %>%
  mutate(Race = ifelse(n() > 1, "Multiple", Race)) %>%
  distinct() %>% 
  ungroup() %>%
  select(Race)

Output

  Race                            
  <chr>                           
1 Multiple                        
2 White/Caucasian                 
3 American Indian/Alaskan Native  
4 Pacific Islander/Native Hawaiian

Data

df <- structure(list(`White/Caucasian` = c("1", "1", "-", "-"), `Black/African American` = c("-", 
"-", "-", "-"), Hispanic = c("1", "-", "-", "-"), `Pacific Islander/Native Hawaiian` = c("-", 
"-", "-", "1"), `American Indian/Alaskan Native` = c("-", "-", 
"1", "-")), row.names = c(NA, -4L), class = "data.frame")