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")
    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)

    # 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]

    # Print the type of data in the 'Concated' column
    print("Data type of 'Concated' column:", type(df_sheet2['Concated'].iloc[0]))

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

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

    # Iterate through Concated filters
    for concated_filter in concated_filters:
        # 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({'Column1': [column1],
                                                         'Column2': [column2],
                                                         '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("Dora by Team Medley")

# 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()