创建像在Excel中创建的低效数据框所需的工作量比您想象的要多

示例

df = pd.DataFrame({'model':['A','A','A','A', 'A','A','B','B','B','B'],
               'lot':['aa','ab','ac','ad', 'ae','af','ba','bb','bc','bd'],
               'box':[1,1,2,2,2,3, 1,2,2,2],
               'qty':[0.2, 0.1, 0.4, 0.5, 0.1, 0.2, 0.6, 0.3, 0.2, 0.4]})

df

model   lot box qty
0   A       aa  1   0.2
1   A       ab  1   0.1
2   A       ac  2   0.4
3   A       ad  2   0.5
4   A       ae  2   0.1
5   A       af  3   0.2
6   B       ba  1   0.6
7   B       bb  2   0.3
8   B       bc  2   0.2
9   B       bd  2   0.4

步骤1

在df中创建sum列

g = df.groupby(['model', 'box'])
cond1 = g.cumcount().eq(g['qty'].transform('count').sub(1).div(2).astype('int'))
df['sum'] = g['qty'].transform('sum').where(cond1)

df

model   lot box qty sum
0   A       aa  1   0.2 0.3
1   A       ab  1   0.1 NaN
2   A       ac  2   0.4 NaN
3   A       ad  2   0.5 1.0
4   A       ae  2   0.1 NaN
5   A       af  3   0.2 0.2
6   B       ba  1   0.6 0.6
7   B       bb  2   0.3 NaN
8   B       bc  2   0.2 0.9
9   B       bd  2   0.4 NaN

第二步

对框数据框进行小计

df1 = (df
       .groupby('model').agg({'box':'max', 'sum':'sum'})
       .assign(lot=lambda x: x['box'].astype('str') + 'box')
       .drop('box', axis=1)
       .reset_index())

df1

model   sum lot
0   A       1.5 3box
1   B       1.5 2box

步骤3

concat df & df1

pd.concat([df, df1]).sort_values('model').reset_index(drop=True)

输出：

model   lot     box qty sum
0   A       aa      1.0 0.2 0.3
1   A       ab      1.0 0.1 NaN
2   A       ac      2.0 0.4 NaN
3   A       ad      2.0 0.5 1.0
4   A       ae      2.0 0.1 NaN
5   A       af      3.0 0.2 0.2
6   A       3box    NaN NaN 1.5
7   B       ba      1.0 0.6 0.6
8   B       bb      2.0 0.3 NaN
9   B       bc      2.0 0.2 0.9
10  B       bd      2.0 0.4 NaN
11  B       2box    NaN NaN 1.5