scripts.load_dictionary module

Data Dictionary Loader Module

Processes data dictionary Excel files, intelligently splitting sheets into multiple tables based on structural boundaries and saving in JSONL format.

This module provides intelligent table detection and extraction from complex Excel layouts, automatically handling multi-table sheets, β€œignore below” markers, and duplicate column naming.

Public API

The module exports 2 main functions via __all__:

  • load_study_dictionary: High-level function to process dictionary files

  • process_excel_file: Low-level function for custom processing workflows

Key Features

  • Multi-table Detection: Automatically splits sheets with multiple tables

  • Boundary Detection: Uses empty rows/columns to identify table boundaries

  • β€œIgnore Below” Support: Handles special markers to segregate extra tables

  • Duplicate Column Handling: Automatically deduplicates column names

  • Progress Tracking: Real-time progress bars

  • Verbose Logging: Detailed tree-view logs with timing (v0.0.12+)

  • Metadata Injection: Adds __sheet__ and __table__ fields

Verbose Mode

When running with --verbose flag, detailed logs are generated including sheet-by-sheet processing, table detection results (rows/columns), β€œignore below” marker detection, and timing for sheets, tables, and overall processing.

Table Detection Algorithm

The module uses a sophisticated algorithm to detect tables:

  1. Identify horizontal strips (separated by empty rows)

  2. Within each strip, identify vertical sections (separated by empty columns)

  3. Extract each non-empty section as a separate table

  4. Deduplicate column names by appending numeric suffixes

  5. Check for β€œignore below” markers and segregate subsequent tables

  6. Add metadata fields and save to JSONL

See also

-

doc:../user_guide/usage - Usage examples and detailed tutorials

-

func:load_study_dictionary - High-level dictionary processing function

-

func:process_excel_file - Low-level custom processing

-

mod:scripts.extract_data - For dataset extraction

scripts.load_dictionary.load_study_dictionary(file_path=None, json_output_dir=None, preserve_na=True)[source]

Load and process study data dictionary from Excel to JSONL format.

Parameters:

  • file_path (Optional[str]) – Path to Excel file (defaults to config.DICTIONARY_EXCEL_FILE)

  • json_output_dir (Optional[str]) – Output directory (defaults to config.DICTIONARY_JSON_OUTPUT_DIR)

  • preserve_na (bool) – If True, preserve empty cells as None; if False, use pandas defaults

Return type:

bool

Returns:

True if processing was successful, False otherwise

scripts.load_dictionary.process_excel_file(excel_path, output_dir, preserve_na=True)[source]

Extract all tables from an Excel file and save as JSONL files.

Parameters:

  • excel_path (str) – Path to the Excel file to process

  • output_dir (str) – Directory where output JSONL files will be saved

  • preserve_na (bool) – If True, preserve empty cells as None; if False, use pandas defaults

Return type:

bool

Returns:

True if processing was successful, False otherwise

Overview

The load_dictionary module processes Excel-based data dictionaries with intelligent table detection, automatic splitting, and duplicate column handling.

Enhanced in v0.0.8:

  • βœ… Added explicit public API definition via __all__ (2 exports)

  • βœ… Enhanced module docstring with comprehensive usage examples (97 lines, 1,400% increase)

  • βœ… Verified return type hints on all functions and robust error handling

  • βœ… Code quality verified with backward compatibility preserved

Public API (2 Exports)

The module explicitly exports these 2 functions via __all__:

  1. load_study_dictionary - High-level function to process dictionary files with config defaults

  2. process_excel_file - Low-level function for custom processing workflows

This explicit API definition:

  • βœ… Provides clear separation of public vs internal API

  • βœ… Improves IDE autocomplete and type checking

  • βœ… Prevents accidental usage of private implementation details

  • βœ… Documents the stable, supported interface

Enhanced Documentation (v0.0.8)

The module docstring has been enhanced from 165 to 2,480 characters (97 lines), including:

Three Comprehensive Usage Examples:

  1. Basic Usage - Process with default configuration from config module

  2. Custom File Processing - Process custom Excel file with specific output directory

  3. Advanced Configuration - Custom NA handling for specialized use cases

Key Features Highlighted:

  • Multi-table detection: Automatically splits sheets with multiple tables

  • Boundary detection: Uses empty rows/columns to identify table boundaries

  • β€œIgnore below” support: Handles special markers to segregate extra tables

  • Duplicate column handling: Automatically deduplicates column names

  • Progress tracking: Real-time progress bars

  • Metadata injection: Adds __sheet__ and __table__ fields

Algorithm Documentation:

  • 7-step table detection process explained

  • Empty row/column boundary detection

  • Horizontal/vertical strip processing

  • Table extraction and metadata addition

