재고수불부 작성
일반적으로 IT 팀이 있는 대기업에서는 제품 재고수불부를 시스템에서 자동 생성을 해 주는 프로그램을
가지고 있겠으나 중소기업에서는 저가의 ERP 시스템을 구매하여 사용하다 보니 재고수불부를 수작업으로
생성을 하게 된다.
이때 재고수불부 생성을 위한 소스 파일들을 각 담당자들이 엑셀로 수작업을 통해 생성을 하게 되는데
이를 바탕으로 재고수불부를 역시 수작업으로 생성하게 된다.
데이타가 많이 않으면 엑셀에서 피벗테이블을 통해 만들 수도 있겠으나 파이썬 공부를 위해 이를 파이썬으로
만들어 보았다.
주피터 랩 설치
파이썬에는 많은 모듈들이 존재한다.이중 Data Frame 기능을 제공하는 Pandas 와 엑셀 기능을 제공하는 Openpyxl 모듈을 사용 해 보겠다.
import pandas as pd
from pandas import DataFrame
import sys
import numpy as np
from openpyxl import load_workbook
---------------------------------------------------------------------------
ModuleNotFoundError Traceback (most recent call last)
<ipython-input-1-abb412c68070> in <module>
----> 1 import pandas as pd
2 from pandas import DataFrame
3 import sys
4 import numpy as np
5 from openpyxl import load_workbook
ModuleNotFoundError: No module named 'pandas'
모듈을 import 하였더니 모듈이 없다는 메세지가 뜬다.
이럴때에는 해당 모듈을 설치 해 준다.
pip install pandas
Collecting pandas
Downloading pandas-1.2.4-cp39-cp39-macosx_10_9_x86_64.whl (10.7 MB)
|████████████████████████████████| 10.7 MB 5.0 MB/s eta 0:00:01
Requirement already satisfied: python-dateutil>=2.7.3 in /usr/local/Cellar/
jupyterlab/3.0.14/libexec/lib/python3.9/site-packages (from pandas) (2.8.1)
Collecting numpy>=1.16.5
Downloading numpy-1.20.2-cp39-cp39-macosx_10_9_x86_64.whl (16.1 MB)
|████████████████████████████████| 16.1 MB 10.4 MB/s eta 0:00:01
Requirement already satisfied: pytz>=2017.3 in /usr/local/Cellar/jupyterlab/
3.0.14/libexec/lib/python3.9/site-packages (from pandas) (2021.1)
Requirement already satisfied: six>=1.5 in /usr/local/Cellar/jupyterlab/3.0.14/
libexec/lib/python3.9/site-packages (from python-dateutil>=2.7.3->pandas) (1.15.0)
Installing collected packages: numpy, pandas
Successfully installed numpy-1.20.2 pandas-1.2.4
WARNING: You are using pip version 21.0.1; however, version 21.1.1 is available.
You should consider upgrading via the '/usr/local/Cellar/jupyterlab/3.0.14/libexec/
bin/python3.9 -m pip install --upgrade pip' command.
Note: you may need to restart the kernel to use updated packages.
pip install numpy
Requirement already satisfied: numpy in /usr/local/Cellar/jupyterlab/
3.0.14/libexec/lib/python3.9/site-packages (1.20.2)
WARNING: You are using pip version 21.0.1; however, version 21.1.1 is available.
You should consider upgrading via the '/usr/local/Cellar/jupyterlab/
3.0.14/libexec/bin/python3.9 -m pip install --upgrade pip' command.
Note: you may need to restart the kernel to use updated packages.
pip install openpyxl
Collecting openpyxl
Using cached openpyxl-3.0.7-py2.py3-none-any.whl (243 kB)
Collecting et-xmlfile
Downloading et_xmlfile-1.1.0-py3-none-any.whl (4.7 kB)
Installing collected packages: et-xmlfile, openpyxl
Successfully installed et-xmlfile-1.1.0 openpyxl-3.0.7
WARNING: You are using pip version 21.0.1; however, version 21.1.1 is available.
You should consider upgrading via the '/usr/local/Cellar/jupyterlab/
3.0.14/libexec/bin/python3.9 -m pip install --upgrade pip' command.
Note: you may need to restart the kernel to use updated packages.
이제 설치를 다 했으니 각종 모듈들을 import 해 준다.
import pandas as pd
from pandas import DataFrame
import sys
import numpy as np
from openpyxl import load_workbook
아무 에러 없이 import 가 되었다.
나의 경우에는 test 폴더를 생성해서 소스 파일들을 갖다 놓고 이 프로그램을 테스티 해 보았다.
이를 위해 프로그램에서 반복적으로 사용하는 폴더 경로인 test 폴더를 변수로 설정하였다.
var_path = '/Users/wizzen2801/Python_WZN/Test/'
아래와 같은 컬럼명이 있는 Data Frame 을 생성 후 엑셀로 저장한다.
df = DataFrame(columns = ['Product_Code', 'Product_Name', 'BOH_Qty', 'BOH_Amt', 'IN_Qty', 'IN_Amt', 'OUT_Qty', 'OUT_Amt', 'EOH_Qty', 'EOH_Amt', 'Adjust_Qty', 'Adjust_Amt'])
df.to_excel(var_path+'INV_MOV.xlsx', index=False)
제대로 생성이 되었는지 살펴본다.
df
아래 소스 파일들을 모두 합쳐서 INV_MOV_Total.xlsx 이름으로 저장한다.
excel_names = [var_path+'INV_MOV.xlsx', var_path+'BOH.xlsx', var_path+'IN.xlsx', var_path+'OUT.xlsx', var_path+'EOH.xlsx']
excels = [pd.ExcelFile(name) for name in excel_names]
frames = [x.parse(x.sheet_names[0], header=None,index_col=None) for x in excels]
frames[1:] = [df[1:] for df in frames[1:]]
combined = pd.concat(frames)
combined.to_excel(var_path+'INV_MOV_Total.xlsx', header=False, index=False)
생성된 파일을 Pivot 테이블로 변환한다.
df = pd.read_excel(var_path+'INV_MOV_Total.xlsx')
df = df.pivot_table(index = ['Product_Code'], aggfunc = {'BOH_Qty':sum, 'BOH_Amt':sum, 'IN_Qty':sum, 'IN_Amt':sum, 'OUT_Qty':sum, 'OUT_Amt':sum, 'EOH_Qty':sum, 'EOH_Amt':sum})
제품명을 코드 마스터 테이블에서 가져와 제품코드별 매칭하여 Product_name 컬럼에 넣어준다.
df2 = pd.read_excel(var_path+'Code_Master.xlsx')
df = df.join(df2.set_index('Product_Code')['Product_Name'], on='Product_Code')
피벗테이블로 변환시 칼럼 위치가 변경되는데 이것의 순서를 다시 바로잡아 엑셀로 저장한다.
df = df.reindex(columns=['Product_Name', 'BOH_Qty','BOH_Amt','IN_Qty','IN_Amt', 'OUT_Qty', 'OUT_Amt', 'EOH_Qty', 'EOH_Amt', 'Adjust_Qty', 'Adjust_Amt'])
df.to_excel(var_path+'INV_MOV_Pivot.xlsx')
df
위와 같이 원하는 결과 값을 얻어서 엑셀로 잘 저장을 해 놓았다.
재고수불부를 작성하다 보면 기초 + 입고 = 출고 + 재고 가 일치하여야 하나
여러가지 이유로 그렇지 못할 경우가 발생하는데 이의 차이를 계산해서 adjust 칼럼에 그 값을 넣는다.
wb = load_workbook(var_path+'INV_MOV_Pivot.xlsx')
ws = wb.active
for r in ws.rows:
row_index = r[0].row
ws.cell(row=row_index, column=11).value = "=C"+str(row_index)+"+E"+str(row_index)+"-G"+str(row_index)+"-I"+str(row_index)+''
ws['K1'] ='Adjust_Qty'
ws.cell(row=row_index, column=12).value = "=D"+str(row_index)+"+F"+str(row_index)+"-H"+str(row_index)+"-J"+str(row_index)+''
ws['L1'] ='Adjust_Amt'
wb.save(var_path+'INV_MOV_Final.xlsx')
이제 아래와 같이 완성된 프로그램을 만들었다.
import pandas as pd
from pandas import DataFrame
import sys
import numpy as np
from openpyxl import load_workbook
var_path = '/Users/wizzen2801/Python_WZN/Test/'
df = DataFrame(columns = ['Product_Code', 'Product_Name', 'BOH_Qty', 'BOH_Amt', 'IN_Qty', 'IN_Amt', 'OUT_Qty', 'OUT_Amt', 'EOH_Qty', 'EOH_Amt', 'Adjust_Qty', 'Adjust_Amt'])
df.to_excel(var_path+'INV_MOV.xlsx', index=False)
excel_names = [var_path+'INV_MOV.xlsx', var_path+'BOH.xlsx', var_path+'IN.xlsx', var_path+'OUT.xlsx', var_path+'EOH.xlsx']
excels = [pd.ExcelFile(name) for name in excel_names]
frames = [x.parse(x.sheet_names[0], header=None,index_col=None) for x in excels]
frames[1:] = [df[1:] for df in frames[1:]]
combined = pd.concat(frames)
combined.to_excel(var_path+'INV_MOV_Total.xlsx', header=False, index=False)
df = pd.read_excel(var_path+'INV_MOV_Total.xlsx')
df = df.pivot_table(index = ['Product_Code'], aggfunc = {'BOH_Qty':sum, 'BOH_Amt':sum, 'IN_Qty':sum, 'IN_Amt':sum, 'OUT_Qty':sum, 'OUT_Amt':sum, 'EOH_Qty':sum, 'EOH_Amt':sum})
df2 = pd.read_excel(var_path+'Code_Master.xlsx')
df = df.join(df2.set_index('Product_Code')['Product_Name'], on='Product_Code')
df = df.reindex(columns=['Product_Name', 'BOH_Qty','BOH_Amt','IN_Qty','IN_Amt', 'OUT_Qty', 'OUT_Amt', 'EOH_Qty', 'EOH_Amt', 'Adjust_Qty', 'Adjust_Amt'])
df.to_excel(var_path+'INV_MOV_Pivot.xlsx')
wb = load_workbook(var_path+'INV_MOV_Pivot.xlsx')
ws = wb.active
for r in ws.rows:
row_index = r[0].row
ws.cell(row=row_index, column=11).value = "=C"+str(row_index)+"+E"+str(row_index)+"-G"+str(row_index)+"-I"+str(row_index)+''
ws['K1'] ='Adjust_Qty'
ws.cell(row=row_index, column=12).value = "=D"+str(row_index)+"+F"+str(row_index)+"-H"+str(row_index)+"-J"+str(row_index)+''
ws['L1'] ='Adjust_Amt'
wb.save(var_path+'INV_MOV_Final.xlsx')
