How to find the next occurrence in a data.frame in R?

user_15071519 0
Viewed 119

Assume we have an email dataset with a sender and a recipient in every row. We want to find the next occurrence in the dataset for which the sender and the recipient are interchanged. So if sender==x & recipient==y, we are looking for the next row that has sender==y & recipient==x. Subsequently, we want to calculate the difference between counts for those observations. See the column diff_count for the desired output.

# creating the data.frame
id = 1:10
sender = c(1, 2, 3, 2, 3, 1, 2, 1, 2, 3)
recipient = c(2, 1, 2, 3, 1, 2, 3, 3, 1, 1)
count = c(1, 4, 5, 7, 12, 17, 24, 31, 34, 41)
df <- data.frame(id, sender, recipient, count)

# output should look like this
df$diff_count <- c(3, 13, 2, NA, 19, 17, NA, 10, NA, NA)

If there are no more observations that satisfy the requirement, then we simply fill in NA. Solution should be relatively easy with tidyverse, but I seem not to be able to do it.

tidyverse r
2 Answers

There should be easier ways, but below is one way using a custom function in tidyverse style:

library(dplyr)

calc_diff <- function(df, send, recp, cnt) {
  df %>% 
    slice_tail(n = nrow(df) - cur_group_rows()) %>% 
    filter(sender == send, recipient == recp) %>% 
    slice_head(n = 1) %>% 
    pull(count) %>% 
    {ifelse(length(.) == 0, NA, .)} %>% 
    `-`(., cnt)   
}

df %>% 
  rowwise(id) %>% 
  mutate(diff_count = calc_diff(df,
                                send = recipient,
                                recp = sender,
                                cnt = count))
#> # A tibble: 10 x 5
#> # Rowwise:  id
#>       id sender recipient count diff_count
#>    <int>  <dbl>     <dbl> <dbl>      <dbl>
#>  1     1      1         2     1          3
#>  2     2      2         1     4         13
#>  3     3      3         2     5          2
#>  4     4      2         3     7         NA
#>  5     5      3         1    12         19
#>  6     6      1         2    17         17
#>  7     7      2         3    24         NA
#>  8     8      1         3    31         10
#>  9     9      2         1    34         NA
#> 10    10      3         1    41         NA

Created on 2021-08-20 by the reprex package (v2.0.1)

user_93493020

Another dplyr-way without a custom function but several self joins:

library(dplyr)

data %>% 
  left_join(data, 
            by = c("sender" = "recipient", "recipient" = "sender"),
            suffix = c("", ".y")) %>% 
  filter(id < id.y) %>% 
  group_by(id) %>% 
  slice_min(id.y) %>% 
  ungroup() %>% 
  mutate(diff_count = count.y - count) %>% 
  right_join(data) %>% 
  select(-matches("\\.(y|x)")) %>% 
  arrange(id)

returns

Joining, by = c("id", "sender", "recipient", "count")
# A tibble: 10 x 5
      id sender recipient count diff_count
   <int>  <dbl>     <dbl> <dbl>      <dbl>
 1     1      1         2     1          3
 2     2      2         1     4         13
 3     3      3         2     5          2
 4     4      2         3     7         NA
 5     5      3         1    12         19
 6     6      1         2    17         17
 7     7      2         3    24         NA
 8     8      1         3    31         10
 9     9      2         1    34         NA
10    10      3         1    41         NA
user_125052510
Related