Compare two rows in one column R

January 13, 2022

I want to compare values that are listed in one column over two rows in R. The rows are identified through an ID variable. I want to produce a new variable that tells me which of both values is lower than the other. Here an example:

value   id   new_var
    1    1     lower
    2    1    higher
    5    2    higher
    3    2     lower
   10    3     lower
   11    3    higher
    2    4     lower
    1    4    higher

Thank you!

>Solution :

If there are more than two elements per group, do a group by, and create a condition with ifelse/case_when to create the ‘new_var’

library(dplyr)
df1 %>% 
  group_by(id) %>% 
  mutate(new_var = case_when(value == max(value) ~ "higher",
      TRUE ~ "lower")) %>% 
  ungroup
# A tibble: 8 × 3
  value    id new_var
  <int> <int> <chr>  
1     1     1 lower  
2     2     1 higher 
3     5     2 higher 
4     3     2 lower  
5    10     3 lower  
6    11     3 higher 
7     2     4 higher 
8     1     4 lower

An option is also to arrange and then create a new column in the order with rep (assuming 2 elements per id)

df1 %>%
  arrange(id, value) %>% 
  mutate(new_var = rep(c("lower", "higher"), length.out = n()))

data

df1 <- structure(list(value = c(1L, 2L, 5L, 3L, 10L, 11L, 2L, 1L), id = c(1L, 
1L, 2L, 2L, 3L, 3L, 4L, 4L)), row.names = c(NA, -8L), class = "data.frame")