Collapsing ranked information into single column

Question

I have a survey where people rank some schools. The survey results in multiple columns with 1s and 2s. I need to colapse these into a persons first and second choice. Here is an exampe of the data that I have.

df1 <- tibble(Person  = c(1 , 2 , 3 , 4 , 5 , 6 , 7), 
School1 = c(NA, 1 , 2 , NA, NA, NA, 1 ), 
School2 = c(NA, 2 , 1 , NA, NA, 1 , NA), 
School3 = c(1 , NA, NA, NA, NA, 2 , NA),
School4 = c(2 , NA, NA, 1 , 2 , NA, NA), 
School5 = c(NA, NA, NA, 2 , 1 , NA, 2))

  Person School1 School2 School3 School4 School5
   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>
1      1      NA      NA       1       2      NA
2      2       1       2      NA      NA      NA
3      3       2       1      NA      NA      NA
4      4      NA      NA      NA       1       2
5      5      NA      NA      NA       2       1
6      6      NA       1       2      NA      NA
7      7       1      NA      NA      NA       2

Here is the result that I need.

df2 <- tibble(Person  = c(1 , 2 , 3 , 4 , 5 , 6 , 7), 
School1 = c(NA, 1 , 2 , NA, NA, NA, 1 ), 
School2 = c(NA, 2 , 1 , NA, NA, 1 , NA), 
School3 = c(1 , NA, NA, NA, NA, 2 , NA), 
School4 = c(2 , NA, NA, 1 , 2 , NA, NA), 
School5 = c(NA, NA, NA, 2 , 1 , NA, 2), 
Firstchoice = c('School3', 'School1', 'School2', 'School4', 'School5', 'School2', 'School1'), 
Secondchoice = c('School4', 'School2', 'School1', 'School5', 'School4', 'School3', 'School5'))

  Person School1 School2 School3 School4 School5 Firstchoice Secondchoice
   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl> <chr>       <chr>       
1      1      NA      NA       1       2      NA School3     School4     
2      2       1       2      NA      NA      NA School1     School2     
3      3       2       1      NA      NA      NA School2     School1     
4      4      NA      NA      NA       1       2 School4     School5     
5      5      NA      NA      NA       2       1 School5     School4     
6      6      NA       1       2      NA      NA School2     School3     
7      7       1      NA      NA      NA       2 School1     School5

I have looked at mutate, and using a for loop however I can't figure out how to get them to work since they would need to do inline updates of a column.

Any help would be appreciated.

score 0 · Accepted Answer · answered Mar 20 '19 at 20:10

One tidyverse possibility could be:

 df1 %>%
  gather(var, val, -Person) %>%
  mutate(val = ifelse(val == 1, "Firstchoice", 
                      ifelse(val == 2, "Secondchoice", NA_character_))) %>%
  na.omit() %>%
  spread(val, var) %>%
  left_join(df1, by = c("Person" = "Person"))

  Person Firstchoice Secondchoice School1 School2 School3 School4 School5
   <dbl> <chr>       <chr>          <dbl>   <dbl>   <dbl>   <dbl>   <dbl>
1      1 School3     School4           NA      NA       1       2      NA
2      2 School1     School2            1       2      NA      NA      NA
3      3 School2     School1            2       1      NA      NA      NA
4      4 School4     School5           NA      NA      NA       1       2
5      5 School5     School4           NA      NA      NA       2       1
6      6 School2     School3           NA       1       2      NA      NA
7      7 School1     School5            1      NA      NA      NA       2

score 0 · Answer 2 · answered Mar 20 '19 at 20:31

0

Piece of cake with tidyr:

choices <- gather(df1, key = "school", value = "choice", -Person, na.rm=TRUE)
choices <- arrange(choices, Person, choice)

answered Mar 20 '19 at 20:31

SteveM

2,226
3
12
16

Collapsing ranked information into single column

2 Answers2