Tkinter interface to search and filter Pandas DataFrame and display rows in Treeview
Tkinter window
We will keep one entry widget e1 to collect the user input and on click of the Buttonb1 we will call the function my_search().
import tkinter as tk
my_w = tk.Tk()
my_w.geometry("500x350") # width x height
my_w.title("plus2net.com") # Adding a title
l1 = tk.Label(my_w, text="Search", width=5, font=18) # added one Label
l1.grid(row=1, column=1, padx=3, pady=10)
e1 = tk.Entry(my_w, width=35, bg="yellow", font=18) # added one Entry box
e1.grid(row=1, column=2, padx=1)
b1 = tk.Button(my_w, text="Search", width=7, font=18, command=lambda: my_search())
b1.grid(row=1, column=3, padx=2)
my_w.mainloop()
my_search() Function
First we will create a list of column names or headers from the main dataframe df.
We will read the user inputs in entry widget e1 and then remove the space from both sides by using strip().
If the user input is not a digit then we will use string method contains() to match against the name column of the source DataFrame df.
We used tolist() to create a list from the DataFrame
def my_search():
l1 = list(df) # List of column names as headers
query = e1.get().strip() # get user entered string
if query.isdigit(): # if query is number
str1 = df["id"] == int(query) #
else:
str1 = df.name.str.contains(query, case=False) # name column value matching
df2 = df[(str1)] # combine all conditions using | operator
r_set = df2.to_numpy().tolist() # Create list of list using rows
Creating Treeview
Inside the function my_search() we will use Tkinter Treeview to display Tabular data. Here the final output of the query or the resulted dataframe is displayed using the Treeview.
trv = ttk.Treeview(my_w, selectmode="browse") # selectmode="browse" or "extended"
trv.grid(row=2, column=1, columnspan=3, padx=10, pady=20) #
trv["height"] = 10 # Number of rows to display, default is 10
trv["show"] = "headings"
Previously we have collected all column headers as a list l1 from the source dataframe df. We will first display the headers by looping through the list l1.
Dynamic Creation of Header & Columns in Treeview »
trv["columns"] = l1
for i in l1:
trv.column(i, width=90, anchor="c")
# Headings of respective columns
trv.heading(i, text=i)
Inserting data rows to Treeview
From our resulted DataFrame we have collected the list of rows as r_set. Using this we will insert each row to our Treeview trv.
for dt in r_set:
v = [r for r in dt] # creating a list from each row
trv.insert("", "end", iid=v[0], values=v) # adding row
import pandas as pd
from tkinter import ttk # for treeview
df = pd.read_excel("F:\\data\\student.xlsx") # create DataFrame
import tkinter as tk
my_w = tk.Tk()
my_w.geometry("500x350") # width x height
my_w.title("plus2net.com") # Adding a title
l1 = tk.Label(my_w, text="Search", width=5, font=18) # added one Label
l1.grid(row=1, column=1, padx=3, pady=10)
e1 = tk.Entry(my_w, width=35, bg="yellow", font=18) # added one Entry box
e1.grid(row=1, column=2, padx=1)
b1 = tk.Button(my_w, text="Search", width=7, font=18, command=lambda: my_search())
b1.grid(row=1, column=3, padx=2)
def my_search():
l1 = list(df) # List of column names as headers
query = e1.get().strip() # get user entered string
if query.isdigit(): # if query is number
str1 = df["id"] == int(query) #
else:
str1 = df.name.str.contains(query, case=False) # name column value matching
df2 = df[(str1)] # combine all conditions using | operator
r_set = df2.to_numpy().tolist() # Create list of list using rows
trv = ttk.Treeview(my_w, selectmode="browse") # selectmode="browse" or "extended"
trv.grid(row=2, column=1, columnspan=3, padx=10, pady=20) #
trv["height"] = 10 # Number of rows to display, default is 10
trv["show"] = "headings"
# column identifiers
trv["columns"] = l1
for i in l1:
trv.column(i, width=90, anchor="c")
# Headings of respective columns
trv.heading(i, text=i)
for dt in r_set:
v = [r for r in dt] # creating a list from each row
trv.insert("", "end", iid=v[0], values=v) # adding row
my_w.mainloop()
Without using search button
KeyRelease event to search and filter Pandas DataFrame and display rows in Treeview
As we type the matching records will be displayed and there is no need to press Search button to get the result.
We can bind KeyReleaseevent of entry widget e1 to trigger the function.
e1.bind("<KeyRelease>",my_search)
We have to change the function declaration.
def my_search(*args):
def my_search(*args):
l1=list(df) # List of column names as header
-----
-----
e1.bind("<KeyRelease>",my_search)
Multiple word search
When we use more than one word to search then all words are to be checked for matching results. All the matching results against each word of the entered string are to be joined to return the final result.
Searching multiple words in DataFrame and display results in Treeview Part III
Step 1
Break the input string using split() and one list is created with each words. (remove the negative words from the list if required. )
words=query.split(' ') # List of words by breaking a string.
To remove some words or not to consider a list of words for searching.
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
Step 2
Loop through all words and append to filtered dataframe df2 by using contains() string method.
for w in words:
df2=df2.append(df[df.name.str.contains(w,case=False)],ignore_index=True)
Step 3
From the final dataframe df2 remove the duplicate rows if any. Display the dataframe in Treeview.
df2=df2.drop_duplicates() # remove duplicate rows
Using concat()
In place of append() we can use concat() as we may get this warning.
FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.
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.
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("500x350") # 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)
df2 = df.iloc[:0] # create dataframe of same structure or columns
def my_search(*args):
global df2
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=df2.drop_duplicates() # remove duplicate rows
r_set=df2.to_numpy().tolist() # create list of list using rows
trv=ttk.Treeview(my_w,selectmode='browse')
trv.grid(row=2,column=1,columnspan=3,padx=10,pady=20)
trv['height']=10
trv['show']='headings'
trv['columns']=l1
for i in l1:
trv.column(i,width=90,anchor='c')
trv.heading(i,text=i)
for dt in r_set:
v=[r for r in dt]
trv.insert("",'end',iid=v[0],values=v)
e1.bind("<KeyRelease>",my_search)
def my_save():
global df2
file_path = filedialog.asksaveasfilename(
filetypes=[("Excel file", ".xlsx")],
defaultextension=".xlsx")
if(file_path): # user has not Cancelled the browse window
df2.to_excel(file_path,index=False) # Save in Excel file
my_w.mainloop()
We can save the output in different sources and formats. Here is the list to which we can save our filtered dataframe df2 List of Outputs from DataFrame »
Above function my_save() we will extended to include .csv ( Comma Separated Values ) file while saving. To identify which file type the user has selected we have used endswidth() string method.
def my_save():
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
We will use to_clipboard() to copy the filtered DataFrame df2 to clipboard. After copying we will display a message for a duration of 3000 milliseconds.
Searching and saving DataFrame and copy to clipboard Part IV
Number of records
By using len() we will get the number of rows in our filtered DataFrame df2. This number we will convert to string type by using str() and using configure option we will display in our Labell2.
l2.config(text='No of records: '+str(len(df2)))
import pandas as pd
from tkinter import ttk
from tkinter import filedialog
df=pd.read_excel("F:\\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)
df2 = df.iloc[:0]
def my_search(*args):
global df2
df2 = df.iloc[:0] # one blank dataframe
l1=list(df) # List of column names as header
query=e1.get().strip()
if query.isdigit():
str1=df['id']==int(query)
else:
words=query.split(' ') # list of words
words_stop=['to','and','or']
words=list(set(words)-set(words_stop))
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()
l2.config(text='No of records: '+str(len(df2)))
r_set=df2.to_numpy().tolist() # create list of list using rows
trv=ttk.Treeview(my_w,selectmode='browse')
trv.grid(row=3,column=1,columnspan=3,padx=10,pady=20)
trv['height']=10
trv['show']='headings'
trv['columns']=l1
for i in l1:
trv.column(i,width=90,anchor='c')
trv.heading(i,text=i)
for dt in r_set:
v=[r for r in dt]
trv.insert("",'end',iid=v[0],values=v)
e1.bind("<KeyRelease>",my_search)
def my_save():
global df2
file_path=filedialog.asksaveasfilename(filetypes=[('Excel File','.xlsx'),
('CSV file','.csv')],defaultextension='.xlsx')
if file_path: # user has not cancelled the operation
if file_path.endswith('.csv'):
df2.to_csv(file_path,index=False)
else:
df2.to_excel(file_path,index=False)
def my_copy():
df2.to_clipboard()
l3.config(text='Copied')
l3.after(3000,lambda:l3.config(text=''))
my_w.mainloop()