머신러닝

출고일정 변경 관리 (로지스틱)

정문가 2020. 12. 23. 14:19
PH_LPN DATA와 출고일정 파일 통합

PO(BIZFLEX),차수 별 신규 ITEM#와 LPN# 추가 기입

In [4]:
import pandas as pd
import numpy as np
import xlrd
import os

from xlrd import open_workbook

wb1=open_workbook("C:\\Users\\sjkim6\\Desktop\\CROSSWALK-TEST.xlsx", logfile=open(os.devnull, 'w'))
cross=pd.read_excel(wb1, dtype=str, engine='xlrd')

PO = ['91125ES','91125WS','91209ES','91209WS','91216WS','91224ES','91224WS','200106E','200106W','200114E','200114W','200120E','200120W',
      '200127E','200127W','200203E','200203W','200217E','200217W','200320E','200320W','200330E','200330W','200420E','200420W',
      '200504W','200522E','200522W','200529E','200529W','200601E','200601W','200602E','200602W','200617E','200617W','200623E','200623W',
      '200624E','200624W','20211ES','20211WS','200323E','200323W','200712E','200712W']

for i in PO:
    
    wb2=open_workbook("C:\\Users\\sjkim6\\Desktop\\Bizflex_LPN\\{}.xls".format(i), logfile=open(os.devnull, 'w'))
    biz=pd.read_excel(wb2, header=1, engine='xlrd')
    biz=biz[biz['작시번호'].notna()] #오더 캔슬된 작시 제거, 선적완료된 차수 여전히 포함(제거필요)
                      
    wb3=open_workbook("C:\\Users\\sjkim6\\Desktop\\BOXSTICKER\\originaldata\\{}.xlsx".format(i), logfile=open(os.devnull, 'w'))
    data=pd.read_excel(wb3, dtype=str, engine='xlrd') 
    data['ITEM#'] = pd.Series(data[['STYLE','COL','SIZE']].fillna('').values.tolist()).str.join('.')
    data['ITEM#']=data['ITEM#'].apply(str)
    data['FULL LPN#']=data['FULL LPN#'].apply(str) #바이어 제공 lpn 데이터. 바이어 제공 안한 LPN# 추가 점검 필요
    
    cross=cross[['SKU#','ITEM#']]
    data_cross=pd.merge(data, cross, on=['ITEM#'], how='outer') #lpn 데이터에 기존 sku#추가
    
    biz=biz[['작시번호','BUYER S/#','TEAM COLOR NAME','BUYER SIZE','SHIP MODE','목적지 명','SKU#']]
    biz_data_cross=pd.merge(biz, data_cross, on=['SKU#']) #투입된 오더와 데이터 연결. 선적된건 여전히 포함(제거필요)
    
    biz_data_cross.rename(columns = {'작시번호':'INST#','원납기':'ExFTY'},inplace = True)
    biz_data_cross['LPN1']=biz_data_cross['LPN#'].str[0:5]
    biz_data_cross['LPN2']=biz_data_cross['LPN#'].str[5:8]
    biz_data_cross['LPN3']=biz_data_cross['LPN#'].str[8:11]
    biz_data_cross['LPN4']=biz_data_cross['LPN#'].str[11]

    sample=biz_data_cross[['INST#','TEAM COLOR NAME','BUYER SIZE','STYLE','COL','COLDSP','SIZE',
                           'PO NUM','DESCRIP','W/H','LPN1','LPN2','LPN3','LPN4','LPN#']]
    
    sample.to_excel('LPN_{}(INST).xlsx'.format(i)) #PO별 신규 item#와 lpn# 매칭 완료. 선적된차수 여전히 포함(제거필요).바이어 제공안한 LPN# 점검필요

PO별 개별파일을 1개 파일로 통합

In [5]:
import pandas as pd
import numpy as np
import xlrd
import os

from xlrd import open_workbook

PO = ['91125ES','91125WS','91209ES','91209WS','91216WS','91224ES','91224WS','200106E','200106W','200114E','200114W','200120E','200120W',
      '200127E','200127W','200203E','200203W','200217E','200217W','200320E','200320W','200330E','200330W','200420E','200420W',
      '200504W','200522E','200522W','200529E','200529W','200601E','200601W','200602E','200602W','200617E','200617W','200623E','200623W',
      '200624E','200624W','20211ES','20211WS','200323E','200323W','200712E','200712W']

df=pd.DataFrame(dtype='string')
    
