Recoding single variable spread across several in R

Question

I am working with survey data that has a question about race. Each race category is its own variable. Here is what I want to do:

Create a new variable, p.race.
Assign p.race the value of one of the eight variables for race/ethnicity (below).
Determine whether an individual marked two or more races and assign p.race the value "Two or more races" in such cases.
Assign p.race the value "Hispanic or Latino" when they indicated this ethnicity.
Create a new variable, p.poc, to indicate if they are a person of color (i.e., not white, including Hispanic/Latino). This shall be 0 or 1.

The eight race categories are white*, black*, Asian*, AIAN*, NHPI*, some other race*, two or more races*, and Hispanic; where * denotes not Hispanic or Latino ethnicity.

Here is what I tried so far for parsing out "Two or more races":

p['p.race'] <- NA # create new variable for race

# list of variable names that store a string indicating the race
## e.g., `race_white` would be either blank or contain "White, European, Middle Eastern, or Caucasian"
race.list <- c('p.race_white', 'p.race_black', 'p.race_asian', 'p.race_aian', 'p.race_nhpi', 'p.race_other')

# iterate through each record
for ( n in 1:length(p) ) {
  multiflag = 0

  # iterate through the race list
  for ( i in race.list ) {

    # if it is not blank, +1 to multiflag
    if ( p$i[n] != '' ) {
      multiflag <- multiflag + 1
    }
  }

  # if multiflag was flagged more than once, assign "Two or more races" to `race`
  if ( multiflag > 1 ) {
    p$p.race[n] <- 'Two or more races'
  }
}

When executed, it returns this error:

> Error in if (p$i[n] != "") { : argument is of length zero

And here is my poc variable coding with error below:

p['p.poc'] <- 0 # create a new variable for whether they are a person of color
for ( n in 1:length(p) ) {
  if ( p$p.race_black[n] == 'Black, African-American, or African'
       | p$p.race_asian[n] == 'Asian or Asian-American'
       | p$p.race_aian[n] == 'American Indian or Alaskan Native'
       | p$p.race_nhpi[n] == 'Native Hawaiian or other Pacific Islander'
       | p$p.race_other[n] == 'Other (please specify)'
       | p$p.hispanic[n] == 'Yes') {
    p$p.poc[n] <- 1
  }
}

> Error in if (p$p.race_black[n] == "Black, African-American, or African" |  : 
  missing value where TRUE/FALSE needed

I don't really know where to start for assigning the new race variable one of the eight race categories without making it a very long code.

If it helps, below are the survey questions:

Q1. Do you consider yourself of Hispanic, Latino, or Spanish origin?

Yes
No

Q2. Which race do you identify with (check all that apply)?

White, European, Middle Eastern, or Caucasian
Black, African-American, or African
Asian or Asian-American
American Indian or Alaskan Native
Native Hawaiian or other Pacific Islander
Other (please specify)

And here is the sample output (text truncated):

> p[264:271]
#    
#      p.hispanic  p.race_white p.race_black p.race_asian p.race_aian p.race_nhpi p.race_other
#   1  Yes         White
#   2  No          White
#   3  No                       Black
#   4  No          White                     Asian
#   5  Yes                                                                        Some other race

And here is a dput output:

> dput(p[264:270])
structure(list(p.hispanic = structure(c(2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 2L, 2L, 2L, 2L, 2L, 3L, 2L, 2L, 
2L, 2L, 2L, 2L, 3L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 3L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), .Label = c("", "No", "Yes"
), class = "factor"), p.race_white = structure(c(2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 1L, 2L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 1L, 1L, 2L, 2L, 2L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 1L, 2L, 2L, 2L, 
1L, 2L, 2L, 2L, 2L, 2L, 2L, 1L, 1L, 2L, 2L, 2L, 2L, 1L, 2L, 2L, 
2L, 1L, 2L, 1L, 2L, 2L, 2L, 1L, 2L, 2L, 2L, 2L), .Label = c("", 
"White, European, Middle Eastern, or Caucasian"), class = "factor"), 
    p.race_black = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 
    1L, 1L, 1L, 1L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = c("", 
    "Black, African-American, or African"), class = "factor"), 
    p.race_asian = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 2L, 1L, 1L, 1L, 2L, 1L, 1L, 1L, 1L), .Label = c("", 
    "Asian or Asian-American"), class = "factor"), p.race_aian = structure(c(1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 1L, 
    1L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 1L), .Label = c("", "American Indian or Alaskan Native"
    ), class = "factor"), p.race_nhpi = c(NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), 
    p.race_other = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 1L, 
    1L, 1L, 1L, 1L, 1L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = c("", 
    "Other (please specify)"), class = "factor")), .Names = c("p.hispanic", 
"p.race_white", "p.race_black", "p.race_asian", "p.race_aian", 
"p.race_nhpi", "p.race_other"), class = "data.frame", row.names = c(NA, 
-79L))

