Search code examples
rdplyrsubtotal

Calculations using subtotals and totals

I have a dataset where I calculate weightings based on total mpg and apply this weighting to a constant to calculate column "distribution". What I want to do is, to allocate another constant of 100 to only those cars where the maker is Mercedes. So I first calculate where each individual car stands as of the total and allocate 1000 called distribution plus another 100 on just the Mercedes cars. How can I do this?

library(data.table)
library(dplyr)
a <- mtcars
setDT(a, keep.rownames = TRUE)
colnames(a)[1] <- "Car"
b <- a %>%
  select(Car,mpg) %>%
  mutate(Weighting = mpg / sum(mpg)) %>%
  mutate(Distribution = Weighting * 1000)

Example output: Couldn't figure out how to get it in R but here is an excel image that shows the expected results. Wanted Output

The only solution that comes to mind is to calculate it separately and assign it as a constant.

Your help is more than appreciated. Aksel

Solution

  • Sorry for taking up peoples time, but it seems like the easiest solution for me was to first calculate the total Mercedes balance and assign it to a variable and then do my remaining calculations by referring to my variable.

    If anyone faces a similar issue, please drop me a line and I would me more than happy to help.