Wringing Data Out of PDFs

What happens if the data you need to analyze comes in PDF forms? This is what I tackled and solved last week. I wrote recently about the frequent need to work with data in the format it comes, but the examples in that blog were already spreadsheet data albeit requiring rearrangement to ingest for analysis. PDF's add a different hurdle to the task. Do you have situations like this with your data? Reach out if I can help (and read on for a few details about this "PDF Parsing" solution).

A client of mine gets auto-generated monthly PDF reports on her clients' businesses where each report consists of numerous pages. The pages give the overall summary (Page 1) followed by breakdowns by business sub-categories (thankfully with the same format on each page). No matter how familiar such a report might get, it is easy to miss trends in the data. Getting just PDF tables each month means no ability to graph or re-summarize the data; data are scattered across the report's pages making it difficult to hold a whole picture in mind. The format also makes it impossible to combine data across months or across businesses to see bigger trends. Yes. We are working on getting it as spreadsheet data, but in the mean time...

To illustrate, here is a PDF page mockup for a hypothetical, Boulder CO store where each page is the data on a product type like Jackets, Camping Gear etc. My solution for this translates the multi-page PDF's like this into spreadsheet tables that can be used for whole business summaries like the Sales Summary example below or for graphing the data to uncover trends (my mockup PDF has two pages. The real data data are 40+)

Technical Learning from Creating this Solution

AI Tools Are Getting Better and Better

