\r \r

Auto Estimate Generator\r

\r

Business Case\r

\r

Problem Statement\r

Manual estimate creation challenges:\r

• Time-consuming quantity mapping\r
• Inconsistent pricing rules\r
• Errors in calculations\r
• Difficulty updating estimates\r \r

Solution\r

Automated estimate generation from BIM/QTO data using configurable pricing rules and assembly mappings.\r \r

Technical Implementation\r

\r

import pandas as pd\r
from typing import Dict, Any, List, Optional, Callable\r
from dataclasses import dataclass, field\r
from enum import Enum\r
\r
\r
class ElementType(Enum):\r
    WALL = "wall"\r
    FLOOR = "floor"\r
    CEILING = "ceiling"\r
    DOOR = "door"\r
    WINDOW = "window"\r
    COLUMN = "column"\r
    BEAM = "beam"\r
    FOUNDATION = "foundation"\r
    ROOF = "roof"\r
    STAIR = "stair"\r
    MEP = "mep"\r
\r
\r
@dataclass\r
class QTOItem:\r
    element_id: str\r
    element_type: ElementType\r
    name: str\r
    quantity: float\r
    unit: str\r
    properties: Dict[str, Any] = field(default_factory=dict)\r
\r
\r
@dataclass\r
class PricingRule:\r
    rule_id: str\r
    name: str\r
    element_type: ElementType\r
    conditions: Dict[str, Any] = field(default_factory=dict)\r
    unit_cost: float = 0\r
    assembly_code: str = ""\r
    cost_breakdown: Dict[str, float] = field(default_factory=dict)\r
\r
\r
@dataclass\r
class EstimateItem:\r
    qto_element_id: str\r
    description: str\r
    quantity: float\r
    unit: str\r
    unit_cost: float\r
    total_cost: float\r
    rule_applied: str\r
    wbs_code: str = ""\r
\r
\r
class AutoEstimateGenerator:\r
    """Generate estimates from QTO data automatically."""\r
\r
    def __init__(self, project_name: str):\r
        self.project_name = project_name\r
        self.pricing_rules: List[PricingRule] = []\r
        self.qto_items: List[QTOItem] = []\r
        self.estimate_items: List[EstimateItem] = []\r
        self.unmapped_items: List[QTOItem] = []\r
\r
    def add_pricing_rule(self, rule: PricingRule):\r
        """Add pricing rule."""\r
        self.pricing_rules.append(rule)\r
\r
    def load_pricing_rules_from_df(self, df: pd.DataFrame):\r
        """Load pricing rules from DataFrame."""\r
\r
        for _, row in df.iterrows():\r
            conditions = {}\r
            if 'material' in row:\r
                conditions['material'] = row['material']\r
            if 'thickness_min' in row:\r
                conditions['thickness_min'] = row['thickness_min']\r
            if 'thickness_max' in row:\r
                conditions['thickness_max'] = row['thickness_max']\r
\r
            rule = PricingRule(\r
                rule_id=row['rule_id'],\r
                name=row['name'],\r
                element_type=ElementType(row['element_type'].lower()),\r
                conditions=conditions,\r
                unit_cost=float(row['unit_cost']),\r
                assembly_code=row.get('assembly_code', ''),\r
                cost_breakdown={\r
                    'labor': float(row.get('labor_pct', 0.4)),\r
                    'material': float(row.get('material_pct', 0.5)),\r
                    'equipment': float(row.get('equipment_pct', 0.1))\r
                }\r
            )\r
            self.add_pricing_rule(rule)\r
\r
    def load_qto_from_df(self, df: pd.DataFrame):\r
        """Load QTO items from DataFrame."""\r
\r
        for _, row in df.iterrows():\r
            properties = {}\r
            for col in df.columns:\r
                if col not in ['element_id', 'element_type', 'name', 'quantity', 'unit']:\r
                    properties[col] = row[col]\r
\r
            qto = QTOItem(\r
                element_id=str(row['element_id']),\r
                element_type=ElementType(row['element_type'].lower()),\r
                name=row['name'],\r
                quantity=float(row['quantity']),\r
                unit=row['unit'],\r
                properties=properties\r
            )\r
            self.qto_items.append(qto)\r
