[Python] How to show styled dataframe in tkinter console

i have written a python code, the provided code is a Python application that processes an Excel file and highlights specific rows in yellow based on certain criteria define in different def functions. It uses the Pandas library for data manipulation, Tkinter for the graphical user interface, and pandastable for displaying the DataFrame in a table format. for some reason i am not able to see the colour highlighting in tkinter console however i can see it in excel output saved.

Here's a breakdown of the code's functionality:

Imports: The code imports necessary libraries: Pandas for data manipulation, NumPy for numerical operations, Tkinter for creating the GUI, filedialog for opening files, messagebox for displaying messages, and pandastable for creating tables.

Highlighting functions: The code defines several functions that highlight rows based on different criteria:

highlight_missing_values: Highlights rows where certain columns are missing for floating rate types.

highlight_missing_ccy: Highlights rows where the currency is missing but the base price index or base price curve is present.

highlight_negative_spread: Highlights rows where the spread is negative for asset type rows.

highlight_spread_validation: Highlights rows where the spread is outside of a specified range.

highlight_explicit_validation: Highlights rows where the "Interpret Entries As" column is not set to "Margin - Explicit" for floating or managed rate types.

highlight_COA_ccy: Adds a new column to the DataFrame based on the planning node and currency.

process_excel function: This function is the main entry point of the application. It performs the following steps:

Opens a file dialog to let the user select an Excel file. Reads different sheets from the selected Excel file. Cleans and merges the data from the sheets. Applies the highlighting functions to the merged DataFrame. Displays the DataFrame in a pandastable within the Tkinter window. Saves the output DataFrame to a new Excel file.

i have tried the below piece of code but its not highlighting the row in yellow as per the defined function in tkinter console, i am able to see the dataframe in tkinter console but its unstyled one.

import pandas as pd
import numpy as np
from tkinter import Tk, filedialog, Label, Button, messagebox, Frame
from pandastable import Table

# Function to highlight missing values
def highlight_missing_values(row):
if row['COA_Type'] == 'Floating' and pd.notna(row['Currency']):
is_blank = row[['Lockout period', 'lookback(days)', 'Reset Interval', 'Amortization Type', 'Rate Index/ Coupon Spread Reference Index']].isnull().any()
return 'background-color: yellow' if is_blank else ''
return ''

# Function to highlight missing currency
def highlight_missing_ccy(row):
return 'background-color: yellow' if pd.isna(row['Currency']) and (pd.notna(row['Base Price index']) or pd.notna(row['Base Price Curve'])) else ''

# Function to highlight negative spread
def highlight_negative_spread(row):
return 'background-color: yellow' if row['A/L/O'] == 'A' and row['Period1'] < 0 else ''

# Function to highlight spread validation
def highlight_spread_validation(row):
return 'background-color: yellow' if row['Period1'] > 35 or row['Period1'] < -10 else ''

# Function to highlight explicit validation
def highlight_explicit_validation(row):
return 'background-color: yellow' if row['COA_Type'] in ['Floating', 'Managed'] and pd.notna(row['Interpret Entries As']) and row['Interpret Entries As'] != 'Margin - Explicit' else

# Function to highlight COA currency
def highlight_COA_ccy(df):
new_column_value = None
for index in range(len(df)):
planning_node_str = str(df.loc[index, 'Planning_node'])
if planning_node_str.endswith('(QRM)') or planning_node_str.endswith(' OTHER USD'):
new_column_value = planning_node_str
df.loc[index, 'coa_ccy'] = new_column_value
cols = df.columns.tolist()
planning_node_id_idx = cols.index('Planning_node_id')
cols.insert(planning_node_id_idx + 1, cols.pop(cols.index('coa_ccy')))
return df[cols]

# Function to read the Excel file and perform operations
def process_excel():
# Open file dialog to select Excel file
file_path = filedialog.askopenfilename(title="Select an Excel file", filetypes=(("Excel files", "*.xlsx"), ("All files", "*.*")))
if not file_path:
messagebox.showerror("Error", "No file selected!")
return

try:
xls = pd.ExcelFile(file_path)

# Read sheets from the workbook
sheet1_df = pd.read_excel(xls, sheet_name='Sheet1', header=1)
sheet2_df = pd.read_excel(xls, sheet_name='Sheet2', header=1)
eba_mapping = pd.read_excel(xls, sheet_name='EBA-mapping')
index_currency_df = pd.read_excel(xls, sheet_name='Index-ccy', names=['Index', 'ccy'])

# Data cleaning and merging
sheet1_df = sheet1_df.apply(lambda x: x.str.strip() if x.dtype == "object" else x)
sheet2_df = sheet2_df.apply(lambda x: x.str.strip() if x.dtype == "object" else x)

selected_columns_df1 = sheet1_df[['Planning_node', 'Planning_node_id', 'Base Price index']]
selected_columns_df2 = sheet2_df[['Lockout period', 'lookback(days)', 'Base Price index']]

common_df = pd.concat([selected_columns_df1, selected_columns_df2], join='outer', axis=1, ignore_index=False)

merged_df = pd.merge(common_df, eba_mapping[['COA', 'BS Flag', 'Rate Type']], left_on='Planning_node', right_on='COA', how='left')
merged_df = merged_df.drop(columns=['COA']).rename(columns={'BS Flag': 'A/L/O', 'Rate Type': 'COA_Type'})

# Apply the COA ccy function
merged_df = highlight_COA_ccy(merged_df)

# Display the DataFrame in the pandastable
display_df(merged_df)

# Save the output to an Excel file
output_file = file_path.replace('.xlsx', '_output.xlsx')
merged_df.to_excel(output_file, engine='openpyxl', index=False)
messagebox.showinfo("Success", f"File saved as {output_file}")

except Exception as e:
messagebox.showerror("Error", str(e))

# Function to display the DataFrame in the Tkinter console using pandastable
def display_df(df):
# Clear the previous table if it exists
for widget in output_frame.winfo_children():
widget.destroy()

# Create a new Table in the output_frame
table = Table(output_frame, dataframe=df, showtoolbar=True, showstatusbar=True)
table.show()

# Highlight rows based on logic from highlight functions
for index, row in df.iterrows():
style = highlight_missing_values(row)
style += highlight_missing_ccy(row)
style += highlight_negative_spread(row)
style += highlight_spread_validation(row)
style += highlight_explicit_validation(row)

if style:
table.getWidget().item(index).config(background=style.split(':')[-1])

# Main Tkinter window
root = Tk()
root.title("Excel Processor")
root.geometry("800x600")

# Label and Button
label = Label(root, text="Press the button to select an Excel file for processing.")
label.pack(pady=10)

process_button = Button(root, text="Select Excel File", command=process_excel)
process_button.pack(pady=10)

# Frame for output console
output_frame = Frame(root)
output_frame.pack(pady=10, fill='both', expand=True)

# Run the application
root.mainloop()

Continue reading...