Overview

We converted the CSV of the temperature rise data of a certain object to Excel, and created a GUI tool that automatically inputs the temperature rise time, colors the cells at any temperature, and creates a chart.

・ GUI キャプチャ.PNG

-Automatically created Excel file (* Graph is filled) キャプチャ2.PNG

environment

Windows10
Python3.8
Tkinter

Full text

ExcelMaker

`ExcelMaker.pyw`


import os, sys
import pandas as pd
import openpyxl as px
import tkinter as tk
import tkinter.filedialog as fl
import tkinter.messagebox as mb
from openpyxl.chart import ScatterChart, Reference, Series
from openpyxl.styles import PatternFill

#File selection function
def select_file():
    global file_path
    filetype = [("all file", "*.csv")] #View CSV file
    iDir = os.path.abspath(r"<Directory path>") #最初に表示したいDirectory path
    file_path = fl.askopenfilename(initialdir=iDir, filetypes=filetype) #Specifying the path
    file1.set(file_path) #Show selected path

#Excel creation function
def make_excel():
    excel_name = str(os.path.splitext(file_path)[0]) + "-1.xlsx" #Excel name
    df = pd.read_csv(file_path, skiprows=57, usecols=[2], encoding="cp932") #Read CSV
    df.drop(df.tail(3).index, inplace=True) #Erase the last 3 lines and replace the file
    df_float = df.astype("float").round(1) #Round to the first decimal place

    #If there is a file with the same name, check if you want to overwrite it
    if os.path.isfile(excel_name):
        res = mb.askquestion("", "There is a file with the same name. Do you want to overwrite?")
        #Overwrite file
        if res == "yes":
            df_float.to_excel(excel_name, header=False, index=False)
        #Exit the program
        elif res == "no":
            mb.showinfo("", "Please check the file name again")
            sys.exit()
    #Create a new file
    else:
        df_float.to_excel(excel_name, header=False, index=False)

    #Excel operation relations
    wb = px.load_workbook(excel_name)
    ws = wb.active
    sheet = wb["Sheet1"]
    sc = sheet.cell
    wc = ws.cell

    sheet.insert_cols(0, 1) #Insert one column at the beginning

    temp_var = int(entry_temp.get()) - 10 #Target temperature-10℃
    start = 1 #Temperature rise start time
    cell_diff1 = 0 #Difference between upper and lower cells

    #Comparing the upper and lower cells, if the temperature rises by 3 or more three times in a row, the temperature rise starts.
    while cell_diff1 <= 3:
        start += 1
        cell_diff3 = float(sc(row=start+1, column=2).value) - float(sc(row=start, column=2).value)
        if cell_diff3 >= 3:
            cell_diff2 = float(sc(row=start+2, column=2).value) - float(sc(row=start+1, column=2).value)
            if cell_diff2 >= 3:
                cell_diff1 = float(sc(row=start+3, column=2).value) - float(sc(row=start+2, column=2).value)

    end = start #Last line of data
    v1 = 0 #Temperature rise time
    
    #Temperature rise time is 0.Enter 5 each
    while sc(row=end, column=2).value is not None:
        wc(row=end, column=1, value=v1)
        end += 1
        v1 += 0.5

    keep = start #Retention start time
    fill = PatternFill(fill_type="solid", fgColor="FFFF00") #Fill the cell with yellow
    temp_var = int(entry_temp.get()) - 10 #Target temperature-10℃
    
    #Target temperature-10 ° C line
    while sc(row=keep, column=2).value <= temp_var:
        keep += 1

    #If the first decimal place is 5, move down one line
    if str(sc(row=keep, column=1).value)[-1] == str(5):
        keep = keep + 1
        
    #Color the retention start time cell
    wc(row=keep, column=1).fill = fill
    wc(row=keep, column=2).fill = fill

    v2 = 0 #Temperature rise time
    
    #Target temperature-Retention time is 0 from 10 ℃.Enter 5 each
    while keep != end:
        wc(row=keep, column=3, value=v2)
        keep += 1
        v2 += 0.5
        #Color the cell for the relevant retention time
        if int(entry_time1.get()) == v2 or int(entry_time2.get()) == v2 or int(entry_time3.get()) == v2:
            wc(row=keep, column=1).fill = fill
            wc(row=keep, column=2).fill = fill
            wc(row=keep, column=3).fill = fill
            
            max_entry_time = keep #Maximum retention time

    #Align cell format with first decimal place
    for row in sheet:
        for cell in row:
            cell.number_format = "0.0"

    #Creating a graph
    chart = ScatterChart()
    
    x_values = Reference(ws, min_row=start, min_col=1, max_row=end, max_col=1) #x axis(Temperature rise time)
    y_values = Reference(ws, min_row=start, min_col=2, max_row=end, max_col=2) #y axis(temperature)

    graph = Series(y_values, x_values)
    chart.series.append(graph)
    
    ws.add_chart(chart, "D"+str(max_entry_time)) #Display chart in row at end of retention
    
    wb.save(excel_name) #Save Excel file
    mb.showinfo("", "I created an excel file")

