How to randomly sample entire group based on multiple grouping conditions

user_7257777 0
Viewed 57

I have a dataframe like this:

df <- tribble(
  ~uniquename, ~frame, ~id,       ~datetime,      
  "unique1",1, "b1", "2021-05-05 07:05:01", 
  "unique1",1, "b5" , "2021-05-05 07:05:01", 
  "unique1",2, "b1", "2021-05-05 07:05:03", 
  "unique1",2, "b2", "2021-05-05 07:05:03", 
  "unique1",2, "b3" , "2021-05-05 07:05:03", 
  "unique1",3, "b2", "2021-05-05 07:07:03", 
  "unique1",3, "b4" , "2021-05-05 07:07:03", 
  "unique2",1, "b3", "2021-06-06 09:17:25",
  "unique2",1, "b4", "2021-06-06 09:17:25", 
  "unique2",12, "b5", "2021-06-06 09:20:17", 
  "unique2",12, "b6" , "2021-06-06 09:20:17",
  "unique2",16, "b1", "2021-06-06 09:20:59", 
  "unique2",16, "b2", "2021-06-06 09:20:59", 
  "unique2",16, "b3" , "2021-06-06 09:20:59", 
  "unique2",16, "b4", "2021-06-06 09:20:59")

I am trying to extract a random group of rows (specifically, a random group from the column 'frame') every single minute (based on datetime) for every unique grouping variable (uniquename). To try and make this sound clearer, within every single unique 'uniquename' variable, I want to extract a single grouped frame every n minutes (in this case 1 minute, but in theory could be 5, 10, etc).

So for the example of 1 minute the results would look like this:

result_df <- tribble(
  ~uniquename, ~frame, ~id,       ~datetime,      
  "unique1",2, "b1", "2021-05-05 07:05:03", 
  "unique1",2, "b2", "2021-05-05 07:05:03", 
  "unique1",2, "b3" , "2021-05-05 07:05:03", 
  "unique1",3, "b2", "2021-05-05 07:07:03", 
  "unique1",3, "b4" , "2021-05-05 07:07:03", 
  "unique2",1, "b3", "2021-06-06 09:17:25",
  "unique2",1, "b4", "2021-06-06 09:17:25", 
  "unique2",12, "b5", "2021-06-06 09:20:17", 
  "unique2",12, "b6" , "2021-06-06 09:20:17")

As you can see, within 'unique1', only frames 2 and 3 were kept, because frames 1 and 2 were <1 minute apart from each other and I only wanted to randomly select one of them to stay.

As I've tried this, I created a new column that creates a time stamp for every minute and tried slicing like so:

df <- df %>% group_by(uniquename) %>% mutate(mincut = cut(datetime, "1 min")) %>% group_by(uniquename,mincut) %>% slice_sample()

But this only slices 1 row per group, not the whole group.

In data.table (which is actually quite preferable since my dataframe has ~1000000 rows), I tried this (with the mincut column from the dplyr code included) but it also only extracts 1 row per the group, not the entire group.

df[df[, sample(.I, 1), by=c('uniquename','mincut')][[3]],]

Is there any way to modify these codes, or another approach that will allow me to extract an entire group for every minute within uniquename?

Thanks very much in advance.

data.table tidyverse r
1 Answers

You can use lubridate::floor_date to create groups and then filter one randomly sampled frame per group. You can manually set the interval you need in floor_date, here it's "1 minute".

df %>% 
  mutate(datetime = ymd_hms(datetime),
           fl = floor_date(datetime, "1 minute")) %>% 
  group_by(uniquename, fl) %>% 
  filter(frame == sample(unique(frame), 1))

output:

# A tibble: 11 × 5
# Groups:   uniquename, floor [4]
   uniquename frame id    datetime            fl              
   <chr>      <dbl> <chr> <dttm>              <dttm>             
 1 unique1        2 b1    2021-05-05 07:05:03 2021-05-05 07:05:00
 2 unique1        2 b2    2021-05-05 07:05:03 2021-05-05 07:05:00
 3 unique1        2 b3    2021-05-05 07:05:03 2021-05-05 07:05:00
 4 unique1        3 b2    2021-05-05 07:07:03 2021-05-05 07:07:00
 5 unique1        3 b4    2021-05-05 07:07:03 2021-05-05 07:07:00
 6 unique2        1 b3    2021-06-06 09:17:25 2021-06-06 09:17:00
 7 unique2        1 b4    2021-06-06 09:17:25 2021-06-06 09:17:00
 8 unique2       16 b1    2021-06-06 09:20:59 2021-06-06 09:20:00
 9 unique2       16 b2    2021-06-06 09:20:59 2021-06-06 09:20:00
10 unique2       16 b3    2021-06-06 09:20:59 2021-06-06 09:20:00
11 unique2       16 b4    2021-06-06 09:20:59 2021-06-06 09:20:00
user_134606020
Related