How to display pandas dataframe properly using tkinter?

Question

I am fairly new to python and I am attempting to create a tool which displays the number of rows and columns of all sheets of Excel workbooks in a folder. I am looking to display a data frame as the final result using tkinter, however the display is not coming out correctly as the last two columns of the dataframe appear on a new line. I was wondering how to rectify this issue. I have tried using PyQT5, but this kept crashing my Kernel, and I have tried using Treeviews, I but can't figure out how to write this dataframe properly to a Treeview. Below is my current code:

import pandas as pd
import tkinter as tk
import glob 
import os
import xlrd

def folder_row_count():
    folder_path = f_path_entry.get()
    file_extension = file_ext_var.get()
    window = tk.Tk()
    t1 = tk.Text(window)
    t1.grid()
    if file_extension == "xlsx":
        filenames = []
        sheetnames = []
        sheetrows = []
        sheetcols = []
        for fname in  glob.glob(os.path.join(folder_path, f"*.{file_extension}")):
            wb = xlrd.open_workbook(fname)
            filename = []
            sheetname = []
            sheetrow = []
            sheetcol = []
            for sheet in wb.sheets():
                filename.append(os.path.basename(fname))
                sheetname.append(sheet.name)
                sheetrow.append(sheet.nrows)
                sheetcol.append(sheet.ncols)
            filenames.append(filename)
            sheetnames.append(sheetname)
            sheetrows.append(sheetrow)
            sheetcols.append(sheetcol)
        flat_filenames = [item for filename in filenames for item in filename]
        flat_sheetnames = [item for sheetname in sheetnames for item in sheetname]
        flat_sheetrows = [item for sheetrow in sheetrows for item in sheetrow]
        flat_sheetcols = [item for sheetcol in sheetcols for item in sheetcol]
    df = pd.DataFrame({'File Name': flat_filenames,
                       'Sheet Name': flat_sheetnames,
                       'Number Of Rows': flat_sheetrows,
                       'Number Of Columns': flat_sheetcols
                        })
    main_df = df.append(df.sum(numeric_only = True).rename('Total'))
    t1.insert(tk.END, main_df)
    window.mainloop()

file_ext_list = ["xlsx"]

window = tk.Tk()

window.title("Row Counter")

tk.Label(window, text = "Choose File Type:").grid(row = 1, column = 0)

file_ext_var = tk.StringVar(window)
file_ext_dd = tk.OptionMenu(window, file_ext_var, *file_ext_list)
file_ext_dd.config(width = 10)
file_ext_dd.grid(row = 1, column = 1)

tk.Label(window, text = "Folder Path:").grid(row = 2, column = 0)

f_path_entry = tk.Entry(window)
f_path_entry.grid(row = 2, column = 1)

tk.Button(window, text = "Count Rows", command = folder_row_count).grid(row = 4, column = 1)

window.mainloop()

Secondly, I would greatly appreciate any commentary on how I can improve upon this code and make it more efficient.

Thanks in advance.

Henry Yik · Accepted Answer · 2019-09-07 06:16:55Z

5

You just need to iterate over your df by iterrows and insert them into your Treeview. Below is a basic sample:

import tkinter as tk
from tkinter import ttk
import pandas as pd

root = tk.Tk()

sample = {"File Name":[f"file_{i}" for i in range(5)],
          'Sheet Name': [f"sheet_{i}" for i in range(5)],
          'Number Of Rows': [f"row_{i}" for i in range(5)],
          'Number Of Columns': [f"col_{i}" for i in range(5)]
          }
df = pd.DataFrame(sample)
cols = list(df.columns)

tree = ttk.Treeview(root)
tree.pack()
tree["columns"] = cols
for i in cols:
    tree.column(i, anchor="w")
    tree.heading(i, text=i, anchor='w')

for index, row in df.iterrows():
    tree.insert("",0,text=index,values=list(row))

root.mainloop()

Also I see you are using xlrd to first read your excel before turning it into a Dataframe. Why don't you use pandas.read_excel instead?

answered Sep 7, 2019 at 6:16

Henry Yik

22.6k5 gold badges21 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Legend Over a year ago

Sorry for the late reply, Thanks very much. This solved my issue. I will have a look at the read_excel function.

Collectives™ on Stack Overflow

How to display pandas dataframe properly using tkinter?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related