Calculate difference between sum of grouped variable and another variable

user_11078618 0
Viewed 47

I have a data frame like this:

example.df <- data.frame(Year = rep(1:3, each = 3),
                         Product = rep(c("bottle_water", "tap_water", "juice"), 3),
                         Product_grouped = rep(c("water", "water", "juice"), 3),
                         Required = c(10, 15, 10, 10, 20, 10, 10, 35, 0),
                         Group_avail = rep(c(25, 25, 10), 3),
                         Purchased = c(0, 0, 0, 5, 0, 0, 20, 0, 0))
> example.df
  Year      Product Product_grouped Required Group_avail Purchased
1    1 bottle_water           water       10          25         0
2    1    tap_water           water       15          25         0
3    1        juice           juice       10          10         0
4    2 bottle_water           water       10          25         5
5    2    tap_water           water       20          25         0
6    2        juice           juice       10          10         0
7    3 bottle_water           water       10          25        20
8    3    tap_water           water       35          25         0
9    3        juice           juice        0          10         0

Where for each Year, I have the Required quantities of each Product. Two of the products (tap_water and bottled_water) can be grouped in water. In this case, the total quantity available is shown in Group_avail.

In years when the total Required Product_grouped is greater than the Group_avail, I want to purchase the difference. However, the difference must be Purchased in bottle_water only. For example, in Year 2, we require 20 tap_water and 10 bottle_water but there is only a 25 water available, so 5 bottle_water is Purchased.

I'm looking specifically for a solution suitable for piping.

Thanks!

Edit I've added another Year for further clarity on what I want. In any situation where the total Required for the Product_grouped is surpassed, the difference should always be purchased in bottle_water.

data-wrangling dplyr dataframe r
2 Answers

A little inelegant, but this use piping to achieve the result by splitting out bottle_water and then adding back into the dataframe.

temp <- example.df

temp <- temp %>% 
  group_by(Product_grouped, Year) %>% 
  filter(Product %in% c("bottle_water", "tap_water")) %>% 
  mutate(Required_grouped = sum(Required)) %>% 
  filter(Product == "bottle_water") %>% 
  mutate(Purchased = ifelse(Required_grouped - Group_avail > 0, Required_grouped - Group_avail, 0)) %>% 
  select(-"Required_grouped")
  
example.df <- example.df %>% 
  filter(Product != "bottle_water") %>% 
  bind_rows(temp) %>% 
  arrange(Year)

If the order is important, then set Product to a factor and add to arrange(Year).

user_95414150

I hope this is what you are looking for. For your purpose I first modified Required column to show the sum of every Product_grouped and then I check whether the sum in first row of every group surpasses the Group_avail or not. In case it does we calculate the difference provided that our first category is always bottle_water. In case you want to revert the Required column back to its original form I can make modifications just wanted to make sure this is what you are looking for first.

library(dplyr)

example.df %>%
  group_by(Year, Product_grouped) %>%
  mutate(Required = sum(Required),
         Purchased02 = if_else(Required >= Group_avail & Product == "bottle_water", 
                               Required - Group_avail, 0))

# A tibble: 9 x 7
# Groups:   Year, Product_grouped [6]
   Year Product      Product_grouped Required Group_avail Purchased Purchased02
  <int> <chr>        <chr>              <dbl>       <dbl>     <dbl>       <dbl>
1     1 bottle_water water                 25          25         0           0
2     1 tap_water    water                 25          25         0           0
3     1 juice        juice                 10          10         0           0
4     2 bottle_water water                 30          25         5           5
5     2 tap_water    water                 30          25         0           0
6     2 juice        juice                 10          10         0           0
7     3 bottle_water water                 45          25        20          20
8     3 tap_water    water                 45          25         0           0
9     3 juice        juice                  0          10         0           0
user_143145200
Related