How to combine multiple ethnicity columns into one in R?

Question

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:

White/Caucasian	Black/African American	Hispanic	Pacific Islander/Native Hawaiian	American Indian/Alaskan Native
1	-	1	-	-
1	-	-	-	-
-	-	-	-	1
-	-	-	1	-

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

Race
Multiple
White
American Indian/Alaskan Native
Pacific Islander/Native Hawaiian

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

Just wanted to comment - are you sure you don't want to create two columns, one for "Race" and one for "Ethnicity" being two separate social constructs? In your case, you can keep `Hispanic` separate to indicate whether identifies with "Hispanic" ethnicity (vs. "Non-Hispanic"). You can take the other "Race" columns and determine if "multi-race" or not. — Ben, Mar 19 '22 at 12:48

score 4 · Answer 1 · answered Mar 19 '22 at 02:57

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")

score 1 · Accepted Answer · answered Mar 19 '22 at 03:12

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")

How to combine multiple ethnicity columns into one in R?

2 Answers2