It's pasted. It looks awful -- let me know if I should repost in another way. — oatmilkyway, Oct 15 '14 at 03:32

score 2 · Answer 1 · edited Oct 15 '14 at 06:07

This is not very elegant, but I think it works. Using loops, especially nested loops, is not very "R" since they are slow but also have side effects like cluttering your workspace.

and you might want to change how this treats p.poc if race is unspecified because it defaults to 1 which may not be what you want.

So here is one way:

tmp <- lapply(1:nrow(p), function(ii) {
  ## this checks for columns that aren't blank or NA, takes the colname
  ## and strips off the prefix
  tmp <- gsub('p.race_', '', names(p)[which(p[ii, -1] != '' & !is.na(p[ii, -1])) + 1])

  ## some special cases for > 1 race and blanks and p.poc
  tmp <- ifelse(length(tmp) > 1, 'Two or more', tmp)
  tmp[is.na(tmp)] <- 'Not specified'
  tmp <- ifelse(p[ii, 1] %in% 'Yes', 'Hispanic or Latino', tmp)
  p.poc <- (!grepl('white', tmp)) * 1

  return(list(p.race = tmp, p.poc = p.poc))
})

head(do.call(rbind, tmp), 20)

#   p.race               p.poc
# [1,] "white"               0    
# [2,] "white"               0    
# [3,] "white"               0    
# [4,] "white"               0    
# [5,] "white"               0    
# [6,] "white"               0    
# [7,] "white"               0    
# [8,] "white"               0    
# [9,] "asian"               1    
# [10,] "white"              0    
# [11,] "other"              1    
# [12,] "white"              0    
# [13,] "white"              0    
# [14,] "white"              0    
# [15,] "Hispanic or Latino" 1    
# [16,] "white"              0    
# [17,] "white"              0    
# [18,] "white"              0    
# [19,] "white"              0    
# [20,] "white"              0   

## and combine back to the data frame
p <- cbind(p, do.call(rbind, tmp))

data:

p <- structure(list(p.hispanic = structure(c(2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 2L, 2L, 2L, 2L, 2L, 3L, 2L, 2L, 
2L, 2L, 2L, 2L, 3L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 3L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), .Label = c("", "No", "Yes"
), class = "factor"), p.race_white = structure(c(2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 1L, 2L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 1L, 1L, 2L, 2L, 2L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 1L, 2L, 2L, 2L, 
1L, 2L, 2L, 2L, 2L, 2L, 2L, 1L, 1L, 2L, 2L, 2L, 2L, 1L, 2L, 2L, 
2L, 1L, 2L, 1L, 2L, 2L, 2L, 1L, 2L, 2L, 2L, 2L), .Label = c("", 
"White, European, Middle Eastern, or Caucasian"), class = "factor"), 
p.race_black = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 
1L, 1L, 1L, 1L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = c("", 
"Black, African-American, or African"), class = "factor"), 
p.race_asian = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 2L, 1L, 1L, 1L, 2L, 1L, 1L, 1L, 1L), .Label = c("", 
"Asian or Asian-American"), class = "factor"), p.race_aian = structure(c(1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 1L, 
1L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L), .Label = c("", "American Indian or Alaskan Native"
), class = "factor"), p.race_nhpi = c(NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), 
p.race_other = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 1L, 
1L, 1L, 1L, 1L, 1L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = c("", 
"Other (please specify)"), class = "factor")), .Names = c("p.hispanic", 
"p.race_white", "p.race_black", "p.race_asian", "p.race_aian", 
"p.race_nhpi", "p.race_other"), class = "data.frame", row.names = c(NA, 
  -79L))

