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:
- 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.
- 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).
- 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.