\r
    def find_matching_rule(self, qto_item: QTOItem) -> Optional[PricingRule]:\r
        """Find pricing rule that matches QTO item."""\r
\r
        matching_rules = []\r
\r
        for rule in self.pricing_rules:\r
            if rule.element_type != qto_item.element_type:\r
                continue\r
\r
            # Check conditions\r
            match = True\r
            for key, value in rule.conditions.items():\r
                if key.endswith('_min'):\r
                    prop_name = key[:-4]\r
                    if prop_name in qto_item.properties:\r
                        if qto_item.properties[prop_name] \x3C value:\r
                            match = False\r
                elif key.endswith('_max'):\r
                    prop_name = key[:-4]\r
                    if prop_name in qto_item.properties:\r
                        if qto_item.properties[prop_name] > value:\r
                            match = False\r
                else:\r
                    if key in qto_item.properties:\r
                        if qto_item.properties[key] != value:\r
                            match = False\r
\r
            if match:\r
                matching_rules.append(rule)\r
\r
        # Return most specific rule (most conditions)\r
        if matching_rules:\r
            return max(matching_rules, key=lambda r: len(r.conditions))\r
        return None\r
\r
    def generate_estimate(self) -> Dict[str, Any]:\r
        """Generate estimate from QTO items."""\r
\r
        self.estimate_items = []\r
        self.unmapped_items = []\r
        total_cost = 0\r
\r
        for qto in self.qto_items:\r
            rule = self.find_matching_rule(qto)\r
\r
            if rule:\r
                item_cost = qto.quantity * rule.unit_cost\r
\r
                self.estimate_items.append(EstimateItem(\r
                    qto_element_id=qto.element_id,\r
                    description=f"{qto.name} ({rule.name})",\r
                    quantity=qto.quantity,\r
                    unit=qto.unit,\r
                    unit_cost=rule.unit_cost,\r
                    total_cost=round(item_cost, 2),\r
                    rule_applied=rule.rule_id,\r
                    wbs_code=rule.assembly_code\r
                ))\r
                total_cost += item_cost\r
            else:\r
                self.unmapped_items.append(qto)\r
\r
        return {\r
            'project': self.project_name,\r
            'total_qto_items': len(self.qto_items),\r
            'mapped_items': len(self.estimate_items),\r
            'unmapped_items': len(self.unmapped_items),\r
            'mapping_rate': round(len(self.estimate_items) / len(self.qto_items) * 100, 1) if self.qto_items else 0,\r
            'total_cost': round(total_cost, 2),\r
            'items': self.estimate_items\r
        }\r
\r
    def get_cost_by_element_type(self) -> Dict[str, float]:\r
        """Get cost breakdown by element type."""\r
\r
        by_type = {}\r
        for qto in self.qto_items:\r
            for est_item in self.estimate_items:\r
                if est_item.qto_element_id == qto.element_id:\r
                    type_name = qto.element_type.value\r
                    by_type[type_name] = by_type.get(type_name, 0) + est_item.total_cost\r
\r
        return {k: round(v, 2) for k, v in by_type.items()}\r
\r
    def get_unmapped_summary(self) -> pd.DataFrame:\r
        """Get summary of unmapped items."""\r
\r
        if not self.unmapped_items:\r
            return pd.DataFrame()\r
\r
        data = []\r
        for item in self.unmapped_items:\r
            data.append({\r
                'Element ID': item.element_id,\r
                'Type': item.element_type.value,\r
                'Name': item.name,\r
                'Quantity': item.quantity,\r
                'Unit': item.unit,\r
                'Properties': str(item.properties)\r
            })\r
\r
        return pd.DataFrame(data)\r
\r
    def export_to_excel(self, output_path: str) -> str:\r
        """Export estimate to Excel."""\r
\r
        result = self.generate_estimate()\r
\r
        with pd.ExcelWriter(output_path, engine='openpyxl') as writer:\r
            # Summary\r
            summary_df = pd.DataFrame([{\r
                'Project': self.project_name,\r
                'Total QTO Items': result['total_qto_items'],\r
                'Mapped Items': result['mapped_items'],\r
                'Unmapped Items': result['unmapped_items'],\r
                'Mapping Rate %': result['mapping_rate'],\r
                'Total Cost': result['total_cost']\r
            }])\r
            summary_df.to_excel(writer, sheet_name='Summary', index=False)\r
