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 |
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
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))