JSON data from API missing fields causing DataFrame column misalignment issues

Other Questions Airtable
1

I’m working with JSON data from an API and running into problems when converting it to a pandas DataFrame. The main issue is that some records have missing fields which throws off the column alignment.

Here’s what my JSON structure looks like:

api_response = {
    'pagination': '--',
    'data': [{
        'timestamp': '2023-01-01',
        'values': {
            'name': 'John',
            'age': 25,
            'score': 85,
            'category': 'premium',
            'metadata': {'type': 'user'}
        }
    }]
}

But sometimes records look like this with missing fields:

incomplete_record = {
    'timestamp': '2023-01-02', 
    'values': {
        'name': 'Jane',
        'age': 30,
        'category': 'basic',
        'metadata': {'type': 'user'}
    }
}

Notice how the ‘score’ field is completely absent. When I try to create a DataFrame using the first record’s keys as column headers, the missing fields in subsequent records cause everything to shift around incorrectly.

I’m using the fields from the first record as my column template, but when other records are missing certain keys, the data doesn’t align properly with the expected columns. Has anyone dealt with this kind of inconsistent JSON structure before? Any suggestions on how to handle missing fields gracefully would be really helpful.

2

Had this exact problem with inconsistent API endpoints. I preprocess the nested values before creating the DataFrame - works way better than other methods I’ve tried.

I flatten each record completely first, then let pandas handle the missing values:

flattened_records = []
for record in api_response['data']:
    flat_data = record['values'].copy()
    flat_data['timestamp'] = record['timestamp']
    # Handle nested metadata if present
    if 'metadata' in flat_data:
        flat_data.update({f"metadata_{k}": v for k, v in flat_data['metadata'].items()})
        del flat_data['metadata']
    flattened_records.append(flat_data)

df = pd.DataFrame(flattened_records)

This beats normalize for nested structures like your metadata field, plus you skip the overhead of multiple DataFrame operations. Missing fields become NaN automatically without column alignment headaches. I’ve used this pattern for two years across different API integrations and it handles schema changes really well.

3

totally get it! pd.json_normalize() is super helpful for this! just make sure to run df = pd.json_normalize(api_response['data']) and it’ll handle those missing fields by filling them with NaNs. saves a lot of hassle!

4

Been there many times. The normalize approach works but I handle this differently.

I grab all unique keys first, then build the DataFrame:

all_keys = set()
for record in api_response['data']:
    all_keys.update(record['values'].keys())

# Then create rows with all possible columns
rows = []
for record in api_response['data']:
    row = {key: record['values'].get(key, None) for key in all_keys}
    row['timestamp'] = record['timestamp']
    rows.append(row)

df = pd.DataFrame(rows)

You get consistent columns across all records and missing values become None/NaN naturally. Plus you control the structure.

Learned this the hard way dealing with inconsistent API responses from third party services. Key scanning saved me from tons of debugging when new fields randomly showed up.

5

Hit this same issue with financial APIs that randomly drop optional fields. Pandas concat with sort=False saved me - keeps column order intact and handles missing data cleanly.

I break each record into its own DataFrame first, then merge:

record_dfs = []
for record in api_response['data']:
    flat_record = record['values'].copy()
    flat_record['timestamp'] = record['timestamp']
    record_dfs.append(pd.DataFrame([flat_record]))

final_df = pd.concat(record_dfs, ignore_index=True, sort=False)

Concat automatically aligns columns and fills gaps with NaN, plus you skip the key scanning that bogs down large datasets. Way more reliable when you’re dealing with messy nested JSON where missing fields pop up at different levels.