CODEWITHSUNDEEP
r
Marina
Marina

Reputation: 143

Loop over rows matching conditions from another table

Please, find below a simplified version of my (very much huge) data set:

data <- data.frame(PRODUCT= c('TOMATO','APPLE','TOMATO','BANANA','BANANA','TOMATO','APPLE','BANANA'),UNIT = c('BOX','BIG BOX','BIG BAG','BIG BOX','BIG BAG','BOX','BOX','BIG BAG'), QUANTITY = c(20,1,5,7,9,1,5,100))


conversion <-matrix(c(300,150,100,50,222,420,350,60,120),3,3)
colnames(conversion) <- c('BOX', 'BIG BOX', 'BIG BAG')
rownames(conversion) <- c('TOMATO','APPLE','BANANA')

My question is how to obtain a new column ('ESTIMATED') in data as the result of the multiplication between data$QUANTITY and the matching value from conversion.

This may be a pretty basic question, but I'm unsuccessfully trying to code for and if loops using UNIT and PRODUCT as indices.

The desired result would look like this:

RESULT <- data.frame(PRODUCT= c('TOMATO','APPLE','TOMATO','BANANA','BANANA','TOMATO','APPLE','BANANA'),UNIT = c('BOX','BIG BOX','BIG BAG','BIG BOX','BIG BAG','BOX','BOX','BIG BAG'), QUANTITY = c(20,1,5,7,9,1,5,100),ESTIMATED = c(6000,222,1750,2940,1080,300,750,12000))

I would highly appreciate any suggestion. Thank you very much.

Upvotes: 1

Views: 51

Answers (3)

AnilGoyal
AnilGoyal

Reputation: 26218

Simple tidyverse strategy

library(tidyverse)

data %>% mutate(ESTIMATED = pmap_dbl(data, ~ ..3 * conversion[..1, ..2]))

#>   PRODUCT    UNIT QUANTITY ESTIMATED
#> 1  TOMATO     BOX       20      6000
#> 2   APPLE BIG BOX        1       222
#> 3  TOMATO BIG BAG        5      1750
#> 4  BANANA BIG BOX        7      2940
#> 5  BANANA BIG BAG        9      1080
#> 6  TOMATO     BOX        1       300
#> 7   APPLE     BOX        5       750
#> 8  BANANA BIG BAG      100     12000

Or with apply in baseR

data$ESTIMATE <- apply(data, 1,  \(.x) as.numeric(.x[3])*conversion[.x[1], .x[2]] )

Upvotes: 1

Yuriy Saraykin
Yuriy Saraykin

Reputation: 8880

tidyverse

data <-
  data.frame(
    PRODUCT = c(
      'TOMATO',
      'APPLE',
      'TOMATO',
      'BANANA',
      'BANANA',
      'TOMATO',
      'APPLE',
      'BANANA'
    ),
    UNIT = c(
      'BOX',
      'BIG BOX',
      'BIG BAG',
      'BIG BOX',
      'BIG BAG',
      'BOX',
      'BOX',
      'BIG BAG'
    ),
    QUANTITY = c(20, 1, 5, 7, 9, 1, 5, 100)
  )

conversion <- matrix(c(300, 150, 100, 50, 222, 420, 350, 60, 120), 3, 3)
colnames(conversion) <- c('BOX', 'BIG BOX', 'BIG BAG')
rownames(conversion) <- c('TOMATO', 'APPLE', 'BANANA')

library(tidyverse)
conversion_melt <-
  rownames_to_column(.data = as.data.frame(conversion), var = "PRODUCT") %>%
  pivot_longer(-PRODUCT, names_to = "UNIT", values_to = "AMOUNT")

left_join(data, conversion_melt, by = c("PRODUCT", "UNIT")) %>%
  mutate(ESTIMATED = QUANTITY * AMOUNT)
#>   PRODUCT    UNIT QUANTITY AMOUNT ESTIMATED
#> 1  TOMATO     BOX       20    300      6000
#> 2   APPLE BIG BOX        1    222       222
#> 3  TOMATO BIG BAG        5    350      1750
#> 4  BANANA BIG BOX        7    420      2940
#> 5  BANANA BIG BAG        9    120      1080
#> 6  TOMATO     BOX        1    300       300
#> 7   APPLE     BOX        5    150       750
#> 8  BANANA BIG BAG      100    120     12000

Created on 2021-06-30 by the reprex package (v2.0.0)

data.table

library(data.table)
setDT(data)
conversion <- as.data.frame(conversion)
conversion$PRODUCT <- c('TOMATO', 'APPLE', 'BANANA')
setDT(conversion)

conversion_melt <-
  melt(
    data = conversion,
    measure.vars = names(conversion)[-ncol(conversion)],
    id.vars = "PRODUCT",
    variable.name = "UNIT", 
    value.name = "AMOUNT"
    )

merge(data, conversion_melt, all.x = TRUE, by = c("PRODUCT", "UNIT"))[, ESTIMATED := QUANTITY * AMOUNT][]
#>    PRODUCT    UNIT QUANTITY AMOUNT ESTIMATED
#> 1:   APPLE BIG BOX        1    222       222
#> 2:   APPLE     BOX        5    150       750
#> 3:  BANANA BIG BAG        9    120      1080
#> 4:  BANANA BIG BAG      100    120     12000
#> 5:  BANANA BIG BOX        7    420      2940
#> 6:  TOMATO BIG BAG        5    350      1750
#> 7:  TOMATO     BOX       20    300      6000
#> 8:  TOMATO     BOX        1    300       300

Created on 2021-06-30 by the reprex package (v2.0.0)

Upvotes: 1

Rui Barradas
Rui Barradas

Reputation: 76412

Though there already is an accepted answer, here is a base R way.

i <- match(data$PRODUCT, row.names(conversion))
j <- match(data$UNIT, colnames(conversion))

RESULT <- data
RESULT$ESTIMATED <- data$QUANTITY * conversion[cbind(i, j)]

Upvotes: 3

Related Questions