CODEWITHSUNDEEP
r
Marcus
Marcus

Reputation: 63

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

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

Upvotes: 6

Views: 216

Answers (4)

Danby
Danby

Reputation: 108

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

Upvotes: 1

markus
markus

Reputation: 26373

You can do

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

Upvotes: 5

Ahorn
Ahorn

Reputation: 3876

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

Upvotes: 3

Sotos
Sotos

Reputation: 51622

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

Upvotes: 4

Related Questions