Assigning values to multiple columns after applying elif logic

Question

I am trying to assign material counts to multiple different columns in a dataframe based on some logic.

This is what I have so far:

df['8footer'] = ((df[(df['type'] == 'Linear Section') & (df.qty <= 8.17)]['qty'])*(df['wall sides'])).fillna(0)
df['9footer'] = ((df[(df['type'] == 'Linear Section') & (df.qty > 8.17) & (df.qty <= 9.17)]['qty'])*(df['wall sides']))\
.fillna(0)
df['10footer'] = ((df[(df['type'] == 'Linear Section') & (df.qty > 9.17) & (df.qty <= 10.17)]['qty'])*(df['wall sides']))\
.fillna(0)
df['12footer'] = ((df[(df['type'] == 'Linear Section') & (df.qty > 10.17) & (df.qty <= 14)]['qty'])*(df['wall sides']))\
.fillna(0)

Where I'm getting stuck is after the final line (df.qty > 14). for qty > 14 I basically want to waterfall values into columns ['8footer', 9footer', '10footer'] based on the above logic. The logic that I would essentially be trying to apply on df.qty > 14 is:

if df.qty > 14:
    x = df.qty % 12
    if x < 8.17:
        df['8footer'] = x
    elif x < 9.17:
        df['9footer'] = x
    elif x < 10.17:
        df['10footer'] = x
    else:
        df['12footer'] = x

I've tried writing the above logic use np.select(), pd.where() etc. but I'm having trouble assigning to different columns based on logic. This is a little bit beyond my skillset in python. Any help would be appreciated.

TIA

As an example:

index	qty	8footer	9footer	10footer	12footer
0	7	7	0	0	0
1	8.5	0	8.5	0	1
2	9.5	0	0	9.5	0
3	10.5	0	0	0	10.5
4	46	0	0	10	36

I've mocked up a table above which resembles my desired df. As indicated above I have the logic sorted out for index location 0,1,2 and 3. I can't figure out how to solve for index location 4.

Index location 4 has a qty of 46. I am looking to optimize material usage so I have divided the quantity by the largest size (12footer), assigned the divisable number to the 12footer column and assigned the remainder to the next optimal column (10footer). Hopefully this helps provide some clarity on the desired outcome.

Answer 1

Assuming this example:

df = pd.DataFrame({'qty': [8,20,21,22,23]})

You can use cut combined with pivot:

bins = [0, 8.17, 9.17, 10.17, 12]
labels = ['8footer', '9footer', '10footer', '12footer']

s = df['qty'].where(df['qty'].gt(14)).mod(12)

out = df.join(
 s.dropna()
  .to_frame()
  .assign(col=pd.cut(s, bins=bins, labels=labels))
  .pivot(columns='col', values='qty')
 )

print(out)

Output:

   qty  8footer  9footer  10footer  12footer
0    8      NaN      NaN       NaN       NaN
1   20      8.0      NaN       NaN       NaN
2   21      NaN      9.0       NaN       NaN
3   22      NaN      NaN      10.0       NaN
4   23      NaN      NaN       NaN      11.0