#Creating a GUI
if __name__ == "__main__":
    root = tk.Tk()
    root.title("Convert CSV to Excel")

    #frame1
    frame1 = tk.LabelFrame(root, text="Select files")
    frame1.grid(row=0, columnspan=2, sticky="we", padx=5)
    
    #Selection button
    select_button = tk.Button(frame1, text="Choice", command=select_file, width=10)
    select_button.grid(row=0, column=3)

    #View file path
    file1 = tk.StringVar()
    file1_entry = tk.Entry(frame1, textvariable=file1, width=35)
    file1_entry.grid(row=0, column=2, padx=5)

    #frame2
    frame2 = tk.LabelFrame(root, text="conditions")
    frame2.grid(row=1, sticky="we")

    #Target temperature text
    text_temp = tk.Label(frame2, text="Target temperature (℃)", width=20)
    text_temp.grid(row=0, column=0, padx=5)

    #Retention time text
    text_time = tk.Label(frame2, text="Retention time (seconds):Multiple specifications are possible", width=25)
    text_time.grid(row=0, column=1)

    #Run button
    action_button = tk.Button(frame2, text="Run", command=make_excel, width=15)
    action_button.grid(row=3, column=0)

    #Input field for target temperature
    entry_temp = tk.Entry(frame2, width=15)
    entry_temp.grid(row=1, column=0, padx=5)

    #Retention time input field
    entry_time1 = tk.Entry(frame2, width=15)
    entry_time1.grid(row=1, column=1, padx=5, pady=5)
    entry_time1.insert(tk.END, 0)

    entry_time2 = tk.Entry(frame2, width=15)
    entry_time2.grid(row=2, column=1, padx=5, pady=5)
    entry_time2.insert(tk.END, 0)

    entry_time3 = tk.Entry(frame2, width=15)
    entry_time3.grid(row=3, column=1, padx=5, pady=5)
    entry_time3.insert(tk.END, 0)

root.mainloop()

Commentary

Preparation

`ExcelMaker.pyw`


import os, sys
import pandas as pd
import openpyxl as px
import tkinter as tk
import tkinter.filedialog as fl
import tkinter.messagebox as mb
from openpyxl.chart import ScatterChart, Reference, Series
from openpyxl.styles import PatternFill

Import the required modules. Create GUI with tkinter, read CSV with pandas, and operate Excel with ʻopenpyxl`.

Function when selecting a file

`ExcelMaker.pyw`


#File selection function
def select_file():
    global file_path
    filetype = [("all file", "*.csv")] #View CSV file
    iDir = os.path.abspath(r"<Directory path>") #最初に表示したいDirectory path
    file_path = fl.askopenfilename(initialdir=iDir, filetypes=filetype) #Specifying the path
    file1.set(file_path) #Show selected path

Creates the process when the select button is pressed. Display only CSV with filetype, and specify the directory to be opened first when the select button is pressed for ʻiDir. In IDLE used for the development environment this time, if you enclose the character string with quotation marks, an error may occur unless you add r` at the beginning, so enter it.

