Select group by comparing two other columns in R

Question

I have a dataframe that looks like this:

  date.x     dol        PIFF_ID event
               
1 2018-02-22 2018-04-03  371700    NA
2 2018-02-23 2018-04-03  371700    NA
3 2018-02-24 2018-04-03  371700    NA
4 2018-02-25 2018-04-03  371700     1
5 2018-02-26 2018-04-03  371700    NA
6 2018-02-27 2018-04-03  371700    NA

It can be recreated with this dput:

structure(list(date.x = structure(c(17584, 17585, 17586, 17587, 
17588, 17589, 17590, 17591, 17592, 17593, 17594, 17595, 17596, 
17597, 17598, 17599, 17600, 17601, 17602, 17603, 17604, 17605, 
17606, 17607, 17608, 17609, 17610, 17611, 17612, 17613, 17614, 
17615, 17616, 17617, 17618, 17619, 17620, 17621, 17622, 17623, 
17624, 17625, 17626, 17627, 17628, 17629, 17630, 17631, 17632, 
17633, 17634, 17635, 17636, 17637, 17638, 17639, 17640, 17641, 
17642, 17643, 17644, 12931, 12932, 12933, 12934, 12935, 12936, 
12937, 12938, 12939, 12940, 12941, 12942, 12943, 12944, 12945, 
12946, 12947, 12948, 12949, 12950, 12951, 12952, 12953, 12954, 
12955, 12956, 12957, 12958, 12959, 12960, 12961, 12962, 12963, 
12964, 12965, 12966, 12967, 12968, 12969, 12970, 12971, 12972, 
12973, 12974, 12975, 12976, 12977, 12978, 12979, 12980, 12981, 
12982, 12983, 12984, 12985, 12986, 12987, 12988, 12989, 12990, 
12991, 11586, 11587, 11588, 11589, 11590, 11591, 11592, 11593, 
11594, 11595, 11596, 11597, 11598, 11599, 11600, 11601, 11602, 
11603, 11604, 11605, 11606, 11607, 11608, 11609, 11610, 11611, 
11612, 11613, 11614, 11615, 11616, 11617, 11618, 11619, 11620, 
11621, 11622, 11623, 11624, 11625, 11626, 11627, 11628, 11629, 
11630, 11631, 11632, 11633, 11634, 11635, 11636, 11637, 11638, 
11639, 11640, 11641, 11642, 11643, 11644, 11645, 11646, 14059, 
14060, 14061, 14062, 14063, 14064, 14065, 14066, 14067, 14068, 
14069, 14070, 14071, 14072, 14073, 14074, 14075, 14076, 14077, 
14078, 14079, 14080, 14081, 14082, 14083, 14084, 14085, 14086, 
14087, 14088, 14089, 14090, 14091, 14092, 14093, 14094, 14095, 
14096, 14097, 14098, 14099, 14100, 14101, 14102, 14103, 14104, 
14105, 14106, 14107, 14108, 14109, 14110, 14111, 14112, 14113, 
14114, 14115, 14116, 14117, 14118, 14119, 14059, 14060, 14061, 
14062, 14063, 14064, 14065, 14066, 14067, 14068, 14069, 14070, 
14071, 14072, 14073, 14074, 14075, 14076, 14077, 14078, 14079, 
14080, 14081, 14082, 14083, 14084, 14085, 14086, 14087, 14088, 
14089, 14090, 14091, 14092, 14093, 14094, 14095, 14096, 14097, 
14098, 14099, 14100, 14101, 14102, 14103, 14104, 14105, 14106, 
14107, 14108, 14109, 14110, 14111, 14112, 14113, 14114, 14115, 
14116, 14117, 14118, 14119), class = "Date"), dol = structure(c(17624, 
17624, 17624, 17624, 17624, 17624, 17624, 17624, 17624, 17624, 
17624, 17624, 17624, 17624, 17624, 17624, 17624, 17624, 17624, 
17624, 17624, 17624, 17624, 17624, 17624, 17624, 17624, 17624, 
17624, 17624, 17624, 17624, 17624, 17624, 17624, 17624, 17624, 
17624, 17624, 17624, 17624, 17624, 17624, 17624, 17624, 17624, 
17624, 17624, 17624, 17624, 17624, 17624, 17624, 17624, 17624, 
17624, 17624, 17624, 17624, 17624, 17624, 12971, 12971, 12971, 
12971, 12971, 12971, 12971, 12971, 12971, 12971, 12971, 12971, 
12971, 12971, 12971, 12971, 12971, 12971, 12971, 12971, 12971, 
12971, 12971, 12971, 12971, 12971, 12971, 12971, 12971, 12971, 
12971, 12971, 12971, 12971, 12971, 12971, 12971, 12971, 12971, 
12971, 12971, 12971, 12971, 12971, 12971, 12971, 12971, 12971, 
12971, 12971, 12971, 12971, 12971, 12971, 12971, 12971, 12971, 
12971, 12971, 12971, 12971, 11626, 11626, 11626, 11626, 11626, 
11626, 11626, 11626, 11626, 11626, 11626, 11626, 11626, 11626, 
11626, 11626, 11626, 11626, 11626, 11626, 11626, 11626, 11626, 
11626, 11626, 11626, 11626, 11626, 11626, 11626, 11626, 11626, 
11626, 11626, 11626, 11626, 11626, 11626, 11626, 11626, 11626, 
11626, 11626, 11626, 11626, 11626, 11626, 11626, 11626, 11626, 
11626, 11626, 11626, 11626, 11626, 11626, 11626, 11626, 11626, 
11626, 11626, 14099, 14099, 14099, 14099, 14099, 14099, 14099, 
14099, 14099, 14099, 14099, 14099, 14099, 14099, 14099, 14099, 
14099, 14099, 14099, 14099, 14099, 14099, 14099, 14099, 14099, 
14099, 14099, 14099, 14099, 14099, 14099, 14099, 14099, 14099, 
14099, 14099, 14099, 14099, 14099, 14099, 14099, 14099, 14099, 
14099, 14099, 14099, 14099, 14099, 14099, 14099, 14099, 14099, 
14099, 14099, 14099, 14099, 14099, 14099, 14099, 14099, 14099, 
14099, 14099, 14099, 14099, 14099, 14099, 14099, 14099, 14099, 
14099, 14099, 14099, 14099, 14099, 14099, 14099, 14099, 14099, 
14099, 14099, 14099, 14099, 14099, 14099, 14099, 14099, 14099, 
14099, 14099, 14099, 14099, 14099, 14099, 14099, 14099, 14099, 
14099, 14099, 14099, 14099, 14099, 14099, 14099, 14099, 14099, 
14099, 14099, 14099, 14099, 14099, 14099, 14099, 14099, 14099, 
14099, 14099, 14099, 14099, 14099, 14099, 14099), class = "Date"), 
    PIFF_ID = c(371700, 371700, 371700, 371700, 371700, 371700, 
    371700, 371700, 371700, 371700, 371700, 371700, 371700, 371700, 
    371700, 371700, 371700, 371700, 371700, 371700, 371700, 371700, 
    371700, 371700, 371700, 371700, 371700, 371700, 371700, 371700, 
    371700, 371700, 371700, 371700, 371700, 371700, 371700, 371700, 
    371700, 371700, 371700, 371700, 371700, 371700, 371700, 371700, 
    371700, 371700, 371700, 371700, 371700, 371700, 371700, 371700, 
    371700, 371700, 371700, 371700, 371700, 371700, 371700, 450200, 
    450200, 450200, 450200, 450200, 450200, 450200, 450200, 450200, 
    450200, 450200, 450200, 450200, 450200, 450200, 450200, 450200, 
    450200, 450200, 450200, 450200, 450200, 450200, 450200, 450200, 
    450200, 450200, 450200, 450200, 450200, 450200, 450200, 450200, 
    450200, 450200, 450200, 450200, 450200, 450200, 450200, 450200, 
    450200, 450200, 450200, 450200, 450200, 450200, 450200, 450200, 
    450200, 450200, 450200, 450200, 450200, 450200, 450200, 450200, 
    450200, 450200, 450200, 450200, 1600200, 1600200, 1600200, 
    1600200, 1600200, 1600200, 1600200, 1600200, 1600200, 1600200, 
    1600200, 1600200, 1600200, 1600200, 1600200, 1600200, 1600200, 
    1600200, 1600200, 1600200, 1600200, 1600200, 1600200, 1600200, 
    1600200, 1600200, 1600200, 1600200, 1600200, 1600200, 1600200, 
    1600200, 1600200, 1600200, 1600200, 1600200, 1600200, 1600200, 
    1600200, 1600200, 1600200, 1600200, 1600200, 1600200, 1600200, 
    1600200, 1600200, 1600200, 1600200, 1600200, 1600200, 1600200, 
    1600200, 1600200, 1600200, 1600200, 1600200, 1600200, 1600200, 
    1600200, 1600200, 2060200, 2060200, 2060200, 2060200, 2060200, 
    2060200, 2060200, 2060200, 2060200, 2060200, 2060200, 2060200, 
    2060200, 2060200, 2060200, 2060200, 2060200, 2060200, 2060200, 
    2060200, 2060200, 2060200, 2060200, 2060200, 2060200, 2060200, 
    2060200, 2060200, 2060200, 2060200, 2060200, 2060200, 2060200, 
    2060200, 2060200, 2060200, 2060200, 2060200, 2060200, 2060200, 
    2060200, 2060200, 2060200, 2060200, 2060200, 2060200, 2060200, 
    2060200, 2060200, 2060200, 2060200, 2060200, 2060200, 2060200, 
    2060200, 2060200, 2060200, 2060200, 2060200, 2060200, 2060200, 
    2060300, 2060300, 2060300, 2060300, 2060300, 2060300, 2060300, 
    2060300, 2060300, 2060300, 2060300, 2060300, 2060300, 2060300, 
    2060300, 2060300, 2060300, 2060300, 2060300, 2060300, 2060300, 
    2060300, 2060300, 2060300, 2060300, 2060300, 2060300, 2060300, 
    2060300, 2060300, 2060300, 2060300, 2060300, 2060300, 2060300, 
    2060300, 2060300, 2060300, 2060300, 2060300, 2060300, 2060300, 
    2060300, 2060300, 2060300, 2060300, 2060300, 2060300, 2060300, 
    2060300, 2060300, 2060300, 2060300, 2060300, 2060300, 2060300, 
    2060300, 2060300, 2060300, 2060300, 2060300), event = c(NA, 
    NA, NA, 1, NA, NA, NA, NA, 2, 2, NA, 3, 3, 3, 3, NA, NA, 
    4, 4, 4, NA, NA, 5, NA, 6, 6, 6, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, 7, 7, 7, NA, NA, 8, 8, 8, NA, NA, NA, 9, 9, 9, 
    9, 9, NA, 10, NA, NA, NA, NA, NA, NA, NA, NA, 1, 1, 1, 1, 
    NA, NA, NA, 2, NA, 3, 3, NA, NA, NA, 4, 4, 4, 4, 4, 4, NA, 
    NA, NA, NA, NA, NA, 5, 5, NA, NA, 6, 6, 6, 6, 6, NA, NA, 
    7, 7, 7, 7, 7, 7, 7, 7, NA, NA, NA, 8, 8, NA, 9, 9, NA, NA, 
    10, 10, NA, 11, 11, 1, 1, 1, 1, 1, 1, NA, NA, NA, 2, 2, NA, 
    NA, 3, 3, NA, 4, 4, NA, 5, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, 6, 6, NA, NA, 7, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, 8, 8, NA, 9, 9, NA, NA, NA, NA, NA, 
    NA, NA, NA, 1, NA, 2, 2, 2, NA, NA, 3, 3, NA, NA, 4, 4, 4, 
    4, 4, NA, NA, 5, 5, 5, 5, NA, NA, NA, NA, NA, 6, 6, 6, 6, 
    6, 6, 6, 6, NA, 7, NA, 8, 8, 8, NA, NA, NA, NA, NA, 9, 9, 
    NA, 10, NA, 11, 11, NA, 12, 12, NA, NA, NA, NA, NA, 1, NA, 
    2, 2, 2, NA, NA, 3, 3, NA, NA, 4, 4, 4, 4, 4, NA, NA, 5, 
    5, 5, 5, NA, NA, NA, NA, NA, 6, 6, 6, 6, 6, 6, 6, 6, NA, 
    7, NA, 8, 8, 8, NA, NA, NA, NA, NA, 9, 9, NA, 10, NA, 11, 
    11, NA, 12, 12, NA, NA, NA, NA)), row.names = c(NA, -305L
), class = c("tbl_df", "tbl", "data.frame"))
> df = df %>% 
+   group_by(PIFF_ID) %>% 
+   filter(cur_group_id() %in% 1:2) %>% 
+   ungroup()
> dput(df)
structure(list(date.x = structure(c(17584, 17585, 17586, 17587, 
17588, 17589, 17590, 17591, 17592, 17593, 17594, 17595, 17596, 
17597, 17598, 17599, 17600, 17601, 17602, 17603, 17604, 17605, 
17606, 17607, 17608, 17609, 17610, 17611, 17612, 17613, 17614, 
17615, 17616, 17617, 17618, 17619, 17620, 17621, 17622, 17623, 
17624, 17625, 17626, 17627, 17628, 17629, 17630, 17631, 17632, 
17633, 17634, 17635, 17636, 17637, 17638, 17639, 17640, 17641, 
17642, 17643, 17644, 12931, 12932, 12933, 12934, 12935, 12936, 
12937, 12938, 12939, 12940, 12941, 12942, 12943, 12944, 12945, 
12946, 12947, 12948, 12949, 12950, 12951, 12952, 12953, 12954, 
12955, 12956, 12957, 12958, 12959, 12960, 12961, 12962, 12963, 
12964, 12965, 12966, 12967, 12968, 12969, 12970, 12971, 12972, 
12973, 12974, 12975, 12976, 12977, 12978, 12979, 12980, 12981, 
12982, 12983, 12984, 12985, 12986, 12987, 12988, 12989, 12990, 
12991), class = "Date"), dol = structure(c(17624, 17624, 17624, 
17624, 17624, 17624, 17624, 17624, 17624, 17624, 17624, 17624, 
17624, 17624, 17624, 17624, 17624, 17624, 17624, 17624, 17624, 
17624, 17624, 17624, 17624, 17624, 17624, 17624, 17624, 17624, 
17624, 17624, 17624, 17624, 17624, 17624, 17624, 17624, 17624, 
17624, 17624, 17624, 17624, 17624, 17624, 17624, 17624, 17624, 
17624, 17624, 17624, 17624, 17624, 17624, 17624, 17624, 17624, 
17624, 17624, 17624, 17624, 12971, 12971, 12971, 12971, 12971, 
12971, 12971, 12971, 12971, 12971, 12971, 12971, 12971, 12971, 
12971, 12971, 12971, 12971, 12971, 12971, 12971, 12971, 12971, 
12971, 12971, 12971, 12971, 12971, 12971, 12971, 12971, 12971, 
12971, 12971, 12971, 12971, 12971, 12971, 12971, 12971, 12971, 
12971, 12971, 12971, 12971, 12971, 12971, 12971, 12971, 12971, 
12971, 12971, 12971, 12971, 12971, 12971, 12971, 12971, 12971, 
12971, 12971), class = "Date"), PIFF_ID = c(371700, 371700, 371700, 
371700, 371700, 371700, 371700, 371700, 371700, 371700, 371700, 
371700, 371700, 371700, 371700, 371700, 371700, 371700, 371700, 
371700, 371700, 371700, 371700, 371700, 371700, 371700, 371700, 
371700, 371700, 371700, 371700, 371700, 371700, 371700, 371700, 
371700, 371700, 371700, 371700, 371700, 371700, 371700, 371700, 
371700, 371700, 371700, 371700, 371700, 371700, 371700, 371700, 
371700, 371700, 371700, 371700, 371700, 371700, 371700, 371700, 
371700, 371700, 450200, 450200, 450200, 450200, 450200, 450200, 
450200, 450200, 450200, 450200, 450200, 450200, 450200, 450200, 
450200, 450200, 450200, 450200, 450200, 450200, 450200, 450200, 
450200, 450200, 450200, 450200, 450200, 450200, 450200, 450200, 
450200, 450200, 450200, 450200, 450200, 450200, 450200, 450200, 
450200, 450200, 450200, 450200, 450200, 450200, 450200, 450200, 
450200, 450200, 450200, 450200, 450200, 450200, 450200, 450200, 
450200, 450200, 450200, 450200, 450200, 450200, 450200), event = c(NA, 
NA, NA, 1, NA, NA, NA, NA, 2, 2, NA, 3, 3, 3, 3, NA, NA, 4, 4, 
4, NA, NA, 5, NA, 6, 6, 6, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
7, 7, 7, NA, NA, 8, 8, 8, NA, NA, NA, 9, 9, 9, 9, 9, NA, 10, 
NA, NA, NA, NA, NA, NA, NA, NA, 1, 1, 1, 1, NA, NA, NA, 2, NA, 
3, 3, NA, NA, NA, 4, 4, 4, 4, 4, 4, NA, NA, NA, NA, NA, NA, 5, 
5, NA, NA, 6, 6, 6, 6, 6, NA, NA, 7, 7, 7, 7, 7, 7, 7, 7, NA, 
NA, NA, 8, 8, NA, 9, 9, NA, NA, 10, 10, NA, 11, 11)), row.names = c(NA, 
-122L), class = c("tbl_df", "tbl", "data.frame"))

