Call on data from one dataframe to help sort another-CodePudding

I would like to create a "menu" of items you could find at a grocery store. My goal is to generate every permutation of picking 5 pieces of fruit (with replacement), and find the total cost of the 5 fruit. Here's what I have currently.

fruit = data.frame(c("apple", "banana", "orange", "pear", "strawberry"))
price = data.frame(c(0.99, 1.59, 0.70, 1.29, 2.99))

## Rename column headers
colnames(fruit)[1] = "Fruit"
colnames(price)[1] = "Price"

menu = cbind(fruit, price)

## Turn fruit dataframe into a vector
fruit_vector = as.vector(fruit[[1]])

## Generate all permutations

permutations = data.frame(expand.grid(Fruit_1 = fruit_vector,
                                      Fruit_2 = fruit_vector,
                                      Fruit_3 = fruit_vector,
                                      Fruit_4 = fruit_vector,
                                      Fruit_5 = fruit_vector))

Here's the part that I'm stuck on: I would like to create a 6th column in my dataframe that takes all 5 fruit from a row and sums their prices for a single total price.

Is there a way to call on information from my "menu" data frame that matches the price of an apple to 0.99, the price of a banana to 1.59, etc.?

Thoughts (other than the questionable food prices)?

I thought about making individual columns to have the price of fruit_1, the price of fruit_2, etc, and them sum them up into an 11th column, but I feel like that is less efficient, and that still doesn't solve my issue of seeing an apple and having R automatically identify that as 0.99.

CodePudding user response：

An option using dplyr:

library(dplyr)


fruit = data.frame(c("apple", "banana", "orange", "pear", "strawberry"))
price = data.frame(c(0.99, 1.59, 0.70, 1.29, 2.99))

## Rename column headers
colnames(fruit)[1] = "Fruit"
colnames(price)[1] = "Price"

menu = cbind(fruit, price)

## Turn fruit dataframe into a vector
fruit_vector = as.vector(fruit[[1]])

## Generate all permutations

permutations = data.frame(expand.grid(Fruit_1 = fruit_vector,
                                      Fruit_2 = fruit_vector,
                                      Fruit_3 = fruit_vector,
                                      Fruit_4 = fruit_vector,
                                      Fruit_5 = fruit_vector)) %>% 
  mutate(id = row_number())

permutations %>% 
  mutate(across(Fruit_1:Fruit_5, ~case_when(. == "apple" ~ 0.99,
                                         . == "banana" ~ 1.59,
                                         . == "orange" ~ 0.7,
                                         . == "pear" ~ 1.29,
                                         . == "strawberry" ~ 2.99))) %>% 
  rowwise() %>% 
  mutate(total = sum(c(Fruit_1, Fruit_2, Fruit_3, Fruit_4, Fruit_5))) %>% 
  ungroup() %>% 
  select(id, total) %>% 
  right_join(permutations)
#> Joining, by = "id"
#> # A tibble: 3,125 × 7
#>       id total Fruit_1    Fruit_2 Fruit_3 Fruit_4 Fruit_5
#>    <int> <dbl> <fct>      <fct>   <fct>   <fct>   <fct>  
#>  1     1  4.95 apple      apple   apple   apple   apple  
#>  2     2  5.55 banana     apple   apple   apple   apple  
#>  3     3  4.66 orange     apple   apple   apple   apple  
#>  4     4  5.25 pear       apple   apple   apple   apple  
#>  5     5  6.95 strawberry apple   apple   apple   apple  
#>  6     6  5.55 apple      banana  apple   apple   apple  
#>  7     7  6.15 banana     banana  apple   apple   apple  
#>  8     8  5.26 orange     banana  apple   apple   apple  
#>  9     9  5.85 pear       banana  apple   apple   apple  
#> 10    10  7.55 strawberry banana  apple   apple   apple  
#> # … with 3,115 more rows

CodePudding user response：

You can build your menu simply by using a named vector.

fruit_price <- unlist(price)
names(fruit_price) <- unlist(fruit)

fruit_price
     apple     banana     orange       pear strawberry 
      0.99       1.59       0.70       1.29       2.99

Then combine it with the existing permutations data frame

head(data.frame(permutations, 
                total_price = apply(permutations, 1, function(x) 
                  sum(fruit_price[x]))))
     Fruit_1 Fruit_2 Fruit_3 Fruit_4 Fruit_5 total_price
1      apple   apple   apple   apple   apple        4.95
2     banana   apple   apple   apple   apple        5.55
3     orange   apple   apple   apple   apple        4.66
4       pear   apple   apple   apple   apple        5.25
5 strawberry   apple   apple   apple   apple        6.95
6      apple  banana   apple   apple   apple        5.55