This works for your example data, but I'd recommend testing it carefully before
using it.
> dat <- data.frame(ID=11:14, VALUE=c(1, 5, 3, 2)*10000)
> HURD <- c(50, 75, 100)*1000
> PCT <- c(.02, .04, .08, .1)
> dat$CVALUE <- cumsum(dat$VALUE)
> dat$LVALUE <- dat$CVALUE - dat$VALUE
> dat
ID VALUE CVALUE LVALUE
1 11 10000 10000 0
2 12 50000 60000 10000
3 13 30000 90000 60000
4 14 20000 110000 90000
>
> for (idx in seq_len(nrow(dat))) {
+ rng <- sort(c(HURD, unlist(dat[idx,3:4])))
+ a <- which(names(rng) == "LVALUE")
+ b <- which(names(rng) == "CVALUE")
+ diff(rng[a:b])
+ ng <- length(diff(rng[a:b]))
+ dat$MARGE[idx] <- sum(PCT[a:(a+ng-1)]* diff(rng[a:b]))
+ }
> dat
ID VALUE CVALUE LVALUE MARGE
1 11 10000 10000 0 200
2 12 50000 60000 10000 1200
3 13 30000 90000 60000 1800
4 14 20000 110000 90000 1800
-------------------------------------
David L Carlson
Department of Anthropology
Texas A&M University
College Station, TX 77840-4352
-----Original Message-----
From: Jeff Newmiller [mailto:[email protected]]
Sent: Monday, March 9, 2015 2:22 PM
To: Matthias Weber
Cc: David L Carlson; [email protected]
Subject: Re: [R] calculate value in dependence of target value
> target <- 100000
>
> breakpts <- data.frame( PctTarget=c(50,75,100,Inf), Mult=c(2,4,8,10) )
> breakpts$LastPct <- c( 0, breakpts$PctTarget[ -nrow( breakpts ) ] )
> breakpts$Range <- cut( breakpts$PctTarget, c( 0, breakpts$PctTarget ),
include.lowest=TRUE )
> breakpts$DeltaPct <- with( breakpts, diff( c( 0, PctTarget ) ) )
> breakpts$CumMARGE <- target / 1e4 * with( breakpts, cumsum( DeltaPct *
Mult ) )
> breakpts$LastCumMARGE <- c( 0, breakpts$CumMARGE[ -nrow( breakpts ) ] )
>
> dta <- data.frame( ID=11:14, VALUE=c(10000,50000,30000,20000) )
> dta$CumVALUE <- cumsum( dta$VALUE )
> dta$CumPct <- 100 * dta$CumVALUE / target
> dta$Range <- cut( dta$CumPct, c( 0, breakpts$PctTarget ),
include.lowest=TRUE )
>
> dta
ID VALUE CumVALUE CumPct Range
1 11 10000 10000 10 [0,50]
2 12 50000 60000 60 (50,75]
3 13 30000 90000 90 (75,100]
4 14 20000 110000 110 (100,Inf]
> breakpts
PctTarget Mult LastPct Range DeltaPct CumMARGE LastCumMARGE
1 50 2 0 [0,50] 50 1000 0
2 75 4 50 (50,75] 25 2000 1000
3 100 8 75 (75,100] 25 4000 2000
4 Inf 10 100 (100,Inf] Inf Inf 4000
>
> #dta2 <- merge( dta, breakpts, all.x=TRUE, by="Range" )
> #dta2 <- dta2[ order( dta2$ID ), ]
>
> dta2 <- cbind( dta, breakpts[ match( dta$Range, breakpts$Range ),
-which( "Range"==names( breakpts ) ) ] )
>
> dta2$CumMARGE <- with( dta2, Mult/100 * ( CumVALUE - target * LastPct /
100 ) + LastCumMARGE )
> dta2$MARGE <- with( dta2, diff( c( 0, CumMARGE ) ) )
>
> dta2
ID VALUE CumVALUE CumPct Range PctTarget Mult LastPct DeltaPct
CumMARGE LastCumMARGE MARGE
1 11 10000 10000 10 [0,50] 50 2 0 50
200 0 200
2 12 50000 60000 60 (50,75] 75 4 50 25
1400 1000 1200
3 13 30000 90000 90 (75,100] 100 8 75 25
3200 2000 1800
4 14 20000 110000 110 (100,Inf] Inf 10 100 Inf
5000 4000 1800
>
>
> target <- 100000
>
> breakpts <- data.frame( PctTarget=c(50,75,100,Inf), Mult=c(2,4,8,10) )
> breakpts$LastPct <- c( 0, breakpts$PctTarget[ -nrow( breakpts ) ] )
> breakpts$Range <- cut( breakpts$PctTarget, c( 0, breakpts$PctTarget ),
include.lowest=TRUE )
> breakpts$DeltaPct <- with( breakpts, diff( c( 0, PctTarget ) ) )
> breakpts$CumMARGE <- target / 1e4 * with( breakpts, cumsum( DeltaPct *
Mult ) )
> breakpts$LastCumMARGE <- c( 0, breakpts$CumMARGE[ -nrow( breakpts ) ] )
>
> dta <- data.frame( ID=11:14, VALUE=c(10000,50000,30000,20000) )
> dta$CumVALUE <- cumsum( dta$VALUE )
> dta$CumPct <- 100 * dta$CumVALUE / target
> dta$Range <- cut( dta$CumPct, c( 0, breakpts$PctTarget ),
include.lowest=TRUE )
>
> dta
ID VALUE CumVALUE CumPct Range
1 11 10000 10000 10 [0,50]
2 12 50000 60000 60 (50,75]
3 13 30000 90000 90 (75,100]
4 14 20000 110000 110 (100,Inf]
> breakpts
PctTarget Mult LastPct Range DeltaPct CumMARGE LastCumMARGE
1 50 2 0 [0,50] 50 1000 0
2 75 4 50 (50,75] 25 2000 1000
3 100 8 75 (75,100] 25 4000 2000
4 Inf 10 100 (100,Inf] Inf Inf 4000
>
> #dta2 <- merge( dta, breakpts, all.x=TRUE, by="Range" )
> #dta2 <- dta2[ order( dta2$ID ), ]
>
> dta2 <- cbind( dta, breakpts[ match( dta$Range, breakpts$Range ),
-which( "Range"==names( breakpts ) ) ] )
>
> dta2$CumMARGE <- with( dta2, Mult/100 * ( CumVALUE - target * LastPct /
100 ) + LastCumMARGE )
> dta2$MARGE <- diff( c( 0, dta2$CumMARGE ) )
>
> dta2
ID VALUE CumVALUE CumPct Range PctTarget Mult LastPct DeltaPct
CumMARGE LastCumMARGE MARGE
1 11 10000 10000 10 [0,50] 50 2 0 50
200 0 200
2 12 50000 60000 60 (50,75] 75 4 50 25
1400 1000 1200
3 13 30000 90000 90 (75,100] 100 8 75 25
3200 2000 1800
4 14 20000 110000 110 (100,Inf] Inf 10 100 Inf
5000 4000 1800
>
On Mon, 9 Mar 2015, Matthias Weber wrote:
> Hi David,
>
> thanks for the reply. My spelling of the numbers was not correct. What I mean
> with 100.000 is 100000.00 !
> I have corrected the values in my example below me.
>
> Maybe you can understand it better now.
>
> Crucially is, that the "MARGE" rises up in dependence of the ID. The ID 11
> will be count with 2% because we don't reach the 50% hurdle (50000). The ID
> 12 will reach the 50% hurdle, so the ID 12 should be count with 1200 (result
> of 40000 * 2% + 10000 * 4%). The 10000 with 4% will be credited more, because
> they exceed the 50% Target Value.
>
> Thanks for your help.
>
> Best regards.
>
> Mat
>
> -----Urspr?ngliche Nachricht-----
> Von: David L Carlson [mailto:[email protected]]
> Gesendet: Montag, 9. M?rz 2015 16:08
> An: Matthias Weber; [email protected]
> Betreff: RE: calculate value in dependence of target value
>
> It is very hard to figure out what you are trying to do.
>
> 1. All of the VALUEs are greater than the target of 100 2. Your description
> of what you want does not match your example.
>
> Perhaps VALUE should be divided by 1000 (e.g. not 10000, but 10)?
> Perhaps your targets do not apply to VALUE, but to cumulative VALUE?
>
> -------------------------------------
> David L Carlson
> Department of Anthropology
> Texas A&M University
> College Station, TX 77840-4352
>
>
>
> -----Original Message-----
> From: R-help [mailto:[email protected]] On Behalf Of Matthias Weber
> Sent: Monday, March 9, 2015 7:46 AM
> To: [email protected]
> Subject: [R] calculate value in dependence of target value
>
> Hello together,
>
> i have a litte problem. Maybe anyone can help me.
>
> I have to calculate a new column in dependence of a target value.
>
> As a example: My target value is 100000. At the moment I have a data.frame
> with the following values.
>
> ID VALUE
> 1 11 10000
> 2 12 50000
> 3 13 30000
> 4 14 20000
>
> The new column ("MARGE") should be calculated with the following graduation:
> Until the VALUE reach 50% of the target value (50000) = 2%
>
> Until the VALUE reach 75% of the target value (75000) = 4%
>
> Until the VALUE reach 100% of the target value (<100000) = 8%
>
> If the VALUE goes above 100% of the value (>100000) = 10%
>
> The result looks like this one:
>
> ID VALUE MARGE
> 1 11 10000 200 (result of 10000 * 2%)
> 2 12 50000 1200 (result of 40000 * 2% + 10000 * 4%)
> 3 13 30000 1800 (result of 15000 * 4% + 15000 * 8%)
> 4 14 20000 1800 (result of 10000 * 8% + 10000 * 10%)
>
> Is there anyway to calculate the column "MARGE" automatically in R?
>
> Thanks a lot for your help.
>
> Best regards.
>
> Mat
>
> This e-mail may contain trade secrets, privileged, undisclosed or otherwise
> confidential information. If you have received this e-mail in error, you are
> hereby notified that any review, copying or distribution of it is strictly
> prohibited. Please inform us immediately and destroy the original
> transmittal. Thank you for your cooperation.
>
> Diese E-Mail kann Betriebs- oder Geschaeftsgeheimnisse oder sonstige
> vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtuemlich
> erhalten haben, ist Ihnen eine Kenntnisnahme des Inhalts, eine
> Vervielfaeltigung oder Weitergabe der E-Mail ausdruecklich untersagt. Bitte
> benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank.
>
> ______________________________________________
> [email protected] mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
---------------------------------------------------------------------------
Jeff Newmiller The ..... ..... Go Live...
DCN:<[email protected]> Basics: ##.#. ##.#. Live Go...
Live: OO#.. Dead: OO#.. Playing
Research Engineer (Solar/Batteries O.O#. #.O#. with
/Software/Embedded Controllers) .OO#. .OO#. rocks...1k
______________________________________________
[email protected] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
