CODEWITHSUNDEEP
pythonpandasdataframeapply
Gnudiff
Gnudiff

Reputation: 4305

Can I somehow apply incremental values in groups, in Pandas?

If I have a large dataframe in Pandas, let's say df:

item_serial_number, barcode
12312313-123123123  ABC
12312313-123123124  ABC
...
44312313-123123125  DEF
55512313-123123126  DEF

This df lists devices that have different physical sizes. So a different amount of each kind of device ABC or DEF fits into its kind of box.

I have a list how many of which kind of devices fit into their boxes, in a dataframe bf:

barcode, items_per_box
ABC   3
DEF   2
...

I need to assign each device to a numbered box, packing them according to their size. So every three ABC items should go into same box number, next ones into next box number, etc. Every 2 DEF items should go into same box number, etc.

So the end result should be:

item_serial_number, barcode, box_number
12312313-123123123  ABC   1
12312313-123123124  ABC   1
12312313-123123128  ABC   1
12312313-123123121  ABC   2
12312313-123123101  ABC   2
12312313-123123121  ABC   2
12312313-123123125  ABC   3
...
44312313-123123125  DEF   1
55512313-123123126  DEF   1
12312313-123125123  DEF   2
12312313-123126124  DEF   2
12312313-123127123  DEF   3
12312313-123128124  DEF   3

Currently I have a for loop going through barcodes of boxf and applying each number by another loop through df.

Is it possible to do it elegantly with pandas .apply or similar functions, or am I stuck with some for loops looping over each barcode? I looked around here on SO, but perhaps I can't think of correctly wording the problem, as I found no duplicates of this question.

Upvotes: 1

Views: 81

Answers (1)

rhug123
rhug123

Reputation: 8768

I think this is what you are looking for:

df['box_number'] = df.groupby('barcode').cumcount().floordiv(df['barcode'].map(bf.set_index('barcode')['items_per_box'].to_dict()),axis=0).add(1)

Upvotes: 1

Related Questions