How to identify a paired transaction using r

Question

I have a data frame like the one below

AccountNo<-c(11223344,11223344,11223344,1133399,1133399,127788,127788)
transactiondesc<-c("BUY","BUY","SELL","SELL","SELL","BUY","BUY")

I want to the code to see for an account if it has both BUY and SELL (regardless of the number of BUY and SELL)

expected output:

AccountNo<-c(11223344,11223344,11223344,1133399,1133399,127788,127788)
transactiondesc<-c("BUY","BUY","SELL","SELL","SELL","BUY","BUY")
TRUE/FALSE<-c("TRUE","TRUE","TRUE","FALSE","FALSE","FALSE","FALSE")

I was using the following approach:

Testing<-Combined %>%
  group_by(AccountNo)%>%
  mutate(BUY = case_when(transactiondesc == 'BUY' ~ 1,
                                   TRUE ~ 0))%>%
mutate(BUY = case_when(transactiondesc == 'SELL' ~ 1,
                                   TRUE ~ 0))

Didn't get what i want

score 2 · Accepted Answer · answered Jul 22 '20 at 17:23

One option using the tidyverse:

library(dplyr)
dat %>%
  group_by(AccountNo) %>%
  mutate(BuySell = all(c("BUY", "SELL") %in% transactiondesc)) %>%
  ungroup()
# # A tibble: 7 x 3
#   AccountNo transactiondesc BuySell
#       <dbl> <chr>           <lgl>  
# 1  11223344 BUY             TRUE   
# 2  11223344 BUY             TRUE   
# 3  11223344 SELL            TRUE   
# 4   1133399 SELL            FALSE  
# 5   1133399 SELL            FALSE  
# 6    127788 BUY             FALSE  
# 7    127788 BUY             FALSE

Or base R:

agg <- aggregate(transactiondesc ~ AccountNo, data=dat, FUN = function(x) all(c("BUY", "SELL") %in% x))
agg
#   AccountNo transactiondesc
# 1    127788           FALSE
# 2   1133399           FALSE
# 3  11223344            TRUE
colnames(agg)[2] <- "BuySell"
merge(dat, agg, by = "AccountNo", all.x = TRUE)
#   AccountNo transactiondesc BuySell
# 1    127788             BUY   FALSE
# 2    127788             BUY   FALSE
# 3   1133399            SELL   FALSE
# 4   1133399            SELL   FALSE
# 5  11223344             BUY    TRUE
# 6  11223344             BUY    TRUE
# 7  11223344            SELL    TRUE

Data:

dat <- structure(list(AccountNo = c(11223344, 11223344, 11223344, 1133399, 
1133399, 127788, 127788), transactiondesc = c("BUY", "BUY", "SELL", 
"SELL", "SELL", "BUY", "BUY")), class = "data.frame", row.names = c(NA, 
-7L))

perfect, works. Will accept as soon as it lets me – Piccinin1992 Jul 22 '20 at 17:25 — Piccinin1992, Jul 22 '20 at 17:25

How to identify a paired transaction using r

1 Answers1