In [1]:
import pandas as pd
In [2]:
pp = pd.read_excel("data/week plan.xlsx",sheet_name="Sheet1")
# SO, FG Code, SO Date, SO Delivery Date, Planned Dispatch Date, Order Qty, Balance Qty, Production Week, Line1, Output,
# Shift, Per Hour Output
In [84]:
day = pd.date_range("2019-01-01 07:00:00",periods=350, freq='8H')
day = day.drop(pd.Timestamp('2019-01-06 07:00:00'))
day = day.drop(pd.Timestamp('2019-01-06 15:00:00'))
day = day.drop(pd.Timestamp('2019-01-06 23:00:00'))
day = day.drop(pd.Timestamp('2019-01-13 07:00:00'))
day = day.drop(pd.Timestamp('2019-01-13 15:00:00'))
day = day.drop(pd.Timestamp('2019-01-13 23:00:00'))
day = day.drop(pd.Timestamp('2019-01-20 07:00:00'))
day = day.drop(pd.Timestamp('2019-01-20 15:00:00'))
day = day.drop(pd.Timestamp('2019-01-20 23:00:00'))
day = day.drop(pd.Timestamp('2019-01-26 07:00:00'))
day = day.drop(pd.Timestamp('2019-01-26 15:00:00'))
day = day.drop(pd.Timestamp('2019-01-26 23:00:00'))
day = day.drop(pd.Timestamp('2019-01-27 07:00:00'))
day = day.drop(pd.Timestamp('2019-01-27 15:00:00'))
day = day.drop(pd.Timestamp('2019-01-27 23:00:00'))
In [4]:
line = pp['Line2'].unique()
line1 = pp['Line1'].unique()
tpt = pp.loc[pp.Line2=="Toothpaste Line"]
tpt.reset_index(inplace=True)
tpt.drop('index',axis=1)
Out[4]:
In [85]:
dp = pd.DataFrame(columns=('Date','Line','Qty','SO','FG Code'))
for ll in line1:
    dff = tpt.loc[tpt.Line1==ll]
    dd = 0                      # Track Date
    hh = 8                      # Track Shift
    for i in dff.index:
        bb = tpt.loc[i,'Balance']
        if ff != tpt.loc[i,'FG Code']:
            hh = hh - 4
            if hh <= 0:
                dd = dd + 1
                hh = 8 + hh
        while bb > 0:
            if bb - tpt.loc[i,'Output'] >=0:
                if hh == 8:
                    d = {'Date': [day[dd]], 'Line': [ll], 'Qty':[tpt.loc[i,'Output']],'SO':[tpt.loc[i,'SO']],'FG Code':[tpt.loc[i,'FG Code']]}
                    d = pd.DataFrame(data=d)
                    dp= pd.concat([dp,d])
                    bb = bb - tpt.loc[i,'Output']
                    dd = dd + 1
                    ff = tpt.loc[i,'FG Code']
                else:
                    bal = tpt.loc[i,'Output']*(hh)/8
                    d = {'Date': [day[dd]], 'Line': [ll], 'Qty':[bal],'SO':[tpt.loc[i,'SO']],'FG Code':[tpt.loc[i,'FG Code']]}
                    d = pd.DataFrame(data=d)
                    dp = pd.concat([dp,d])
                    bb = bb - bal
                    dd = dd + 1
                    hh = 8
                    ff = tpt.loc[i,'FG Code']
            if bb - tpt.loc[i,'Output'] < 0:
                if bb - tpt.loc[i,'Output']*hh/8 < 0:
                    d = {'Date': [day[dd]], 'Line': [ll], 'Qty':[bb],'SO':[tpt.loc[i,'SO']],'FG Code':[tpt.loc[i,'FG Code']]}
                    d = pd.DataFrame(data=d)
                    dp = pd.concat([dp,d])
                    hh = hh - bb*8/tpt.loc[i,'Output']
                    bb = 0
                    ff = tpt.loc[i,'FG Code']
                    if hh <= 0:
                        dd = dd + 1
                        hh = 8 - hh
                elif bb - tpt.loc[i,'Output']*hh/8 >= 0:
                    bal = tpt.loc[i,'Output']*(hh)/8
                    d = {'Date': [day[dd]], 'Line': [ll], 'Qty':[bal],'SO':[tpt.loc[i,'SO']],'FG Code':[tpt.loc[i,'FG Code']]}
                    d = pd.DataFrame(data=d)
                    dp = pd.concat([dp,d])
                    bb = bb - bal
                    dd = dd + 1
                    hh = 8
                    ff = tpt.loc[i,'FG Code']
dp['Qty'].fillna(0,inplace=True)
In [87]:
dp.to_excel('data/day plan.xlsx')