When used within a controlled work process (and that's a very important caveat!), current AI tools are reaching amazing levels. I attacked and solved the above problem with Claude Code. Within a few hours, with me guiding and approving and it doing the legwork, I added PDF as a supported file format in my toolbox. Claude and I created the Trailhead mockup. We then validated a demonstration project of extracting its data to an overall Dataframe and summaries. Importantly, the scripts are generically configurable and work with my client's more complex report format that contains 500+ values per page versus 20 or so for the mockup. Per the caveat, AI would give nothing but dysfunction here without the strong guidance of being fed references including my toolbox code and Claude skills describing my extensive general expectations and example code style for projects.

My "How I AI" System Makes it Go

To kick this off, I fed it a prompt with a few sentences describing the goal and an example of my client's actual, 48-page report with its 20,000+ values to import and reshape. I asked it to author an overall "Change" document per the template in my "python-project-changes" AI skill. Claude suggested an 8-part plan including the Trailhead mockup. We tweaked this a bit and then worked each of the 8 parts through using my "How I AI" system that allows me to collaboratively work with it and review what it does and how it does it.

Open-Source Wins Again

Claude brought the PDFPlumber open-source code library to my attention and we did a quick exploration with that on the full-scale report. That was convincing that PDFPlumber could use the colored, header blocks on a form to locate sub-tables. As an aside, there are other wayfinding options in reserve such as using specific words. Here is the Trailhead page with its identifying highlights from the PDFPlumber layout. Claude wrote a go-between pdfplumber_tools.py module to add to my toolbox. It accesses PDFPlumber and honors my toolbox' existing architecture that imports and then parses raw data.

The resulting, raw, parsed table is something that can be adapted to any data analysis and visualization purpose. It uses page_number as a key and merges in category from the page header. The sharp-eyed reader will note that we created a solution for merged cells such as the "Floor Staff" example in the bottom table. This overall table is a jumping off point for subsetting and pivoting to look at category highlights, sales trends and staffing allocations.

by J.D.

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.

by J.D.

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.

by J.D.

How I "AI" for Consulting Projects

I now use AI constantly for client projects. I'm sharing this detailed "how-to" as an inspiration for fellow modelers. It's easy to feel a sense of "AI hopelessness" relative to those out there who have hit the seventh level. I follow Boris Cherny on Twitter as a classic example. Yes. The Claude guy. I learn from people like him, but I do not have dozens of agents in motion orchestrated from my iPhone like he does. However, I use AI agents to crank out high quality work for clients in the form of data pipelines, models and dashboards that can organize, predict and visualize their situations. That means moving in the direction of, but staying back from, the bleeding edge.

This is evolving quickly, but here is my current system for Python or VBA projects to create solutions for clients. My big AI-related theme over the past year is much greater emphasis on pre-planning. For all recent changes done in concert with an AI agent, I now possess a specification document co-written with the agent documenting details of what was done either starting from scratch or from modifying a client's existing tools. My efficiency gain from using AI is a huge leap versus the old way.

  • The AI coding agent writes my code using VS Code and its chat+context files harness
  • AI also writes validation tests that certify correctness of project code.
  • I use markdown format notes in the Obsidian app to make AI-readable project background for the agent. I expose these to AI situationally with "read this" instructions in VS Code Copilot chat and in high-level Obsidian instruction notes.
  • For bigger projects, I create an Obsidian graph subfolder (graph = linked, single-topic notes) with project background for me and the agent. I version control this as a separate Github repository from the project and test code.
  • I keep close supervision of AI-generated work including personal, detailed review of code and other outputs the AI generates. I also run/debug the validation tests myself.
  • I use a modular, well-described architecture for projects. It represents data and reports as structured data "objects" aka tables and scenario models that include descriptive metadata about the objects and their variables in addition to the actual data.
  • I use instructions and skills files to ground the the coding gent with "this is how we do things around here" for general and specific coding practices. The skills *.md files are also where I correct negative behaviors by the agent by asking it to write down "how will we avoid doing things this way in the future".
  • To get ready for coding, the agent and I interact using Matt Pocock's outstanding and elegant Grill-me skill. We jointly create a “Change_” specification Obsidian note that was my agreement with the agent on the work to be done. We create this jointly before turning the agent loose on coding. This specification then serves as permanent documentation of the change and how it was executed.

Obsidian for Background Context
For work projects, I store all relevant notes in one subfolder in my Obsidian Vault that I host on the cloud to make it available across my laptop and other devices. Since an AI agent is doing the actual coding, this enables working on the requirements development while sipping coffee in Starbucks with just an iPhone in tow.

Context files are key to moving beyond simple chat into agent-based AI development. I love the free Obsidian app for both personal and work note keeping. Obsidian recently replaced Evernote as my personal note-keeping solution. It stores each note as an open-format, text file in markdown format. It is amazing how this can supersede using Microsoft Word for many purposes.

VS Code and Its Copilot Chat as a Harness
I use VS Code as the overall work environment aka IDE. It is free, but Copilot Chat within it is a nominal monthly charge for access to Claude and OpenAI coding agents. I currently pay $40 per month for that access to premium models after experience showed this to be worthwhile. I use a consistent src and tests subfolder arrangement for all projects with src containing the project files and a tests/test_data subfolder for validation data. My standard code toolbox lets me instantly reorient execution to use of either validation or production data as inputs.

The screen picture shows my VS Code environment for the Excel Steps open-source add-in project. The chat window shows an example interaction with the agent. I use Github for version control. VS Code allows me to instance a repository and commit changes to the project files. I include the project's Obsidian graph/notes folder in the VS Code workspace, and I version control it with a separate Github repo from the project files. This graph folder is the ExcelSteps_Graph twistie at the bottom of the Explorer tree at left. All other files are in the Excel_Steps project folder

Feeding Foundational Context to the Coding Agent
VS Code and the AI models are built to access standard background files visible in the Explorer file tree at the left of the screen picture above. Within the project's .github folder, copilot-instructions.md contains the basic instructions for how I want the agent to do things. Here I describe the data structures and basic coding and testing practices to use. My Python and VBA instructions include function and test function templates to standardize those aspects. This is where I set the tone that we develop highly modular code based on single-action functions in a predictable architecture. It also describes my preferred error handling approach.

Because it is a bad idea to "overfeed" context to the agent, skills.md files work in concert with copilot-instructions.md to guide the agent on additional topics. The skill format includes lines of YAML "frontmatter" (within the "---" fences in the example below) that the agent keeps resident in memory. It then accesses the document's details when relevant. This example corrected an overly-verbose coding practice by the agent and quite simply got it doing things in a way that I wanted it to.

Test-Based Validation
I use Pytest tests for projects in Python and a test suite workbook I wrote for VBA (example in Excel_Steps repo). I do not pre-write tests nor do I expect the agent to do so, but I always ask the agent to write tests that validate the project code. I involve myself personally in running them to check the quality. Testing is a relatively uncommon practice among working engineers, scientists and consultants. In my opinion, that's a mistake. With what AI agents can do, there is no excuse for not generating test code to validate.

It is invaluable to break things up into single-action functions and have at least one test per function to prove execution as expected. In Python, Brian Okken's book, Python Testing with Pytest is an excellent tutorial. In VBA, my VBA test suite workbook gets co-developed with the project and can execute all project macros to check their outputs and actions on a workbook. My respect for this approach dates to excellent mentoring by a former P&G colleague and Computer Scientist, Mike Hilton. In the early 2000's, he dazzled me with Excel test suite macros that could inspect and compare worksheets relative to a standard. Such testing can be rerun whenever changes are made to avoid introducing errors in complex projects.

While my copilot-instructions.md sets the expectation and code templates for testing, this can sometimes be an afterthought for the AI agent. Here is an example interaction to prod it to consider how testing will be set up in a specification for a change.

Spec Document from Grill Me Session
Matt Pocock's grill-me skill has just a few sentences, and its effectiveness made it one of the most popular items in the global Github/developer community. It asks the agent to "Interview me relentlessly about every aspect of this plan until we reach a shared understanding". My link here is to one of his skills repo's subfolders, but this entire repo and his Youtube channel are worth browsing. He is a clear-spoken voice on use of AI for coding.

I feed the agent an initial, high-level background paragraph describing the project. It includes relevant "Read this background Obsidian note/code file" instructions to ground the agent in previous work. I then ask it to grill me with questions per the skill. Once that runs its course, the agent writes a Changexyz note according to a template I feed it in another, project-changes skill I wrote (vba-project-changes is an example in the Excel_Steps project). I typically do not even read the Change note in great detail. The agent generates it as a summary of our back and forth. My experience is that it does a great job doing so.

Executing the Change
Once the Change_ document is complete, I authorize the agent to make the change/write the code. In the example below, it generated approximately 1000 lines of project and test code while modifying 9 files/modules. Next step: I run the tests and ensure things are working. If there are outages, they often stem from giving the agent incomplete background such as an incomplete description of complex input data.

A Note About Developing in VBA
Developing in VBA requires a special solution, but it is a good one. Excel is a standard for many of my clients and a bedrock in my project team experiences at P&G. Developing in its VBA macro language has been critical to making excellent user interfaces for clients. However, VS Code was built for Python (and many other language) development but not for VBA directly. It cannot directly access Excel files.

There is a workaround. Traditionally, we access the Excel VBA editor and run macros/code from within an open Excel workbook. The editor is literally unchanged since the mid 1990's, so it is truly ancient albeit highly useful tech. Felix Zumstein's brilliant extension to the xlwings open source package syncs code from the VBA editor into VS Code. This gives the AI agent access to do its work, and current models know VBA very well. My screen pic above shows a terminal at bottom of the screen for invoking this. I keep separate Terminals open for the project and test workbooks and run separate xlwings vba edit sessions for each. This Youtube video by Felix gives a demo of how this works. To note, xlwings vba edit is Windows only. That necessitates using Windows VS Code. I was happy to switch to that using OSX/Parallels to take advantage of the capability.

by J.D.

Data "Straightening": Our Toolbox is Ready to Help

My 6-year journey with Data Delve LLC consulting has had the (very intentional) side effect of building a suite of generic tools. These are useful and cost-effective for helping small to medium sized businesses with what I call "data straightening" applications. I'm now offering a "data tuneup" check at a fixed cost to help small and medium sized businesses assess their opportunities. If you want to move forward, the toolbox and latest AI tools are waiting to help. Reach out to me if you have an application you want to discuss.

The Opportunity

Data expertise is great, but, in this case, it comes with decades of experience working with project owners to tease out what's needed and create solutions. Often, what people need is a cost-efficient onramp for getting their data organized, analyzed and in a form where they can consider whether to move into further, digital transformation steps.

Do you have such applications? Here are some questions to consider if you run a small to medium-sized business:
* Do you have key data in a collection of spreadsheets stashed on employees' computers or in small databases?
* Do you have technical, lab data or business data workflows that involve repetitively copy/pasting data to create outputs?
* Do you know of lab or business data calculations that could be automated to make them efficient and less error prone?

And finally, the most current topic these days:
* Do you have data that you would love help getting organized so you can try out asking AI for insights about it?

If the answer is "yes". I can help, and I bring a consulting-derived toolbox and experience to bear in efficiently creating solutions.

A Toolbox for Efficiency

A core strategy of Data Delve from its founding in 2019 has been creating proprietary solutions for clients but doing so while also developing and bringing along reapplicable "open source" tools for doing the generic parts. Although every project is unique, I noticed that what goes on behind the scenes is oh so common.
* Everyone has rows/columns data tables to import, transform and link. It makes sense to have a world class toolbox for that.
* Spreadsheets are pervasive and make an ideal user-facing "dashboard" for working with many lab and business data situations. (Consulting experience bears out that the president, GM or R&D leader usually wants to see things in Excel....). The example below (Columns B to F) is a toolbox "object" that can be instantly configured onto a template's Home or Settings sheet that also houses the buttons to make things happen. I call this a Scenario Model object. It's refreshable with VBA to install preset formulas, and the object takes care of formatting and setting/refreshing both user-facing range names and programmatic way finding so that individual setting values are available for calculations throughout the application. The object can also sense its extent, so adding new settings is a breeze.

Our code base is in two, common languages. We have a VBA toolbox as illustrated above for organizing and curating spreadsheet data and building Excel "applications" and "dashboards". We also have well-organized, Python language tools for situations where that makes sense (and it often does). Within these, there are premade toolsets for building automated pipelines to bring in data that were formerly copy/pasted or manually downloaded from internal or 3rd party sources. We also have premade tools that can be configured to graph data to visualize what's going to augment just looking at a sea of numbers.

In parallel to Data Delve's progress, AI tools have progressed to the point where the AI can understand our code and macros and apply them to new problems. I am comfortable how to feed that "this is how we do things around here" context into the AI development process as copilot-instructions and "skills". This makes an efficient combination for helping companies with the data/spreadsheet straightening tasks.

A Case Study

A business process case study gives an example of the opportunity inherent in digital transformation. My expertise for putting these pieces together is not new, but the Data Delve toolbox enables this immensely. The images illustrate a case study from our online training course. It is inspired by "big company" P&G experience but is a good illustration of what is possible with even a giant application like a diaper business platform's R&D work.

The images below represent the before and after. The R&D data system existed initially (first image) as standalone spreadsheets and unstructured lists etc. owned by various functional experts. They had to "query" each other by chat, email and in person meetings to connect the pieces.

We took it to an interconnected system where we could instantly answer basic questions like, "Which new products will use the XYZ raw material?" and "What's a model for how much total raw material cost our development program will incur based on the number of test products we plan to run?" That's a great digital transformation that gave immediate benefits to the team and demonstrated the value.

Has P&G progressed beyond what I started for Pampers R&D data? Certainly yes, but that's the point. We created a low cost on ramp for doing that, and we did it in a practical way that was immediately useful to a team of folks collaborating to get new products and manufacturing systems launched.

by J.D.

Data And Modeling Tools - Training Beyond the Basics

It's a challenge to build a personal toolset for working routinely with business and technical data and for doing more advanced analytics and modeling. This blog is an opinionated but hopefully well-informed guide to that. The opinions are based on my decades of corporate experience combined with consulting experience. Along the way, I created a series of tools and trainings to help people learn best practices for working with data. The coded tools also comprise our starters for client projects --I openly post these to facilitate peer review with experts, but you are also welcome to use them for your own purposes in the spirit of the open-source software community.

Our Data Techniques Training is a general overview and guides to practices for using the best software for the job –something that is difficult to know given the siloed nature of various parts of the data, analytics, Python and R language communities. They can all drift into the territory of, “if all you have is a hammer, everything is a nail."  Importantly, the pdf at this link also has a tutorial on how to download repositories such as those linked below.

The links here guide to learning a solid, exploratory data analysis and modeling toolset comprised of Python for building coded models and for transforming data, JMP software by SAS for exploratory analysis and for visualizing data and Microsoft Excel for building spreadsheet models and for presenting results in a nicely-formatted way. My blog, The Right Modeling And Analysis Tools For the Job, gives guidance on a commonly-available toolset that for me is the best software tool for the job for R&D, engineering design work and for working multifunctional corporate teams including Project Management and Finance functions. I share watch outs on not overusing Excel and Python for cases where they are not the optimal software. The Excel guidance is based on observing corporate multifunctional teams and the Microsoft-centric Analytics world. The "don't overuse Python" guidance is based on my tabletop "consumer research" informally chatting about JMP and similar Python tools with grad students, faculty and industrial folks at global SciPy conferences. For those in the Python world, JMP's ease of use and integration of statistics and visuals is eye opening and even shocking for use in exploratory data analysis.

Getting Started with Using Python for Scripting Data and Modeling Tasks

Learning a language like Python from scratch is a longer journey. It is a good path to start walking, but shortcuts are good too. Without some free time and persistence, is is easy to get distracted and lose energy for the long walk without the encouragement of practical use cases. If you are up for making that push, I recommend the Coursera Applied Data Science with Python Specialization. That said though, for making a quick start, here is a Github repository with some practical basics for jumping into using Python to open and rearrange your data for further analysis:

  1. The repository contains a quick primer on the Python language in the form of the Python_Jump_Start.ipynb Jupyter Notebook. It walks, in a humane way, through Python basics like lists, dictionaries and imported libraries. The original audience for this was Chemical Engineering undergrads who had a course in Python as freshman but then didn't see it again until Senior year.
  2. The repo also contains a Jupyter notebook, Pandas_Fundamentals.ipynb, to jump start you on using the most valuable, pre-built Python library for working with data tables (called “DataFrames” in Pandas nomenclature).
  3. If you are completely new to running Python, the Anaconda Distribution is recommended. Use the JupyterLab tool within it for creating and running code-containing Jupyter notebooks (aka  *.ipynb files).

Hints on Using AI Tools for Training Yourself

A strong suggestion with learning data techniques is to work through them with a browser open to an AI tool like ChatGPT. It knows Python. It knows Pandas. It knows a lot about a lot of things! You can prompt it with something like, “I’m new to data science tools and am working through a short tutorial on Python and Jupyter notebooks. Please explain what a DataFrame is for someone like me.” You can then ask it followup questions like, “Show me an example of creating a DataFrame, subsetting it to just some of its rows and columns and writing that to an Excel file.” This is a great use of the AI tools for training purposes!!

JMP For Data Visualization, Statistics and Data Reshaping

Our best practices blog touched on how valuable (and under-utilized) JMP is especially for non-coders. This repository gives a quick intro to it. I highly recommend taking advantage of JMP's free, 30-day trial to try it out and get started. Unless your company is on the verge of bankruptcy, the license fee is well worth the cost!. Experience shows that those who lean entirely on Excel, often fail to visualize their data and miss out on the insights that can come from that. JMP makes it easy to subset data to look at different parts. It also contains easy-to-use data reshaping tools for doing routine tasks like joining (aka merging), concatenating and summarizing.

Using Excel in the Best Possible Way

Excel is overused in many corporate and smaller company situations –yes, even the Finance folks are guilty here. Two examples are using it for exploratory data analysis or thinking that you must learn the so-called “New Excel” unless you are an Analytics guru.

My take is that Microsoft bolted on so-called "New Excel" features in the early 2010’s without integrating them well into the legacy product. While this might have made sense at the time, the development of Python tools and the continued excellence of JMP make it unnecessary and even counter-productive to veer into using New Excel features like tables, Power Query, Python-in-Excel etc. in most corporate contexts. The exception might be if you are a bonafide Analyst where knowing these tools is career price-of-entry.  As you think about building models for yourself and others, know that most people in the non-Analytics corporate world are simply not up for the equivalent of learning a foreign language (aka a completely different syntax and toolset from base Excel) when they already speak their “native language” and can do their spreadsheet work in the syntax they know. A better path is to learn additional "base Excel" features like range naming, so you can better curate your models while still working in familiar legacy Excel formula syntax.

Our online training course shares best practices for using Excel in a business environment. We teach how to curate simple or complex models in a collaborative way that does not lead to them being fragile and unknowable. We teach how to use Excel’s formatting features to have intuitive and pleasant user interfaces for your work.  We teach range naming and how to make intuitive and robust lookups across data tables.

Once you know the legacy Excel feature set, the ExcelSteps repository shares an add-in to automate building curated models and formatting them nicely. It revolves around the concept of using two types of "structured data objects" to organize things. These are rows/columns tables and what we call. The first structured data object is rows/columns tables of course. The second is what I call a “Scenario Model.” Its template is a columns by rows format that provides great wayfinding for single-valued inputs and outputs in calculation models. Multi-column Scenario models are sometimes a preferred alternative to rows/columns for working with multi-observation data. Experience shows that the aesthetics of these can be preferable for applications like time-based models or multi-step cost models.  Our VBA project starter repository contains a workbook with macro code for starting a project. This gets into coding details (probably more than you want if you are not a VBA coder) However, the repository's Readme file gives additional details and shows examples of the formats. The DemoStudy.xlsm workbook is accompanied by Tests_DemoStudy.xlsm, which is a fully functioning VBA test suite that we use for validating all details of projects.

The best overall advice for moving into this Excel fluency space is to take the training course and install the add-in. Spend some time getting comfortable with an example model using the ExcelSteps recipe sheet for inserted columns and with the Scenario Model format. Can you add your own calculated columns or Scenario Model variables to existing?  Use ChatGPT/AI tools to familiarize with Excel functions that are new to you. Useful examples are ISERROR, OFFSET. ROUND and SUMIF. An example AI prompt is, "Explain how the Excel ISERROR function works. Show me an example."

Next level Python, Excel modeling and JMP topics:

There is a lot more to add to move into more advanced topics, but here is a start.

  • Practical Python for Modelers is the basis for a corporate training for more experienced Python coders having engineering/science backgrounds. It teaches best practices for building and validating models in a way that is compatible with using AI tools for coding. It is based on a blog on the characteristics of robust Python models
  • TDD_OOP_Tutorial walks through a case study on the best practice of using object-oriented Python programming in concert with Pytest unit testing. See the tutorial.md file in the repository.
  • JMP Regression Training gives a case study on using JMP to build a model explaining how to scale up a consumer goods process

For actual scripting, given the current state of AI tools, you need to be able to check and even correct the AI tool's responses. That said, they are extremely useful for answering all kinds of basic questions. When given a background prompt on what you are doing and how things interact, tools like Github Copilot can write your code and unit tests to 90% complete. Practicing with prompting the tool quickly teaches you how to optimize this. For me, the optimization continues. Here are the opening sentences to my background prompt for developing a VBA project:

Here is the background for the project. I will ask you to write class methods and unit tests using the example template shown below

We are working on a VBA project that involves creating a class called CleaningStudy to execute multistep procedures. We will validate it using custom VBA test suite in a separate workbook to test the CleaningStudy class and associated methods in a project workbook whose VBA Project is called VBAProject_Clean. The test suite uses a Procedures class to manage and report test results. Each "procedure" simply calls methods in order to complete a use case or sub use case within another procedure.

Key Concepts
Tables Class: Manages table data within the project. We instance it as tbls and often pass it as an argument to methods in classes like CleaningStudy because it makes all needed data available to the method
ErrorHandling Class: Handles errors in the StatsTest class. We instance it as global object, errs, in all driver subroutines that instance classes like CleaningStudy.
Test Class: Used in the test suite to run tests and report results. We instance it as tst in every unit test
Procedures Class: We instance it as prices. It is used in the test suite to group tests by procedures that consist of multiple project functions and associated tests

...code templates etc.

by J.D.

Setting up "Courteous" (and Efficient and Accurate) Data Entry

This Tech Talk blog shares how to create custom, Conditional Formatting in a Microsoft Excel template to make manual data entry efficient and accurate. See the background blog on the user interface design motivation for this, and let us know if we can help you with your data situation!

Excel and Google sheets both allow creating Conditional Formatting rules for a range of cells to highlight specific cells when a condition is true based on a custom formula. In the example below, the data entry column, Panel_Score (Column D), needs two such rules --one to set the dark blue box highlighting the next cell as values are entered, and another to highlight remaining cells in light blue. The example also shows a rule that further highlights the current row by putting light blue shading on columns A to C adjacent to the darker blue box. With this in place, the dark blue highlighting will move as new data are entered.

In Excel, the Conditional Formatting feature is in the Home menu tab. Our formatting rules will use relative references to look at what’s going on in the current and previous row cells in the column where data is being entered. The best way to avoid getting confused while setting this up is to create the rules in just cell D2 and then paste its formatting (Copy / Paste Special / Formats) to the remaining cells in the column.

To start, type in values and column headings to make a mockup of the template shown above. Select cell D2 and choose Home tab / Conditional Formatting / New Rule. Click on “Use a formula to determine which cells to format” as shown above. For the dark blue box, the formula needs to check whether two conditions are true. We want the dark blue highlight if the previous cell contains a value and if the current cell is still blank. Since both need to be true, we use Excel’s AND() function to contain them. We want NOT(ISBLANK(D1)) and ISBLANK(D2) –with the two conditions separated by a comma in the AND function as shown below. An “=” character is required at the beginning of the formula. After entering the formula. Set the Format to fill cells with blue and click the OK button

That takes care of the darker blue highlighting on the current cell. To also highlight the remaining cells in the column with light blue, we need to enter a second rule. That one’s formula is shown below in the dialog box --to the left of the light blue shading. It is not an issue here but note that Conditional Formatting rules apply themselves in order –with the last one taking priority if multiple rules evaluate to True for a cell.  Once you have created both rules, you can copy D2 and Paste its format to the other cells.

Conditional formatting gets a lot of glory as a feature for creating heat maps and other highlights on regions of cells, but this shows how to make it useful as a user interface guide for data entry. Similarly, it can be used to do limits checking and highlight non-valid values such as a ‘6’ when the data should conform to a 0 to 5 scale.

by J.D.

“Courteous” (and Efficient and Accurate) Data Entry

Data entry is a great illustration of making modeling and data software courteous as discussed in a previous blog. However, with the data entry use case, this is not just a nicety. Accuracy and efficiency are necessities for entering panel and other measurements where results are typed in by humans. Here I share an example based on a recent consulting project where we streamlined a client’s data entry templates and analysis pipeline for the purpose of modeling the correlation between panel data and lab measurement data.  See this separate, Tech Talk blog containing details on how to set this up in a spreadsheet data entry template.

Do you gather data from multiple sources, including manually entered grading or lab data? Do your suppliers? This blog shares food for thought on auditing and improving those data sources for likelihood of generating accurate data. This is also a way to thoughtfully enable your lab personnel to be efficient and happy. Let us know if we can help you and your team in these areas.

In the context of data entry, how do we get the template to behave like a considerate human? The user interface should encourage data entry to occur comfortably in the same rows/columns template that will subsequently be used for analysis. It should guide the researcher to enter the data in the correct cell location, and it should make it easy to find this location and move from value to value. While not shown here, it may also do some checking of the data to alert the researcher to improper values. A 6 on a 0 to 5 scale? Not in our template! The considerate data entry form also shows the progress and what’s left to do. Below is an illustration of this using spreadsheet Conditional Formatting features to dynamically highlight cell locations and status as graders’ results are recorded.

In the illustration, the dark blue box highlights the current data entry location. Lighter blue below highlights what is left as the panel proceeds. Both use Conditional Formatting to dynamically shade cells based on current and previous cell contents. The dark blue box moves as new values are entered. Additionally, Column C illustrates using Conditional Formatting to highlight changes and avoid the visual clutter of repeated values. This was described in the previous blog and is a great technique for discouraging, use of merged cells, which breaks rows/columns structure.  One last UI touch on display here is freezing Row 1 so that column headers remain in view, this avoids the common urge to break data entry up into multiple template copies which then must be combined to analyze the full data set.

These simple user interface innovations greatly lower the risk of data entry errors, and they allow an experimenter to feel comfortable entering data directly into a large rows/columns table.

by J.D.

Getting Color Right in Products

Color is a feature of many products’ branding and benefits. Here I discuss how the “easy way” is not preferred for tolerancing color –that is quantifying  how close one measured color is to another either for quality control or for quantifying cleaning product efficacy. To do this appropriately in clients’ applications, we use a validated calculation template, and its formulas adapt readily to scripts in data pipelines. The template generates both ΔE* and CMC ΔE* color difference statistics –with the latter recommended for its superior correlation to how humans perceive color differences. Note that officially, the difference measures have a “*” in their names, but we will omit that for simplicity here.

Color Basics and the Easy (But Not Preferred) Way to Quantify Differences

Color is typically quantified by three numbers that can be measured by lab instruments or online sensors. The three sub-measures are L* (Lightness/darkness), a* (so-called red-to-green), and b* (yellow-to-blue). They locate a set of measurements in 3-D space as diagrammed below. High L-color of 100 is white and low L-color of zero is black. All points in between on the L-axis (e.g. a* and b* = 0) are a shade of gray ranging from very light to very dark.

The a and b rectangular coordinates are also commonly converted to polar coordinates --L,C,H space. Here the radius length is referred to as Chroma, C. It reflects the intensity of the color starting as a shade of gray at the a-b origin and increasing in visual intensity as C increases. The hue angle from 0 to 360 degrees, h, quantifies the color’s shade from red to green to blue to purple in a continuous scale while rotating around the a-b origin. The meaning of hue can be understood from the diagram. A hue angle of 45 degrees is a shade of red (e.g. the bold arrow on the diagram is at 45 degrees). Similarly, a hue angle of 135 degrees is green; 225 degrees is blue and so on.

Wikimedia Commons CIELAB chroma.svg

For quantifying color differences, it is neither convenient nor consumer relevant to individually control L, a and b (or L, C and h). Who wants to simultaneously track three numbers on a specification when one will do? The easy and historically-used method is to compute the ΔE color difference whose equation is shown below. It is the Euclidean distance between a color and its target/standard. The circle in the plot shows constant ΔE around a target in the a-b plane if L* is fixed. In 3-D space, these boundaries are spheres. ΔE is easy to calculate, but it doesn’t conform to how humans perceive color differences. We discuss CMC ΔE next. It was developed to overcome the shortcomings of ΔE, and the graph shows a CMC ΔE tolerance boundary for comparison. Note that the absolute limits are not comparable between the two systems. To display similar-sized regions, we chose 4.0 for one and 7.0 for the other.

CMC ΔE: Quantifying Color Differences in a Consumer-Relevant Way

Consumer relevance comes from switching to using the CMC ΔE statistic. Its tolerances are stretched and resized to correspond to human perception. This makes CMC DE boundaries elliptical in the a-b plane and in the out-of-plane L* direction --making them ellipsoids in three dimensions. They are oriented along lines of constant hue angle and are stretched in the Chroma direction as shown in the graph above.

The elliptical shape accounts for the fact that humans don’t perceive Chroma differences as readily as they do L* or especially hue differences. Adding to the complexity, human perception also depends on proximity to the a-b origin and to black (low L*) or white (high L*) in the third dimension. At least this latter point makes sense qualitatively. It’s difficult to tell what color an object is in a dark room.

The graph below shows color tiles generated to illustrate differences. These were generated by the excellent, colorizer.org website and have the same CMC ΔE of 4.0 versus the target. The differences are subtle at this level (and possibly especially viewed on a computer monitor). At this hue angle of 21 degrees (e.g. red), the +h* sample is slightly yellower and -h* is a bit more purplish. The -C* tile tends towards grayish versus +C*, and +L* and -L* tend towards lighter and darker respectively. The latter difference is made especially noticeable by placing those two tiles side-by-side. Acceptability would depend on the application.

The formula for CMC ΔE (See the Wikipedia Color Science article or ASTM standard D2244-16) is understandably complex based on the above discussion, but it is straightforward in Excel or in data pipeline scripts. Equations are shown at the bottom of this post, and a previous Tech Notes blog discusses some intricacies of h calculation. To safeguard calculations, our template stores the CMC ΔE and other formulas in a “refresh from” recipe sheet that keeps things updated and correct as data are entered and analyzed.

A rule of thumb from literature and personal experience is that, for non-textured/smooth samples in the stringent setting of well-lit, side-by-side testing, a CMC ΔE of 0.75 is the threshold where differences become perceptible with statistical significance in a consumer panel. Under these conditions, a CMC ΔE of 2.0 is clearly noticeable leading to comments like, “That one looks a bit yellower” or “That one is the same color but somewhat duller.”  If the product is more textured or if the lighting is not perfect, it takes a bigger difference to be noticeable.

For a given product context, CMC ΔE limits can be validated by consumer panel protocols such as triangle testing where products (for example makeup foundation shades, paints or molded plastics) are generated that vary CMC ΔE. In such a panel, panelists are asked to choose which of three products are different from the other two.

Some situations are harder to test because of the difficulty of generating samples having a specified difference. Tolerances for liquid products can often be set by conducting design-of-experiment (DOE) panel testing. This gets into what would be a separate blog topic about color formulation, but liquid products of a specified color difference can be made in test quantities using a mixing model that calculates the amounts to blend of mono-pigment versions of the product.

A watchout in panel testing is ensuring that the test design is appropriate for the in-market context. If the product is textured/matte finish or consumers are unlikely to experience true “side-by-side” or “well-lit” conditions, a higher tolerance is appropriate. In the case of cleaning efficacy, the goal is to bring the substrate back to its original color –imperceptibly different from the pristine material. This might lead to a side-by-side context in market if the consumer will see both cleaned and uncleaned zones.

If you are not yet using CMC ΔE as your color difference statistic, you should be! Please reach out if we can help!

 

 

CMC ΔE Excel Formula

CMC ΔE Mathematical Formula Per ASTM D2244-16

by J.D.

Well-Behaved Color Data Calculations

Color tolerancing is critical for quality control on colored products and for applications like assessing efficacy of cleaning products. We use a standard Excel data entry template (subject of an upcoming post) that can calculate the CMC Delta-E statistic to quantify the color difference between two samples in a way that correlates to human perceptibility (see the formula here under the "CMC l:c (1984)" heading or see ASTM D2244-16). This blog covers details of calculating needed hue angles from CIELAB (aka L,a,b color) data as commonly measured by lab instruments. It is an illustration of how good models go to lengths to get calculation details right even when that is not easy. The graph’s second set of datapoint labels shows the needed 0 to 360 degree hue angles for measurements mocked up to all have a Chroma (e.g. radius) equal to 10. The data file is ATAN_Check.xlsx in this Github repository. These calculations serve as our validation of this portion of the CMC ΔE template calculations --validation being a key characteristic of best-in-class models

Starting with CIELAB data necessitates converting between rectangular x, y (a-color and b-color) and polar r, Θ (e.g. Chroma, C, and Hue angle, h) coordinates. No problem, right? An initial, (and stay tuned, incorrect), thought is that a data entry template could utilize Excel’s built-in ATAN function to compute h as arctangent of b/a –with the color science reminder that a-color is red-to-green and b-color is blue-to-yellow and, when converted to polar coordinates, the radius or Chroma, C, is the intensity of the color and h is the hue which describes whether the color is red, green, yellow and so on.

Per the ATAN column (Column C) below, that function only returns results as first and fourth quadrant angles from -90 to +90 degrees and with #DIV/0! unpleasantness at the points where a-color is zero. We don’t get the required 0 to 360 degree hue angles. (If you are unfamiliar with Excel’s “@” notation in these formulas, see the note at the end).

We get past this situation by using Excel’s ATAN2 function. Historically, ATAN2 probably represents Microsoft recognizing ATAN’s limitations and (almost...keep reading) correcting them. The ATAN2 column (Column D )and the first labels on the graph’s datapoints shows ATAN2 calculations for our test dataset.

ATAN2 results range from -180 to +180 degrees with no errors when a-color (aka x axis value) is zero. That’s better, but still not 0 to 360 degrees. For that, we add a conditional correction in the ATAN2_mod (Column E) values that converts negative angles to positive. These are the second set of labels on the graph datapoints. This modified ATAN2 formula solves the problem of getting 0 to 360 degree hue angles for color tolerancing.

Note on Excel “@” Notation for Referring to Named Ranges

Our mockup file’s formulas use the modeling best practice of naming columns A and B “a_meas” and “b_meas”, respectively and using what Microsoft calls “implicit intersection” with the “@” symbol preceding the names in the formula. The “@” says to return the row’s value from the named column, and this is a best practice for creating easy-to-understand formulas (e.g. compare symbolic “@b_meas/@a_meas” here to classic cell reference syntax like “B4/A4”).  The first is easy to understand as the ratio of b:a colors. The second is more cryptic. We teach these best practices and more in our Digital Transformation with Excel online course.

by J.D.