Turn dummy variables into weights

Question

I have a table of dummy variables where the value is either 1 or NA. I know want to create a uniform weight for these dummies across rows. This is my beginning dataset, it is in xts format:

           NESN ROG NOVN ZURN ABBN UBSG LONN
1989-12-01   1   NA   1   1    NA   1    NA
1990-01-01   1   NA   1   1    1    1    NA

I would then like to split the 1's so that the sum of the row is equal to 1. The ending dataset would then look like this:

            NESN ROG NOVN ZURN ABBN UBSG LONN
1989-12-01  0.25 NA  0.25 0.25 NA   0.25  NA
1990-01-01  0.2  NA  0.2  0.2  0.2  0.2   NA

So if there are five stocks with a dummy equal to 1 in the whole row the 1 will be changed to 0.2. If there are 4 stocks it will be changed to 0.25 and so on. So far I was thinking of using replace to replace the 1's with their respective weight. However, I do not know how to do this over multiple rows with different weights. I am doing this so that I can ultimately calculate portfolio turnover.

Thank you for the answers. However, I ended up constructing my portfolios using the portsort package where the portfolio turnover function is already built-in along with other useful functions. — Elias K., May 24 '21 at 12:12

LC-datascientist · Accepted Answer · 2021-05-24T10:29:34.980

You can divide the data frame by its row sums.

# data structure
df <- structure(list(NESN = c(1L, 1L), ROG = c(NA, NA), NOVN = c(1L, 
1L), ZURN = c(1L, 1L), ABBN = c(NA, 1L), UBSG = c(1L, 1L), LONN = c(NA, 
NA)), class = "data.frame", row.names = c("1989-12-01", "1990-01-01"
))

# solution
df/rowSums(df, na.rm=T)
#           NESN ROG NOVN ZURN ABBN UBSG LONN
#1989-12-01 0.25  NA 0.25 0.25   NA 0.25   NA
#1990-01-01 0.20  NA 0.20 0.20  0.2 0.20   NA

score 1 · Answer 2 · answered May 24 '21 at 10:18

You can use apply rowwise and transpose:

df <- data.frame(NESN = c(1,1), ROG = c(NA,NA), NOVN = c(1,1),
           ZURN = c(1,1), ABBN = c(NA,1), UBSG = c(1,1),
           LONN = c(NA,NA))

t(apply(df, 1, function(x){x/sum(x, na.rm = TRUE)}))

     NESN ROG NOVN ZURN ABBN UBSG LONN
[1,] 0.25  NA 0.25 0.25   NA 0.25   NA
[2,] 0.20  NA 0.20 0.20  0.2 0.20   NA

Turn dummy variables into weights

2 Answers2