Remove single observations then duplicate remaining observations

Question

For the following data, I'd like to do the following:

[1] remove observations with only one repeated measure / observation for that id

[2] if the remaining id's have fewer than 5 repeated measures, repeat those observations till there's at least 5 observations (for example if there are 2 rows for an id, duplicate them 3 times)

Data:

structure(list(id = c("0101", "0102", "0102", "0103", "0103", 
"0103", "0104", "0104", "0104", "0104", "0104", "0105", "0105", 
"0105", "0105", "0105", "0106", "0106", "0106", "0106", "0106", 
"0107", "0107", "0107", "0107", "0107", "0108", "0108", "0108", 
"0108"), date = c("10/01/91", "12/03/91", "05/05/92", "06/22/92", 
"12/17/92", "07/14/93", "07/28/92", "01/14/93", "08/11/93", "02/03/94", 
"08/23/94", "09/24/92", "03/05/93", "10/18/93", "04/14/94", "05/31/94", 
"01/13/93", "07/27/93", "03/10/94", "09/01/94", "03/09/95", "01/15/93", 
"07/23/93", "02/07/94", "07/28/94", "02/07/95", "03/19/93", "10/04/93", 
"05/17/94", "11/15/94"), y = c(0, 0, 9, 0, -11, -11, 0, 10, 9, 
4, 5, 0, -7, -17, -13, -17, 0, 6, 6, 1, 3, 0, -9, -13, -18, -17, 
0, -8, -8, -10)), row.names = c(1L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 
10L, 11L, 12L, 13L, 14L, 15L, 16L, 17L, 18L, 19L, 20L, 21L, 22L, 
23L, 24L, 25L, 26L, 27L, 28L, 29L, 30L, 31L), class = "data.frame")

score 4 · Accepted Answer · answered Apr 20 '23 at 03:49

I've been explicit about the duplication so that it's always a multiple, even if that takes you above the exact 5 per group count. Using the data.table package:

library(data.table)
setDT(df)
df[
    df[, if(.N == 1) NULL
         else if(.N < 5) rep(.I, (5 %/% .N) + 1)
         else .I,  by=id]$V1
]

#        id     date     y
#    <char>   <char> <num>
# 1:   0102 12/03/91     0
# 2:   0102 05/05/92     9
# 3:   0102 12/03/91     0
# 4:   0102 05/05/92     9
# 5:   0102 12/03/91     0
# 6:   0102 05/05/92     9
# 7:   0103 06/22/92     0
# 8:   0103 12/17/92   -11
# 9:   0103 07/14/93   -11
#10:   0103 06/22/92     0
#11:   0103 12/17/92   -11
#12:   0103 07/14/93   -11
# ...

score 3 · Answer 2 · answered Apr 20 '23 at 03:38

Seems straightforward, but I may have misunderstood; does this solve your problem?

library(tidyverse)

df <- structure(list(id = c("0101", "0102", "0102", "0103", "0103", 
                            "0103", "0104", "0104", "0104", "0104", "0104", "0105", "0105", 
                            "0105", "0105", "0105", "0106", "0106", "0106", "0106", "0106", 
                            "0107", "0107", "0107", "0107", "0107", "0108", "0108", "0108", 
                            "0108"), date = c("10/01/91", "12/03/91", "05/05/92", "06/22/92", 
                                              "12/17/92", "07/14/93", "07/28/92", "01/14/93", "08/11/93", "02/03/94", 
                                              "08/23/94", "09/24/92", "03/05/93", "10/18/93", "04/14/94", "05/31/94", 
                                              "01/13/93", "07/27/93", "03/10/94", "09/01/94", "03/09/95", "01/15/93", 
                                              "07/23/93", "02/07/94", "07/28/94", "02/07/95", "03/19/93", "10/04/93", 
                                              "05/17/94", "11/15/94"), y = c(0, 0, 9, 0, -11, -11, 0, 10, 9, 
                                                                             4, 5, 0, -7, -17, -13, -17, 0, 6, 6, 1, 3, 0, -9, -13, -18, -17, 
                                                                             0, -8, -8, -10)), row.names = c(1L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 
                                                                                                             10L, 11L, 12L, 13L, 14L, 15L, 16L, 17L, 18L, 19L, 20L, 21L, 22L, 
                                                                                                             23L, 24L, 25L, 26L, 27L, 28L, 29L, 30L, 31L), class = "data.frame")

df %>%
  group_by(id) %>%
  filter(n() >= 2) %>%
  expand_grid(count = 1:5) %>%
  group_by(id) %>%
  slice_max(order_by = count, n = 5, 
            with_ties = FALSE) %>%
  select(-count)
