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
-Automatically created Excel file (* Graph is filled)
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()
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`.
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.
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)
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 is
True`, 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.
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.
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.
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.
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()
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.
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.
-How to use openpyxl to read and write Excel files (xlsx) with Python -Note on how to use openpyxl in python
Recommended Posts