CODEWITHSUNDEEP
rmatch
TiberiusGracchus2020
TiberiusGracchus2020

Reputation: 409

Multiply subset of columns by values in second data frame using match in R

I have a data frame which looks like this

data <- read.table(text="
  Country A B
1 FRA     1 2
2 GER     2 1
", header=TRUE)

I have a reference data frame which looks like this

ref <- read.table(text="
  Names Values
1     A      5
2     B     10
", header=TRUE)

I want to multiply each column by corresponding row in Ref having same Name (while retaining non-numeric rows without a match)

the result should be this

> result
  Country  A  B
1 FRA      5 20
2 GER     10 10

Upvotes: 1

Views: 191

Answers (2)

MKR
MKR

Reputation: 1700

This would also be an option. It's a little long though and the above suggested answer might be better.

library(tidyverse)
data <- data.frame(
  Country = c("FRA", "GER"),
  A = c(1,2),
  B = c(2,1)
)

ref <- data.frame(
  Names = c("A", "B"),
  Values = c(5, 15)
)

data %>% pivot_longer(cols = c(A,B), values_to = c("Value"), names_to = "Names") %>% 
left_join(ref, by = "Names") %>% 
mutate(New_Value = Value * Values) %>% 
select(-Value, -Values) %>% 
pivot_wider(names_from = Names, values_from = New_Value)

Upvotes: 0

akrun
akrun

Reputation: 887223

We can subset the columns of interest ('nm1'), multiply with the replicated corresponding 'Values' of 'ref' after matching the column names with the 'Names' column, and update those columns

nm1 <- c("A", "B")
result <- data
result[nm1] <-data[nm1] * ref$Values[match(nm1, ref$Names)][col(data[nm1])]
result
#  Country  A  B
#1     FRA  5 20
#2     GER 10 10

Upvotes: 1

Related Questions