Skip to content
The Chat Archive
gpt-3.5-turbo-0613· OpenAI WildChat-4.8M (AI2)

import pandas as pd from tkinter import Tk from tkinter.filedialog import…

  1. User

    import pandas as pd from tkinter import Tk from tkinter.filedialog import askopenfilenames import os

    Open a Tk window

    Tk().withdraw()

    Prompt the user to select input files

    input_files = askopenfilenames(title="Select input files", filetypes=[("Excel Files", "*.xlsx")])

    Prompt the user to select the “Staff Gl Mapping.xlsx” file

    mapping_file = askopenfilenames(title="Select Staff Gl Mapping file", filetypes=[("Excel Files", ".xlsx")])

    Prompt the user to select the “Employees Details Report old.xlsx” file

    employees_file = askopenfilenames(title="Select Employees Details Report file", filetypes=[("Excel Files", ".xlsx")])

    Read the mapping file

    mapping_df = pd.read_excel(mapping_file[0])

    Read the employees details file

    employees_df = pd.read_excel(employees_file[0]) employees_df["Position"] = employees_df["Position"].astype(str) #employees_df = employees_df.drop_duplicates(subset=["Worker Name"])

    Create an empty dataframe to store merged data

    merged_df = pd.DataFrame()

    #Read and merge the input files for file in input_files: df = pd.read_excel(file) df["Project Description"] = df["Name"].str.split("|").str[2] df["Worker Name"] = df["Name"].str.split("|").str[4] df["Current"] = df["Debit"] - df["Credit"] df = df.rename(columns={"Closing balance": "Cumulative"}) file_name, _ = os.path.splitext(os.path.basename(file)) df["Date"] = file_name merged_df = pd.concat([merged_df, df])

    Merge with the mapping file based on “Main account” column

    merged_df = pd.merge(merged_df, mapping_df[["Main account", "Name.1"]], left_on="MainAccount", right_on="Main account", how="left")

    Rename the columns

    #print(merged_df.index) merged_df = merged_df.rename(columns={ "Projects": "Project ID", "Worker": "Worker ID", "Name.1": "Name", })

    Merge with the employees details file based on “Worker Name” and “Personal Number” columns

    merged_df["Worker ID"] = pd.to_numeric(merged_df["Worker ID"], errors="coerce") employees_df["Personal Number"] = pd.to_numeric(employees_df["Personal Number"], errors="coerce") merged_df = pd.merge(merged_df, employees_df[["Personal Number", "Position", "Projects"]], left_on=["Worker ID"],right_on="Personal Number", how="left")

    Remove the unwanted columns

    unwanted_cols = ["MainAccount", "Division", "Site", "Opening balance", "Main account"] merged_df = merged_df.drop(columns=unwanted_cols)

    print(merged_df.index)

    Reorder the columns

    new_order = ["Project ID", "Project Description", "Date", "Worker ID", "Worker Name", "Name", "Current", "Cumulative", "Position", "Projects"] merged_df = merged_df[new_order]

    Write the merged dataframe to an output Excel file

    output_file = "merged_data.xlsx" merged_df.to_excel(output_file, index=False)

    print("Merged dataframe has been written to", output_file)

  2. Assistant

    The code provided reads in multiple input files (selected by the user) and merges them into a single dataframe.

    Here's a breakdown of the code:

    1. The code opens a Tk window to prompt the user to select input files. The selected files are stored in the input_files variable.
    2. The code prompts the user to select two additional files - the "Staff Gl Mapping" file and the "Employees Details Report" file. The selected files are stored in the mapping_file and employees_file variables, respectively.
    3. The mapping file (mapping_df) and employees details file (employees_df) are read in as pandas dataframes.
    4. An empty dataframe (merged_df) is created to store the merged data.
    5. For each input file, the code reads in the file as a pandas dataframe (df), manipulates the data, renames the columns, and adds it to the merged_df dataframe.
    6. The merged_df dataframe is merged with the mapping file based on the "Main account" column. The merged columns are renamed.
    7. The merged_df dataframe is merged with the employees details file based on the "Worker Name" and "Personal Number" columns.
    8. Unwanted columns are dropped from the merged_df dataframe.
    9. The column order is rearranged.
    10. The merged dataframe is saved to an output Excel file named "merged_data.xlsx".

    The script also prints the index of the merged_df dataframe and a confirmation message with the name of the output file.