Using tidyverse to clean up rank-choice survey

user_13007959 0
Viewed 96

I have survey data in R that looks like this, where I've presented people with two groups of actions - High and Low - and asked them to rank each action. Each group contains unique actions, marked by the letter (6 actions in total).

 id   A_High   B_High   C_High   D_Low   E_Low    F_Low
001       5         2         1      6       4        3
002       6         4         3      5       2        1
003       3         1         6      2       4        5
004       6         5         2      1       3        4

I need a new df that looks like the one below, where each High action is assigned a new numeric rank (between 0 and 3) corresponding to the number of Low action items that were ranked below that High action.

For example, a person with id 001 ranked A_High at number 5, B_High at 2, and C_High at 1. A_High's new rank would be 1 (since only 1 Low action, D_Low is ranked below A_High), B_High's new rank would be 3 (since all 3 Low actions were ranked below B_High), and C_High's new rank would be 3 (since all 3 Low actions were ranked below C_High).

 id   A_High_rank   B_High_rank   C_High_rank   
001             1             3             3                      
002             0             1             1           
003             2             3             0           
004             0             0             2

I have a sense that this can be done with if/else statements but suspect that there should be a far more efficient way of achieving this with tidyverse. In the real dataset, I have 1000+ rows and 12 actions (6 High and 6 Low). I would appreciate any help on this.

Thanks!

Data:

"id   A_High   B_High   C_High   D_Low   E_Low    F_Low
001       5         2         1      6       4        3
002       6         4         3      5       2        1
003       3         1         6      2       4        5
004       6         5         2      1       3        4"
tidyr tidyverse case-when survey r
2 Answers

A base R option would be to loop over the 'High' columns, get the rowSums of the logical matrix created by checking if it less than the 'Low' column, and rename those output by appending _rank as suffix

out <- cbind(df1[1], sapply(df1[2:4],
    function(x) rowSums(x < df1[endsWith(names(df1), 'Low')])))
names(out)[-1] <- paste0(names(out)[-1], "_rank")

-output

out
#  id A_High_rank B_High_rank C_High_rank
#1  1           1           3           3
#2  2           0           1           1
#3  3           2           3           0
#4  4           0           0           2

Or using dplyr

library(dplyr)
df1 %>% 
     transmute(id, across(ends_with('High'), 
        ~  rowSums(. <  select(df1, ends_with('Low'))), .names = '{.col}_rank'))
# id A_High_rank B_High_rank C_High_rank
#1  1           1           3           3
#2  2           0           1           1
#3  3           2           3           0
#4  4           0           0           2

data

df1 <- structure(list(id = 1:4, A_High = c(5L, 6L, 3L, 6L), B_High = c(2L, 
4L, 1L, 5L), C_High = c(1L, 3L, 6L, 2L), D_Low = c(6L, 5L, 2L, 
1L), E_Low = c(4L, 2L, 4L, 3L), F_Low = c(3L, 1L, 5L, 4L)), 
class = "data.frame", row.names = c(NA, 
-4L))
user_37322710

After much suffering, this is the tidyverse solution I came up with. This was fun!

library(tidyverse)

data %>%
  pivot_longer(cols = ends_with("_High"), names_to = "High Variables", values_to = "High") %>%
  pivot_longer(cols = ends_with("_Low"), names_to = "Low Variables", values_to = "Low") %>%
  filter(High-Low < 0) %>%
  group_by(`High Variables`, `id`) %>%
  summarise(Count = n()) %>%
  pivot_wider(names_from = `High Variables`, values_from = Count) %>%
  arrange(id)

Translation: The first two line create two pairs of columns and leave id untouched. Each pair has two columns, one with the original column names, and the other with the values. Each pait of columns represents either High or Low.

Then, I filtered all the rows, keeping only those where Low was greater than High. Then I counted how many where left for each id and reversed back the format.

Now I just have to figure out how to turn those NAs into 0s.

Here's the output:

> data %>%
+   pivot_longer(cols = ends_with("_High"), names_to = "High Variables", values_to = "High") %>%
+   pivot_longer(cols = ends_with("_Low"), names_to = "Low Variables", values_to = "Low") %>%
+   filter(High < Low) %>%
+   group_by(`High Variables`, `id`) %>%
+   summarise(Count = n()) %>%
+   pivot_wider(names_from = `High Variables`, values_from = Count) %>%
+   arrange(id)
`summarise()` regrouping output by 'High Variables' (override with `.groups` argument)
# A tibble: 4 x 4
     id A_High B_High C_High
  <int>  <int>  <int>  <int>
1     1      1      3      3
2     2     NA      1      1
3     3      2      3     NA
4     4     NA     NA      2
user_145069880
Related