Find out how to Construct a Light-weight Information Pipeline with Airtable and Python

Picture by Editor | ChatGPT

# Introduction

Airtable not solely provides a versatile, spreadsheet-like interface for knowledge storage and evaluation, it additionally offers an API for programmatic interplay. In different phrases, you possibly can join it to exterior instruments and applied sciences — for example, Python — to construct knowledge pipelines or processing workflows, bringing your outcomes again to your Airtable database (or just “base”, in Airtable jargon).

This text demonstrates the right way to create a easy, ETL-like pipeline utilizing the Airtable Python API. We are going to keep on with the free tier, making certain the method works with out paid options.

# Airtable Dataset Setup

Whereas the pipeline constructed on this article will be simply tailored to quite a lot of datasets, for these new to Airtable and needing an Airtable venture and saved dataset as a place to begin, we advocate you comply with this current introductory tutorial to Airtable and create a tabular dataset referred to as “Prospects”, containing 200 rows and the next columns (see picture):

Prospects dataset/desk in Airtable | Picture by Creator

# Airtable-Python Information Pipeline

In Airtable, go to your consumer avatar — on the time of writing, it’s the circled avatar positioned on the bottom-left nook of the app interface — and choose “Builder Hub”. Within the new display screen (see screenshot beneath), click on on “Private entry tokens”, then on “Create token”. Give it a reputation, and be sure you add at the very least these two scopes: knowledge.data:learn and knowledge.data:write. Likewise, choose the bottom the place your clients desk is positioned within the “Entry” part, in order that your token has configured entry to this base.

Creating an Airtable API token | Picture by Creator

As soon as the token has been created, copy and retailer it rigorously in a protected place, as it will likely be proven solely as soon as. We’ll want it later. The token begins with pat adopted by a protracted alphanumeric code.

One other key piece of data we might want to construct our Python-based pipeline that interacts with Airtable is the ID of our base. Return to your base within the Airtable internet interface, and as soon as there, you need to see that its URL within the browser has a syntax like: https://airtable.com/app[xxxxxx]/xxxx/xxxx. The half we’re curious about copying is the app[xxxx] ID contained between two consecutive slashes (/): that is the bottom ID we are going to want.

With this in hand, and assuming you have already got a populated desk referred to as “Prospects” in your base, we’re prepared to start out our Python program. I might be utilizing a pocket book for coding it. In case you are utilizing an IDE, chances are you’ll must barely change the half the place the three Airtable surroundings variables are outlined, to have them learn from an .env file as a substitute. On this model, for simplicity and ease of illustration, we are going to immediately outline them in our pocket book. Let’s begin by putting in the mandatory dependencies:

!pip set up pyairtable python-dotenv

Subsequent, we outline the Airtable surroundings variables. Discover that for the primary two, you must substitute the worth along with your precise entry token and base ID, respectively:

import os
from dotenv import load_dotenv # Obligatory provided that studying variables from a .env file
from pyairtable import Api, Desk
import pandas as pd

PAT = "pat-xxx" # Your PAT (Private Entry Token) is pasted right here
BASE_ID = "app-xxx" # Your Airtable Base ID is pasted right here
TABLE_NAME = "Prospects"

api = Api(PAT)
desk = Desk(PAT, BASE_ID, TABLE_NAME)

We have now simply arrange an occasion of the Python Airtable API and instantiated a connection level to the shoppers desk in our base. Now, that is how we learn the complete dataset contained in our Airtable desk and cargo it right into a Pandas DataFrame. You simply have to be cautious to make use of the precise column names from the supply desk for the string arguments contained in the get() technique calls:

rows = []
for rec in desk.all():  # honors 5 rps; auto-retries on 429s
    fields = rec.get("fields", {})
    rows.append({
        "id": rec["id"],
        "CustomerID": fields.get("CustomerID"),
        "Gender": fields.get("Gender"),
        "Age": fields.get("Age"),
        "Annual Earnings (ok$)": fields.get("Annual Earnings (ok$)"),
        "Spending Rating (1-100)": fields.get("Spending Rating (1-100)"),
        "Earnings class": fields.get("Earnings Class"),
    })

df = pd.DataFrame(rows)

As soon as the information has been loaded, it’s time to apply a easy transformation. For simplicity, we are going to simply apply one transformation, however we may apply as many as wanted, simply as we’d normally do when preprocessing or cleansing datasets with Pandas. We are going to create a brand new binary attribute, referred to as Is Excessive Worthto indicate high-value clients, i.e., these whose earnings and spending rating are each excessive:

def high_value(row):
    attempt:
        return (row["Spending Score (1-100)"] >= 70) and (row["Annual Income (k$)"] >= 70)
    besides TypeError:
        return False

df["Is High Value"] = df.apply(high_value, axis=1)
df.head()

Ensuing dataset:

Airtable knowledge transformation with Python and Pandas | Picture by Creator

Lastly, it’s time to write the adjustments again to Airtable by incorporating the brand new knowledge related to the brand new column. There’s a little caveat: we first must manually create a brand new column named “Excessive Worth” in our Airtable clients desk, with its kind set to “Checkbox” (the equal of binary categorical attributes). As soon as this clean column has been created, run the next code in your Python program, and the brand new knowledge might be mechanically added to Airtable!

updates = []
for _, r in df.iterrows():
    if pd.isna(r["id"]):
        proceed
    updates.append({
        "id": r["id"],
        "fields": {
            "Excessive Worth": bool(r["Is High Value"])
        }
    })

if updates:
    desk.batch_update(updates)

Time to return to Airtable and see what modified in our supply clients desk! If at first look you see no adjustments and the brand new column nonetheless appears empty, do not panic simply but. Not many purchasers are labeled as “excessive worth”, and chances are you’ll must scroll down a bit to see some labeled with a inexperienced tick signal:

Up to date clients desk | Picture by Creator

That is it! You simply constructed your personal light-weight, ETL-like knowledge pipeline primarily based on a bidirectional interplay between Airtable and Python. Nicely performed!

# Wrapping Up

This text centered on showcasing knowledge capabilities with Airtable, a flexible and user-friendly cloud-based platform for knowledge administration and evaluation that mixes options of spreadsheets and relational databases with AI-powered features. Particularly, we confirmed the right way to run a light-weight knowledge transformation pipeline with the Airtable Python API that reads knowledge from Airtable, transforms it, and masses it again to Airtable — all throughout the capabilities and limitations of Airtable’s free model.

Iván Palomares Carrascosa is a pacesetter, author, speaker, and adviser in AI, machine studying, deep studying & LLMs. He trains and guides others in harnessing AI in the true world.