test2,py

import pandas as pd

def project_tracking(file1, file2):
    df1 = pd.read_csv(file1, delimiter = ';')
    df2 = pd.read_csv(file2, delimiter = ';')
    merged_data = pd.merge(df1, df2, on = 'project_id')

    # Allows to modify the new df directly, but removes the project_id indexing
    merged_data.set_index('project_id', inplace = True)

    # Extract project types and construction status types
    building_types = [col for col in df1.columns if col not in ('project_id', 'total')]
    statuses = [col for col in df2.columns if col not in ('project_id', 'total')]

    # Initialize new columns to 0
    for building in building_types:
        for status in statuses:
            merged_data[f"{building}_{status}"] = 0

    for idx, row in merged_data.iterrows():
        total_construction = row['total_y']  # <-- Use total from construction_rehab.csv
        # if total_construction == 0:
        #     continue  # Avoid division by zero(?)

        # Create a dict to assign ratio to constuction/rehab
        status_ratios = {
            status: row[status] / total_construction
            for status in statuses
        }

        # Apply ratios for each valid building and status
        for building in building_types:
            value = row[building]
            if value > 0:
                for status in statuses:
                    merged_data.at[idx, f"{building}_{status}"] = value * status_ratios[status]

    # Removes unneccsary rows (totals and unmerged names) 
    merged_data.drop(columns=building_types + statuses + ['total_x', 'total_y'], inplace=True)
    merged_data.reset_index(inplace = True)

    # Save result
    merged_data.to_csv("output.txt", index = False, sep = ';', header = True)

project_tracking("file4.csv", "construction_rehab2.csv")