0

I have two data frames, df1 has stock symbols and values. df2 correlations with the same names but arranged as rows. df1 has many more columns than df2, but all columns that are in df2 exist in df1. I need to multiply matching columns and store newly created values as a new dataframe. The new dataframe will only have a stock symbol and then all multiplications of df1*df2. The data looks like this:

df1

 A Company Symbol   Earn.GR MF  Effic MF
TRUE    1.320005832 -0.080712181

df2: 

    Variable    Corr
1   Val MF  0.312140675
2   Earn.GR.withCorr MF 0.992410721

I have tried this code, but not getting the expected result: Transpose df2: 

df2 <- transpose (df2)
rownames(df2) <- colnames(df2)

Match and multiply columns

df3 <-  df1[names(df1) %in% names(df2)] <- sapply(names(df1[names(df1) %in% names(df2)]), 
                                            function(x) df1[[x]] * df2[[x]])

Thanks in advance.

Vito K
  • 11
  • 2
  • 1
    Welcome to StackOverflow. Is there any code example you could provide showing us what you've done so far? – Gonzo345 Aug 07 '19 at 08:17
  • 1
    I would suggest taking a look [here](https://stackoverflow.com/a/5963610/7856717) for guidelines on how to provide a reproducible example. I believe what you are looking for can be done with `dplyr` and `join` , `mutate` functions, but we won't be able to help much if you don't provide us with an example. – Steve Aug 07 '19 at 08:21

2 Answers2

1

With base R, you could do something like this

df1 = as.data.frame(matrix(1:14,2,7))
df2 = as.data.frame(matrix(15:28,2,7))
names(df1)= letters[1:7]
names(df2)= c("a","d",letters[9:12],"b")

m = match(names(df1),names(df2))

newdf = setNames(df1[,which(!is.na(m))]*df2[,na.omit(m)],
                 paste0("mult_",names(df2[,na.omit(m)])))

> newdf
  mult_a mult_b mult_d
1     15     81    119
2     32    112    144
boski
  • 2,437
  • 1
  • 14
  • 30
  • I have tried, but because the number of columns is not different I am getting an error: Error in Ops.data.frame(df1[, which(!is.na(m))], df2[, na.omit(m)]) : ‘*’ only defined for equally-sized data frames – Vito K Aug 07 '19 at 08:46
1

Find common columns using intersect, subset from both the dataframe and multiply

common_cols <- intersect(names(df1), names(df2))
df3 <- df1[common_cols] * df2[common_cols]
df3

df3
#   a   c
#1  2 144
#2  6 169
#3 12 196
#4 20 225
#5 30 256

data

df1 <- data.frame(a = 1:5, b = 11:15, c = 12:16)
df2 <- data.frame(a = 2:6, d = 11:15, c = 12:16, e = 1:5)

Update

Since you have unI think you need to merge before multiplying

df3 <- merge(df1[common_cols], df2[common_cols], by = "Company")
cbind(df3[1], df3[-1][c(TRUE, FALSE)] * df3[-1][c(FALSE, TRUE)])
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
  • Thanks Ronak, getting this error: Error in Ops.data.frame(df1[common_cols], df2[common_cols]) : ‘*’ only defined for equally-sized data frames – Vito K Aug 07 '19 at 08:37
  • 3
    @VitoK yes, looks like `nrow(df1)` is not same as `nrow(df2)`. Can you update your post with `dput(df1)` and `dput(df2)` ? Also how do you wish to multiply the two dataframes when the number of rows are not equal? – Ronak Shah Aug 07 '19 at 08:40
  • @RonakShah wouldn't this only work if the same-named columns are in the same position? – boski Aug 07 '19 at 08:50
  • @boski no since we are selecting column by names, they'll be in the same order while multiplying. Check for `df1 <- data.frame(a = 1:5, b = 11:15, c = 12:16); df2 <- data.frame(c = 2:6, d = 11:15, e = 12:16, a = 1:5)` – Ronak Shah Aug 07 '19 at 08:55
  • @RonakShah I am thinking the first step should be to transpose df2, and then it will be only one value per column. I have updated the description. – Vito K Aug 07 '19 at 09:24
  • @RonakShah For merge I don't want to specify "by" – Vito K Aug 07 '19 at 09:28