CODEWITHSUNDEEP
rdataframematrix-multiplication
lebelinoz
lebelinoz

Reputation: 5068

How to do matrix dot products with a subset of a dataframe's columns

I have a data table where the several (but not all) columns are factors:

df = read.table(text = "
      date        stock   ret     DivYield PB  ROE
    1 2017-06-30  AAPL    0.05    0.050    12 0.10
    2 2017-06-30  GOOG    0.25    0.055    11 0.12
    3 2017-06-30  MSFT    -0.3    0.020    16 0.12
    4 2017-07-31  AAPL    -.02    0.055    11 0.10
    5 2017-07-31  GOOG    0.25    0.050    12 0.10
    6 2017-07-31  MSFT    0.01    0.025    14 0.12                
", header = TRUE)

I want to multiply the last three columns (my "factor" columns) by weights and sum them together to calculate a z-score:

factor.weights = c(0.3, 0.45, 0.25)
names(factor.weights) = c("DivYield", "PB", "ROE")

The result should look something like this:

        date stock   ret   z.score
1 2017-06-30  AAPL   0.05  5.4400
2 2017-06-30  GOOG   0.25  4.9965
3 2017-06-30  MSFT  -0.30  7.2360
4 2017-07-31  AAPL  -0.02  4.9915
5 2017-07-31  GOOG   0.25  5.4400
6 2017-07-31  MSFT   0.01  6.3375

I obtained the above by going

df.answer = data.frame(date = df$date, stock = df$stock, ret = df$ret, 
                   z.score = df$DivYield * factor.weights["DivYield"] + 
                             df$PB * factor.weights["PB"] + 
                             df$ROE * factor.weights["ROE"])

But I need something more clever since my true data has dozens of columns, and I determine the factor.weights programmatically.

Any ideas on how to do this kind of matrix multiplication on just a select few columns?

Upvotes: 0

Views: 970

Answers (2)

Andryas Waurzenczak
Andryas Waurzenczak

Reputation: 469

You need to transpose your df then multiply by your factor.weights and then transpose again the result. As follows:

df$z.score <- rowSums(t(t(df[,4:6]) * factor.weights))

Upvotes: 1

Benjamin Christoffersen
Benjamin Christoffersen

Reputation: 4841

Here is a solution using base R

> factor.weights = c(0.3, 0.45, 0.25)
> names(factor.weights) = c("DivYield", "PB", "ROE")
> 
> # With base R
> df$answer <-  as.matrix(df[names(factor.weights)]) %*% factor.weights
> df[, setdiff(colnames(df), setdiff(names(factor.weights), "ret"))]
        date stock   ret answer
1 2017-06-30  AAPL  0.05 5.4400
2 2017-06-30  GOOG  0.25 4.9965
3 2017-06-30  MSFT -0.30 7.2360
4 2017-07-31  AAPL -0.02 4.9915
5 2017-07-31  GOOG  0.25 5.4400
6 2017-07-31  MSFT  0.01 6.3375

Upvotes: 3

Related Questions