#> # A tibble: 35 × 3
#> # Groups:   id [7]
#>    id    date         y
#>    <chr> <chr>    <dbl>
#>  1 0102  12/03/91     0
#>  2 0102  05/05/92     9
#>  3 0102  12/03/91     0
#>  4 0102  05/05/92     9
#>  5 0102  12/03/91     0
#>  6 0103  06/22/92     0
#>  7 0103  12/17/92   -11
#>  8 0103  07/14/93   -11
#>  9 0103  06/22/92     0
#> 10 0103  12/17/92   -11
#> # … with 25 more rows

^{Created on 2023-04-20 with reprex v2.0.2}

Ritchie Sacramento · Answer 3 · 2023-04-20T04:01:43.993

This will remove groups of n = 1, recycle groups with less than 5 observations to 5 (and leave 5 or more unchanged):

library(dplyr)

dat %>%
  filter(n() > 1, .by = id) %>%
  slice(rep(seq(n()), length.out = max(n(), 5)), .by = id)

     id     date   y
1  0102 12/03/91   0
2  0102 05/05/92   9
3  0102 12/03/91   0
4  0102 05/05/92   9
5  0102 12/03/91   0
6  0103 06/22/92   0
7  0103 12/17/92 -11
8  0103 07/14/93 -11
9  0103 06/22/92   0
10 0103 12/17/92 -11
...

GKi · Answer 4 · 2023-04-20T07:34:59.413

A way in base using split and rep in combination with [.

DF[unlist(lapply(split(seq_len(nrow(DF)), DF$id), \(i)
          if(length(i) > 1) rep(i, ceiling(5 / length(i))))),]

Result

       id     date   y
3    0102 12/03/91   0
4    0102 05/05/92   9
3.1  0102 12/03/91   0
4.1  0102 05/05/92   9
3.2  0102 12/03/91   0
4.2  0102 05/05/92   9
5    0103 06/22/92   0
6    0103 12/17/92 -11
7    0103 07/14/93 -11
5.1  0103 06/22/92   0
6.1  0103 12/17/92 -11
7.1  0103 07/14/93 -11
8    0104 07/28/92   0
9    0104 01/14/93  10
10   0104 08/11/93   9
11   0104 02/03/94   4
12   0104 08/23/94   5
13   0105 09/24/92   0
14   0105 03/05/93  -7
15   0105 10/18/93 -17
16   0105 04/14/94 -13
17   0105 05/31/94 -17
18   0106 01/13/93   0
19   0106 07/27/93   6
20   0106 03/10/94   6
21   0106 09/01/94   1
22   0106 03/09/95   3
23   0107 01/15/93   0
24   0107 07/23/93  -9
25   0107 02/07/94 -13
26   0107 07/28/94 -18
27   0107 02/07/95 -17
28   0108 03/19/93   0
29   0108 10/04/93  -8
30   0108 05/17/94  -8
31   0108 11/15/94 -10
28.1 0108 03/19/93   0
29.1 0108 10/04/93  -8
30.1 0108 05/17/94  -8
31.1 0108 11/15/94 -10

Data

DF <- structure(list(id = c("0101", "0102", "0102", "0103", "0103", 
"0103", "0104", "0104", "0104", "0104", "0104", "0105", "0105", 
"0105", "0105", "0105", "0106", "0106", "0106", "0106", "0106", 
"0107", "0107", "0107", "0107", "0107", "0108", "0108", "0108", 
"0108"), date = c("10/01/91", "12/03/91", "05/05/92", "06/22/92", 
"12/17/92", "07/14/93", "07/28/92", "01/14/93", "08/11/93", "02/03/94", 
"08/23/94", "09/24/92", "03/05/93", "10/18/93", "04/14/94", "05/31/94", 
"01/13/93", "07/27/93", "03/10/94", "09/01/94", "03/09/95", "01/15/93", 
"07/23/93", "02/07/94", "07/28/94", "02/07/95", "03/19/93", "10/04/93", 
"05/17/94", "11/15/94"), y = c(0, 0, 9, 0, -11, -11, 0, 10, 9, 
4, 5, 0, -7, -17, -13, -17, 0, 6, 6, 1, 3, 0, -9, -13, -18, -17, 
0, -8, -8, -10)), row.names = c(1L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 
10L, 11L, 12L, 13L, 14L, 15L, 16L, 17L, 18L, 19L, 20L, 21L, 22L, 
23L, 24L, 25L, 26L, 27L, 28L, 29L, 30L, 31L), class = "data.frame")

Remove single observations then duplicate remaining observations

4 Answers4