CODEWITHSUNDEEP
rdataframe
Vito K
Vito K

Reputation: 11

R, Multiply columns from different data frames and store new values as new dataframe

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.

Upvotes: 0

Views: 1372

Answers (2)

Ronak Shah
Ronak Shah

Reputation: 388982

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

Upvotes: 1

boski
boski

Reputation: 2467

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

Upvotes: 1

Related Questions