for i in PO:
    file=open_workbook("C:\\Users\\sjkim6\\desktop\\lpndata차수별0828\\LPN_{}(INST).xlsx".format(i), logfile=open(os.devnull, 'w'))
    file1=pd.read_excel(file, engine='xlrd',dtype={'FULL LPN#':str})
    
    df=df.append(file1, ignore_index=True)
  
    
df.to_excel('LPN_total_0831.xlsx') #모든 PO를 1개 파일로 통합 완료.

출고일정과 기존 데이터(차수,신규ITEM#,LPN#) 통합

In [3]:
import pandas as pd
import numpy as np
import xlrd
import os
import xlsxwriter

from xlrd import open_workbook

OR_data=['08212020-3']
VA_data=['08252020']

data=open_workbook("C:\\Users\\sjkim6\\LPN_total(CHECKING)0831.xlsx", logfile=open(os.devnull, 'w'))
data1=pd.read_excel(data, header=0, engine='xlrd',dtype={'FULL LPN#':str,'LPN#':str,'INST#':str})
#data1=data1[data1['S.DEL'].notna()] #선적완료 또는 캔료된 차수 제거 (출고 예정 없는 차수).바이어 제공안한 LPN# 점검필요
data1['INST#']='0'+data1['INST#']
data1['LPN#'].fillna('need LPN#',inplace=True) #바이어제공안한LPN#점검필요
#data1=data1.drop(['QTY/INST'],axis=1)

for i in OR_data:
    oregon=open_workbook("C:\\Users\\sjkim6\\desktop\\Oregon_shipping_schedule_{}.xlsx".format(i), logfile=open(os.devnull, 'w'))
    oregon1=pd.read_excel(oregon, header=0, engine='xlrd')
    oregon1['INST#']=oregon1['INST#'].apply(str)
    oregon1['INST#']='0'+oregon1['INST#']
    oregon1=pd.melt(oregon1, id_vars=['INST#','차수별최초출고일','선적지','PO No','Item Number','Team Color','Buyer Size','QTY(pcs)',
                                 '기존납기','공장-메모','구분'], var_name='바이어요청납기',value_name='QTY/바이어요청납기별')
    oregon1=oregon1[oregon1['QTY/바이어요청납기별'].notna()]
           

for i in VA_data:
    virginia=open_workbook("C:\\Users\\sjkim6\\desktop\\Virginia_shipping_schedule_{}.xlsx".format(i), logfile=open(os.devnull, 'w'))
    virginia1=pd.read_excel(virginia, header=0, engine='xlrd')
    virginia1['INST#']=virginia1['INST#'].apply(str)
    virginia1['INST#']='0'+virginia1['INST#']
    virginia1=pd.melt(virginia1, id_vars=['INST#','차수별최초출고일','선적지','PO No','Item Number','Team Color','Buyer Size','QTY(pcs)',
                                 '기존납기','공장-메모','구분'], var_name='바이어요청납기',value_name='QTY/바이어요청납기별')
    virginia1=virginia1[virginia1['QTY/바이어요청납기별'].notna()]
    
                
shipping=pd.concat([oregon1,virginia1], join='outer',ignore_index=True)
shipping.rename(columns={'Team Color':'TEAM COLOR NAME', 'Buyer Size':'BUYER SIZE'},inplace=True)

sample=pd.merge(shipping, data1, on=['INST#','BUYER SIZE','TEAM COLOR NAME'], how='outer', indicator='indicator_info',suffixes=('_shipping','_lpn'))
sample=sample.drop(['차수별최초출고일','기존납기','BUYER SIZE','PO NUM','FULL LPN#','공장-메모'], axis=1) #'EXFTY_lpn, 'INST+TEAM'
#sample.rename(columns={'QTY(pcs)': '출고잔량', 'S.DEL':'작시별최초출고일(작시납기)'},inplace=True) #

mask= sample['indicator_info'].isin(['right_only'])
sample=sample[~mask]
#sample.sort_values(by='작시별최초출고일(작시납기)',axis=0)
sample.to_excel('납기_LPN_통합.xlsx',index=False, engine='xlsxwriter') #출고 일정 파일, LPN#, 신규 ITEM# 통합 완료.

바이어가 제공한 LPN 바코드가 누락된 차수 (바이어 요청용)

In [4]:
sample2= sample[sample['LPN#']=='need LPN#']
sample3=sample[sample['LPN#'].isnull()]
sample4=pd.concat([sample2, sample3], join='outer',ignore_index=True)
sample4.to_excel('Need LPN.xlsx',index=False, engine='xlsxwriter') # 바이어 제공 안한 데이터 요청용 파일