How do I extract either second occurrence of repeated subject ID or first if not repeated?

user_13562811 0
Viewed 209

I am looking to extract the second occurrence of a subject ID (the entire row of their data) OR the first if the row is not repeated.

These data are from repeated visits and we are interested in only subjects from last-non-missing data, meaning that either the subject has "screening" information and no "injection" or they have both. This is how we define "baseline." If subject has both, we only want to retain the row of data with the injection (last data before treatment), if only screening then screening (that's then the last data before treatment and will equal baseline).

Here's some data:

df1 <- data.frame(ID = c(1, 2, 2, 3, 3, 4),
                  visit = c('screening', 'screening', 'injection', 'screening', 
'injection', 'screening'),
                  var2 = c(1, 6, 3, 12, 0, 2))

What I have attempted:

  • Separating out and re-merging data frames that contain the two qualifiers for these subjects. But when I do so, the columns just get duplicated, producing a wide instead of long dataset (when they obviously match by the exact same ID).
  • Using a filter in dplyr with multiple conditions, but it only catches those with screening because that always comes first for the repeated subjects.

Suggestions?

r
6 Answers

use slice_tail()

library(dplyr, warn.conflicts = F)
df1 %>%
  group_by(ID) %>%
  slice_tail()

#> # A tibble: 4 x 3
#> # Groups:   ID [4]
#>      ID visit      var2
#>   <dbl> <chr>     <dbl>
#> 1     1 screening     1
#> 2     2 injection     3
#> 3     3 injection     0
#> 4     4 screening     2

Created on 2021-07-23 by the reprex package (v2.0.0)

user_28848590

Groupwise conditions can be coded easily with dplyr. This will always extract the last row for each ID.

library(dplyr)

df1 %>% 
  group_by(ID) %>% 
  filter(row_number() == n())

If you want to always extract the first or second row, use min() on top of the code above.

df1 %>% 
  group_by(ID) %>% 
  filter(row_number() == min(n(), 2))

In both cases, the result is the filtered data formatted as a tibble

# A tibble: 4 x 3
# Groups:   ID [4]
     ID visit      var2
  <dbl> <fct>     <dbl>
1     1 screening     1
2     2 injection     3
3     3 injection     0
4     4 screening     2
user_43570170

A base R option with subset + ave + match

subset(
    df1,
    !!ave(match(visit, c("screening", "injection")), ID, FUN = function(x) x == length(x))
)

gives

  ID     visit var2
1  1 screening    1
3  2 injection    3
5  3 injection    0
6  4 screening    2
user_121587570

Using duplicated from base R

subset(df1, !duplicated(ID, fromLast = TRUE))
  ID     visit var2
1  1 screening    1
3  2 injection    3
5  3 injection    0
6  4 screening    2
user_37322710

I've used the new pipe below so replace |> with %>% if you prefer or can only use the old pipe (from magrittr rather than base). But essentially I've addressed these conditions separately. First I used unique() to remove duplicate rows. Then I've created additional indicators to select the second row with the same ID where one exists (now that there are no duplicate rows).

library(tidyverse)

df1 <- data.frame(ID = c(1, 2, 2, 3, 3, 4),
                  visit = c('screening', 'screening', 'injection', 'screening', 
                            'injection', 'screening'),
                  var2 = c(1, 6, 3, 12, 0, 2))

df1 |> 
# Remove exact duplicate rows
  unique() |> 
  group_by(ID) |> 
  # Create two new indicators, one which shows the row number for the group (i.e first, second, ...)
  # The second indicator shows the total number of duplicate entries for the ID
  mutate(row_number_by_id = row_number(), count = n()) |> 
  # Select only the rows that appear second or that only have one entry for the ID
  filter(count == 1 | row_number_by_id == 2)
#> # A tibble: 4 x 5
#> # Groups:   ID [4]
#>      ID visit      var2 row_number_by_id count
#>   <dbl> <chr>     <dbl>            <int> <int>
#> 1     1 screening     1                1     1
#> 2     2 injection     3                2     2
#> 3     3 injection     0                2     2
#> 4     4 screening     2                1     1

Created on 2021-07-23 by the reprex package (v2.0.0)

user_155096200

You can use the min() function to refer to the alphabetical order of the visit string.

> df1 %>% filter(visit == "screening" || visit == "injection") %>%
group_by(ID) %>% summarise(min(visit))
# A tibble: 4 x 2
     ID `min(visit)`
  <dbl> <chr>       
1     1 screening   
2     2 injection   
3     3 injection   
4     4 screening

See other summarise function here.

Or using the row_number()

df1 %>% filter(visit == "screening" || visit == "injection") %>%
group_by(ID) %>% filter(row_number() == max(row_number()))

If you have another column that can identify the order of these rows, I would suggest you use that one.

user_164832240
Related