How to allocate total amount to detail-CodePudding

There are dataframe detail and total ,how to allocate total amount intotal to detail and create new column as amount_allocate_from_total? The result as the image attached .

detail <- data.frame(cagetory=c("a","a","a","b","b","b","b","c","c"),
                       amount=c(1,3,7,2,1,4,6,1,6))

total <- data.frame(cagetory=c("a","b","c"),
                     amount=c(20,10,9))

CodePudding user response：

From the image it seems you want to subtract amount in total with sum of all values in detail except the last one for each cagetory and place it in the last row.

Here's a dplyr option.

library(dplyr)

inner_join(detail, total, by = 'cagetory') %>%
  group_by(cagetory) %>%
  mutate(amount_allocate_from_total = 
         replace(amount.x, n(), last(amount.y) - sum(amount.x[-n()]))) %>%
  ungroup

# cagetory amount.x amount.y amount_allocate_from_total
#  <chr>       <dbl>    <dbl>                      <dbl>
#1 a               1       20                          1
#2 a               3       20                          3
#3 a               7       20                         16
#4 b               2       10                          2
#5 b               1       10                          1
#6 b               4       10                          4
#7 b               6       10                          3
#8 c               1        9                          1
#9 c               6        9                          8