CODEWITHSUNDEEP
r
DBT
DBT

Reputation: 47

Consecutive wins/losses R

I am still new to R and learning methods for conducting analysis. I have a df which I want to count the consecutive wins/losses based on column "x9". This shows the gain/loss (positive value or negative value) for the trade entered. I did find some help on code that helped with assigning a sign, sign lag and change, however, I am looking for counter to count the consecutive wins until a loss is achieved then reset, and then count the consecutive losses until a win is achieved. Overall am looking for assistance to adjust the counter to reset when consecutive wins/losses are interrupted. I have some sample code below and a attached .png to explain my thoughts

#Read in df 
df=vroom::vroom(file = "analysis.csv")

#Filter df for specfic order types
df1 = filter(df, (x3=="s/l") |(x3=="t/p"))

#Create additional column to tag wins/losses in df1 
index <- c("s/l","t/p")
values <- c("Loss", "Win")
df1$col2 <- values[match(df1$x3, index)]
df1

#Mutate df to review changes, attempt to review consecutive wins and losses & reset when a 
#positive / negative value is encountered
df2=df1 %>%
  mutate(sign = ifelse(x9 > 0, "pos", ifelse(x9 < 0, "neg", "zero")), # get the sign of the value
         sign_lag = lag(sign, default = sign[9]),                     # get previous value (exception in the first place)
         change = ifelse(sign == sign_lag, 1 , 0),                    # check if there's a change
         series_id = cumsum(change)+1) %>%                            # create the series id
  print() -> dt2

enter image description here

Upvotes: 0

Views: 134

Answers (1)

r2evans
r2evans

Reputation: 160687

I think you can use rle for this. By itself, it doesn't immediately provide a grouping-like functionality, but we can either use data.table::rleid or construct our own function:

# borrowed from https://stackoverflow.com/a/62007567/3358272
myrleid <- function(x) {
  rl <- rle(x)$lengths
  rep(seq_along(rl), times = rl)
}

x9 <- c(-40.57,-40.57,-40.08,-40.08,-40.09,-40.08,-40.09,-40.09,-39.6,-39.6,-49.6,-39.6,-39.61,-39.12,-39.12-39.13,782.58,-41.04)
tibble(x9) %>%
  mutate(grp = myrleid(x9 > 0)) %>%
  group_by(grp) %>%
  mutate(row = row_number()) %>%
  ungroup()
# # A tibble: 17 x 3
#       x9   grp   row
#    <dbl> <int> <int>
#  1 -40.6     1     1
#  2 -40.6     1     2
#  3 -40.1     1     3
#  4 -40.1     1     4
#  5 -40.1     1     5
#  6 -40.1     1     6
#  7 -40.1     1     7
#  8 -40.1     1     8
#  9 -39.6     1     9
# 10 -39.6     1    10
# 11 -49.6     1    11
# 12 -39.6     1    12
# 13 -39.6     1    13
# 14 -39.1     1    14
# 15 -78.2     1    15
# 16 783.      2     1
# 17 -41.0     3     1

Upvotes: 1

Related Questions