Output Structure:

  • Directory tree showing multi-table output organization

  • β€œextraas” folder structure for ignored tables

  • Clear naming conventions for output files

For Complete Examples:

See the module docstring for ready-to-use code snippets that demonstrate:

  • Real-world file paths and directory structures

  • Error handling patterns

  • Expected output formats

  • Processing options (preserve_na, custom paths)

Functions

load_study_dictionary

scripts.load_dictionary.load_study_dictionary(file_path=None, json_output_dir=None, preserve_na=True)[source]

Load and process study data dictionary from Excel to JSONL format.

Parameters:

  • file_path (Optional[str]) – Path to Excel file (defaults to config.DICTIONARY_EXCEL_FILE)

  • json_output_dir (Optional[str]) – Output directory (defaults to config.DICTIONARY_JSON_OUTPUT_DIR)

  • preserve_na (bool) – If True, preserve empty cells as None; if False, use pandas defaults

Return type:

bool

Returns:

True if processing was successful, False otherwise

High-level API for loading a data dictionary Excel file.

Parameters:

  • file_path (str, optional): Path to data dictionary Excel file (defaults to config.DICTIONARY_EXCEL_FILE)

  • json_output_dir (str, optional): Directory for output JSONL files (defaults to config.DICTIONARY_JSON_OUTPUT_DIR)

  • preserve_na (bool, optional): If True, preserve empty cells as None (default: True)

Returns:

  • bool: True if processing was successful, False otherwise

Example:

from scripts.load_dictionary import load_study_dictionary

# Use config defaults
success = load_study_dictionary()

# Or specify custom paths
success = load_study_dictionary(
    file_path="data/data_dictionary.xlsx",
    json_output_dir="results/data_dictionary_mappings"
)

process_excel_file

scripts.load_dictionary.process_excel_file(excel_path, output_dir, preserve_na=True)[source]

Extract all tables from an Excel file and save as JSONL files.

Parameters:

  • excel_path (str) – Path to the Excel file to process

  • output_dir (str) – Directory where output JSONL files will be saved

  • preserve_na (bool) – If True, preserve empty cells as None; if False, use pandas defaults

Return type:

bool

Returns:

True if processing was successful, False otherwise

Process all sheets in a data dictionary Excel file.

Parameters:

  • excel_path (str): Path to Excel file

  • output_dir (str): Directory for output files

  • preserve_na (bool, optional): If True, preserve empty cells as None (default: True)

Returns:

  • bool: True if processing was successful, False otherwise

Example:

from scripts.load_dictionary import process_excel_file

success = process_excel_file(
    excel_path="data/dictionary.xlsx",
    output_dir="results/dictionary_output"
)

Table Detection

_split_sheet_into_tables

scripts.load_dictionary._split_sheet_into_tables(df)[source]

Split DataFrame into multiple tables based on empty row/column boundaries.

Parameters:

df (DataFrame) – DataFrame to split into separate tables

Return type:

List[DataFrame]

Returns:

List of DataFrames, each representing a detected table

Automatically detect and split tables within a sheet.

Algorithm:

  1. Find maximum consecutive empty rows/columns

  2. Use as threshold for splitting

  3. Handle special β€œIgnore below” markers

  4. Split at boundaries

  5. Return list of tables

Parameters:

  • df (pd.DataFrame): Input DataFrame (one sheet)

Returns:

  • list: List of DataFrames (one per table)

Example:

import pandas as pd
from scripts.load_dictionary import _split_sheet_into_tables

df = pd.read_excel("sheet.xlsx")
tables = _split_sheet_into_tables(df)
print(f"Found {len(tables)} tables")

Table Processing

_process_and_save_tables

scripts.load_dictionary._process_and_save_tables(all_tables, sheet_name, output_dir)[source]

Process detected tables, apply filters, add metadata, and save to JSONL files.

Parameters:

  • all_tables (List[DataFrame]) – List of DataFrames representing detected tables

  • sheet_name (str) – Name of the Excel sheet being processed

  • output_dir (str) – Directory where JSONL files will be saved

Return type:

None

Process and save detected tables to JSONL files.

Parameters:

  • tables (list): List of DataFrames

  • sheet_name (str): Original sheet name

  • output_dir (str): Output directory

Output Files:

  • Single table: {sheet_name}_table.jsonl

  • Multiple tables: {sheet_name}_table_1.jsonl, {sheet_name}_table_2.jsonl, etc.

Column Handling

_deduplicate_columns

scripts.load_dictionary._deduplicate_columns(columns)[source]

Make column names unique by appending numeric suffixes to duplicates.

Parameters:

columns – List of column names (may contain duplicates or NaN values)

Return type:

List[str]

Returns:

List of unique column names with numeric suffixes where needed

