reciprocated_transactions.R

# working out whether transactions have reciprocated

library(tibble)
library(dplyr)

# let's boil this example down

df_boil <- tribble(
    ~sender, ~receiver, ~transaction_id,
    1,        2,              "A",
    2,        1,              "B",
    3,        1,              "C"
)

df_boil
#> # A tibble: 3 × 3
#>   sender receiver transaction_id
#>    <dbl>    <dbl>          <chr>
#> 1      1        2              A
#> 2      2        1              B
#> 3      3        1              C

# so you want times where 1 has sent to 2 AND 2 has sent to 1.

df_boil_rec <- df_boil %>%
    # make a new column that is the combination of both send and receive
    mutate(concat = paste0(sender,receiver),
           rev_con = paste0(receiver,sender),
           # ask if the concat is in the reversed concat
           reciprocated = if_else(concat %in% rev_con,
                                  # if it is, they have reciprocated
                                  true = "reciprocated",
                                  # if not, they haven't
                                  false = "not_reciprocated"))

df_boil_rec
#> # A tibble: 3 × 6
#>   sender receiver transaction_id concat rev_con     reciprocated
#>    <dbl>    <dbl>          <chr>  <chr>   <chr>            <chr>
#> 1      1        2              A     12      21     reciprocated
#> 2      2        1              B     21      12     reciprocated
#> 3      3        1              C     31      13 not_reciprocated

# subset the rows based upon whether or not they have reciprocated
df_boil_rec %>% filter(reciprocated == "reciprocated")
#> # A tibble: 2 × 6
#>   sender receiver transaction_id concat rev_con reciprocated
#>    <dbl>    <dbl>          <chr>  <chr>   <chr>        <chr>
#> 1      1        2              A     12      21 reciprocated
#> 2      2        1              B     21      12 reciprocated


# An example with similar(ish) data to your screenshot ----------------

df_bigger_example <- tribble(
    ~sender, ~receiver, ~transaction_id,
    "AU100",  "AU200",     "A",
    "AU200",  "AU300",     "C",
    "AU300",  "AU100",     "Q",
    "AU200",  "AU100",     "M",
    "AU300",  "AU200",     "G"
)

df_bigger_example
#> # A tibble: 5 × 3
#>   sender receiver transaction_id
#>    <chr>    <chr>          <chr>
#> 1  AU100    AU200              A
#> 2  AU200    AU300              C
#> 3  AU300    AU100              Q
#> 4  AU200    AU100              M
#> 5  AU300    AU200              G

df_bigger_example %>%
mutate(concat = paste0(sender,receiver),
       rev_con = paste0(receiver,sender),
       reciprocated = if_else(concat %in% rev_con,
                      "reciprocated",
                      "not_reciprocated"))
#> # A tibble: 5 × 6
#>   sender receiver transaction_id     concat    rev_con     reciprocated
#>    <chr>    <chr>          <chr>      <chr>      <chr>            <chr>
#> 1  AU100    AU200              A AU100AU200 AU200AU100     reciprocated
#> 2  AU200    AU300              C AU200AU300 AU300AU200     reciprocated
#> 3  AU300    AU100              Q AU300AU100 AU100AU300 not_reciprocated
#> 4  AU200    AU100              M AU200AU100 AU100AU200     reciprocated
#> 5  AU300    AU200              G AU300AU200 AU200AU300     reciprocated

df_reciprocated <-
    df_bigger_example %>%
    mutate(concat = paste0(sender,receiver),
           rev_con = paste0(receiver,sender),
           reciprocated = if_else(concat %in% rev_con,
                                  "reciprocated",
                                  "not_reciprocated"))

df_reciprocated
#> # A tibble: 5 × 6
#>   sender receiver transaction_id     concat    rev_con     reciprocated
#>    <chr>    <chr>          <chr>      <chr>      <chr>            <chr>
#> 1  AU100    AU200              A AU100AU200 AU200AU100     reciprocated
#> 2  AU200    AU300              C AU200AU300 AU300AU200     reciprocated
#> 3  AU300    AU100              Q AU300AU100 AU100AU300 not_reciprocated
#> 4  AU200    AU100              M AU200AU100 AU100AU200     reciprocated
#> 5  AU300    AU200              G AU300AU200 AU200AU300     reciprocated

df_reciprocated %>% filter(reciprocated == "reciprocated")
#> # A tibble: 4 × 6
#>   sender receiver transaction_id     concat    rev_con reciprocated
#>    <chr>    <chr>          <chr>      <chr>      <chr>        <chr>
#> 1  AU100    AU200              A AU100AU200 AU200AU100 reciprocated
#> 2  AU200    AU300              C AU200AU300 AU300AU200 reciprocated
#> 3  AU200    AU100              M AU200AU100 AU100AU200 reciprocated
#> 4  AU300    AU200              G AU300AU200 AU200AU300 reciprocated