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

Question

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?

akrun · Accepted Answer · 2018-09-05 03:36:28Z

1

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

edited Sep 5, 2018 at 3:36

akrun

891k38 gold badges590 silver badges700 bronze badges

answered Sep 5, 2018 at 0:48

jazzurro

23.6k36 gold badges72 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Evan Friedland · Accepted Answer · 2018-09-05 02:05:53Z

1

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

answered Sep 5, 2018 at 2:05

Evan Friedland

3,1841 gold badge14 silver badges28 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related