\r
            # Estimate items\r
            items_df = pd.DataFrame([{\r
                'Element ID': item.qto_element_id,\r
                'Description': item.description,\r
                'Quantity': item.quantity,\r
                'Unit': item.unit,\r
                'Unit Cost': item.unit_cost,\r
                'Total Cost': item.total_cost,\r
                'WBS': item.wbs_code,\r
                'Rule': item.rule_applied\r
            } for item in self.estimate_items])\r
            items_df.to_excel(writer, sheet_name='Estimate', index=False)\r
\r
            # By element type\r
            by_type_df = pd.DataFrame([\r
                {'Element Type': k, 'Cost': v}\r
                for k, v in self.get_cost_by_element_type().items()\r
            ])\r
            by_type_df.to_excel(writer, sheet_name='By Type', index=False)\r
\r
            # Unmapped items\r
            unmapped_df = self.get_unmapped_summary()\r
            if not unmapped_df.empty:\r
                unmapped_df.to_excel(writer, sheet_name='Unmapped', index=False)\r
\r
        return output_path\r
\r
    def suggest_missing_rules(self) -> List[Dict[str, Any]]:\r
        """Suggest pricing rules for unmapped items."""\r
\r
        suggestions = []\r
        seen_types = set()\r
\r
        for item in self.unmapped_items:\r
            key = (item.element_type.value, str(item.properties))\r
            if key not in seen_types:\r
                seen_types.add(key)\r
                suggestions.append({\r
                    'element_type': item.element_type.value,\r
                    'sample_name': item.name,\r
                    'properties': item.properties,\r
                    'count': sum(1 for i in self.unmapped_items\r
                                if i.element_type == item.element_type\r
                                and str(i.properties) == str(item.properties))\r
                })\r
\r
        return sorted(suggestions, key=lambda x: x['count'], reverse=True)\r
```\r
\r
## Quick Start\r
\r
```python\r
# Initialize generator\r
generator = AutoEstimateGenerator("Office Building A")\r
\r
# Add pricing rules\r
generator.add_pricing_rule(PricingRule(\r
    rule_id="W-001",\r
    name="Interior Wall - Drywall",\r
    element_type=ElementType.WALL,\r
    conditions={"material": "Drywall"},\r
    unit_cost=45.00,\r
    assembly_code="09.29.10"\r
))\r
\r
generator.add_pricing_rule(PricingRule(\r
    rule_id="W-002",\r
    name="Exterior Wall - Masonry",\r
    element_type=ElementType.WALL,\r
    conditions={"material": "Masonry"},\r
    unit_cost=125.00,\r
    assembly_code="04.21.13"\r
))\r
\r
# Load QTO data\r
generator.qto_items = [\r
    QTOItem("W-001", ElementType.WALL, "Interior Wall L1", 500, "SF", {"material": "Drywall"}),\r
    QTOItem("W-002", ElementType.WALL, "Exterior Wall", 1200, "SF", {"material": "Masonry"})\r
]\r
\r
# Generate estimate\r
result = generator.generate_estimate()\r
print(f"Total Cost: ${result['total_cost']:,.2f}")\r
print(f"Mapping Rate: {result['mapping_rate']}%")\r
```\r
\r
## Common Use Cases\r
\r
### 1. Cost by Element Type\r
```python\r
by_type = generator.get_cost_by_element_type()\r
for element_type, cost in by_type.items():\r
    print(f"{element_type}: ${cost:,.2f}")\r
```\r
\r
### 2. Unmapped Items\r
```python\r
unmapped = generator.get_unmapped_summary()\r
print(unmapped)\r
```\r
\r
### 3. Rule Suggestions\r
```python\r
suggestions = generator.suggest_missing_rules()\r
for s in suggestions:\r
    print(f"Need rule for: {s['element_type']} ({s['count']} items)")\r
```\r
\r
## Resources\r
- **DDC Book**: Chapter 3.2 - QTO and Automated Estimates\r
- **Website**: https://datadrivenconstruction.io\r