Get the file name selected in filedialog.askopenfilename and set it to global because you want to use it in other functions. Select the file path with file1.set (file_path) and display it in the frame to the left of the button.

Excel-created functions

Read CSV and check if there is an Excel file with the same name

`ExcelMaker.pyw`


#Excel creation function
def make_excel():
    excel_name = str(os.path.splitext(file_path)[0]) + "-1.xlsx" #Excel name
    df = pd.read_csv(file_path, skiprows=57, usecols=[2], encoding="cp932") #Read CSV
    df.drop(df.tail(3).index, inplace=True) #Erase the last 3 lines and replace the file
    df_float = df.astype("float").round(1) #Round to the first decimal place

    #If there is a file with the same name, check if you want to overwrite it
    if os.path.isfile(excel_name):
        res = mb.askquestion("", "There is a file with the same name. Do you want to overwrite?")
        #Overwrite file
        if res == "yes":
            df_float.to_excel(excel_name, header=False, index=False)
        #Exit the program
        elif res == "no":
            mb.showinfo("", "Please check the file name again")
            sys.exit()
    #Create a new file
    else:
        df_float.to_excel(excel_name, header=False, index=False)

キャプチャ3.PNG First, extract the necessary parts from the CSV above and create an Excel file. In order to convert CSV to Excel name, use str (os.path.splitext (file_path) [0]) +" -1.xlsx " to delete the dot and below (.csv) at the end of the file path, and Add -1.xlsx.

Read the CSV with pd.read_csv. Since the required data was from the 58th row onward in the second column, specify skiprows = 57, usecols = [2] as arguments. Also, if it is left as it is, an error will occur due to the type, so specify ʻencoding =" cp932 "`.

There was unnecessary data in the last 3 lines of the read CSV, so delete it with df.drop (df.tail (3) .index) and replace the CSV file again with ʻinplace = True. It is a floating point type rounded to the first decimal place with df.astype ("float "). round (1)`.

