def create_table(df):
df1 = df.copy()
df1.amount = df1.amount.astype(int)
df1.cycle_times = df1.cycle_times.astype(float)
df1.cycle_times = df1.amount * df1.cycle_times.astype(float)
df1.drop(joining_indices(df1), inplace=True)
df1.drop(df1[df1.product_no.eq(df1.product_no.shift(1, fill_value=0))
& df1.level.eq(1)].index,
inplace=True)
production_path = df1.groupby("station",
as_index=False).agg({"level": np.mean})
production_path.sort_values("level", ascending=False, inplace=True)
machine_legend_table = production_path["station"].copy()
machine_legend_table.index = list(
range(1, machine_legend_table.shape[0] + 1))
machine_legend_table = machine_legend_table.to_frame()
production_path.drop("level", axis=1, inplace=True)
production_path.columns = [1]
production_path = production_path.transpose()
production_path.columns = list(range(1, production_path.shape[1] + 1))
df1 = df1.groupby(["product_no", "station"],
as_index=False).agg({"cycle_times": sum})
product_family_legend_table = pd.DataFrame(
df1.product_no.unique().tolist(),
columns=["product_no"],
index=list(range(1,
len(df1.product_no.unique().tolist()) + 1)))
prod_count = df1.product_no.nunique()
grouped_df = df1.groupby("station", as_index=False).agg({
"product_no":
"count",
"cycle_times": ["min", "mean", "max"]
})
grouped_df.columns = ["station", "product_no", "min", "mean", "max"]
temp_df = pd.merge(left=production_path.transpose(),
right=grouped_df,
how="left",
left_on=1,
right_on="station")
# station_path = temp_df.station.transpose()
# probabilities = (temp_df.product_no/prod_count).transpose()
# times = temp_df.cycle_times.transpose()
return temp_df
def create_tables(self, pivot=True):
# df => merged_file
df = self.merged_file.copy()
df.amount = df.amount.astype(int)
df.cycle_times = df.cycle_times.astype(float)
df.cycle_times = df.amount*df.cycle_times.astype(float)
df.drop(joining_indices(df), inplace = True)
df.drop(df[df.product_no.eq(df.product_no.shift(1, fill_value = 0)) & df.level.eq(1)].index, inplace = True)
# Legends for the machine route and the product number index
production_path = df.groupby("station", as_index = False).agg({"level": np.mean})
production_path.sort_values("level", ascending = False, inplace = True)
machine_legend = production_path["station"].copy()
machine_legend.index = list(range(1, machine_legend.shape[0] + 1))
machine_legend = machine_legend.to_frame()
production_path.drop("level", axis = 1, inplace = True)
production_path.columns = [1]
production_path = production_path.transpose()
production_path.columns = list(range(1, production_path.shape[1] + 1))
product_no_legend = DataFrame(df.product_no.unique().tolist(), columns = [1])
machine_info_df = merge(left = machine_legend, right = self.machine_info, how = "left", left_on = "station",
right_on = "machine").fillna(1).iloc[:, [0, 2, 3]]
machine_info_df.index = list(range(1, machine_info_df.shape[0] + 1))
# Creation of h(times), k(binary), d(order), f(amount), s(shift) and c(cost) tables
df = df.groupby(["product_no", "station"], as_index = False).agg({"cycle_times": sum})
h = df.pivot("product_no", "station", "cycle_times").fillna(0)
k = df.copy()
k.at[k.cycle_times > 0, "cycle_times"] = 1
k = k.pivot("product_no", "station", "cycle_times").fillna(0)
h = h[machine_legend.iloc[:, 0].to_list()]
k = k[machine_legend.iloc[:, 0].to_list()]
f = machine_info_df["quantity"].to_frame().transpose().copy()
s = machine_info_df["shift"].to_frame().transpose().copy()
c = DataFrame(index = list(range(1, 4)), columns = [1], data = [0, 23, 151])
if pivot:
d = self.pivot_days()
else:
d = self.plan.copy()
for curr_table in [d, h, k]:
curr_table.index = list(range(1, curr_table.shape[0]+1))
return product_no_legend, machine_legend, d, h, k, f, s, c
def create_tables(self):
self.cross_trim()
product_family_legend = DataFrame(list(
self.merged_file.product_no.str.split(".").apply(
lambda x: x[0]).unique()),
columns=["product_family"])
prod_path = self.merged_file.groupby(by="station", as_index=False).agg(
{"level": "mean"})
prod_path.sort_values(by="level", ascending=False, inplace=True)
machine_legend = DataFrame(prod_path.station.to_list(),
columns=["station"])
bom_data = self.merged_file.copy()
bom_data.amount = bom_data.amount.astype(int)
bom_data.cycle_times = bom_data.cycle_times.astype(float)
bom_data.cycle_times = bom_data.amount * bom_data.cycle_times.astype(
float)
bom_data.drop(joining_indices(bom_data), inplace=True)
bom_data.drop(bom_data[
bom_data.product_no.eq(bom_data.product_no.shift(1, fill_value=0))
& bom_data.level.eq(1)].index,
inplace=True)
bom_data = bom_data.groupby(by=["product_no", "station"],
as_index=False).agg({"cycle_times": "sum"})
bom_data["product_family"] = bom_data.product_no.str.split(".").apply(
lambda x: x[0])
prod_cnt = {
x: bom_data[bom_data.product_family == x].product_no.nunique()
for x in list(bom_data.product_family.unique())
}
bom_data = bom_data.groupby(by=["product_family", "station"],
as_index=False).agg({
"cycle_times": "mean",
"product_no": "count"
})
bom_data.columns = [
"product_family", "station", "cycle_times", "product_count"
]
bom_data[
"probability"] = bom_data.product_count / bom_data.product_family.replace(
prod_cnt)
machine_info_df = merge(
left=machine_legend,
right=self.machine_info,
how="left",
left_on="station",
right_on="machine").fillna(1).iloc[:, [0, 2, 3, 4, 5]]
# AVERAGE ORDER SIZE
avg_df = self.order_history.orders.copy()
avg_df["product_family"] = avg_df.product_no.str.split(".").apply(
lambda x: x[0])
avg_df = avg_df.groupby("product_family", as_index=False).agg({
"date":
"count",
"amount":
"sum"
})
avg_df["order_amount"] = avg_df.amount / avg_df.date
avg_df.order_amount = avg_df.order_amount.floordiv(1) + 1
avg_df.drop(["date", "amount"], axis=1, inplace=True)
o = pd.merge(left=pd.DataFrame(
product_family_legend.product_family.unique(),
columns=["product_family"]),
right=avg_df,
how="left",
left_on="product_family",
right_on="product_family").fillna(
avg_df.order_amount.mean().__floordiv__(1))
o.set_index(keys="product_family", inplace=True)
# avg_df.drop(["date", "amount"], inplace = True, axis = 1)
# AVERAGE ORDER SIZE
# SETUP TIMES
st = pd.merge(left=prod_path.station,
right=self.setup,
how="left",
left_on="station",
right_on="stations_list").iloc[:, [0, 2]].copy()
st[st.columns[1]] = st[st.columns[1]].fillna(0)
st.set_index(keys="station", inplace=True)
st = st.transpose()
# SETUP TIMES
# OUTSOURCE AVAILABILITY
outsource_perm = pd.merge(
left=prod_path.station,
right=self.machine_info,
how="left",
left_on="station",
right_on="machine"
).loc[:, ["station", "outsource_availability"]].copy()
outsource_perm[outsource_perm.columns[1]] = outsource_perm[
outsource_perm.columns[1]].fillna(0)
outsource_perm.set_index(keys="station", inplace=True)
outsource_perm = outsource_perm.transpose()
# OUTSOURCE AVAILABILITY
d = self.forecast.copy()
h = bom_data.pivot("product_family", "station",
"cycle_times").fillna(0)
h = h[machine_info_df[machine_info_df.columns[0]]].copy()
k = bom_data.pivot("product_family", "station",
"probability").fillna(0)
k = k[machine_info_df[machine_info_df.columns[0]]].copy()
f = machine_info_df["quantity"].to_frame().transpose().copy()
# Workdays will be implemented
w = DataFrame([25, 24, 25, 25, 25, 19, 26, 23, 25, 25, 26, 23],
index=list(range(1, 13))).transpose()
# Budget part will be implemented
# b = DataFrame([100000]*12, index = list(range(1, 13))).transpose()
# Costs will be implemented
c = DataFrame([20, 23, 151], index=[1, 2, 3]).transpose()
# Machine costs will be implemented
cr = merge(left=prod_path,
right=machine_info_df,
how="left",
left_on="station",
right_on="station").fillna(
0)["procurement_cost"].to_frame().transpose()
for curr_df in [
product_family_legend, machine_legend, d, h, k, f, o, st, cr,
outsource_perm
]:
curr_df.index = list(range(1, curr_df.shape[0] + 1))
curr_df.columns = list(range(1, curr_df.shape[1] + 1))
d = create_scenarios(d)
d = d.astype(int)
return product_family_legend, machine_legend, d, h, k, f, w, c, cr, o, st, outsource_perm
def create_tables(self, pivot=True):
self.cross_trim()
# df => merged_file
df = self.merged_file.copy()
df.amount = df.amount.astype(int)
df.cycle_times = df.cycle_times.astype(float)
df.cycle_times = df.amount * df.cycle_times.astype(float)
df.drop(joining_indices(df), inplace=True)
df.drop(df[df.product_no.eq(df.product_no.shift(1, fill_value=0))
& df.level.eq(1)].index,
inplace=True)
# Legends for the machine route and the product number index
production_path = df.groupby("station",
as_index=False).agg({"level": "mean"})
production_path.sort_values("level", ascending=False, inplace=True)
machine_legend = production_path["station"].copy()
machine_legend.index = list(range(1, machine_legend.shape[0] + 1))
machine_legend = machine_legend.to_frame()
production_path.drop("level", axis=1, inplace=True)
production_path.columns = [1]
production_path = production_path.transpose()
production_path.columns = list(range(1, production_path.shape[1] + 1))
product_no_legend = DataFrame(df.product_no.unique().tolist(),
index=list(
range(1,
df.product_no.nunique() + 1)),
columns=["product_no"])
machine_info_df = merge(left=machine_legend,
right=self.machine_info,
how="left",
left_on="station",
right_on="machine").fillna(1).iloc[:,
[0, 2, 3]]
machine_info_df.index = list(range(1, machine_info_df.shape[0] + 1))
# AVERAGE ORDER SIZE
avg_df = self.order_history.orders.groupby("product_no",
as_index=False).agg({
"date":
"count",
"amount":
"sum"
})
avg_df["order_amount"] = avg_df.amount / avg_df.date
avg_df.order_amount = avg_df.order_amount.floordiv(1) + 1
avg_df.drop(["date", "amount"], axis=1, inplace=True)
o = pd.merge(left=pd.DataFrame(self.plan.product_no.unique(),
columns=["product_no"]),
right=avg_df,
how="left",
left_on="product_no",
right_on="product_no").fillna(
avg_df.order_amount.mean().__floordiv__(1))
o.set_index(keys="product_no", inplace=True)
# AVERAGE ORDER SIZE
# SETUP TIMES
st = pd.merge(left=production_path.transpose(),
right=self.setup,
how="left",
left_on=1,
right_on="stations_list").iloc[:, [0, 2]].copy()
st[st.columns[1]] = st[st.columns[1]].fillna(0)
st.set_index(keys=[1], inplace=True)
st = st.transpose()
# SETUP TIMES
# OUTSOURCE AVAILABILITY
outsource_perm = pd.merge(left=production_path.transpose(),
right=self.machine_info,
how="left",
left_on=1,
right_on="machine").loc[:, [
production_path.transpose().
columns[0], "outsource_availability"
]].copy()
outsource_perm[outsource_perm.columns[1]] = outsource_perm[
outsource_perm.columns[1]].fillna(0)
outsource_perm.set_index(keys=[1], inplace=True)
outsource_perm = outsource_perm.transpose()
# OUTSOURCE AVAILABILITY
# Creation of h(times), k(binary), d(order), f(amount), s(shift) and c(cost) tables
df = df.groupby(["product_no", "station"],
as_index=False).agg({"cycle_times": sum})
h = df.pivot("product_no", "station", "cycle_times").fillna(0)
k = df.copy()
k.at[k.cycle_times > 0, "cycle_times"] = 1
k = k.pivot("product_no", "station", "cycle_times").fillna(0)
h = h[machine_legend.iloc[:, 0].to_list()]
k = k[machine_legend.iloc[:, 0].to_list()]
# CHANGE THIS TO ANY OR IN .TO_LIST MEHOD
f = []
for machine in machine_info_df["station"]:
if bool(sum(self.math_model_output.station.str.contains(
machine))) and bool(
sum(machine_info_df.station.str.contains(machine))):
f.append(machine_info_df.loc[machine_info_df.station ==
machine, "quantity"].values[0] +
self.math_model_output.loc[
self.math_model_output.station == machine,
"investments"].values[0])
elif bool(
sum(self.math_model_output.station.str.contains(machine))
== 0) and bool(
sum(machine_info_df.station.str.contains(machine))):
f.append(
machine_info_df.loc[machine_info_df.station == machine,
"quantity"].values[0])
else:
f.append(1)
# if machine in self.math_model_output.
f = pd.DataFrame(data=f, columns=["quantity"]).transpose().copy()
s = []
for machine in machine_info_df["station"]:
if bool(sum(self.math_model_output.station.str.contains(machine))):
s.append(self.math_model_output.loc[
self.math_model_output.station == machine,
"shift"].values[0])
elif bool(sum(machine_info_df.station.str.contains(machine))):
s.append(
machine_info_df.loc[machine_info_df.station == machine,
"shift"].values[0])
else:
s.append(1)
s = pd.DataFrame(data=s, columns=["shift"]).transpose().copy()
c = DataFrame(index=[1, 2, 3], columns=[1], data=[0, 23,
151]).transpose()
if pivot:
d = self.pivot_days()
else:
d = self.plan.copy()
for curr_table in [d, h, k, o]:
curr_table.index = list(range(1, curr_table.shape[0] + 1))
for curr_table in [h, k, st, outsource_perm, f, s]:
curr_table.columns = list(range(1, curr_table.shape[1] + 1))
return product_no_legend, machine_legend, d, h, k, f, s, c, o, st, outsource_perm
def create_tables(self):
self.cross_trim()
product_family_legend = pd.DataFrame(list(
self.merged_file.product_no.str.split(".").apply(
lambda x: x[0]).unique()),
columns=["product_family"])
prod_path = self.merged_file.groupby(by="station", as_index=False).agg(
{"level": "mean"})
prod_path.sort_values(by="level", ascending=False, inplace=True)
machine_legend = pd.DataFrame(prod_path.station.to_list(),
columns=["station"])
bom_data = self.merged_file.copy()
bom_data.amount = bom_data.amount.astype(int)
bom_data.cycle_times = bom_data.cycle_times.astype(float)
bom_data.cycle_times = bom_data.amount * bom_data.cycle_times.astype(
float)
bom_data.drop(joining_indices(bom_data), inplace=True)
bom_data.drop(bom_data[
bom_data.product_no.eq(bom_data.product_no.shift(1, fill_value=0))
& bom_data.level.eq(1)].index,
inplace=True)
bom_data = bom_data.groupby(by=["product_no", "station"],
as_index=False).agg({"cycle_times": "sum"})
bom_data["product_family"] = bom_data.product_no.str.split(".").apply(
lambda x: x[0])
prod_cnt = {
x: bom_data[bom_data.product_family == x].product_no.nunique()
for x in list(bom_data.product_family.unique())
}
bom_data = bom_data.groupby(by=["product_family", "station"],
as_index=False).agg({
"cycle_times": "mean",
"product_no": "count"
})
bom_data.columns = [
"product_family", "station", "cycle_times", "product_count"
]
bom_data[
"probability"] = bom_data.product_count / bom_data.product_family.replace(
prod_cnt)
machine_info_df = pd.merge(
left=machine_legend,
right=self.machine_info,
how="left",
left_on="station",
right_on="machine").fillna(1).iloc[:, [0, 2, 3]]
d = self.forecast.copy()
h = bom_data.pivot("product_family", "station",
"cycle_times").fillna(0)
h = h[machine_info_df[machine_info_df.columns[0]]].copy()
k = bom_data.pivot("product_family", "station",
"probability").fillna(0)
k = k[machine_info_df[machine_info_df.columns[0]]].copy()
f = machine_info_df["quantity"].to_frame().transpose().copy()
# Workdays will be implemented
w = pd.DataFrame([25, 24, 25, 25, 25, 19, 26, 23, 25, 25, 26, 23],
index=list(range(1, 13))).transpose()
# Budget part will be implemented
# b = pd.DataFrame([100000]*12, index = list(range(1, 13))).transpose()
# Costs will be implemented
c = pd.DataFrame([20, 23, 30], index=[1, 2, 3]).transpose()
# Machine costs will be implemented
cr = pd.DataFrame([100000] * f.shape[1],
index=list(range(1, f.shape[1] + 1))).transpose()
for curr_df in [product_family_legend, machine_legend, d, h, k, f]:
curr_df.index = list(range(1, curr_df.shape[0] + 1))
curr_df.columns = list(range(1, curr_df.shape[1] + 1))
self.product_family_legend, self.machine_legend, self.forecast, self.times, self.route_prob, self.machine_cnt, self.workdays, self.cost, self.machine_price = product_family_legend, machine_legend, d, h, k, f, w, c, cr