yaml_shredder.table_generator

  1"""Generate tabular structures from nested YAML/JSON data."""
  2
  3from pathlib import Path
  4from typing import Any
  5
  6import pandas as pd
  7
  8
  9class TableGenerator:
 10    """Generate relational tables from nested data structures."""
 11
 12    def __init__(self):
 13        """Initialize the table generator."""
 14        self.tables = {}
 15        self.relationships = []
 16
 17    def generate_tables(self, data: dict[str, Any], root_table_name: str = "ROOT") -> dict[str, pd.DataFrame]:
 18        """
 19        Generate tables from nested data.
 20
 21        Args:
 22            data: Source data dictionary
 23            root_table_name: Name for the root table
 24
 25        Returns:
 26            Dictionary of table_name -> DataFrame
 27        """
 28        # Extract root-level scalar fields into root table
 29        root_data = {k: v for k, v in data.items() if not isinstance(v, (list, dict))}
 30        if root_data:
 31            self.tables[root_table_name] = pd.DataFrame([root_data])
 32
 33        # Process nested structures
 34        self._process_structure(data, root_table_name, {})
 35
 36        return self.tables
 37
 38    def _process_structure(self, obj: Any, parent_table: str, parent_keys: dict[str, Any], path: str = "") -> None:
 39        """
 40        Recursively process structure to extract tables.
 41
 42        Args:
 43            obj: Object to process
 44            parent_table: Name of parent table
 45            parent_keys: Keys from parent for foreign key relationships
 46            path: Current path in structure
 47        """
 48        if isinstance(obj, dict):
 49            for key, value in obj.items():
 50                current_path = f"{path}.{key}" if path else key
 51
 52                if isinstance(value, list) and value and isinstance(value[0], dict):
 53                    # Array of objects -> create table
 54                    table_name = self._path_to_table_name(current_path)
 55                    self._create_table_from_array(value, table_name, parent_table, parent_keys)
 56                elif isinstance(value, dict):
 57                    # Nested object -> continue traversal
 58                    self._process_structure(value, parent_table, parent_keys, current_path)
 59
 60    def _create_table_from_array(
 61        self, array: list[dict[str, Any]], table_name: str, parent_table: str, parent_keys: dict[str, Any]
 62    ) -> None:
 63        """
 64        Create a table from an array of objects.
 65
 66        Args:
 67            array: Array of objects
 68            table_name: Name for the table
 69            parent_table: Parent table name
 70            parent_keys: Parent keys for relationships
 71        """
 72        # Flatten objects and add parent foreign keys
 73        rows = []
 74        for i, item in enumerate(array):
 75            row = self._flatten_dict(item)
 76
 77            # Add parent foreign keys
 78            for parent_key, parent_value in parent_keys.items():
 79                row[f"parent_{parent_key}"] = parent_value
 80
 81            # Add row index for ordering
 82            row["_row_index"] = i
 83
 84            rows.append(row)
 85
 86        # Create DataFrame
 87        df = pd.DataFrame(rows)
 88
 89        # Store table
 90        self.tables[table_name] = df
 91
 92        # Record relationship
 93        if parent_keys:
 94            self.relationships.append(
 95                {"parent_table": parent_table, "child_table": table_name, "foreign_keys": list(parent_keys.keys())}
 96            )
 97
 98        # Process nested arrays within this array
 99        for i, item in enumerate(array):  # noqa: B007
100            item_keys = {**parent_keys}
101            # Add identifying keys from this level
102            for key in ["id", "name", "code"]:
103                if key in item:
104                    item_keys[key] = item[key]
105                    break
106
107            for key, value in item.items():
108                if isinstance(value, list) and value and isinstance(value[0], dict):
109                    nested_table_name = f"{table_name}_{key}"
110                    self._create_table_from_array(value, nested_table_name, table_name, item_keys)
111
112    def _flatten_dict(self, d: dict[str, Any], parent_key: str = "", sep: str = "_") -> dict[str, Any]:
113        """
114        Flatten nested dictionary, keeping only scalar values.
115
116        Args:
117            d: Dictionary to flatten
118            parent_key: Parent key for nested items
119            sep: Separator for nested keys
120
121        Returns:
122            Flattened dictionary
123        """
124        items = []
125
126        for k, v in d.items():
127            new_key = f"{parent_key}{sep}{k}" if parent_key else k
128
129            if isinstance(v, dict):
130                # Recursively flatten nested dict
131                items.extend(self._flatten_dict(v, new_key, sep=sep).items())
132            elif isinstance(v, list):
133                # For lists, convert to JSON string or skip
134                if v and not isinstance(v[0], dict):
135                    # Simple list - join as string
136                    items.append((new_key, ", ".join(map(str, v))))
137                # Skip lists of objects (they become separate tables)
138            else:
139                # Scalar value
140                items.append((new_key, v))
141
142        return dict(items)
143
144    def _path_to_table_name(self, path: str) -> str:
145        """
146        Convert path to table name.
147
148        Args:
149            path: Path like "actions" or "warehouse.settings"
150
151        Returns:
152            Table name
153        """
154        parts = path.replace(".", "_").split("_")
155        return "_".join(parts).upper()
156
157    def save_tables(self, output_dir: str | Path, format: str = "csv") -> None:
158        """
159        Save all tables to files.
160
161        Args:
162            output_dir: Directory to save tables
163            format: Output format ('csv', 'parquet', 'excel')
164        """
165        output_dir = Path(output_dir)
166        output_dir.mkdir(parents=True, exist_ok=True)
167
168        for table_name, df in self.tables.items():
169            if format == "csv":
170                filepath = output_dir / f"{table_name}.csv"
171                df.to_csv(filepath, index=False)
172            elif format == "parquet":
173                filepath = output_dir / f"{table_name}.parquet"
174                df.to_parquet(filepath, index=False)
175            elif format == "excel":
176                filepath = output_dir / f"{table_name}.xlsx"
177                df.to_excel(filepath, index=False)
178
179            print(f"Saved {table_name}: {len(df)} rows, {len(df.columns)} columns -> {filepath}")
180
181    def print_summary(self) -> None:
182        """Print summary of generated tables."""
183        print(f"\n{'=' * 60}")
184        print("GENERATED TABLES SUMMARY")
185        print(f"{'=' * 60}\n")
186
187        print(f"Total tables: {len(self.tables)}\n")
188
189        for table_name, df in self.tables.items():
190            print(f"Table: {table_name}")
191            print(f"  Rows: {len(df)}")
192            print(f"  Columns: {len(df.columns)}")
193            print(f"  Column names: {', '.join(df.columns[:5])}")
194            if len(df.columns) > 5:
195                print(f"    ... and {len(df.columns) - 5} more")
196            print()
197
198        if self.relationships:
199            print(f"{'-' * 60}")
200            print("RELATIONSHIPS:")
201            print(f"{'-' * 60}\n")
202            for rel in self.relationships:
203                print(f"{rel['parent_table']} -> {rel['child_table']}")
204                print(f"  Foreign keys: {', '.join(rel['foreign_keys'])}\n")