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

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

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

Recommended Posts

[Python] Creating a GUI tool that automatically processes CSV of temperature rise data in Excel
Code reading of faker, a library that generates test data in Python
A well-prepared record of data analysis in Python
I made a program in Python that reads CSV data of FX and creates a large amount of chart images
Full-width and half-width processing of CSV data in Python
Python that merges a lot of excel into one excel
A collection of Excel operations often used in Python
MALSS, a tool that supports machine learning in Python
Consolidate a large number of CSV files in folders with python (data without header)
A program that summarizes the transaction history csv data of SBI SECURITIES stocks [Python3]
A tool that automatically turns the gacha of a social game
Published a library that hides character data in Python images
A Python script that stores 15 years of MLB game data in MySQL in 10 minutes (Baseball Hack!)
A set of script files that do wordcloud in Python3
A python script that converts Oracle Database data to csv
The story of creating a bot that displays active members in a specific channel of slack with python
A simple data analysis of Bitcoin provided by CoinMetrics in Python
A function that measures the processing time of a method in python
Impressions of touching Dash, a data visualization tool made by python
[Python] Creating a scraping tool Memo
[Python] About creating a tool to create a new Outlook email based on the data of the JSON file and the part that got caught
How to send a visualization image of data created in Python to Typetalk
A summary of Python e-books that are useful for free-to-read data analysis
Summary of processes often performed in Pandas 1 (CSV, Excel file related operations)
I want to color a part of an Excel string in Python
Plot CSV of time series data with unixtime value in Python (matplotlib)
Let's create a customer database that automatically issues a QR code in Python
Display a list of alphabets in Python 3
Create a simple GUI app in Python
I created a password tool in Python.
Various ways to read the last line of a csv file in Python
From Excel file to exe and release of tool that spits out CSV
Basic data frame operations written by beginners in a week of learning Python
I made a tool in Python that right-clicks an Excel file and divides it into files for each sheet.
Until you get daily data for multiple years of Japanese stocks and save it in a single CSV (Python)
A memo to generate a dynamic variable of class from dictionary data (dict) that has only standard type data in Python3
Draw a graph of a quadratic function in Python
Ubuntu18.04.05 Creating a python virtual environment in LTS
A memo that I wrote a quicksort in Python
[Python] A tool that allows intuitive relative import
Get the caller of a function in Python
Automatically create word and excel reports in python
A program that removes duplicate statements in Python
Make a copy of the list in Python
Real-time visualization of thermography AMG8833 data in Python
Rewriting elements in a loop of lists (Python)
Data analysis in Python: A note about line_profiler
The story of reading HSPICE data in Python
Make a joyplot-like plot of R in python
A Vim plugin that automatically formats Python styles
Output in the form of a python array
Simple comparison of Python libraries that operate Excel
Get a glimpse of machine learning in Python
Speed evaluation of CSV file output in Python
Summary of Excel operations using OpenPyXL in Python
Summary of statistical data analysis methods using Python that can be used in business
[Python] A program that finds the shortest number of steps in a game that crosses clouds
A memo that implements the job of loading a GCS file into BigQuery in Python
Predict from various data in Python using Facebook Prophet, a time series prediction tool
I made a program in Python that changes the 1-minute data of FX to an arbitrary time frame (1 hour frame, etc.)
[Python] About creating a tool to display all the pages of the website registered in the JSON file & where it got caught