0

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

r2evans
  • 141,215
  • 6
  • 77
  • 149
DBT
  • 45
  • 7
  • Please do not post an image of code/data/errors: it cannot be copied or searched (SEO), it breaks screen-readers, and it may not fit well on some mobile devices. Ref: https://meta.stackoverflow.com/a/285557 (and https://xkcd.com/2116/). Please just include the code, console output, or data (e.g., `dput(head(x))` or `data.frame(...)`) directly. – r2evans Sep 21 '20 at 18:51
  • It seems really odd to have `df2 = df1 %>% ... %>% print() -> dt2`. They should be equivalent (`identical(df2, dt2)`), so there's no loss here, but just ... odd. – r2evans Sep 21 '20 at 19:12
  • You are correct here, I was just in the lab trying out different methods. They are indeed the same. Thank you for your quick response and help on this. – DBT Sep 22 '20 at 13:46

1 Answers1

1

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
r2evans
  • 141,215
  • 6
  • 77
  • 149