我有以下数据集,我正在根据其余部分计算Net Forecast
列。
实现的逻辑是,
- 如果零件有
Order
<0,我们在同一行中将其与Gross Forecast
一起添加,即0
. - 如果上述计算的
Order
还不是正数,我们将它与上周的Gross Forecast
相加,即-1
。 - 我们按照以下顺序继续这个循环
[0, -1, -2, -3, 1, 2, 3]
,即返回 3 周并前进 3 周,直到Order
为 0。 - 如果没有更多的周数,或者订单未完成,我们将使用
Gross Forecast
列进行的计算转移到Net Forecast
列。
Part Week Gross Forecast Orders Net Forecast
0 A 1 10 0 10
1 A 2 5 0 0
2 A 3 30 0 0
3 A 4 20 0 0
4 A 5 10 -70 0
5 A 6 50 0 0
6 A 7 5 -60 0
7 A 8 30 0 20
8 Z 1 10 0 10
9 Z 2 5 0 -15
10 Z 3 10 0 0
11 Z 4 30 0 0
12 Z 5 30 -90 0
我能够重新创建逻辑,但使用标准iterrows
真的很慢。是否可以使用 Pandas 和 Numpy 对此解决方案进行矢量化?
import pandas as pd
import numpy as np
data = {
"Part": ["A", "A", "A", "A", "A", "A", "A", "A", "Z", "Z", "Z", "Z", "Z"],
"Week": [1, 2, 3, 4, 5, 6, 7, 8, 1, 2, 3, 4, 5],
"Gross Forecast": [10, 5, 30, 20, 10, 50, 5, 30, 10, 5, 10, 30, 30],
"Orders": [0, 0, 0, 0, -70, 0, -60, 0, 0, 0, 0, 0, -90],
}
df = pd.DataFrame(data)
print(df)
# Create Net Forecast column
df["Net Forecast"] = df["Gross Forecast"]
for i, row in df.iterrows():
k = 0
order = 0
inventory = 0
index_list = [0, -1, -2, -3, 1, 2, 3]
if df.loc[i, "Orders"] != 0:
order = df.loc[i, "Orders"]
for j in index_list:
try:
if order < 0 and (df.loc[i, "Part"] == df.loc[i + j, "Part"]):
order = order + df.loc[i + j, "Net Forecast"]
df.loc[i + j, "Net Forecast"] = 0
k = j
else:
break
except KeyError:
break
df.loc[i + k, "Net Forecast"] = order
print(df)
通常,在涉及库存等余额的情况下,可以使用流量的累积来实现"矢量化"。
迭代balance[t] = balance[t-1] + in[t] - out[t]
变得矢量化balance = in.cumsum() - out.cumsum()
import numpy as np
in_ = np.array( [10, 5, 30, 20, 10, 50, 5, 30, 0, 0, 0, 0] )
orders = np.array( [0, 0, 0, 0, 70, 0, 60, 0, 0, 0, 0, 0] )
# 4 extra periods to handle the out of date range.
out_of_date = np.zeros_like( in_ )
out_of_date[ 4: ] = in_[ :-4 ]
# Parts fall out of date after 4 weeks if not already delivered.
# Create cumulatives to work with
cum_in = in_.cumsum() # Constant through calculation
cum_orders = orders.cumsum() # Constant through calculation
cum_ood = out_of_date.cumsum() # Amended at each iteration
cum_deliveries = np.zeros_like( cum_in ) # One period filled each iteration
available = np.zeros_like( in_ ) # One period filled each iteration
deliveries = np.zeros_like( cum_in ) # One period filled each iteration
def decum( in_, axis=-1 ):
""" Take differences in_[t] - in_[t-1] fill first period with in_[0] """
res = in_.copy()
res[ 1: ] = np.diff(in_, 1, axis = axis)
return res
def cum_dels( week ):
""" Calcultes cumulative deliveries at the week specified.
Also calculates the value of deliveries in the week """
available[ week ] = cum_in[ week ] - cum_ood[ week ]
cum_deliveries[ week ] = np.minimum( cum_orders[ week ], available[ week ] )
if week:
deliveries[ week ] = cum_deliveries[ week ] - cum_deliveries[ week-1 ]
else:
deliveries[ week ] = cum_deliveries[ week ] # If week == 0 no difference to take
def amend_ood( week ):
""" Amend the cum_ood for deliveries in the week. """
min_cum_ood = cum_ood[ week ] # Don't subtract to below the cum_ood in this week.
available_notused = available[ week ] - cum_deliveries[ week ]
# Don't subtract any available that's not delivered.
# This has the effect of taking deliveries from the most recent in_
max_subtract = np.maximum( cum_ood[ week: ] - min_cum_ood - available_notused, 0)
# The maximum to subtract is the cum_ood less the fixed bands and never less than zero.
to_subtract = np.minimum( max_subtract, deliveries[ week ] ) # max_subtract clipped at the weeks deliveries
cum_ood[ week: ] -= to_subtract
week_range = range(8)
# Iterate the above functions by week.
# This can be rewritten to calculate all part numbers for each week.
for week in week_range:
cum_dels( week )
amend_ood( week )
print(deliveries)
print(decum(cum_ood)[4:])
需要重写这些函数才能使用 2D 数组,part_number x 周。 然后,每周的每次迭代都会计算该周的所有部件号。
一旦我有时间,我会考虑将其制作为 2d,但这可能会有所帮助。 当然还有优化代码的余地。它是为了帮助我理解我在做什么而写的。
**编辑更改以运行 2D 版本**
out_of_date = np.zeros_like( in_ )
out_of_date[ :, 4: ] = in_[ :, :-4 ]
# Parts fall out of date after 4 weeks if not already delivered.
# Create cumulatives to work with
cum_in = in_.cumsum(axis=1) # Constant through calculation
cum_orders = orders.cumsum(axis=1) # Constant through calculation
cum_ood = out_of_date.cumsum(axis=1) # Amended at each iteration
cum_deliveries = np.zeros_like( cum_in ) # One period filled each iteration
available = np.zeros_like( in_ ) # One period filled each iteration
deliveries = np.zeros_like( cum_in ) # One period filled each iteration
def decum( in_, axis=-1 ):
""" Take differences in_[t] - in_[t-1] fill first period with in_[0] """
res = in_.copy()
res[ :, 1: ] = np.diff(in_, 1, axis = axis)
return res
def cum_dels( week ):
""" Calcultes cumulative deliveries at the week specified.
Also calculates the value of deliveries in the week """
available[ :, week ] = cum_in[ :, week ] - cum_ood[ :, week ]
cum_deliveries[ :, week ] = np.minimum( cum_orders[ :, week ], available[ :, week ] )
if week:
deliveries[ :, week ] = cum_deliveries[ :, week ] - cum_deliveries[ :, week-1 ]
else:
deliveries[ :, week ] = cum_deliveries[ :, week ] # If week == 0 no difference to take
def amend_ood( week ):
""" Amend the cum_ood for deliveries in the week. """
min_cum_ood = cum_ood[ :, week ] # Don't subtract to below the cum_ood in this week.
available_notused = available[ :, week ] - cum_deliveries[ :, week ]
# Don't subtract any available that's not delivered.
# This has the effect of taking deliveries from the most recent in_
max_subtract = np.maximum( cum_ood[ :, week: ] - min_cum_ood[:,None] - available_notused[:,None], 0)
# The maximum to subtract is the cum_ood less the fixed bands and never less than zero.
to_subtract = np.minimum( max_subtract, deliveries[ :, week ].reshape(-1,1) ) # max_subtract clipped at the weeks deliveries
cum_ood[ :, week: ] -= to_subtract
这不会给出与部件号 Z 版本相同的结果。
以下方案预期会产生什么结果?
data = {
"Part": ["Z", "Z", "Z", "Z", "Z", "Z"],
"Week": [1, 2, 3, 4, 5, 6],
"Gross Forecast": [10, 5, 10, 30, 30, 0],
"Orders": [ 0, 0, 0, 0, -90, 0]
}
或者这个
data = {
"Part": ["Z", "Z", "Z", "Z", "Z", "Z", "Z", "Z"],
"Week": [1, 2, 3, 4, 5, 6,7,8],
"Gross Forecast": [10, 5, 10, 30, 30, 0, 0, 100],
"Orders": [ 0,-90, 0, 0, 0, 0, 0, -50]
}