TableCanoniser: Interactive Grammar-Powered Transformation of Messy, Non-Relational Tables to Canonical Tables

Research Background and Problem

• What problems or challenges did the authors identify?
  The authors focus on how to transform "messy," non-relational tables into "normalized tables" suitable for analysis. Current tools have limited capabilities when dealing with non-axis-aligned structures (complex table structures), and verifying the correctness of transformation results is highly challenging. Additionally, existing tools generally lack support for validation requirements and fail to provide an easily understandable record of the normalization process.

• Why is this problem important?
  Non-normalized table data (e.g., spreadsheets) is prevalent in data science and information management but does not meet the structured data standards required for analysis. An automated and transparent table transformation process is critical for efficient data analysis and ensuring traceability of results. Ineffective table transformations can lead to data errors, significantly impacting analysis in business, academic research, and other fields.

• Research Motivation and Related Work
  Beyond existing programming libraries (e.g., Pandas, Tidyr packages) and interactive tools (e.g., Tableau, OpenRefine), fields like TableUnderstanding and Spreadsheet Extraction have also attempted to address this issue. However, these methods require programming expertise and provide limited support for complex table structures. Additionally, emerging large language models (LLMs) attempt to offer code generation assistance but suffer from unreliable outputs and difficulties in verification. The authors aim to overcome these limitations through a novel interactive and declarative solution.

Solution

• What methods or solutions did the authors propose?
  The authors proposed a system called TableCanoniser, which combines a declarative "match-and-extract" syntax with interactive validation tools to help users transform complex tables into normalized tables. The solution comprises three components: a declarative syntax for defining transformation logic, syntax visualization for intuitively presenting normalization rules, and an interactive application tool that supports iterative validation.

• What are the innovative aspects of this solution?

  • Axis Alignment: The authors defined axis-aligned and non-axis-aligned content and used this as a basis to classify common "messy" tables, extending support for complex table structures.
  • Declarative Syntax: More intuitive than existing programming libraries, capable of describing complex logic such as dynamic region size matching and context-based extraction.
  • Validation Support: The system provides highly transparent interactive validation tools that link inputs, outputs, and the transformation process, significantly enhancing traceability and the accuracy of the final tables.

• What are the implementation steps and key technologies used?

  • Syntax Definition: A JavaScript interface (TableCanoniserTemplate) was implemented to parse matching patterns and extraction rules.
  • Visualization Design: Various interconnected visualization tools were provided, including tree structures, bidirectional highlighting between input and output table elements, and mini-maps for matched regions.
  • Interactive Features: The user interface was built using Vue.js and other front-end frameworks, integrating syntax definition and validation functionalities into a unified application.
  • Data Processing: The system supports handling non-aligned content, such as setting dynamic region sizes and extracting multi-level sub-pattern rules.

Research Outcomes

• What specific results were achieved?
  TableCanoniser effectively handles complex table patterns, including irregular repeating blocks, context-dependent content spanning cells, and nested header hierarchies. Through case studies, such as Demo Case 1 and Demo Case 5, the system demonstrated high accuracy and scalability in real-world data.

• What advantages does it have compared to existing solutions?

  • Support for Complex Table Structures: Handles non-axis-aligned table formats, whereas existing tools are more suited for simple row-column structures.
  • Transparent Validation: Provides visual cues linking input data sources and output tables, reducing the cost of error detection.
  • Scalability: Users can customize matching and extraction logic to handle more complex data patterns without requiring significant programming expertise.

• What were the experimental or evaluation results?
  In various task scenarios (e.g., normalizing complex corporate payroll data in the case studies), TableCanoniser demonstrated operability and the ability to handle complex table inputs effectively.

• Limitations and Future Directions

  • Limitations:
    • Limited support for "messy" tables without clear patterns.
    • May appear overly complex for simple transformation tasks.
    • Currently does not directly support "exclusion region" pattern definitions.
  • Future Directions:
    • Develop new preset operations to assist users in handling more complex matching rules.
    • Enhance visual encoding to design clearer mapping overviews.
    • Explore integration with existing upstream and downstream tools, such as table extraction or automation of the normalization process.

Conclusion

The TableCanoniser system combines declarative syntax with interactive validation tools to provide a highly transparent and powerful solution for normalizing complex table data. Through real-world case studies, the system demonstrated its capability to handle diverse and complex tables, as well as its user-friendly and innovative design. By addressing the challenges of non-normalized table transformation, the system significantly improves validation support and the accuracy of final outputs.