CODEWITHSUNDEEP
rdataframematch
luisfego
luisfego

Reputation: 91

Match column and row in another data frame using values from row

I have a data frame df1 with a list of products like this: 

| SKU | Product | Model |  Size  |
|-----|---------|-------|--------|
|   1 | X       | A     | Small  |
|   2 | X       | B     | Large  |
|   3 | X       | B     | Medium |
...

And a data frame df2 with the prices for the each of the models and sizes as follows:

| Model | Small | Medium | Large |
|-------|-------|--------|-------|
| A     |    10 |     12 |    15 |
| B     |     8 |      9 |    10 |
| C     |     7 |      8 |    12 |
| D     |    12 |     13 |    14 |
...

What I want is to match the product model and size in df1 with the data in df2, so that I can add a new column with the price in the first data frame. The result should be the following:

| SKU | Product | Model |  Size  | Price |
|-----|---------|-------|--------|-------|
|   1 | X       | A     | Small  |    10 |
|   2 | X       | B     | Large  |    10 |
|   3 | X       | B     | Medium |     9 |
...

How can I do this using R?

Upvotes: 0

Views: 96

Answers (2)

jazzurro
jazzurro

Reputation: 23574

One way would be the following. You can reshape df2 and create a new column called Price. Then, you merge the data.

library(dplyr)
library(tidyr)

df1 <- data.frame(SKU = 1:3,
                  Product = c("X", "X", "X"),
                  Model = c("A", "B", "B"),
                  Size = c("Small", "Large", "Medium"),
                  stringsAsFactors = FALSE)

df2 <- data.frame(Model = LETTERS[1:4],
                  Small = c(10, 8, 7, 12),
                  Medium = c(12, 9, 8, 13),
                  Large = c(15, 10, 12, 14),
                  stringsAsFactors = FALSE)

gather(df2, key = Size, value = Price, -Model) %>%
      left_join(df1, ., by = c("Model", "Size")) 
  SKU Product Model   Size Price
1   1       X     A  Small    10
2   2       X     B  Large    10
3   3       X     B Medium     9

Upvotes: 1

Evan Friedland
Evan Friedland

Reputation: 3194

Here is a solution using base R:

Jazzurro's data:

df1 <- data.frame(SKU = 1:3,
                  Product = c("X", "X", "X"),
                  Model = c("A", "B", "B"),
                  Size = c("Small", "Large", "Medium"),
                  stringsAsFactors = FALSE)

df2 <- data.frame(Model = LETTERS[1:4],
                  Small = c(10, 8, 7, 12),
                  Medium = c(12, 9, 8, 13),
                  Large = c(15, 10, 12, 14),
                  stringsAsFactors = FALSE)

1 liner:

df1$Price <- apply(df1, 1, function(x) df2[df2[,"Model"] %in% x["Model"],x["Size"]] )
#  SKU Product Model   Size Price
#1   1       X     A  Small    10
#2   2       X     B  Large    10
#3   3       X     B Medium     9

Upvotes: 1

Related Questions