Pfthb

I have a dataset similar to the following:

Journal_ref <- c("1111","2222","2222","2222","3333","3333","4444","4444")
Journal_type <- c("Adj","Rev","Adj","Rev","Rev","Rev","Adj","Adj")
Journal_value <- c(90,10000,12000,80,9000,500,65,2500)
Dataset <- data.frame(Journal_ref,Journal_type,Journal_value)

For each Journal_ref group I am seeking to filter/select rows based on the following conditions:

• Where "Adj" is included within Journal_type, filter/select to return the last "Adj" row in the Journal_ref group, and


• Where "Adj" is not included within Journal_type, filter/select to return the last "Rev" in the Journal_ref group

Based on the example above, the final output required would be:

Journal_ref Journal_type Journal_value
1111 Adj 90
2222 Adj 12000
3333 Rev 500
4444 Adj 2500

I have attempted using various combinations of group_by, filter, if, ifelse, grepl, select and slice with no success.

Any help would be appreciated, particularly using dplyr.

Try this:

library(dplyr)

Dataset %>%
 group_by(Journal_ref, Journal_type) %>%
 summarise(Journal_value = last(Journal_value)) %>%
 ungroup() %>% group_by(Journal_ref) %>%
 filter(!(n() > 1 & Journal_type == "Rev"))

Output:

 Journal_ref Journal_type Journal_value
 <fct> <fct> <dbl>
1 1111 Adj 90
2 2222 Adj 12000
3 3333 Rev 500
4 4444 Adj 2500

Another possible solution:

Dataset %>% 
 group_by(Journal_ref) %>% 
 filter(Journal_type == c("Rev","Adj")[1 + any(Journal_type == "Adj")]) %>% 
 slice(n())

which gives:

# A tibble: 4 x 3
# Groups: Journal_ref [4]
 Journal_ref Journal_type Journal_value
 <fct> <fct> <dbl>
1 1111 Adj 90
2 2222 Adj 12000
3 3333 Rev 500
4 4444 Adj 2500

What this does:

• You group by Journal_ref
  


• Then you filter Journal_type for only Adj when present and Rev when there is no Adj in a group. Using c("Rev","Adj")[1 + any(Journal_type == "Adj")] gives you Adj when there is at least one present in a group and it gives Rev when there is no Adj present in a group.


• Finally use use slice(n()) tot select the last row of each group.

You could also do this with if_else:

Dataset %>% 
 group_by(Journal_ref) %>% 
 filter(Journal_type == if_else(any(Journal_type == "Adj"), "Adj", "Rev")) %>% 
 slice(n())

A dplyr way of doing it is as follows.

library(dplyr)

Dataset %>%
 group_by(Journal_ref) %>%
 mutate(Adj = any(Journal_type == "Adj"),
 i = ifelse(Adj, last(which(Journal_type == "Adj")), last(which(Journal_type == "Rev")))) %>%
 filter(row_number() == i) %>%
 select(-Adj, -i)
## A tibble: 4 x 3
## Groups: Journal_ref [4]
# Journal_ref Journal_type Journal_value
# <fct> <fct> <dbl>
#1 1111 Adj 90
#2 2222 Adj 12000
#3 3333 Rev 500
#4 4444 Adj 2500