1

Following my previous question which was not clear for anyone. I come up with a new reproducible example

I have the following INPUT dataframe in R

DF.IN <- data.frame(
  "Product"= c("X1","X2","X3","X4","X5","X6"),
  "G1"= c("RC1","RC1","RC2","RC2","RC1","RC1"),
  "G2"= c("IN1","IN2","IN1","IN1","IN1","IN2"),
  "MAX.G1"= c("18","18","8","8","18","18"),
  "MAX.G2"= c("20","60","20","20","20","60"),
  "Amount"= c("10","5","3","6","15","1"),
  stringsAsFactors = FALSE
  )

which give

  Product  G1  G2 MAX.G1 MAX.G2 Amount
  X1       RC1 IN1  18     20     10
  X2       RC1 IN2  18     60      5
  X3       RC2 IN1   8     20      3
  X4       RC2 IN1   8     20      6
  X5       RC1 IN1  18     20     15
  X6       RC1 IN2  18     60      1

where

  • G1 and G2 = Group 1 & Group 2
  • Max.G1 and Max.G2 = Max amount allow for G1 and G2 respectively

If we calculate the Cumulative Amount for each group such as

DF.IN[, CumSum.G1 := cumsum(Amount), by = list(G1)]
DF.IN[, CumSum.G2 := cumsum(Amount), by = list(G2)]

We'll have the following results

   Product  G1  G2 MAX.G1 MAX.G2 Amount CumSum.G1 CumSum.G2
    X1      RC1 IN1     18     20     10        10        10
    X2      RC1 IN2     18     60      5        15         5
    X3      RC2 IN1      8     20      3         3        13
    X4      RC2 IN1      8     20      6         9        19
    X5      RC1 IN1     18     20     15        30        34
    X6      RC1 IN2     18     60      1        31         6

What i'm looking for is - for each product- the relevant Amount in which the Cumulative Amount respect the Maximum Amount allow for each group G1 & G2 in the same time

So the OUTPUT desired is something like this

   Product  G1  G2 MAX.G1 MAX.G2 Amount.EXPECTED CumSum.G1 CumSum.G2
     X1     RC1 IN1     18     20       10        10        10
     X2     RC1 IN2     18     60        5        15         5
     X3     RC2 IN1      8     20        3         3        13
     X4     RC2 IN1      8     20        5         8        18
     X5     RC1 IN1     18     20        2        17        20
     X6     RC1 IN2     18     60        0        17         5

Can someone help me out with this issue ? Thanks a lot !!

Nico Hei
  • 11
  • 2
  • I made the correction :) – Nico Hei Jul 19 '16 at 10:00
  • 1
    If the MAX.G1 is 18 why you can't have 18 for CumSum.G1 and only 17 – akrun Jul 19 '16 at 10:01
  • @ akrun, Amount for X5 = 15 | Previous Cumulative Amount for G1 = 15 | Previous Cumulative Amount for G2 = 18. If we take 3, so the CumSum.G1 = 18 (15+3) and CumSum.G2 = 21 (18+3 which is > Max.G2 = 20 ). The Expected Amount should respect the both contraints MAX.G1 and MAX.G2 in the same time. – Nico Hei Jul 19 '16 at 10:04
  • You're using data.table syntax on a data.frame..?!? – Arun Jul 19 '16 at 10:57
  • @ akrun, the following code allows me to use data.table syntax : DF.IN <- data.table(DF.IN) :) – Nico Hei Jul 19 '16 at 11:45
  • Some `pmin`, `pmax` and `shift` might help. I couldn't figure out how to do it cleanly, but here's a similar question: http://stackoverflow.com/q/35918851/ – Frank Jul 19 '16 at 12:51
  • 1
    Hi Franck, thanks for your reply :) I already tested the kind of solution you mentionned. But the thing is if we use the data.table syntax with "by = list(Group1, Group2,...GroupN)" or just " by = list(Group1)", we don't get the solution i'm looking for. – Nico Hei Jul 19 '16 at 14:46

0 Answers0