Untitled

import pandas as pd
import tkinter as tk
from tkinter import filedialog, simpledialog

def display_information():
    # Display text prompt for required columns
    info_text.config(state=tk.NORMAL)
    info_text.delete('1.0', tk.END)
    info_text.insert(tk.END, "Please make sure the selected Excel sheet 1 has the following columns:\n"
                              "1. Software Features\n"
                              "2. Requirement(s)\n"
                              "3. Specification(s)\n\n"
                              "The second Excel sheet 2 should have columns:\n"
                              "- Column1\n"
                              "- Column2\n\n"
                              "The third Excel sheet 3 should have columns:\n"
                              "- Software Features\n"
                              "- Column2\n"
                              "- Column3")
    info_text.config(state=tk.DISABLED)

def process_files():
    # Prompt to choose the first Excel sheet
    path_to_sheet1_excel = filedialog.askopenfilename(
        title="Step 1: Choose Excel Sheet with Columns",
        filetypes=[("Excel Files", "*.xlsx;*.xls")]
    )
    
    # Load data from the first Excel sheet
    df_sheet1 = pd.read_excel(path_to_sheet1_excel)

    # Prompt to choose the second Excel sheet
    path_to_sheet2_excel = filedialog.askopenfilename(
        title="Step 2: Choose Second Excel Sheet with Columns Column1 and Column2",
        filetypes=[("Excel Files", "*.xlsx;*.xls")]
    )

    # Load data from the second Excel sheet
    df_sheet2 = pd.read_excel(path_to_sheet2_excel)

    # Prompt to choose the third Excel sheet
    path_to_sheet3_excel = filedialog.askopenfilename(
        title="Step 3: Choose Third Excel Sheet with Columns Software Features, Column2, and Column3",
        filetypes=[("Excel Files", "*.xlsx;*.xls")]
    )

    # Load data from the third Excel sheet
    df_sheet3 = pd.read_excel(path_to_sheet3_excel)

    # If "Concated" column doesn't exist in the second sheet, create it
    if 'Concated' not in df_sheet2.columns:
        # Prompt to choose the columns for concatenation
        column1_name = simpledialog.askstring("Dora", "Enter the name of the first column:")
        column2_name = simpledialog.askstring("Dora", "Enter the name of the second column:")

        # Concatenate specified columns and create "Concated" column
        df_sheet2['Concated'] = df_sheet2[column1_name] + '|' + df_sheet2[column2_name]

    # If "Concated" column doesn't exist in the third sheet, create it
    if 'Concated' not in df_sheet3.columns:
        # Prompt to choose the columns for concatenation
        column2_name = simpledialog.askstring("Dora", "Enter the name of Column2:")
        column3_name = simpledialog.askstring("Dora", "Enter the name of Column3:")

        # Concatenate specified columns and create "Concated" column
        df_sheet3['Concated'] = df_sheet3['Software Features'] + '|' + df_sheet3[column2_name] + '|' + df_sheet3[column3_name]

    # Extract Concated filters from "Concated" column of Sheet2
    concated_filters_sheet2 = df_sheet2['Concated'].tolist()

    # Extract Concated filters from "Concated" column of Sheet3
    concated_filters_sheet3 = df_sheet3['Concated'].tolist()

    # Create an empty dataframe to store the results
    result_df = pd.DataFrame(columns=['Sheet', 'Column1', 'Column2', 'Column3', 'Concated', 'Features'])

    # Process the second sheet
    for concated_filter in concated_filters_sheet2:
        # Clean the Concated filter to remove non-printable characters
        cleaned_filter = ''.join(char for char in concated_filter if char.isprintable())

        # Split the cleaned Concated filter
        column1, column2 = cleaned_filter.split('|')

        # Filter based on Column1 and Column2 in the first sheet
        filter_condition = (df_sheet1['Requirement(s)'].str.contains(column1)) & (df_sheet1['Specification(s)'].str.contains(column2))
        filtered_df = df_sheet1[filter_condition].copy()

        # Get unique features for the Concated value
        features = ', '.join(filtered_df['Software Features'].unique())

        # Append to the result dataframe
        result_df = pd.concat([result_df, pd.DataFrame({'Sheet': ['Sheet2'],
                                                         'Column1': [column1],
                                                         'Column2': [column2],
                                                         'Column3': ['N/A'],
                                                         'Concated': [concated_filter],
                                                         'Features': [features]})], ignore_index=True)

    # Process the third sheet
    for concated_filter in concated_filters_sheet3:
        # Clean the Concated filter to remove non-printable characters
        cleaned_filter = ''.join(char for char in concated_filter if char.isprintable())

        # Split the cleaned Concated filter
        software_feature, column2, column3 = cleaned_filter.split('|')

        # Filter based on Software Feature, Column2, and Column3 in the third sheet
        filter_condition = (df_sheet3['Software Features'].str.contains(software_feature)) & (df_sheet3['Column2'].str.contains(column2)) & (df_sheet3['Column3'].str.contains(column3))
        filtered_df = df_sheet3[filter_condition].copy()

        # Get unique features for the Concated value
        features = ', '.join(filtered_df['Software Features'].unique())

        # Append to the result dataframe
        result_df = pd.concat([result_df, pd.DataFrame({'Sheet': ['Sheet3'],
                                                         'Column1': ['N/A'],
                                                         'Column2': [column2],
                                                         'Column3': [column3],
                                                         'Concated': [concated_filter],
                                                         'Features': [features]})], ignore_index=True)

    # Display the resulting dataframe
    result_text.config(state=tk.NORMAL)
    result_text.delete('1.0', tk.END)
    result_text.insert(tk.END, result_df.to_string(index=False))
    result_text.config(state=tk.DISABLED)

    # Save result to Excel
    result_df.to_excel('output.xlsx', index=False)

# Create the main application window
app = tk.Tk()
app.title("Excel Sheet Processor")

# Display information text
info_text = tk.Text(app, height=10, width=80, state=tk.DISABLED)
info_text.pack(padx=10, pady=10)
display_information()

# Create buttons and text widget
process_button = tk.Button(app, text="Process Files", command=process_files)
result_text = tk.Text(app, height=10, width=80, state=tk.DISABLED)

# Pack buttons and text widget
process_button.pack(pady=10)
result_text.pack(padx=10, pady=10)

# Start the main loop
app.mainloop()