import pandas as pd from tkinter import Tk from tkinter.filedialog import…
- 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)
- 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:
- The code opens a Tk window to prompt the user to select input files. The selected files are stored in the
input_filesvariable. - 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_fileandemployees_filevariables, respectively. - The mapping file (
mapping_df) and employees details file (employees_df) are read in as pandas dataframes. - An empty dataframe (
merged_df) is created to store the merged data. - 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 themerged_dfdataframe. - The
merged_dfdataframe is merged with the mapping file based on the "Main account" column. The merged columns are renamed. - The
merged_dfdataframe is merged with the employees details file based on the "Worker Name" and "Personal Number" columns. - Unwanted columns are dropped from the
merged_dfdataframe. - The column order is rearranged.
- The merged dataframe is saved to an output Excel file named "merged_data.xlsx".
The script also prints the index of the
merged_dfdataframe and a confirmation message with the name of the output file. - The code opens a Tk window to prompt the user to select input files. The selected files are stored in the