rdplyrtidyrtidyquant

Changing the value in a rank given a condition


I am trying to transform a table of stocks ranked by their return to a matrix of values that would be inputted to PerformanceAnalytics::ReturnPortfolio as the weights of the stocks in the portfolio.

What I am specially looking to do is to transform the ranking value of the 33% best stocks to 1, the 33% worst-performing stocks to -1 and the rest to 0.

This is the original code where I am just taking the best stocks and changing their ranked value to 1:

asset_ranking <- db_Momentum %>%
  dplyr::select(-date) %>%
  as.matrix() %>%
  rowRanks(ties.method = "min") %>%
  as.data.frame() %>%
  dplyr::mutate(date = db_Momentum$date) %>%
  dplyr::select(date, everything()) %>%
  purrr::set_names(c("date", tickets)) %>%
  as_tibble()

# Analyzing Sector Selection
asset_selection <- asset_ranking %>%
  mutate_if(is.numeric, ~ + ( . > (length(tickets) - n_assets_buy))) %>%
  dplyr::select(-date)

This is an example of what it is like now:

AAPL IBM KO T TLT SPY
1 2 3 4 6 5
2 1 3 5 4 6
1 4 2 5 3 6
6 4 5 2 1 3

And this is what I would like it to be:

AAPL IBM KO T TLT SPY
1 1 0 0 -1 -1
1 1 0 -1 0 -1
1 0 1 -1 0 -1
-1 0 -1 1 1 0

Solution

  • We could loop over the rows with apply and MARGIN = 1, get the quantile with probs as .33 and .67, pass that as breaks in the cut, convert to integer and use that index for replacing the values to 1, 0, -1

    asset_selection[] <- t(apply(asset_selection, 1, function(x)
       c(1, 0, -1)[as.integer(cut(x, c(-Inf, quantile(x, c(.33, .67)), Inf)))]))
    

    -output

    asset_selection
    #  AAPL IBM KO  T TLT SPY
    #1    1   1  0  0  -1  -1
    #2    1   1  0 -1   0  -1
    #3    1   0  1 -1   0  -1
    #4   -1   0 -1  1   1   0
    

    data

    asset_selection <- structure(list(AAPL = c(1, 2, 1, 6), 
          IBM = c(2, 1, 4, 4), KO = c(3, 
     3, 2, 5), T = c(4, 5, 5, 2), TLT = c(6, 4, 3, 1), SPY = c(5, 
    6, 6, 3)), class = "data.frame", row.names = c(NA, -4L))