3

I have a .csv file with demographic data for my participants. The data are coded and downloaded from my study database (REDCap) in a way that each race has its own separate column. That is, each participant has a value in each of these columns (1 if endorsed, 0 if unendorsed).

It looks something like this:

SubjID  Sex  Age  White  AA  Asian  Other

 001    F    62   0      1   0      0
 002    M    66   1      0   0      0

I have to use a roundabout way to get my demographic summary stats. There's gotta be a simpler way to do this. My question is, how can I combine these columns into one column so that there is only one value for race for each participant? (i.e. recoding so 1 = white, 2 = AA, etc, and only the endorsed category is being pulled for each participant and added to this column?)

This is what I would like for it to look:

SubjID  Sex  Age  Race

001     F    62   2
002     M    66   1
wibeasley
  • 5,000
  • 3
  • 34
  • 62
Anja Pogarcic
  • 31
  • 2
  • 1
    Please show the expected output – akrun Oct 21 '19 at 18:53
  • Hi, please see [here](https://stackoverflow.com/a/5963610/2414988) to create a minimal reproducible example. – Biblot Oct 21 '19 at 18:56
  • For future projects, consider using the `Multiple Choice Dropdown/Radio (single answer)` field, instead of the `Checkboxes (multiple answers)` field. Not only does it eliminate this transformation, but it also protects against a user endorsing multiple races. If that is legal, it's not correctly accommodated by a simple pivot. The responses would need to be combined somehow. – wibeasley Jan 18 '20 at 18:32

3 Answers3

4

This is more or less similar to our approach with similar data from REDCap. We use pivot_longer for dummy variables. The final Race variable could also be made a factor. Please let me know if this is what you had in mind.

Edit: Added names_ptypes to pivot_longer to indicate that Race variable is a factor (instead of mutate).

library(tidyverse)

df <- data.frame(
  SubjID = c("001", "002"),
  Sex = c("F", "M"),
  Age = c(62, 66),
  White = c(0, 1),
  AA = c(1, 0),
  Asian = c(0, 0),
  Other = c(0, 0)
)

df %>%
  pivot_longer(cols = c("White", "AA", "Asian", "Other"), names_to = "Race", names_ptypes = list(Race = factor()), values_to = "Value") %>%
  filter(Value == 1) %>%
  select(-Value)

Result:

# A tibble: 2 x 4
  SubjID Sex     Age Race 
  <fct>  <fct> <dbl> <fct>
1 001    F        62 AA   
2 002    M        66 White
Ben
  • 28,684
  • 5
  • 23
  • 45
1

Here is another approach using reshape2

df[df == 0] <- NA
df <- reshape2::melt(df, measure.vars = c("White", "AA", "Asian", "Other"), variable.name = "Race", na.rm = TRUE)
df <- subset(df, select = -value)

#  SubjID Sex Age  Race
#    002   M  66 White
#    001   F  62    AA
Matt
  • 2,947
  • 1
  • 9
  • 21
  • 1
    `reshape2` is now the legacy package to `tidyr`. I would recommend writing new code with `pivot_longer()` rather than `melt()` where possible. –  Oct 21 '19 at 19:51
0

Here's a base approach:

race_cols <- 4:7

ind <- max.col(df[, race_cols])

df$Race_number <- ind
df$Race <- names(df[, race_cols])[ind]

df[, -race_cols]

  SubjID Sex Age Race_number  Race
1    001   F  62           2    AA
2    002   M  66           1 White

Data from @Ben

df <- data.frame(
  SubjID = c("001", "002"),
  Sex = c("F", "M"),
  Age = c(62, 66),
  White = c(0, 1),
  AA = c(1, 0),
  Asian = c(0, 0),
  Other = c(0, 0)
)
Cole
  • 11,130
  • 1
  • 9
  • 24