CODEWITHSUNDEEP
rdplyrtidyverse
cityofwisdom
cityofwisdom

Reputation: 63

Multiply rows (with row names) in one data frame with matching column names in another

I have two data frames: 

df1 <- data.frame(Values=c(0.01,0.05), row.names=c("X", "Y"))
df1
  Values
X   0.01
Y   0.05

df2 <-data.frame(c(0,1,1), c(1,0,0), c(1,1,1))
colnames(df2) <- c("X","Y","Z")

df2
  X Y Z
1 0 1 1
2 1 0 1
3 1 0 1

I wish to perform a rowwise operation on df2, where I multiply every column in df2 with its corresponding row in df1 and then perform a summation.

For example, for row 1 of df2, I wish to calculate: 

df2 %>% rowwise %>% mutate(newVAL=(df1["X",]*df2[1,"X"])+(df1["Y",]*df2[1,"Y"]))

while excluding columns that don't match (rows in df1) or have NAs.

I have several thousands of rows in df1 and several thousands of rows and columns in df2.

Any help is much appreciated!!

PS. I have implemented this in Perl using hashes and was using the system() call to perform these calculations within an Rmarkdown document. In order to keep it completely reproducible, I am trying to redo it in R. Happy to share the Perl code if necessary.

Thanks.

Upvotes: 2

Views: 1570

Answers (3)

akrun
akrun

Reputation: 887118

We can also use rep to make the lengths same to multiply and then get the rowSums. It will be more efficient to use rep as it is faster

rowSums(df2[rownames(df1)] * rep(df1$Values, each = nrow(df2)))
#[1] 0.05 0.01 0.01

Or using the tidyrverse packages

library(dplyr)
library(purrr)
df2 %>% 
     select_(.dots = rownames(df1)) %>% 
     map2(df1$Values, `*`) %>%
     reduce(`+`)
#[1] 0.05 0.01 0.01

Update

If we need it as a column,

df2 %>% 
    select_(.dots = rownames(df1)) %>%
    map2(df1$Values, `*`) %>%
    reduce(`+`) %>%
    mutate(df2, total = .)
#  X Y Z total
#1 0 1 1  0.05
#2 1 0 1  0.01
#3 1 0 1  0.01

Upvotes: 1

thelatemail
thelatemail

Reputation: 93813

Here's an attempt in base R matching the rows to the columns between the two sets:

rowSums(
  sweep(df2,
        MARGIN=2,
        STATS=df1$Values[match(colnames(df2), rownames(df1))],
        FUN=`*`),
  na.rm=TRUE
)
#[1] 0.05 0.01 0.01

Upvotes: 3

user7396508
user7396508

Reputation:

If I understand correctly, it looks like you need sweep.

df3 <- sweep(df2[, rownames(df1)], 2, t(df1), '*')
df3$total <- rowSums(df3)

Upvotes: 3

Related Questions