Handle duplicate column names by adding numeric suffixes.

Algorithm:

  1. Track column name occurrences

  2. First occurrence: Keep original name

  3. Subsequent occurrences: Add _1, _2, _3, etc.

Parameters:

  • columns (list): List of column names

Returns:

  • list: Deduplicated column names

Example:

from scripts.load_dictionary import _deduplicate_columns

columns = ['id', 'name', 'id', 'value', 'name']
result = _deduplicate_columns(columns)
print(result)
# Output: ['id', 'name', 'id_1', 'value', 'name_1']

Processing Flow

The dictionary loading follows this flow:

1. load_study_dictionary(file_path, json_output_dir)
   β”‚
   └── process_excel_file(excel_path, output_dir)
       β”‚
       β”œβ”€β”€ For each sheet in Excel file:
       β”‚   β”‚
       β”‚   β”œβ”€β”€ Read sheet: pd.read_excel(sheet_name=sheet)
       β”‚   β”‚
       β”‚   β”œβ”€β”€ _split_sheet_into_tables(df)
       β”‚   β”‚   β”‚
       β”‚   β”‚   β”œβ”€β”€ Find empty row/column boundaries
       β”‚   β”‚   β”‚
       β”‚   β”‚   β”œβ”€β”€ Check for "Ignore below" markers
       β”‚   β”‚   β”‚
       β”‚   β”‚   └── Return: [table1, table2, ...]
       β”‚   β”‚
       β”‚   └── _process_and_save_tables(tables, sheet_name, output_dir)
       β”‚       β”‚
       β”‚       └── For each table:
       β”‚           β”‚
       β”‚           β”œβ”€β”€ _deduplicate_columns(table.columns)
       β”‚           β”‚
       β”‚           β”œβ”€β”€ Create output directory: {output_dir}/{sheet_name}/
       β”‚           β”‚
       β”‚           └── Save as JSONL: {sheet_name}_table_N.jsonl

Table Detection Algorithm

The automatic table detection uses this algorithm:

  1. Find Empty Boundaries:

    # Find max consecutive empty rows
max_empty_rows = 0
for each row:
    if row is all empty:
        count_empty += 1
    else:
        max_empty_rows = max(max_empty_rows, count_empty)
        count_empty = 0

  2. Split at Boundaries:

    # Use threshold to split
threshold = max(max_empty_rows - 1, 1)
current_empty = 0
for each row:
    if row is all empty:
        current_empty += 1
        if current_empty >= threshold:
            # Split here
    else:
        current_empty = 0

  3. Handle Special Markers:

    # Check for "Ignore below" marker
for each row:
    if "ignore below" in row (case-insensitive):
        # Discard everything after this row
        break

Output Structure

The module creates this output structure:

results/data_dictionary_mappings/
β”œβ”€β”€ Sheet1/
β”‚   └── Sheet1_table.jsonl          # Single table
β”‚
β”œβ”€β”€ Sheet2/
β”‚   β”œβ”€β”€ Sheet2_table_1.jsonl        # Multiple tables
β”‚   └── Sheet2_table_2.jsonl
β”‚
└── Codelists/
    β”œβ”€β”€ Codelists_table_1.jsonl
    β”œβ”€β”€ Codelists_table_2.jsonl
    └── Codelists_table_3.jsonl

Examples

Basic Usage

from scripts.load_dictionary import load_study_dictionary
import config

# Use config defaults
success = load_study_dictionary()

# Or specify custom paths
success = load_study_dictionary(
    file_path=config.DICTIONARY_EXCEL_FILE,
    json_output_dir=config.DICTIONARY_JSON_OUTPUT_DIR
)

Process Custom Dictionary

from scripts.load_dictionary import load_study_dictionary

success = load_study_dictionary(
    file_path="my_dictionary.xlsx",
    json_output_dir="output/dictionary"
)

if success:
    print("Dictionary processing completed successfully!")
else:
    print("Dictionary processing failed!")

Read Output Tables

import pandas as pd

# Read a dictionary table
df = pd.read_json(
    'results/data_dictionary_mappings/tblENROL/tblENROL_table.jsonl',
    lines=True
)
print(df.head())

Error Handling

The module handles:

  1. Missing Sheets: Logs warning and skips

  2. Empty Sheets: Skips silently

  3. Duplicate Columns: Automatically renames

  4. Excel Format Errors: Logs error and raises

  5. Write Errors: Logs error and raises

Performance

Typical performance:

  • 14 sheets: < 1 second

  • Multiple tables per sheet: Automatic detection

  • Memory efficient: One sheet at a time

See Also

scripts.extract_data.extract_excel_to_jsonl()

Data extraction

Usage Guide

Usage examples

Architecture

Architecture details