What I want to achieve is: First I want to group it by the column PIFF_ID. I then want to compare the columns dol and date.x. I then want to look at the column event. If it is not NA, I want to get all the rows with the same event-number for that group of PIFF_ID. I cant wrap my head around how to achieve that at the moment.

Expected output

So for example the PIFF_ID is 1000 the dol is 2020-03-03 and date.x is also 2020-03-03. Then lets assume that the event is not na and is for example 10. Then I want to get all the rows for that PIFF_ID where the event == 10.

How I solved it

Besides all the bad examples and the unclear explanation I gave, this is the solution for my specific problem:

df %>%
  filter(!is.na(event)) %>%
  group_by(PIFF_ID, event) %>%
  mutate(
    new_var = ifelse(any(date.x == dol), event, NA)
  ) %>%
  filter(!is.na(new_var)

user_3962914 · Answer

You can try :

library(dplyr)

df %>% group_by(PIFF_ID) %>% filter(event == event[date.x == dol]) %>% ungroup

If you need the entire group if any date matches :

df %>% group_by(PIFF_ID) %>% filter(any(date.x == dol)) %>% ungroup

user_13321647 · Answer

You could try this code:

library(dplyr)
df %>% 
  group_by(PIFF_ID) %>% 
  mutate(newvar = case_when(date.x == dol ~ event))

user_3732271 · Answer

We can also do

 library(dplyr)
df %>%
   group_by(PIFF_ID) %>%
   slice(if(any(date.x == dol)) row_number() else 0)

Select group by comparing two other columns in R

3 Answers