New dataframe with last 6 rows per group in R

user_11572623 0
Viewed 52

I have a dataframe with several groups and a different number of observations per group. I would like to create a new dataframe with no more than n observations per group. Specifically, for the groups that have a largen number I would like to select the n last observations. An example data set:

timea <- c(1,2,3,4,5,6,7,8,9,10,11,12,13,14,21,22,23,24,25,26,27,28,29,30,5,6,7,8,9,10,25,26,27)
groupa <- c(1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,3,3,3,3,3,3,4,4,4)
vara <-c(7,7,8,10,9,2.5,7,8,9,1,3,4,8,9,10,2.5,3,9,8,3,5,8,1,7,9,10,2,6,4,3.5,9,8,6)

test1 <- data.frame(timea,groupa,vara)

I would like a new dataframe with no more than 6 observations per group (groupa), by selecting the last 6 per group. I was trying to find a dplyr solution, maybe using the lag function but I am not sure how to account for the ones that have less than 6 observations.

The expected output would be:

timea <- c(9,10,11,12,13,14,25,26,27,28,29,30,5,6,7,8,9,10, 25, 26,27)
groupa <- c(1,1,1,1,1,1,2,2,2,2,2,2,3,3,3,3,3,3,4,4,4)
vara <-c(9,1,3,4,8,9,8,3,5,8,1,7,9,10,2,6,4,3.5,9,8,6)


output <- data.frame(timea,groupa,vara)

Any ideas would be really appreciated.

subset dataframe r
2 Answers

You can use slice_tail function in dplyr to get last n rows from each group. If the number of rows in a group is less than 6, it will return all the rows for that group.

library(dplyr)
test1 %>% group_by(groupa) %>% slice_tail(n = 6) %>%  ungroup

# A tibble: 21 x 3
#   timea groupa  vara
#   <dbl>  <dbl> <dbl>
# 1     9      1     9
# 2    10      1     1
# 3    11      1     3
# 4    12      1     4
# 5    13      1     8
# 6    14      1     9
# 7    25      2     8
# 8    26      2     3
# 9    27      2     5
#10    28      2     8
# … with 11 more rows
user_39629140

We could use data.table methods

  1. Convert the 'data.frame' to 'data.table' (setDT)
  2. Grouped by 'groupa', get the rowindex (.I) of the last 6 rows
  3. Extract the index and subset the data
library(data.table)
setDT(test1)[test1[, .I[tail(seq_len(.N), 6)], groupa]$V1]
user_37322710
Related