0

I'm not sure how to word this.

I have a situation where I have daily quantities that I need to evenly split into 3 different shifts.

Example
Original DataFrame

product# day quantity
1 Jan 1 30
1 Jan 2 30
2 Jan 2 60

Desired Result Data Frame

product# day quantity shift
1 Jan 1 10 1
1 Jan 1 10 2
1 Jan 1 10 3
2 Jan 2 20 1
2 Jan 2 20 2
2 Jan 2 20 3

I have a working version that is just the easy pd.iterrows() version but i'm wondering if its possible to do it via the explode function or something like you can with strings here. How to separate string into multiple rows in Pandas

edit: The table markdown is working on preview but on on result :/

inzikind
  • 31
  • 5

1 Answers1

0

You can achieve this using the explode function in pandas, since explode is primarily designed for handling lists or Series of values within a DataFrame, you'll need to first create a "shifts" column with the quantities split into separate rows for each shift.

import pandas as pd

# Create the original DataFrame
data = {'product#': [1, 2], 'day': ['Jan 1', 'Jan 2'], 'quantity': [30, 60]}
df = pd.DataFrame(data)

# Calculate the quantity per shift
shifts = 3
df['quantity_per_shift'] = df['quantity'] // shifts

# Create a new DataFrame with shifted quantities
shifted_df = df.explode('quantity_per_shift')

# Assign shift numbers
shifted_df['shift'] = shifted_df.groupby('product#').cumcount() + 1

# Drop the temporary column
shifted_df.drop(columns='quantity_per_shift', inplace=True)

print(shifted_df)
Wasabiiiiiii
  • 27
  • 2
  • This works great, how would it change tho If i make a product on multiple days? | product# | day | quantity | |---|---|---| | 1 | Jan 1 | 30 | | 1 | Jan 2 | 30 | | 2 | Jan 2 | 60 | i would still want to split by day, i just extend the group by to a multi index right? – inzikind Aug 17 '23 at 07:48
  • @inzikind yes! shifted_df['shift'] = shifted_df.groupby(['product#', 'day']).cumcount() + 1 – Wasabiiiiiii Aug 17 '23 at 07:52