The Toughest Data Problem

The toughest data and modeling task, as measured by time spent, is usually (drumroll) not logistic or multivariate regression or control charting or building complex visuals or even machine learning. It is just getting data out of unstructured but repeatable forms and into structured tables where the analysis and learning can occur. Here I describe how I simplify the “janitor work” and put the focus back on getting insights from modeling and visualization of business or technical data.

If data come from a database, it can be queried to give a rows/columns table for exploratory analysis and model building. However, this is often not the situation. Using my code toolboxes for scripts, I just need to populate a “parsing dictionary” with the details about the data format such as the name of the “shape” of the data, how will my script locate each data block’s start and end and how to pull in metadata values that identify each block or other data arrangements. The scripts are completely modular, so it is easy to develop named “parsers” for new data shapes when I encounter them in client work.

You don’t need to be a coding guru to appreciate the elegance that the two lines below can mass-import unstructured data from 100’s of files and flip everything into a single table for further analysis.**

The challenge here is that humans creatively output data into formats or shapes that are repeatable but which are not rows/columns. Below is an example from retail promotions vendor, Ibotta, whose data I used to build a regression model for how ad and promotion spending affect a CPG client’s brand sales. That’s a business use case, but such data rearrangement is also a common need for technical lab data output from measurement instruments or data output by manufacturing production systems.

The format below is VISUALLY appealing. Ironically, I also independently encountered this “Interleaved Column Block” format from a 3rd party, Shopify reporting company whose data I work with on a different client. Net, it is an intuitive design but not what’s needed for modeling. The row locations and number of metadata columns at left can vary as can the number of columns in each repeating block, but the “shape” of the data is the same. It is repeating column blocks of the same measures with a heading at the top and explanatory columns along the left. If you have 50 such files to corral, what do you do? Simply start copy/pasting? That is not a good answer!

To transform this into a structured table, we need to:

• Propagate the Offer Group Name and Retailer Name metadata columns at the left into columns adjacent to rearranged data values
• Populate a Date column by unmerging cells (argghh Excel merged cells!) and propagating the dates that start out above the groups of interleaved Units Redeemed and Redemption Budget columns.
• Rearrange the data by blocks into side-by-side Units Redeemed and Redemption Budget columns.
• Omit the TOTAL row at the bottom since it is a summary statistic.
• Rename the resulting columns to modeling friendly names and names that will not be confusing once this data is merged with other inputs.

Our toolbox makes quick work of this. It takes just minutes to set up and run***. We give the script a dictionary of parameters starting with the name of the shape (e.g. “InterleavedColBlocks” in script lingo) and then shape-specific inputs like how many metadata columns are at left, what row contains the block heading (e.g. dates), what is the first data row and the measure names, how many interleaved columns per block and how to know the end of the data (e.g. the row with “TOTAL” in Column B).

Once the data are transformed into rows/columns, we can efficiently merge by Brand, Retailer and Date with separate Ad Spending and Sales data to model how these inputs affect each brand’s Units Sold. Specifically in this case, I built predictive regression models by brand/SKU and by retailer for my client. These helped disentangle seasonal versus promotion versus ad-driven effects on sales.

Yes. We could have simply asked the vendor for rows/columns data. In fact, we did with a polite email, but, as is common, that can grind things to a halt because of slow responsiveness and infeasibility of changing things up. Responses vary:

• “We have many clients who like this format for looking at their data. I’m sorry but we can’t make a change just for you.”
• “We had an intern create that report format two years ago. We don’t know how to change it now that the intern is not here”
• “That’s the validated format this lab instrument outputs. We could consider changing it the next time we upgrade the software, which we do every 2-3 years.”

Net, it is a huge advantage to have flexibility to work with data as it comes! Let me know if I can help you with yours!

_____

** If you are into the code, see the the demo.ipynb file in this repository. The demo links to and shows examples with our toolbox source code.

*** In our new age of AI-coding, how to set this up is an AI Skill.md file as is how to create capability for a new raw data format.