Find the Excel file you are trying to create with ʻif os.path.isfile (excel_name):for what to do if there is a file with the same name. If the conditional expression at this time isTrue`, proceed to the next if statement (== True can be omitted). In other cases, a new Excel file is created.

If there is a file with the same name, a window for selecting "Yes" and "No" will be displayed in messagebox.askquestion. I don't understand the details yet, but at this time I just defined the variable as res = messagebox.askquestio, but the process actually works and the window is displayed. Therefore, if you try to define it in advance, it will be displayed in an unexpected place, so write it at the timing you want to display it.

The return value of messagebox.askquestio is" yes = yes "and" no = no ", so if yes, the Excel file is overwritten as it is, and if no, the program is terminated withsys.exit ()after displaying the message. To do.

Up to this point, you will have an Excel file that contains the temperature change data in the second column as shown below. キャプチャ.PNG

Input of temperature rise time

`ExcelMaker.pyw`


    #Excel operation relations
    wb = px.load_workbook(excel_name)
    ws = wb.active
    sheet = wb["Sheet1"]
    sc = sheet.cell
    wc = ws.cell

    sheet.insert_cols(0, 1) #Insert one column at the beginning

    temp_var = int(entry_temp.get()) - 10 #Target temperature-10℃
    start = 1 #Temperature rise start time
    cell_diff1 = 0 #Difference between upper and lower cells

    #Comparing the upper and lower cells, if the temperature rises by 3 or more three times in a row, the temperature rise starts.
    while cell_diff1 <= 3:
        start += 1
        cell_diff3 = float(sc(row=start+1, column=2).value) - float(sc(row=start, column=2).value)
        if cell_diff3 >= 3:
            cell_diff2 = float(sc(row=start+2, column=2).value) - float(sc(row=start+1, column=2).value)
            if cell_diff2 >= 3:
                cell_diff1 = float(sc(row=start+3, column=2).value) - float(sc(row=start+2, column=2).value)

    end = start #Last line of data
    v1 = 0 #Temperature rise time
    
    #Temperature rise time is 0.Enter 5 each
    while sc(row=end, column=2).value is not None:
        wc(row=end, column=1, value=v1)
        end += 1
        v1 += 0.5

Compare the upper and lower cells with while cell_diff1 <= 3:, and if the temperature rises by 3 or more three times in a row, the temperature rise starts. First, compare the start line and the line below with cell_diff3 = float (sc (row = start + 1, column = 2) .value) --float (sc (row = start, column = 2) .value), and 3 In the above case, compare one line down with cell_diff2 in the same way, and when dell_diff1 becomes 3 or more, the while statement ends.

Once you know the row to start heating, enter the temperature rise time in the first column with while sc (row = end, column = 2) .value is not None: until the value in the temperature change column is blank. I will continue. キャプチャ1.PNG

Enter retention time

`ExcelMaker.pyw`


    keep = start #Retention start time
    fill = PatternFill(fill_type="solid", fgColor="FFFF00") #Fill the cell with yellow
    temp_var = int(entry_temp.get()) - 10 #Target temperature-10℃
    
    #Target temperature-10 ° C line
    while sc(row=keep, column=2).value <= temp_var:
        keep += 1

    #If the first decimal place is 5, move down one line
    if str(sc(row=keep, column=1).value)[-1] == str(5):
        keep = keep + 1
        
    #Color the retention start time cell
    wc(row=keep, column=1).fill = fill
    wc(row=keep, column=2).fill = fill

    v2 = 0 #Temperature rise time
    
    #Target temperature-Retention time is 0 from 10 ℃.Enter 5 each
    while keep != end:
        wc(row=keep, column=3, value=v2)
        keep += 1
        v2 += 0.5
        #Color the cell for the relevant retention time
        if int(entry_time1.get()) == v2 or int(entry_time2.get()) == v2 or int(entry_time3.get()) == v2:
            wc(row=keep, column=1).fill = fill
            wc(row=keep, column=2).fill = fill
            wc(row=keep, column=3).fill = fill
            
            max_entry_time = keep #Maximum retention time

    #Align cell format with first decimal place
    for row in sheet:
        for cell in row:
            cell.number_format = "0.0"

temp_var = int (entry_temp.get ()) --10 to get the value of the target temperature -10 ℃, and while sc (row = keep, column = 2) .value <= temp_var: to get the value. Examine the line. At that time, if the first decimal point of the temperature rise time is 5, ʻif str (sc (row = keep, column = 1) .value) [-1] == str (5): `to lower one line.

