1

I am currently trying to reduce the values in a column by a certain total amount, until that amount reaches zero. For example, in the data frame created by this code: 

df = data.frame(matrix(0,nrow=10,ncol=2))
colnames(df) <- c("AccountNumber", "BuyAmount")
overbuy <- 500000
df$AccountNumber <- seq(1:10)
df$BuyAmount <- c(35000, 220000, 240000, 0, 195000, 55000, 0, 280000, 65000, 105000)

>df
   AccountNumber BuyAmount
1              1     35000
2              2    220000
3              3    240000
4              4         0
5              5    195000
6              6     50000
7              7         0
8              8    280000
9              9     65000
10            10    105000

I am trying to reduce the nonzero values in the BuyAmount column by 5000 at a time, running along the column and reducing the value of overbuy by 5000 each time. After the first run through the loop, df should look like so:

>df
   AccountNumber BuyAmount
1              1     30000
2              2    215000
3              3    235000
4              4         0
5              5    190000
6              6     45000
7              7         0
8              8    275000
9              9     60000
10            10    100000

And the value of overbuy should be reduced by 40000 to 460000. I would like this loop to continue to run over these values until overbuy reaches 0. In theory, df would end as

>df
   AccountNumber BuyAmount
1              1         0
2              2    150000
3              3    170000
4              4         0
5              5    130000
6              6         0
7              7         0
8              8    210000
9              9         0
10            10     35000

once overbuy reaches 0. My current attempt is:

while(overbuy > 0){
  for(1 in 1:10){
    ifelse(df$BuyAmount[i] != 0, df$BuyAmount[i] <- df$BuyAmount[i] - 5000, "")
    overbuy <- overbuy - 5000
  }
}

Any help would be appreciated!

Nick
  • 45
  • 4
  • What kind of analysis are you performing? There are definitely parts of `dplyr` that could make your code more elegant, but it would help to understand the big picture. – Andrew Brēza Aug 08 '16 at 20:53
  • I currently have a code that will tell me a total amount to buy for each account, but it usually overshoots by a few thousand dollars due to rounding errors (each order can only be placed in increments of $5000). I want to systematically reduce this overshoot to ensure that I don't end up overbuying in each account and putting it into debit. I used an extreme example above to show that once a value hits 0 it should be excluded from the rest of the iterations. – Nick Aug 08 '16 at 20:57
  • Would it be more useful to reduce the price until it was a number evenly divisible by 5000? That way you'd end up with the closest bid price without going over the value. – Andrew Brēza Aug 08 '16 at 21:18
  • Probably not- There's a little more nuance going into the purchasing decisions that I can't really get into. I had tried to divide the total `overbuy` into equal lots across each account but it ends up under-buying by a significant amount if I keep the lots in increments of 5000, which I have to do. Thus, if the amount of overbuy is small then the reductions need to be applied iteratively down the column until `overbuy` reaches 0. – Nick Aug 08 '16 at 21:37
  • What's your desired stop condition? Do you need overbuy to go down to 0 or to 460000? – mkt Aug 08 '16 at 21:39
  • Down to 0 please. The 460000 number was just after it ran down the column the first time (8 reductions of 5000 in the first loop reduce `overbuy` from 500000 to 460000). – Nick Aug 08 '16 at 21:43
  • To give a bit of pseudocode to put what I'm trying to accomplish into plain language, I'm trying to do the following: `Check current row of df$BuyAmount. Is it > 0?` `If yes, is overbuy >0? If no, exit the loop.` `If overbuy is >0, reduce this row by 5000. If not, do nothing.` `Move down one row. If the end of the column is reached, go to row 1.` `Repeat` – Nick Aug 08 '16 at 21:52

2 Answers2

1

I think this works, but it's not elegant and I might not have fully understood your goal. If not, let me know and I'll fix this.

EDITED: 

while(overbuy > 0){
    for(i in 1:10){

        if(df$BuyAmount[i]!=0){
            overbuy <- overbuy - 5000
        }
        df$BuyAmount[i] <- pmax(0, df$BuyAmount[i] - 5000)

        print(overbuy)
        print(df)
        if(overbuy == 0) break
    }
}
mkt
  • 437
  • 7
  • 20
  • I will add that the final df that I get is not exactly the same as your expected output. But I think this follows the logic of your question, so perhaps the error is in your calculations? – mkt Aug 08 '16 at 21:54
  • I think this is still iterating on 0 elements instead of skipping them, but it's certainly close. It looks like it is going along all 10 elements, and returning the maxima of either 0 or -5000 when working at an element == 0, which is where I think the discrepancy comes from. – Nick Aug 08 '16 at 22:03
  • Now gets the same output as your expectation. – mkt Aug 08 '16 at 22:11
1

@mkt looks like you just needed one line, if(df$BuyAmount[i] <= 0) next

while(overbuy > 0){
    for(i in 1:10){
    if(df$BuyAmount[i] <= 0) next
    df$BuyAmount[i] <- pmax(0, df$BuyAmount[i] - 5000)
    overbuy <- overbuy - 5000 
    print(overbuy)
    print(df)
    if(overbuy == 0) break
    }
    if(overbuy == 0) break
}
Nick
  • 45
  • 4