How to break one column to multiple rows

[shasank]

Hi R Community,

Thank you for your time.

I am unable to figure out a way to perform the following operation.
The logic for the operation is to break down a column to multiple rows with limits on each cell value.
for 1st row and second row max is 250. subsequent are 500 max. each value in amount should be broken down to multiple rows based on these set of rules.
for ex:- ID 1 has 1500 as amount. when being broken down. the first row and second row can allow max 250 as a values. and next rows accommodate the rest until the amount value is maxed out

Raw data
ID	Name	Amount
1	aa	1500
2	bb	2000
3	cc	1000
4	dd	500
Final output
ID	Name	Amount
1	aa	250
1	aa	250
1	aa	500
1	aa	500
2	bb	250
2	bb	250
2	bb	500
2	bb	500
2	bb	500
2	bb	500
3	cc	250
3	cc	250
3	cc	500
4	dd	250
4	dd	250

[wolfpack]

Broke the cardinal rule of not using for loops in R, but here's a solution:

input_dat <- data.frame(ID=c(1,2,3,4), 
                  Name=c("aa","bb","cc","dd"), 
                  Amount=c(1500,2000,1000,500))

# For row 1 and 2, limit 250
# For row 3+, limit 500
limit <- c(250,250,500)

output_dat <- data.frame(ID=NULL, Name=NULL, Amount=NULL)

for (i in 1:nrow(input_dat)) {
  total <- input_dat$Amount[i]
  iter <- 1
  while (total > 0) {
    total <- total - limit[iter]
    output_dat <- rbind(output_dat,
                     data.frame(ID=input_dat$ID[i], 
                                Name=input_dat$Name[i], 
                                Amount=limit[iter]))
    # If you get to the last value in limit, stop incrementing
    if (iter < length(limit))
      iter <- iter + 1
  }
}