Selecting and deleting row from DataFrame and showing result in Treeview Part V
To generate DataFrame df2 from the source DataFrame df we have used the function my_search().
In part 1 of DataFrame search, we crated the filtered output and displayed the same in Treeiew trv.
Inside the function data_collect() we will read the user selected row values. By using config() we are updating the text option of the Labell3 to show the values of the selected row.
def data_collect(*args):
global p_id
p_id = trv.selection()[0] # collect selected row id
#print(trv.item(p_id)) # Dictionary of all options
#print(trv.item(p_id)['values']) # List of values
l3.config(text=trv.item(p_id)['values']) # Update the Label with values
We will use global variables as we will be changing the values inside different functions.
df2: Filtered or the result output DataFrame after applying query to the source DataFrame df. trv : Treeview created to display the DataFrame l1 : List of Column headers taken from source DataFrame df
global df2,trv,l1
Here is a list of functions we will be using. my_search() : Searching souce DataFrame and creating output DataFrame as df2. trv_refresh() : Creating Treeview by using DataFrame df2 and header by l1 data_collect() : Collect the Treeview selected row details. my_save() : Save the resultant DataFrame df2 in different file formats. my_copy() : Copy the resultant DataFrame df2 to Clipboard. my_delete() : Delete the selected row of the Treeview trv
my_search()
This function already explained in Part I, this creates the resultant DataFrame df2 based on the user inputs. From Part I the Treeview creation part is taken into separate function trv_refresh() as Treeview is to be updated to reflect the changes done by the user while selecting and deleting row. The last line of the function below calls trv_refresh() to show the Treeview.
def my_search(*args): # Searching source dataframe and create filtered one
global df2,trv,l1
df2 = df.iloc[:0] # create dataframe of same structure or columns
l1=list(df) # List of column names as header
query=e1.get().strip()
if query.isdigit():
df2=df[df['id']==int(query)]
else:
words=query.split(' ') # List of words by breaking a string.
words_stop=['to','and','or'] # List of words to be removed from searching
words=list(set(words)-set(words_stop)) # final list of words to search
for w in words:
#df2=df2.append(df[df.name.str.contains(w,case=False)],ignore_index=True)
df2=pd.concat([df2,df[df.name.str.contains(w,case=False)]])
df2=df2.drop_duplicates() # remove duplicate rows
l2.config(text='No of records: '+str(len(df2)))
trv_refresh() # Show the DataFrame in Treeview
trv_refresh()
Rows of data from DataFrame df2 is used to display the Treeview. Here the header list l1 is taken from the source DataFrame df. The last line of the code below binds the row selection to trigger the funtion data_collect().
Calling this function will refresh the Treeview trv to reflect the changes in DataFrame df2.
def trv_refresh(): # Refresh the Treeview to reflect changes
global df2,trv,l1
r_set=df2.to_numpy().tolist() # create list of list using rows
trv=ttk.Treeview(my_w,selectmode='browse',height=10,
show='headings',columns=l1)
trv.grid(row=3,column=1,columnspan=3,padx=10,pady=20)
for i in l1:
trv.column(i,width=90,anchor='c')
trv.heading(i,text=str(i))
for dt in r_set:
v=[r for r in dt]
trv.insert("",'end',iid=v[0],values=v)
trv.bind("<<TreeviewSelect>>", data_collect)# Triggers on selection of row
data_collect()
Once any row of the Treeview is selected by user, the function data_collect() is triggered. Inside this function the value of the iid of the selected row is collected ( In this case this is same as id column value) . This is used to collect all values of row and same is displayed on the Labell3 by using config() method.
def data_collect(*args): # collect values of selected row
global p_id
p_id = trv.selection()[0] # collect selected row id
l3.config(text=trv.item(p_id)['values']) # display values
my_save()
We can save the filtered output by using asksaveasfile dialog. User can browse the local directory system and enter the file name to save at desired location.
Here our DataFrame df2 is the filtered output so we will use to_excel() to save the DataFrame in Excel format as per the selected path. Note that we have used df2 as global variable as it is used inside different functions.
def my_save(): # save DataFrame as file
global df2
file_path = filedialog.asksaveasfilename(
filetypes=[("Excel file", ".xlsx"),("CSV file", ".csv")],
defaultextension=".xlsx")
if(file_path): # user has not Cancelled the browse window
if file_path.endswith('.csv'):
df2.to_csv(file_path,index=False) # Save in csv format
else:
df2.to_excel(file_path,index=False) # Save in Excel file
my_copy()
On click of the Copy button, this function will use the pandas method to_clipboard() to copy the DataFrame df2 to system clipboard.
The message is displayed for 3 seconds to indicate the user that the DataFrame df2 is copied.
def my_copy(): # Copy the dataframe to clipboard
df2.to_clipboard() # copy dataframe to clipboard
l3.config(text='copied') # Show message to user
l3.after(3000,lambda:l3.config(text='')) # delete after 3 seconds
my_delete()
This function reads resultant DataFrame df2 and the p_id which is the id column value of the user selected row. It uses drop() to delete the row from the DataFrame df2.
After deletion of the row trv_refresh() is called to reflect the changes by creating the Treeview with updated DataFrame df2.
def my_delete(): # Delete the selected row
global df2,p_id
if p_id: # check if not None
df2.drop(df2[df2['id'] == int(p_id)].index, inplace = True)
#print(df2)
trv_refresh() # refresh the Treeview to reflect changes
import pandas as pd
from tkinter import ttk
from tkinter import filedialog # to show file dialog box for saving
df=pd.read_excel("E:\\data\\student.xlsx") # create dataframe
import tkinter as tk
my_w = tk.Tk()
my_w.geometry("620x350") # width x height
my_w.title("plus2net.com") # Adding a title
font1=['Times',28,'normal']
l1=tk.Label(my_w,text="Search", width=5,font=18)
l1.grid(row=1,column=1,padx=3,pady=10)
e1=tk.Entry(my_w,width=15,bg='yellow',font=font1)
e1.grid(row=1,column=2,padx=1)
b1 = tk.Button(my_w, text="Save Result",bg='lightgreen',
font=18, command=lambda: my_save())
b1.grid(row=1, column=3, padx=2)
l2=tk.Label(my_w,text='',bg='lightgreen',anchor='w')
l2.grid(row=2,column=1,sticky='W',padx=3,pady=3)
l3=tk.Label(my_w,text='',bg='lightyellow',anchor='w')
l3.grid(row=2,column=2,columnspan=2,sticky='W',padx=3,pady=3)
b2 = tk.Button(my_w, text="Copy",bg='lightblue',
font=18, command=lambda: my_copy())
b2.grid(row=2, column=3, padx=2)
b3 = tk.Button(my_w, text="Delete",bg='lightyellow',
font=18, command=lambda: my_delete())
b3.grid(row=2, column=4, padx=2)
df2 = df.iloc[:0] # create dataframe of same structure or columns
l1=[] # List to hold column headers
p_id=None
def my_search(*args): # Searching source dataframe and create filtered one
global df2,trv,l1
df2 = df.iloc[:0] # create dataframe of same structure or columns
l1=list(df) # List of column names as header
query=e1.get().strip()
if query.isdigit():
df2=df[df['id']==int(query)]
else:
words=query.split(' ') # List of words by breaking a string.
words_stop=['to','and','or'] # List of words to be removed from searching
words=list(set(words)-set(words_stop)) # final list of words to search
for w in words:
#df2=df2.append(df[df.name.str.contains(w,case=False)],ignore_index=True)
df2=pd.concat([df2,df[df.name.str.contains(w,case=False)]])
df2=df2.drop_duplicates() # remove duplicate rows
l2.config(text='No of records: '+str(len(df2)))
trv_refresh() # Show the DataFrame in Treeview
def trv_refresh(): # Refresh the Treeview to reflect changes
global df2,trv,l1
r_set=df2.to_numpy().tolist() # create list of list using rows
trv=ttk.Treeview(my_w,selectmode='browse',height=10,
show='headings',columns=l1)
trv.grid(row=3,column=1,columnspan=3,padx=10,pady=20)
for i in l1:
trv.column(i,width=90,anchor='c')
trv.heading(i,text=str(i))
for dt in r_set:
v=[r for r in dt]
trv.insert("",'end',iid=v[0],values=v)
trv.bind("<<TreeviewSelect>>", data_collect) # Triggers on selection of row
e1.bind("<KeyRelease>",my_search) # Update search
def data_collect(*args): # collect values of selected row
global p_id
p_id = trv.selection()[0] # collect selected row id
l3.config(text=trv.item(p_id)['values']) # display values
def my_save(): # save DataFrame as file
global df2
file_path = filedialog.asksaveasfilename(
filetypes=[("Excel file", ".xlsx"),("CSV file", ".csv")],
defaultextension=".xlsx")
if(file_path): # user has not Cancelled the browse window
if file_path.endswith('.csv'):
df2.to_csv(file_path,index=False) # Save in csv format
else:
df2.to_excel(file_path,index=False) # Save in Excel file
def my_copy(): # Copy the dataframe to clipboard
global df2,p_id
df2.to_clipboard() # copied dataframe to clipboard
l3.config(text='copied') # Show message to user
l3.after(3000,lambda:l3.config(text='')) # delete after 3 seconds
def my_delete(): # Delete the selected row
global df2,p_id
if p_id: # check if not None
df2.drop(df2[df2['id'] == int(p_id)].index, inplace = True)
#print(df2)
trv_refresh() # refresh the Treeview to reflect changes
l3.config(text='') # Update the label by removing the text
my_w.mainloop()
Integrating Scrollbar
In above code we have option height=10 is set for the Treeview. When we have more than 10 results to show, we have to use down arrow key or mouse to scroll down by selecting any row. Here we can integrate one vertical scroll bar to the Treeview so we can easily navigate through large number of records.
Note that our Treeview has used 3 columnspan, so our vertical scroll bar should be placed on column=4.
We are adding code to trv_refresh() function
def trv_refresh(): # Refresh the Treeview to reflect changes
global df2,trv,l1
r_set=df2.to_numpy().tolist() # create list of list using rows
trv=ttk.Treeview(my_w,selectmode='browse',height=10,
show='headings',columns=l1)
trv.grid(row=3,column=1,columnspan=3,padx=10,pady=20)
for i in l1:
trv.column(i,width=90,anchor='c')
trv.heading(i,text=str(i))
for dt in r_set:
v=[r for r in dt]
trv.insert("",'end',iid=v[0],values=v)
trv.bind("<<TreeviewSelect>>", data_collect) # Triggers on selection of row
vs = ttk.Scrollbar(my_w,orient="vertical", command=trv.yview)#V Scrollbar
trv.configure(yscrollcommand=vs.set) # connect to Treeview
vs.grid(row=3,column=4,sticky='ns') # Place on grid