Last year, I changed my job from a production engineering job to a data analysis job, and I have more opportunities to write Python. I'm currently working on a project to develop a pre-processing tool for machine learning. Meanwhile, looking back on my time as a production engineer, I thought that there were various tasks that could be automated </ b>. Based on the knowledge of Python gained after changing jobs and the knowledge of the days of production engineers, I will describe the material that can be used when working at the manufacturing site again. I hope it will be helpful for those who are facing similar needs. This article looks good if you combine existing technologies! First of all, I would like to point out that there is nothing new technically.
When I was working as a production engineer, I had the task of collecting the data from the process at the end of the day </ b>. Save the manufacturing equipment logs and inspection results as a csv file for each product, and aggregate them at the end of the day to check the progress rate and defect rate of the day. In the process in charge, as shown in the figure below, the specifications were such that four csv were output each time one product was manufactured.
◎ Setting value log of manufacturing equipment ◎ Status log of manufacturing equipment ◎ Product shape data ◎ Judgment result calculated from shape data
The part to judge the shape was made in Python, but this is what I instructed the judgment method and asked the programmer of the information system department to create it. The above four csvs were aggregated into one Excel file every day. In other words, 4 x the number of manufactured files were put together. If we start putting together from the end of production on that day, it will be completed at night, so we asked the manufacturers to gather in between production. Of course, this work doesn't need to be done by anyone at all. </ b> I was asked to do it in the gap time, but I regret that I shouldn't have taken the time because it is not work that adds value to the product. I should have had that time devoted to ideas for reducing defects (= work that only people can do). </ b> Now, I'll show you how to automate this task with your current knowledge.
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/318960/2a05f2d0-00a0-59ea-f834-ea13c344610e.png "width=90%>
There are a few things to keep in mind in order to create automation tools in Python and use them in the manufacturing floor. As a premise, it is assumed that the production engineer (= me) creates the tool and the manufacturer (= the person who actually makes the product) uses it.
It's best to assume that few people, including production engineers, have programming knowledge at the manufacturing floor. Pass the tool in a form that can be executed with just a click. This is also better from the point of view of not having to take the trouble of the manufacturer. Also, if the behavior of the tool depends on the Python package, it is unreasonable to ask "Please put 0.25.1 for pandas and 3.0.0 for openpyxl ...". It must be passed in a form that can be executed without building an environment.
For example, the data storage destination may be changed due to the circumstances of the site. In that case, if you write the file path in the Python script, you will have to rewrite the Python script every time there is a change. If there is no person who can read and write Python at the site, it will not be possible to deal with it (= it will be called to the site each time).
Depending on the rules of the company or factory, the network at the manufacturing site is often closed within the line or within the factory and not connected to the outside. Since the manufacturing conditions and the acquired data are the manufacturer's first-class confidential information, I think that in most cases the network is physically cut off from the outside so that it will not be leaked to the outside. Even in the case I'm doing now, I go to the customer's factory to see the data.
If Office is installed on the PC at the manufacturing site, it is required that Office can be used (= does not freeze) without problems even while the tool is running. This is because the manufacturer may write a daily report in Excel using the same PC or check Excel in which past data is aggregated. If you can't use Office during the process, your reputation will drop sharply (laughs).
Since the production engineer (non-programmer) creates the automation tool by himself, the use of OSS is a prerequisite. Also, when delivering a product to a customer, it may be required to create it only with OSS because the customer's manufacturing and information system department wants to maintain it as much as possible after handing over the tool.
I wrote something that includes everything (laughs), but since the requests differ depending on each site, let's listen carefully before starting the creation. For example, in the example of aggregating the above csv files,
● Processing for each product (that is, the consolidation is finished at the same time as the production of the day is finished) ● Batch processing after production is finished
There are two possible patterns. We would like to hear these detailed specifications in advance to maximize usability. This time, we will explain by taking "processing for each product" as an example.
(Note) If the judgment result is NG continuously, it is necessary to stop the line urgently as a process abnormality, so the judgment itself is basically performed for each product.
The technologies that can be used for each of the above items are listed.
| Points to remember | counter-measure | Library to use |
|---|---|---|
| Make it executable by people with zero programming knowledge | ・ Make Python script exe ・ Tools are provided as batch files |
PyInstaller |
| Do not write items that are likely to change on a daily basis in Python scripts | Items that are likely to change are command line arguments (sys).Use argv) or format to read from the configuration file | sys |
| You should think that you can't install the package using pip on your PC in the field | Make a Python script an exe | PyInstaller |
| Make Microsoft Office usable without problems during operation | (Example) Measures for stable use of Excel during processing If you want to work with Excel for a long time (such as writing a lot of text), it is better to use VBScript instead of Python. |
openpyxl (Places where processing time is short) |
| Make sure that processing is finished when production is finished (on request) | Process by product | watchdog |
Let me explain in a little more detail. ◎ Correspondence to "make it a form that can be executed by people with zero programming knowledge" </ b> This is possible by converting the created Python script into an exe using a library called PyInstaller. By converting to an exe, it is not necessary to build an environment at the manufacturing site (an image in which the environment is included in the exe). Furthermore, by providing the exe as a batch file, the user can use the tool simply by double-clicking the batch file.
The site that I used as a reference ● Create an exe file with PyInstaller ● Create batch file
◎ Correspondence to "Do not write items that are likely to change on a daily basis in Python scripts" </ b> For items that are likely to change, use command line arguments (described in the batch file) or create a separate configuration file and read it from there. By doing so, even if there is a part you want to change in the field, you can just rewrite the batch file with a text editor, so even people who do not have knowledge of Python can handle it.
The site that I used as a reference How to handle command line arguments in Python
◎ Countermeasures for "It is better to think that you cannot install the package using pip on the PC in the field" </ b> Same as the first measure above
◎ Support for "make Microsoft Office usable without problems during operation" </ b> When working with Excel files in Python, I think the standard is to use a library called openpyxl. However, in the past, when I was working on aggregating multiple sheets using openpyxl, I was unable to operate another Excel file that had nothing to do with it (I haven't figured out the cause). So, this time I decided to use VBScript instead of Python for the part where csv is aggregated. Not limited to Excel, it is better to confirm that Microsoft Office can be used without problems while the tool is running.
◎ Measures for "making sure that processing is completed when production is finished" </ b> We will monitor the folder where the shape data is stored with a library called watchdog and start processing when the data is stored. By doing so, the user only needs to start the tool (= double-click the batch file) before the start of production and stop the batch file (= Ctrl + C) when the production of the day ends.
The site that I used as a reference Command execution triggered by file update (python version)
In addition to the above sites I referred to "Let Python do the boring things, automated processing programming that non-programmers can do".
Below, based on the above points, the code that automatically aggregates multiple csv files into one Excel file is described. Please think of it as a memo after that. ..
I thought about the processing as shown in the figure below. Think of the product as flowing from left to right and the process going from top to bottom.
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/318960/4e656889-2ff8-a2ff-5b64-fbb43c845b76.png "width=90%>
There are 5 files and 3 scripts.
File: ◎ Setting value log (csv) output from the manufacturing equipment ◎ Status log (csv) output from the manufacturing equipment ◎ Shape data (csv) output from the shape measuring machine ◎ Judgment result (csv) calculated based on shape data ◎ Excel that aggregates 4 csv
Each of these files is saved in a separate folder.
script: ◎integration.py 【role】 Control the flow of processing 【motion】 -Monitor the folder where the shape data is saved and judge the shape when a file is generated. (= Execute ShapeJudgment.py) -When the judgment result is saved, execute excel_merge.vbs that aggregates csv. ・ After aggregation, move csv to "Aggregated folder"
◎ShapeJudgment.py 【role】 OK / NG judgment based on shape data 【motion】 Read the shape data and save the judgment result (Note) Since the judgment result is directly related to the quality, the code created by the expert in the information system department is used.
◎excel_merge.vbs 【role】 Aggregate csv files into one Excel file 【motion】 Read the csv file and add it as an Excel file sheet The command line arguments of the batch file finally provided to the user are as follows. It's complicated, but ...
Especially confusing is where you are running ShapeJudgment.py in integration.py, passing sys.argv [1], sys.argv [4], sys.argv [5] as command line arguments. Within ShapeJudgment.py they correspond to sys.argv [0], sys.argv [1], sys.argv [2]. By using the command line arguments, you don't have to write a specific path in your Python script, and if you want to change the path you only have to edit the batch file. </ b>
| Contents | Position in the batch file | integration.Correspondence in py | ShapeJudgment.Correspondence in py |
|---|---|---|---|
| integration.exe path (integration.py converted to exe) |
0th argument | sys.argv[0] | - |
| ShapeJudgment.exe path (ShapeJudgment).py converted to exe) | 1st argument | sys.argv[1] | sys.argv[0] |
| In the folder where the "setting value log" is saved path |
2nd argument | sys.argv[2] | - |
| In the folder where the "device status log" is saved path |
3rd argument | sys.argv[3] | - |
| In the folder where "shape data" is saved path |
4th argument | sys.argv[4] | sys.argv[1] |
| The path of the folder where the "judgment result" is saved | 5th argument | sys.argv[5] | sys.argv[2] |
| Path of the folder where the "aggregated Excel file" is saved | 6th argument | sys.argv[6] | - |
| excel_merge.vbs path | 7th argument | sys.argv[7] | - |
The processing flow is as follows. 10 steps are performed for each product. We will proceed on the assumption that the time required for 10 steps is sufficiently short for the takt time (flowing interval) of the product. If the tact time is short, the next product will flow during processing, so I think you have to devise a processing method. </ b>
| order | Event | Destination (Batch file command line arguments) |
|---|---|---|
| 0 | integration.with py Start monitoring the folder where the shape data is saved |
|
| 1 | Products flow into the process | |
| 2 | The product is processed in the manufacturing equipment | |
| 3 | The setting value log is saved | sys.argv[2] |
| Device status log is saved | sys.argv[3] | |
| 4 | Measure the product with a shape measuring machine | |
| 5 | Shape data is saved | sys.argv[4] |
| 6 | integration.Shape Judgment from py.py is executed | |
| 7 | Judgment result is saved | sys.argv[5] |
| 8 | integration.From py excel_merge.vbs is executed |
|
| 9 | The aggregated Excel file Saved |
sys.argv[6] |
| 10 | integration.with py Move the aggregated csv to another folder |
In the parent folder of each command line argument |
| 11 | Back to 1 (The next product will flow) |
Here is the code that actually confirmed the movement.
Execution environment OS: Windows 10 Home version 1909 Python 3.7.6 watchdog 0.10.2 openpyxl 3.0.0
■ Script that controls the processing flow </ b>
integration.py
#Import required libraries
import datetime
import time
from watchdog.observers import Observer
from watchdog.events import FileSystemEventHandler
import subprocess
import sys
import os
import openpyxl
import shutil
import pathlib
# ShapeJudgment.py and excel_merge.Class that runs vbs
class Execute(FileSystemEventHandler):
def __init__(self, path):
self.path = path
def _run_command(self):
'''
ShapeJudgment on command line arguments.py path (sys.argv[1]）,
The path of the folder where "shape data" is saved (sys.argv[4]）,
The path of the folder where the "judgment result" is saved (sys.argv[5]）
Specify ShapeJudgment.Run the exe
'''
command = [sys.argv[1],sys.argv[4],sys.argv[5]]
subprocess.run(command, shell=True)
'''
Aggregated Excel (sys.argv[6]"Date +"_If there is no "aggregate" name)
Usually made when the first product of the day flows
'''
if not os.path.isfile(
os.path.join(sys.argv[6], datetime.date.today().strftime('%Y-%m-%d') + "_Aggregation.xlsx")):
wb = openpyxl.Workbook()
wb.save(os.path.join(sys.argv[6], datetime.date.today().strftime('%Y-%m-%d')) + "_Aggregation.xlsx")
'''
Each csv (sys.argv[2] ~ sys.argv[5]) To execute VBScript
Aggregated Excel is sys.argv[6]"Date +"_Saved as "aggregate"
'''
command = [r"wscript",
sys.argv[7],sys.argv[2],
sys.argv[3], sys.argv[4],
sys.argv[5],os.path.join(sys.argv[6],datetime.date.today().strftime('%Y-%m-%d'))+"_Aggregation.xlsx"]
subprocess.run(command, shell = True)
'''
Storage location of each csv sys.argv[2] ~ sys.argv[5]Create if the parent does not have an "aggregated" folder
And move the csv that has been aggregated to the "aggregated" folder
If there is already a file with the same name in the "Aggregated" folder, pass it
'''
for i in range(2, 6):
if not os.path.isdir(os.path.join(pathlib.Path(sys.argv[i]).parents[0], "Aggregated")):
os.mkdir(os.path.join(pathlib.Path(sys.argv[i]).parents[0], "Aggregated"))
try:
shutil.move(sys.argv[i] + "\\" + os.listdir(sys.argv[i])[0],
os.path.join(pathlib.Path(sys.argv[i]).parents[0], "Aggregated"))
except shutil.Error:
pass
def on_created(self,event):
self._run_command()
'''
Inspection device shape data(csv)Function to monitor the folder where
The method in the Execute class is executed when a new file is created
The monitoring target is the folder (sys) where "shape data" is saved..argv[4]）
Ctrl+Keep monitoring until C is pressed
'''
def watch_shape(path = sys.argv[4]):
while True:
event_handler = Execute(path)
observer = Observer()
observer.schedule(event_handler,path,recursive = True)
observer.start()
try:
while True:
time.sleep(1)
except KeyboardInterrupt:
observer.stop()
observer.join()
if __name__ == '__main__':
watch_shape()
■ Script to save OK / NG judgment based on shape data </ b>
ShapeJudgment.py
import pandas as pd
import numpy as np
import os
import sys
import glob
#Function to save the judgment result based on the shape data
def main():
'''
Read shape data (assuming that there is always only one csv file at the same time)
Pass if there is no file
sys.argv[1]Is the path of the folder where the shape data is saved
'''
try:
shape_df = pd.read_csv(os.path.join(sys.argv[1] , glob.glob("*.csv")[0]))
except IndexError:
pass
else:
'''
The judgment algorithm works ...
This is a part that is directly related to quality, so after specifying the judgment method
Have an expert in the information system department write
'''
'''
judgment result(judgment_df)Save
sys.argv[2]Is the path of the folder to save the judgment result
The file name is "Judgment result"_Shape data.csv」
'''
judgment_df.to_csv(os.path.join(sys.argv[2], "judgment result_"+glob.glob("*.csv")[0]))
if __name__ == '__main__':
main()
■ A script that aggregates four csv files into one Excel file </ b>
excel_merge.vbs
'--------------------------------------------------------------------------------------
'
'Combine the csv files output from the device into one Excel file for each sheet
'
'
'Attention Do not open the Excel file to be aggregated while executing the script.
'Unlock the Excel files to be aggregated before executing the script (not read-only).
'Do not put anything other than csv files in the output destination folder of manufacturing equipment and inspection equipment.
'If this script terminates abnormally, please terminate the Excel process manually.
'
'
'--------------------------------------------------------------------------------------
Option Explicit
Dim fileSystem
Dim targetFolder
Dim fileList
Dim Excel
Dim merge_Workbook
Dim objWorkbook
Dim objSheet
Dim wkFile
Dim i
'sys.argv[2] = WScript.Arguments(0) :Manufacturing equipment setting value log(csv)Folder path
'sys.argv[3] = WScript.Arguments(1) :Manufacturing equipment status log(csv)Folder path
'sys.argv[4] = WScript.Arguments(2) :Inspection device shape data(csv)Folder path
'sys.argv[5] = WScript.Arguments(3) :Judgment result of inspection equipment(csv)Folder path
'sys.argv[6] = WScript.Arguments(4) :Excel file path that aggregates the above csv
'Open an excel object
Set Excel = CreateObject("Excel.Application")
'Excel display settings (If this setting is set, Excel will not open or close during processing)
Excel.Visible = False
Excel.DisplayAlerts = False
Excel.ScreenUpdating = False
'Load workbooks to aggregate
Set merge_Workbook = Excel.Workbooks.Open(WScript.Arguments(4))
'Loop by file
For i = 0 To 3 'Loop by the number of csv files to be aggregated
'Get a list of files in each folder (assuming there is only one file, but for the time being)
'Create an object that handles the file system
Set fileSystem = CreateObject("Scripting.FileSystemObject")
Set targetFolder = fileSystem.getFolder(WScript.Arguments(i))
Set fileList = targetFolder.Files
For Each wkFile In fileList
Set objWorkbook = Excel.Workbooks.Open(WScript.Arguments(i) & "\" & wkFile.Name)
'Get sheet object(Assuming that there is only one sheet in the copy source file)
Set objSheet = objWorkbook.Sheets(1)
'Get sheet object(Assuming that there is only one sheet in the copy source file)
objSheet.Copy ,merge_Workbook.Sheets(merge_Workbook.Sheets.Count)
'The sheet name is the file name of the csv file (".csv"To delete)
'merge_Workbook.Sheets(merge_Workbook.Sheets.Count).Name = replace(wkFile.Name,".csv","")
'Close csv file
objWorkbook.Close
Set objWorkbook = Nothing
Next
Next
'Delete Sheet1 only the first time
If merge_Workbook.Sheets.Count < 6 Then
'Delete by specifying Sheet1
merge_Workbook.Sheets(1).Delete
End If
'Set the first sheet as the active sheet
merge_Workbook.Sheets(1).Activate
'Overwrite and save the workbook to be aggregated
merge_Workbook.Save
'Close the workbook to be aggregated
merge_Workbook.Close
Set merge_Workbook = Nothing
'Exit Excel
Excel.ScreenUpdating = True
Excel.Quit
Set Excel = Nothing
By running the above code, (4 x number of units manufactured) csv files will be aggregated into one Excel file. This way of summarizing is not quite cool, but it is just an example (laugh)
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/318960/39caf51f-6841-04f3-d8d6-883f73e6205a.png "width=90%>
Next, I will explain how to create a batch file. The batch file is created in two steps.
First of all, it is the exe conversion of 1., but it is done using the above-mentioned Pyinstaller. At the command prompt, change to the directory where integration.py and ShapeJudgment.py exist, and then execute the following command.
$ pyinstaller integration.py --onefile
$ pyinstaller ShapeJudgment.py --onefile
Then
44536 INFO: Building COLLECT COLLECT-00.toc completed successfully.
Is displayed, a folder called dist is created in the same directory. Integral.py and ShapeJudgment.py were converted into exes in that folder. Contains integration.exe and ShapeJudgment.exe.
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/318960/50eb0030-a9ad-2c1d-3156-4f96a16188e8.png "width=90%>
Next, create the batch file in 2. Write the following contents in a text editor such as Notepad, and save it with the extension ".bat". The first line is a comment. Please note that there is a half-width space between each path. Also, ^ (caret) is a symbol required for line breaks.
Executable file.bat
@rem integration.exe bat file to execute
"integration.exe path" ^
"ShapeJudgment.exe path"^
"Path of the folder where the "setting value log" is saved"^
"Path to the folder where the "device status log" is saved"^
"Path of the folder where "shape data" is saved"^
"The path of the folder where the "judgment result" is saved"^
"Path of the folder where the "aggregated Excel file" is saved"^
"excel_merge.vbs path"
↓ Description example
@rem integration.exe bat file to execute
"C:\Users\PycharmProjects\untitled2\dist\integration.exe" ^
"C:\Users\PycharmProjects\untitled2\dist\ShapeJudgment.exe"^
"C:\Users\input\Manufacturing equipment log\Manufacturing equipment setting value log"^
"C:\Users\input\Manufacturing equipment log\Manufacturing equipment status log"^
"C:\Users\input\Inspection equipment log\Inspection device shape data"^
"C:\Users\input\Inspection equipment log\Judgment result of inspection equipment"^
"C:\Users\output\Aggregated Excel file"^
"C:\Users\VBS\excel_merge.vbs"
The 3rd to 10th lines correspond to sys.argv [1] ~ sys.argv [7] written in integration.py respectively.
Various files came out, but the following four files are finally put into the PC at the manufacturing site. Naturally, change the path written in the batch file according to the PC at the manufacturing site.
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/318960/93ebeba5-f3ae-c18a-eb85-78b8bb4b07f8.png "width=70%>
Ask the manufacturer to double-click "Executable file.bat" before the start of production on that day. Then, every time the product flows, the four csv files are automatically aggregated into one Excel file. After the production ends, press "Ctrl + C" to stop the batch file. At that point, the aggregation to the Excel file is completed. </ b>
The above process is an example, It seems that the library described above can be used for various automations that are needed at the manufacturing site.
There are some points to note about the above method, so I will describe them here.
■ The exe file may become huge In exe conversion using Pyinstaller, all the libraries in the Python environment at the time of creation will be imported. Therefore, the file size becomes huge and the operation of the exe may slow down. To avoid this, it is better to exe excluding unused libraries. The following article will be helpful for how to make an exe except for a specific library. Matter that the size became messed up when the program built with Python was converted to exe with PyInstaller [June 2019, solution added]
■ Cannot check aggregated Excel files during production If you open an Excel file during the write operation, a write error will occur. So, if you want to check the Excel file that was aggregated during production, you need to copy it separately and open it.
Nowadays, programming hurdles have dropped dramatically, so I think there will be more opportunities for non-programmers to use programming to automate their work. At that time, what is more important than programming skills may be communication ability to grasp the needs of the field properly and attitude to create "tools that can be used in the field" </ b>.
Recommended Posts