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]

    # 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:
        # Convert concated_filter to string
        concated_filter = str(concated_filter)
        
        # 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
        split_filter = cleaned_filter.split('|')
        
        # Check if there are enough values to unpack and none of them are empty
        if len(split_filter) >= 2 and all(split_filter):
            column1, column2 = split_filter[0], split_filter[1]

            # 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)
        else:
            print("Warning: Split filter does not contain enough values or contains empty values:", split_filter)

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