Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· openaiHugging Face Suzhen/CodeChat

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

    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
    df = pd.merge(df, mapping_df[["Main account", "Name y"]], left_on="MainAccount", right_on="Main account", how="left")
    merged_df = pd.concat([merged_df, df])
    
    # Merge with the employees details file based on “Worker Name” and “Personal Number” columns
    df = pd.merge(df, employees_df[["English Name","Position","Description_4"]], 
              left_on=["Worker Name"], right_on=["English Name"], how="left")
    merged_df = pd.concat([merged_df, df])
    
    
            
    

    Remove the unwanted columns

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

    Rename the columns

    merged_df = merged_df.rename(columns={ "Projects": "Project ID", "Worker": "Worker ID", "Name y": "Name", "Description_4": "Project" })

    Reorder the columns

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

    Fill in the missing Position and Project values with the values from Worker Name

    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 prompts the user to select input files, a mapping file, and an employees details file. It then reads these files into pandas dataframes.

    Next, it creates an empty dataframe to store merged data. It reads and merges the input files one by one, adding additional columns for project description, worker name, current balance, cumulative balance, and date.

    It then merges the data with the mapping file based on the "Main account" column and with the employees details file based on the "Worker Name" column.

    Unwanted columns are removed, and the remaining columns are renamed and reordered. The merged dataframe is then written to an output Excel file.

    The code prints a message indicating that the merged dataframe has been written to the output file.

  3. User

    To above output file i need all blank rows of column "Position" and "Project" populated with respective matching "Worker Name" values for "Position" and "Project"respectively

  4. Assistant

    To populate all blank rows of the "Position" and "Project" columns with the respective matching "Worker Name" values, you can use the following code snippet:

    # Fill in the missing Position and Project values with the values from Worker Name
    merged_df["Position"].fillna(merged_df["Worker Name"], inplace=True)
    merged_df["Project"].fillna(merged_df["Worker Name"], inplace=True)
    

    You should insert this code after the line where the merged dataframe is created (merged_df = merged_df[new_order]) and before the line where the merged dataframe is written to the output Excel file (merged_df.to_excel(output_file, index=False)).

    This code uses the fillna() function from pandas to replace all the missing (NaN) values in the "Position" column with the corresponding "Worker Name" values. The same is done for the "Project" column.

    By doing this, all the blank rows in the "Position" and "Project" columns will be populated with the respective matching "Worker Name" values.

  5. User

    I need the "Position" and "Project values" of respective “Worker Name”, not the “Worker Name”. Above is printing just the “Worker Name”

  6. Assistant

    Apologies for the confusion. To populate the blank rows of the "Position" and "Project" columns with the respective matching values for each "Worker Name", you can use the following code snippet:

    # Fill in the missing Position and Project values with the respective matching values for each Worker Name
    worker_name_mapping = dict(merged_df[["Worker Name", "Position"]].values)
    merged_df["Position"].fillna(merged_df["Worker Name"].map(worker_name_mapping), inplace=True)
    
    project_mapping = dict(merged_df[["Worker Name", "Project"]].values)
    merged_df["Project"].fillna(merged_df["Worker Name"].map(project_mapping), inplace=True)
    

    This code snippet creates dictionaries (worker_name_mapping and project_mapping) using the respective columns "Worker Name", "Position", and "Project" as key-value pairs. These dictionaries are then used with the map() function to fill in the missing values in the "Position" and "Project" columns with the respective matching values based on the "Worker Name".

    This code should be inserted after the line where the merged dataframe is created (merged_df = merged_df[new_order]) and before the line where the merged dataframe is written to the output Excel file (merged_df.to_excel(output_file, index=False)).

    With this modification, the blank rows in the "Position" and "Project" columns will be populated with the respective matching values for each "Worker Name".

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.