CODEWITHSUNDEEP
r
Rene Chan
Rene Chan

Reputation: 985

Using R to calculate pro-rata allocation per product

I have a data-frame with the Product and the quantity. I would like, for each Product to add 3 lines with the pro-rata allocation in sub-product X1, X2, X3. The pro-rate is based on weight:

X1 = 0.19
X2 = 0.26
X3 = 0.55

The dataframe looks like :

df = data.frame(Product = c("A","B","C","D"), value = c(30,35,42,12))


  Product Quantity
1       A       30
2       B       35
3       C       42
4       D       1

The table I would like to build at the end should look like this:

   Product Quantity
1        A    30.00
2       X1     5.70
3       X2     7.80
4       X3    16.50
5        B    35.00
6       X1     0.00
7       X2     9.10
8       X3    19.25
9        C    42.00
10      X1     0.00
11      X2    10.92
12      X3    23.10
13       D    12.00
14      X1     0.00
15      X2     3.12
16      X3     6.60

Is there an R way to do this without using a loop (in case there are too many products) please?

Upvotes: 0

Views: 116

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388982

You can create a dataframe for the pro-rate information and use crossing to create all combination of rows with the original df.

library(dplyr)
ref <- data.frame(Product1 = c('X1', 'X2', 'X3'), 
                  Quantity1 = c(0.19, 0.26, 0.55))

tidyr::crossing(ref, df) %>%
        transmute(Product, Product1, value = Quantity1 * value) %>%
        bind_rows(df) %>%
        arrange(Product, !is.na(Product1)) %>%
        mutate(Product = coalesce(Product1, Product)) %>%
        select(-Product1)

#  Product value
#   <chr>   <dbl>
# 1 A       30   
# 2 X1       5.7 
# 3 X2       7.8 
# 4 X3      16.5 
# 5 B       35   
# 6 X1       6.65
# 7 X2       9.1 
# 8 X3      19.2 
# 9 C       42   
#10 X1       7.98
#11 X2      10.9 
#12 X3      23.1 
#13 D       12   
#14 X1       2.28
#15 X2       3.12
#16 X3       6.6

Upvotes: 1

Related Questions