Pandas group by and sum, but create a new row when a certain amount is exceeded
well, it depends, you're asking an NP problem currently unless you don't want the optimum weight in under 100, there are a few algoritems you can do,
but none are o(n) which is what group by and the sum does, lets say you iterate with iterrows() (try to avoid that), would you be able to do so in one iteration? if you are not looking for an optimum solution (closest to 100 each match) there is an option.
for every company, you have to sort it by increasing values.
using iteration to open a new row every time sum is reaching a 100, at a side variable, and replacing the origin at the end
There isn't a pandas / Numpy standard solution that I know of.
I currently have a data set where im trying to group up rows based on a column and sum the columns where the values are integers.
However, the catch is I would like to create a new row once the sum has reached a certain threshhold
For example, in the below dataframe, I am trying to group the rows based on company name and sum up the weights, however, I do not want weight to exceed 100.
Input dataframe:




Company
Weight




a
30


b
45


a
27


a
40


b
57


a
57


b
32




Output dataframe:




Company
Weight




a
97


a
57


b
89


b
45




I have tried using group by and sum, however, it cannot detect whether or not I have reached a maximum amount.
Is there any way I can achieve this?
Any help would be greatly appreciated!