How to multiply entire row with a matching row name in another dataframe?

user_13768497 0
Viewed 156

Let's suppose I have the following dataframes: 

    test <- data.frame(X = c(1,2,3), Y = c(4,5,6), row.names = c("T1", "T2", "T3"))

    test2 <- data.frame(mean = c(1,2,5), row.names = c("T1", "T2", "T3"))

I want to multiply all rows in the test dataframe by the value in the test2 dataframe, matched by row name. How do I do this to get an answer like this:

    answer <- data.frame(X = c(1,4,15), Y = c(4,10,30), row.names = c("T1", "T2", "T3"))
r
4 Answers

You can do

test * test2[rownames(test), "mean"]
#     X  Y
# T1  1  4
# T2  4 10
# T3 15 30
user_85833930

You need to convert rownames to column first and then join and do any aggregation you need, i.e.

library(tidyverse)

test %>% 
 rownames_to_column('id') %>% 
 left_join(test2 %>% rownames_to_column('id'), by = 'id') %>% 
 mutate_at(vars(c('X', 'Y')), list(~ . * mean)) %>% 
 select(-mean)

#  id  X  Y
#1 T1  1  4
#2 T2  4 10
#3 T3 15 30
user_56355800

Just posting because I was already working on it. Almost same solution as @Sotos, but with across:

library(dplyr)
test %>% 
  rownames_to_column() %>% 
  left_join(test2 %>% rownames_to_column()) %>% 
  mutate(across(X:Y, ~.*mean)) %>% 
  select(-mean)

Joining, by = "rowname"
  rowname  X  Y
1      T1  1  4
2      T2  4 10
3      T3 15 30
user_62421320

I would do the following: 

x <- test %>% 
      rownames_to_column("id") %>% 
      left_join(test2 %>% rownames_to_column("id"), by = "id") %>%
      mutate_at(vars(colnames(test)), list(~ . * mean)) %>% 
      select(-mean)

if you have columns with the same names (e.g. two columns called X), then they will be labelled X.1 and X.2 If you want to get rid of the fullstop and everything after it, I would do:

colnames(x) <- str_replace(colnames(x),"\\..*","")

Hope this helps

user_114282480
Related