df-merger

name	df-merger
description	Merge pandas DataFrames from multiple construction sources. Handle different schemas, keys, and data quality issues.
homepage	https://datadrivenconstruction.io
metadata	{"openclaw":{"emoji":"🐼","os":["darwin","linux","win32"],"homepage":"https://datadrivenconstruction.io","requires":{"bins":["python3"]}}}

DataFrame Merger for Construction Data

Overview

Construction projects combine data from BIM, schedules, costs, and sensors. This skill merges DataFrames from disparate sources with intelligent key matching and schema reconciliation.

Python Implementation

import pandas as pd
import numpy as np
from typing import Dict, Any, List, Optional, Tuple
from dataclasses import dataclass
from enum import Enum
from difflib import SequenceMatcher


class ():
    
    INNER =        
    LEFT =          
    RIGHT =        
    OUTER =        
    CROSS =        



 :
    
    merged_df: pd.DataFrame
    matched_rows: 
    left_only: 
    right_only: 
    merge_quality:   


 :
    

    
    COLUMN_MAPPINGS = {
        : [, , , , ],
        : [, , , ],
        : [, , , ],
        : [, , ],
        : [, , , ],
        : [, , , ],
        : [, , , ],
        : [, , , ],
        : [, , , ],
        : [, , , ]
    }

     ():
        .column_cache: [, ] = {}

     () -> []:
        

        
        common = (df1.columns) & (df2.columns)
         common:
            
             col  common:
                   col.lower()    col.lower():
                     col
             (common)[]

        
         col1  df1.columns:
             col2  df2.columns:
                 ._columns_match(col1, col2):
                     col1

         

     () -> :
        
        col1_lower = col1.lower().replace(, ).replace(, )
        col2_lower = col2.lower().replace(, ).replace(, )

        
         col1_lower == col2_lower:
             

        
         standard, variants  .COLUMN_MAPPINGS.items():
             col1_lower  variants  col2_lower  variants:
                 

        
        similarity = SequenceMatcher(, col1_lower, col2_lower).ratio()
         similarity > 

     () -> pd.DataFrame:
        
        df = df.copy()
        rename_map = {}

         col  df.columns:
            col_lower = col.lower().replace(, ).replace(, )

             standard, variants  .COLUMN_MAPPINGS.items():
                 col_lower  variants:
                    rename_map[col] = standard
                    

         df.rename(columns=rename_map)

     () -> MergeResult:
        

         harmonize:
            left = .harmonize_columns(left)
            right = .harmonize_columns(right)

        
         on    left_on    right_on  :
            common_key = .find_common_key(left, right)
             common_key  :
                 ValueError()
            on = common_key

        
        merged = pd.merge(
            left, right,
            on=on,
            left_on=left_on,
            right_on=right_on,
            how=how.value,
            indicator=,
            suffixes=(, )
        )

        
        matched = (merged[merged[] == ])
        left_only = (merged[merged[] == ])
        right_only = (merged[merged[] == ])

        
        total = (left) + (right)
        quality = (matched * ) / total  total >   

        
        merged = merged.drop(, axis=)

         MergeResult(
            merged_df=merged,
            matched_rows=matched,
            left_only=left_only,
            right_only=right_only,
            merge_quality=(quality, )
        )

     () -> pd.DataFrame:
        

          dfs:
             pd.DataFrame()

        result = dfs[].copy()

         i, df  (dfs[:], ):
            result_obj = .merge(result, df, on=on, how=how)
            result = result_obj.merged_df

         result

     () -> pd.DataFrame:
        

        matches = []

        left_values = left[left_on].dropna().unique()
        right_values = right[right_on].dropna().unique()

         lval  left_values:
            best_match = 
            best_score = 

             rval  right_values:
                score = SequenceMatcher(, (lval).lower(),
                                        (rval).lower()).ratio()
                 score > best_score  score >= threshold:
                    best_score = score
                    best_match = rval

             best_match:
                matches.append({
                    : lval,
                    : best_match,
                    : best_score
                })

        match_df = pd.DataFrame(matches)

        
        left_with_key = left.merge(match_df, left_on=left_on, right_on=, how=)
        result = left_with_key.merge(right, left_on=, right_on=right_on, how=)

         result


 ():
    

     () -> pd.DataFrame:
        

        
        

        bim_df = .harmonize_columns(bim_df)
        schedule_df = .harmonize_columns(schedule_df)

        
        result = .fuzzy_merge(
            bim_df, schedule_df,
            left_on=bim_type_col,
            right_on=schedule_wbs_col,
            threshold=
        )

         result


 ():
    

     () -> pd.DataFrame:
        

        cost_df = .harmonize_columns(cost_df)
        qto_df = .harmonize_columns(qto_df)

        
         key  [, , , ]:
             key  cost_df.columns  key  qto_df.columns:
                result = .merge(cost_df, qto_df, on=key)

                
                result.merged_df[] = (
                    result.merged_df.get(, ) *
                    result.merged_df.get(, )
                )

                 result.merged_df

        
         .fuzzy_merge(
            qto_df, cost_df,
            left_on=    qto_df.columns  qto_df.columns[],
            right_on=    cost_df.columns  cost_df.columns[]
        )