Structured Data – Need Title

Having a “seeing eye” for structured data is powerful unlock both for working with AI agents and fellow humans. This blog gives a checklist and examples for the topic. This is useful whether you are the General Manager or an entry-level marketer or researcher. “Structured data” is a generic term, so I am free to lay out my own experience-based requirements to augment “make it rows/columns” or “put it into a dataframe.”

An easy misconception would be thinking that AI can just deal efficiently with data in whatever form. Analogous to whatever goes on behind this door, at University of Delaware where I did some Chemical Engineering teaching i. Yes, AI can tear through your bowl of data spaghetti and messy, poorly-curated spreadsheets, but that starts with a handicap and is a poor basis for further work and for collaboration with fellow humans. Let’s get into how to do this better (As an aside, to this day, I regret the lack of hyphens in this sign’s text. I also regret that I didn’t knock and go in to see what those inside were up to. In my defense, I was hurrying to get to an appointment.)

Why Does this Matter?

A better approach is to consider data and modeling tasks as a series of transformations on structured, data “objects” that contain variables (columns) and observations (rows). Each data value has nametags or keys that allow you to pull it out and work with it from anywhere in an application. This pipeline or series of transformations approach also lets you independently verify each step, and it is also a gateway to being able to modify a single step independently from the others. Structured data is also more collaborative for both other humans and AI agents because of the predictable-container nature of the objects that implicitly conveys how to work with what’s inside.

<schematic>

The topic of structuring applies to all software used to analyze and transform data. I’m going to use spreadsheet examples below because Excel makes it possible to do both data good and evil with its wide open sea of cells. However, these same principles apply with Python Pandas which inherently stores data in rows/columns dataframes. By my definition though, Pandas Dataframes can still be unstructured.

There are three objectives for learning and practicing a practical definition

• Learn what differentiate structured data from structured summaries.
• Learn how to add “key“ columns of name tags for structured data.
• Learn Excel and Pandas skills like filtering sorting and pivot tables that require starting with structure data

Structured Data Definition

Let’s walk through a practical definition of “structured data”, discussed with examples below

• Data are in rows by columns (observations by variables) or can be “effortlessly” transformed to rows by columns
• Each measurement type is only in a single column; all values in a measurement column correspond to the measurement type
• Rows are all the same “data granularity.” Don’t mix raw data and summary
• Table contains “key” columns whose values uniquely differentiate or “index” each row
• Each “key” variable is in its own column to enable filtering, sorting and summarization
• Tables do not contain blank rows or columns separating data into sub-blocks.
• Rows by columns tables are “homed” to top-left corner of their worksheets and are the only data element on the worksheet.
• Structured tables can always be sorted into random row order and then re-sorted back to original order using the key columns

What are key columns?

Some negative examples and how to make them right (from Digital Transformation with Excel online course…)
Create GitHub repo with a file

Structured Dataframes
Excel skills for structured data
Are pivot tables structured (no but they can get pretty close with the right settings

Excel “tables” feature
Based on my corporate experience and vast internal user research, I avoid this feature in lieu of sticking to using classic Excel and using its longstanding range name feature on tables (and I developed the Excel Steps addin to automate this). There is an Analytics community that are proponents of this. I wish them peace and success (and here is a link to a fantastic fellow consultant in this space if that’s tour path. It’s too a post where he bemoans the difficulty of xyz). My take is that learning a second syntax is too high of a mountain to climb for most corporate folks who use Excel day to day. Microsoft obviously has a strong incentive to keep you in Excel like a walled garden, but Also that complex table operations do not belong in Excel but are better managed by Python Pandas as a precursor to bringing data into Excel.