Assign groups by total sum using R-CodePudding

I have plate_df that makes a pattern of a 96 well plate in column format A1, B1, C1, etc. and a dataframe df2 that has different values in the quantity column.
I am having trouble adding well assignments from plate_df to a new column in df2 that will group those quantities of df2 into groups of 100 until all the quantities have been assigned a well. The dummy data has a pattern, but please treat it like the quantities are random.

#Make pattern for target well assignments 
rowsPlate <- c("A", "B", "C", "D", "E", "F", "G", "H")
columnsPlate <- seq(from = 1, to = 12, by = 1)

wells <- NULL

for (col in columnsPlate){    
 wells <- c(wells, paste0(rowsPlate, col))  
}

#Make df2
plate_df <- data.frame(wells)

df2 <- data.frame(index=seq(1,96, by=1), quantity = sample(0.1:20,8, replace = TRUE))

Looking for something like this

CodePudding user response：

You can calculate a rolling sum of your "quantities" column, then divide this by 100, always rounding up to the nearest whole number. We can then use this numbering as an index into the wells vector to create the labeling. But be sure that you have enough labels in wells to handle all the bins.

# create labels
rowsPlate <- LETTERS[1:8]
columnsPlate <- 1:12
wells <- c(outer(rowsPlate, columnsPlate, paste0))

#Make df2
df2 <- data.frame(index=seq(1,96, by=1), quantity = sample(0.1:20,8, replace = TRUE))


library(tidyverse)

# example output
df3 <- df2 %>% 
  mutate(
    quantity_running = cumsum(quantity),
    bin = ceiling(quantity_running / 100),
    well = wells[bin]
  )

   index quantity quantity_running bin  well
1      1     11.1             11.1   1    A1
2      2     14.1             25.2   1    A1
3      3      9.1             34.3   1    A1
4      4     12.1             46.4   1    A1
5      5      6.1             52.5   1    A1
6      6      8.1             60.6   1    A1
7      7      8.1             68.7   1    A1
8      8      9.1             77.8   1    A1
9      9     11.1             88.9   1    A1
10    10     14.1            103.0   2    B1
11    11      9.1            112.1   2    B1
12    12     12.1            124.2   2    B1
13    13      6.1            130.3   2    B1
14    14      8.1            138.4   2    B1
15    15      8.1            146.5   2    B1
16    16      9.1            155.6   2    B1
17    17     11.1            166.7   2    B1
18    18     14.1            180.8   2    B1
19    19      9.1            189.9   2    B1