Once you know the retention start line, enter the retention time until the last line with while keep! = End:. At that time, ʻif int (entry_time1.get ()) == v2 or int (entry_time2.get ()) == v2 or int (entry_time3.get ()) == v2: , the same line as the specified retention time To fill in and decide where to put the graph, use max_entry_time = keep` to get the row with the longest retention time.

for row in sheet: specifies the entire row of the sheet, for cell in row: specifies the entire column, and cell.number_format =" 0.0 " aligns the cell format with the first decimal point. キャプチャ2.PNG

Graph creation

`ExcelMaker.pyw`


    #Creating a graph
    chart = ScatterChart()
    
    x_values = Reference(ws, min_row=start, min_col=1, max_row=end, max_col=1) #x axis(Temperature rise time)
    y_values = Reference(ws, min_row=start, min_col=2, max_row=end, max_col=2) #y axis(temperature)

    graph = Series(y_values, x_values)
    chart.series.append(graph)
    
    ws.add_chart(chart, "D"+str(max_entry_time)) #Display chart in row at end of retention
    
    wb.save(excel_name) #Save Excel file
    mb.showinfo("", "I created an excel file")

chart = ScatterChart () for a scatter plot, x_values = Reference (ws, min_row = start, min_col = 1, max_row = end, max_col = 1) for the first column on the X axis, y_values = Reference ( Specify the second column on the Y axis with ws, min_row = start, min_col = 2, max_row = end, max_col = 2) . Specify the X and Y axes with graph = Series (y_values, x_values) and add with chart.series.append (graph).

Finally, with ws.add_chart (chart," D "+ str (max_entry_time)), place the graph in the row at the end of retention in column D (4th column), save the file, and finish.

Creating a GUI

`ExcelMaker.pyw`


#Creating a GUI
if __name__ == "__main__":
    root = tk.Tk()
    root.title("Convert CSV to Excel")

    #frame1
    frame1 = tk.LabelFrame(root, text="Select files")
    frame1.grid(row=0, columnspan=2, sticky="we", padx=5)
    
    #Selection button
    select_button = tk.Button(frame1, text="Choice", command=select_file, width=10)
    select_button.grid(row=0, column=3)

    #View file path
    file1 = tk.StringVar()
    file1_entry = tk.Entry(frame1, textvariable=file1, width=35)
    file1_entry.grid(row=0, column=2, padx=5)

    #frame2
    frame2 = tk.LabelFrame(root, text="conditions")
    frame2.grid(row=1, sticky="we")

    #Target temperature text
    text_temp = tk.Label(frame2, text="Target temperature (℃)", width=20)
    text_temp.grid(row=0, column=0, padx=5)

    #Retention time text
    text_time = tk.Label(frame2, text="Retention time (seconds):Multiple specifications are possible", width=25)
    text_time.grid(row=0, column=1)

    #Run button
    action_button = tk.Button(frame2, text="Run", command=make_excel, width=15)
    action_button.grid(row=3, column=0)

    #Input field for target temperature
    entry_temp = tk.Entry(frame2, width=15)
    entry_temp.grid(row=1, column=0, padx=5)

    #Retention time input field
    entry_time1 = tk.Entry(frame2, width=15)
    entry_time1.grid(row=1, column=1, padx=5, pady=5)
    entry_time1.insert(tk.END, 0)

    entry_time2 = tk.Entry(frame2, width=15)
    entry_time2.grid(row=2, column=1, padx=5, pady=5)
    entry_time2.insert(tk.END, 0)

    entry_time3 = tk.Entry(frame2, width=15)
    entry_time3.grid(row=3, column=1, padx=5, pady=5)
    entry_time3.insert(tk.END, 0)

root.mainloop()

キャプチャ.PNG Create this screen. As for the structure, a column for displaying the path of the selected file and a selection button are arranged in frame1, a column for inputting the target temperature and retention time and an execution button are arranged in frame2.

Basically, specify tk.Label for sentences, tk.Button for buttons, tk.Entry for input fields, and place it anywhere with grid. If you want to add the processing when the button is pressed, specify the function in the argument comand, and if you want to put the initial value in the input field, specify the value with .insert.

As an aside, by setting the extension to .pyw, the black screen of the background when the program is started will not be displayed.

Summary

This time, I created something that could be automated in my daily work as well as studying, but because I understood the work itself, the program itself was relatively easy to make. (2H / day x 10 days)

When thinking about the future course, engineers (RPA engineers?) Who introduce such programs to companies to improve work efficiency seem to be interesting, so I will consider them as options.

reference

-How to use openpyxl to read and write Excel files (xlsx) with Python -Note on how to use openpyxl in python

[Python] Creating a GUI tool that automatically processes CSV of temperature rise data in Excel

Overview

environment

Full text

ExcelMaker.pyw

Commentary

Preparation

ExcelMaker.pyw

Function when selecting a file

ExcelMaker.pyw

Excel-created functions

Read CSV and check if there is an Excel file with the same name

ExcelMaker.pyw

Input of temperature rise time

ExcelMaker.pyw

Enter retention time

ExcelMaker.pyw

Graph creation

ExcelMaker.pyw

Creating a GUI

ExcelMaker.pyw

Summary

reference

`ExcelMaker.pyw`

`ExcelMaker.pyw`

`ExcelMaker.pyw`

`ExcelMaker.pyw`

`ExcelMaker.pyw`

`ExcelMaker.pyw`

`ExcelMaker.pyw`

`ExcelMaker.pyw`