Firstly, thanks a lot for taking a look at this! I have lots to learn from your code. However, when I run the code, every record turns to "Two or more" and the person of color flag `p.poc` is '1'. In reality, most records are from white people. Do you know what went wrong? — oatmilkyway, Oct 15 '14 at 05:34
Hm, I wrote that from looking at your `p` data. Does it work correctly if you run the code with the data provided? For the race categories, I only considered two options, blank `''` or `NA`, so if there are other values (spaces, N/A, "none", etc), then that will lead to more than two races. Also if there are additional columns that what I have, that may be messing things up. add a `print(tmp)` after the line with `gsub` to see which columns are being picked up. That might help with the `p.poc` problem, too. Or maybe you just need to use `grepl('white', tmp, ignore.case = TRUE)` instead — rawr, Oct 15 '14 at 11:45
[Here](https://drive.google.com/file/d/0B241gcJBy567S2N3WTBNMFpRSXc/view?usp=sharing) is the actual sample of my data as a `.csv`. There should only be blank values and strings. When I run the code, it doesn't work the way I expected. The `print(tmp)` prints out ALL of the variable names in the data frame, not just race. I tried the `ignore.case = TRUE` but that didn't work either. I also changed `'white'` to `'White, European, Middle Eastern, or Caucasian'` to make sure it wasn't that. — oatmilkyway, Oct 15 '14 at 16:42
I think I have `p.race_` and you only have `race_`, so I can read in your data with `p <- read.csv('~/downloads/race_example.csv', stringsAsFactors = FALSE)` and change `gsub('p.race_')` to `gsub('race_')`, and it works as expected — rawr, Oct 15 '14 at 18:05
Hmm, that's very odd. [Here](http://pastebin.com/Uxd4gLPq) is the code I am executing that gives me an output that has all records "Two or more" and everyone is a person of color. — oatmilkyway, Oct 15 '14 at 18:52
edit: I changed the code in the link slightly. It is giving me _mostly_ "two or more" but sometimes "hispanic or latino" and sometimes just "p.poc". — oatmilkyway, Oct 15 '14 at 18:59
you don't need to initialize the p.poc and p.race columns. That is messing things up because the `which` is looking at all columns minus the first one, so if you add additional ones, you'll get weird results, namely, that `which` is picking up p.race because you have set it as NA and that was one of the conditions in `which`. Take those two lines out and make sure you are consistent with whatever you call the data frame, and you should be good. — rawr, Oct 15 '14 at 19:02
One more question. When I do `str(rd$race)` and `str(rd$poc)`, it says it is a list of 78. Any way to transform the list(s) back into a factor? — oatmilkyway, Oct 15 '14 at 20:54
When I tried applying this to the original dataset (`p` instead of `rd`), it reverted to the error before (mostly 'two or more' and lots of `poc = 1`). I can not find the error. The [code](http://pastebin.com/P4fpD8q3) is posted to pastebin. — oatmilkyway, Oct 15 '14 at 22:01
hard to say without the data. compare the `str` of rd and p. if you could manipulate the columns, names, etc in p to be exactly like rd, just recycle the code that works for rd — rawr, Oct 16 '14 at 04:22

aosmith · Answer 2 · 2014-10-15T17:42:46.803

The way my bring work, this sort of task always seems easier if the data are in a long format instead of a wide format. However, this means a unique ID per response is needed - in a case like this you can just assign an integer to each row.

library(tidyr)
library(dplyr)

# Add individual ID to each row
p = mutate(p, id = 1:n())

Once that is done, I would do a little work to make the p.hispanic column look more like the other race columns, put the dataset in a long format, remove all NA/blanks, then make the two new variables. Once the new variables are made, they can be joined to the original. I use package tidyr for reshaping and dplyr for manipulation.

p %>%
    mutate(p.hispanic = ifelse(p.hispanic == "No", NA, "Hispanic or Latino")) %>% # change p.hispanic column
    gather(category, answer, p.hispanic:p.race_other, na.rm = TRUE) %>%
    filter(answer != "") %>% # get rid of blanks (if were NA would have removed in "gather")
    group_by(id) %>%
    # Create new variable p.race and p.pop based on rules
    mutate(p.race = ifelse(n_distinct(answer) > 1, "Two or more races", answer),
          p.poc = as.integer(p.race == "White, European, Middle Eastern, or Caucasian")) %>%
    slice(1) %>% # take only 1 record for the duplicate id's
    select(-category, - answer) %>% # remove columns that aren't needed
    left_join(p, ., by = "id") %>% # join new columns with original dataset
    select(-id) # remove ID column if not wanted

Once you have this dataset, you could reset the levels of p.race with factor if you want the levels to look a certain way.

Recoding single variable spread across several in R

2 Answers2