Python Generated Pugh Chart Populated from Airtable

For those that have ever started their own personal projects, I’m sure you’ve shared the familiar overwhelming feeling of having lots of unfiltered ideas about how to approach the beginning, but ambiguity regarding the most optimal path to the solution. The idea of selecting one idea among several potential ones is known to engineers as concept selection. Whether the initial collection of ideas came from mind mapping or brainstorming, we need some way to narrow down which solutions we think are most viable. One method of testing viability could be running small scale prototypes of each solution we envision and collect data on their performance. In school, one of the organizational techniques I’ve learned to analyze the collected data is the Pugh Chart (more on exactly what this is later).

I was motivated to complete this project because of how tedious I found making Pugh Charts was, especially for projects with more than 5 viable solutions. By combining Airtable, a spreadsheet-database-hybrid, with a Python script, I can easily generate neat Pugh Charts without all the hassle of performing the same monotonous calculations on my own.

On Pugh Charts

Sample Pugh Chart for robot that lifts and transports cubes. In this, Design 1 has the highest score which represents the most viable solution.

Pugh charts are a visual way to organize and compare multiple designs against their functional requirements early in the design process to select one concept to proceed with. The design with the highest total represents the most viable solution. Weights can be assigned to each requirement as determined by importance. One arbitrary design is chosen to be the ‘control’ and is given a score of 0 for each requirement. Each subsequent design is compared to the control between each category and is assigned a score of +1 if it performs better than the control, -1 if it performs worse, or 0 if it performs equally well. The total is calculated by multiplying each individual cell by the weight in its row, and summing the column.

On Airtable

Airtable is an amazingly powerful organizational tool that packs the power of a database within an intuitive user interface.

Sample Airtable for my student organization BLUElab Thailand.

Although it appears as a spreadsheet, the integration of a database allows users to return queries beyond simple strings and integers — notice in the sample how the last column includes file attachments, which is crazy.

Sample form for my student organization BLUElab Thailand. Responses to this form will automatically populate the spreadsheet.

Users can easily populate the database through the format of a form. This is especially handy when out on the field, as the form fits snugly on mobile devices.

Lastly for developers, Airtable provides a remarkably well documented API.

Airtable offers so much more, and I highly encourage reading about it yourself to make it part of your own project.

The Project

The program prompts the user for 4 things: (1) the name of the table; (2) the names of the columns to be used as requirements; (3) the name of the solution column; and (4) the weights of each requirement.

(1) is included in this program so that users with multiple tables can generate Pugh charts for each of them.

(2) is required for filtering out each column of the table — users that have columns for ‘Attachments’ or for ‘Comments/Notes’ will most likely not need those columns for the Pugh chart.

(3) is required to access the column with each of the designs to be compared with one another.

(4) gives the user the option to add weights to each requirement. Positive values means that the user wants to maximize the requirement, while negative values means that the user wants to minimize the requirement. In the sample above, both of the weights are positive, which means that designs with higher values of ‘Times til explosion (s)’ and ‘% Barrel Filled’ are favored.

Airtable Integration

I used the Python wrapper found here.

For some reason, the import statement needed to be written as above.

You can generate your own base key and API key for your workspace through Airtable’s app. Obtaining the ‘tablename’ through user input allows for configuration across multiple tables.

Comparison Algorithm

TL;DR storing data as key-value pairs in a dictionary where keys are solutions and values are data for each requirement. Averages are taken for keys with more than one entry in Airtable.

Line 112 creates the dictionary I use to store all my data. The keys represent the solutions, and the values represent the data recorded in each requirement.

Lines 114 and 116 receive user input. One is for filtering out each column of the table, and is stored in ‘accept’. The other is for obtaining solution names, which is stored in ‘solncol’.

Line 126 creates a list called ‘acceptlist’ based off of user input of columns to take as functional requirements.

Lines 128–129 appends each entry into a list called ‘categories’ that will be used in part of the display.

Line 131 begins the iteration through the Airtable. The call airtable.get_all() returns all the records, including their IDs and fields.

Lines 132–134 create a new list called ‘newdata’, to which we append relevant query results. 133 begins iterating through each entry in ‘acceptlist’ to query the data from Airtable. In the sample at the beginning of this section, we take the columns “Time til explosion (s)” and “% Barrel Filled”. The call row[‘fields’][‘Time til explosion (s)’] on 134 returns the specific data entry of the solution and appends it to ‘newdata’.

Line 135 creates a list called ‘soln’ which is a list of the solutions (obviously) which will become the keys of our dictionary.

Lines 137 and 144 are the comparison algorithm. For each new solution the program reads, we create a key-value pair with solutions as the key and a list of the data recorded in each category as the value. This is shown in lines 143–144. If the program reads a solution that we have already come across, we want to average the data in every category. This is shown in lines 138–142.

The lines above might be easier to explain using a practical example. As in the sample at the beginning of this section, the two functional requirements are “Time til explosion (s)” and “% Barrel Filled”. Let us assume that we have two solutions in our Airtable whose names are: Solution A and Solution B. We conduct two trials each of Solution A and Solution B, for a total of four trials.

Trial 1, Solution A:
Time til explosion (s) = 5; % Barrel Filled = 100
Trial 2, Solution A:
Time til explosion (s) = 7; % Barrel Filled = 95
Trial 3, Solution B:
Time til explosion (s) = 10; % Barrel Filled = 100
Trial 4, Solution B:
Time til explosion (s) = 8; % Barrel Filled = 90

In our dictionary, we will only have 2 key-value pairs. The keys will be Solution A and Solution B. The values will be lists of the averages of the data in each category. Visually, this can be represented as:
{‘Solution A’: [6, 97], ‘Solution B’: [9, 95]}

PrettyTable

PrettyTable is a simple Python library designed to make it quick and easy to represent tabular data in visually appealing ASCII tables.

I use PrettyTable to display the Pugh Chart.

The leftmost column displays all the functional requirements. The upper row displays all the solutions. The bottom row displays the scores for each solution. As with a normal Pugh chart, scores are counted based off of multiplying the score in each cell with the weight of the requirement and summing the column. + signs in cells represent +1, — signs in cells represent -1, and * represent 0. In the example above, all weights are 1.

Next Steps and Takeaways

Parts of this code could definitely be optimized, as some Big O times are n². I also want to make this more user friendly —as of now, the program is extremely unforgiving when the user makes a typo. I should have implemented try-catch statements to alleviate this issue. I could also display all possible table names, column names, etc. before prompting the user.

I’ve always taken interest in emerging technologies, so working on a project with Airtable piqued my curiosity. I’m impressed at what Airtable has to offer for development, and I’m most certainly going to be working more closely with this tool in the future. Although this post had an extended section dedicated to explaining code, I hoped that I’ve maintained your interest enough to inspire some ideas on how you can integrate Airtable into your next project! You can find everything about this project on my GitHub.