Selecting observations within a data frame and reversing their order

user_11714836 0
Viewed 143

I have a huge data frame that contains many time correlated observations of several variables on a few hundred individuals. The individuals each have a unique number in the ID column. I will use the data simulated below, which is structured similarly to my data to ask my question:

set.seed(123)
dat <- data.frame(ID = rep(letters[1:10], each = 10),
                  time = rep(c(1:10), times = 10),
                  var1 = rnorm(100))

Note that in the real data, the actual number of observations is different for each ID. Say there were a few individuals (e.g., IDs: b, e, and g) that I needed to take the observations for and completely "flip" or "reverse" the order of, and still preserve what data is with each time. By this I mean (using individual b as an example) that the first observation in the data frame for individual b would be the data at "time interval" 10 instead of "time interval" 1. In other words the data would look like this:

ID   time   Var1
a     1
a     2
…     … 
a     10 
b     10
b     9 
b     8
…     …
b     1
c     1
c     2
c     3
c     4
ect...

What is the safest way to do this and maintain their position in the data frame (i.e., b stays in between a and c ect..)?

dataframe r
4 Answers

Using data.table:

library(data.table)
setDT(dat)
ids.to.reverse <- c('b', 'e', 'g')

dat[, if(ID %in% ids.to.reverse) .SD[.N:1] else .SD, by='ID']
user_135665930

One option is to group_split by ID and do the arrange by looping over the list with map based on whether any of the values 'b', 'e', 'g' are %n% the 'ID'

library(dplyr)
library(purrr)
out <- dat %>% 
        group_split(ID) %>%
        map_dfr(~ if(any(c('b', 'e', 'g') %in% first(.x$ID)))
         .x %>%
             arrange(desc(time)) else .x)   

out %>% 
   filter(ID %in% c('a', 'b'))
# A tibble: 20 x 3
#   ID     time    var1
#   <fct> <int>   <dbl>
# 1 a         1 -0.560 
# 2 a         2 -0.230 
# 3 a         3  1.56  
# 4 a         4  0.0705
# 5 a         5  0.129 
# 6 a         6  1.72  
# 7 a         7  0.461 
# 8 a         8 -1.27  
# 9 a         9 -0.687 
#10 a        10 -0.446 
#11 b        10 -0.473 
#12 b         9  0.701 
#13 b         8 -1.97  
#14 b         7  0.498 
#15 b         6  1.79  
#16 b         5 -0.556 
#17 b         4  0.111 
#18 b         3  0.401 
#19 b         2  0.360 
#20 b         1  1.22

Or we can make use of arrange in a hacky way i.e. change the time to negative based on the ID 'b', 'e', 'g' while the rest is positive

out1 <- dat %>%
     arrange(ID,  time * c(1, -1)[c(1 + (ID %in% c('b', 'e', 'g')))])

-checking

all.equal(out, out1, check.attributes = FALSE)
#[1] TRUE
user_37322710

Here is an approach with base R using split, order and rev:

rev.ids <- c("b", "e", "g")
split <- split(dat, dat$ID)
dat <- do.call(rbind,lapply(split,function(x){
  if(x[1,1] %in% rev.ids)
    x[order(rev(x$time)),] 
  else 
    x
  }))
dat
    ID time         var1
1    a    1 -0.560475647
2    a    2 -0.230177489
...
8    a    8 -1.265061235
9    a    9 -0.686852852
10   a   10 -0.445661970
11   b   10 -0.472791408
12   b    9  0.701355902
...
18   b    3  0.400771451
19   b    2  0.359813827
20   b    1  1.224081797
21   c    1 -1.067823706

Edit

I think this data.table approach will be faster:

library(data.table)
rev.ids <- c("b", "e", "g")
setDT(dat)[,.SD[order(time,decreasing = (unlist(.BY) %in% rev.ids))],by = ID]
    ID time         var1
  1:  a    1 -0.560475647
  2:  a    2 -0.230177489
...
  8:  a    8 -1.265061235
  9:  a    9 -0.686852852
 10:  a   10 -0.445661970
 11:  b   10 -0.472791408
 12:  b    9  0.701355902
...
 19:  b    2  0.359813827
 20:  b    1  1.224081797
 21:  c    1 -1.067823706
 22:  c    2 -0.217974915
user_130953260 
library(tidyverse)

dat <- tibble(ID = rep(letters[1:10], each = 10),
                  time = rep(c(1:10), times = 10),
                  var1 = rnorm(100))

flip_vars <- c("b", "e", "g")

dat %>% 
  # This line turns each ID into its own column
  pivot_wider(names_from = ID, values_from = var1) %>% 
  # This line reverses the order of the flip_vars
  mutate_at(
    flip_vars,
    rev
  ) %>% 
  # This puts things back in their original, tidy format
  pivot_longer(cols = a:j, names_to = "ID", values_to = "var1")
user_135520000
Related