Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Why does my code snippet of an JSON file work but the full JSON load does not

I have a snippet of JSON file that I normalize and use record_path on with the meta parameters on other columns and it works fine, but when I load the whole file I get a key error. Below is my code for the snippet and full json download file load.

Snippet Works

import pandas as pd

data = {
    "meta": {
        "disclaimer": "Do not rely on openFDA to make decisions regarding medical care. While we make every effort to ensure that data is accurate, you should assume all results are unvalidated. We may limit or otherwise restrict your access to the API in line with our Terms of Service.",
        "terms": "https://open.fda.gov/terms/",
        "license": "https://open.fda.gov/license/",
        "last_updated": "2024-11-15",
        "results": {
            "skip": 0,
            "limit": 2,
            "total": 118943
        }
    },
    "results": [
        {
            "product_ndc": "73647-062",
            "generic_name": "MENTHOL, CAMPHOR",
            "labeler_name": "Just Brands LLC",
            "brand_name": "JUST CBD - CBD AND THC ULTRA RELIEF",
            "active_ingredients": [
                {
                    "name": "CAMPHOR (SYNTHETIC)",
                    "strength": "2 g/100g"
                },
                {
                    "name": "MENTHOL",
                    "strength": "6 g/100g"
                }
            ],
            "finished": True,
            "packaging": [
                {
                    "package_ndc": "73647-062-04",
                    "description": "113 g in 1 BOTTLE, PUMP (73647-062-04)",
                    "marketing_start_date": "20230314",
                    "sample": False
                }
            ],
            "listing_expiration_date": "20251231",
            "openfda": {
                "manufacturer_name": ["Just Brands LLC"],
                "spl_set_id": ["f664eb79-8897-3a49-e053-2995a90a37b4"],
                "is_original_packager": [True],
                "unii": ["5TJD82A1ET", "L7T10EIP3A"]
            },
            "marketing_category": "OTC MONOGRAPH DRUG",
            "dosage_form": "GEL",
            "spl_id": "16c906dd-6989-9a79-e063-6394a90afa71",
            "product_type": "HUMAN OTC DRUG",
            "route": ["TOPICAL"],
            "marketing_start_date": "20230314",
            "product_id": "73647-062_16c906dd-6989-9a79-e063-6394a90afa71",
            "application_number": "M017",
            "brand_name_base": "JUST CBD - CBD AND THC ULTRA RELIEF"
        },
        {
            "product_ndc": "0591-4039",
            "marketing_end_date": "20250930",
            "generic_name": "CLOBETASOL PROPIONATE",
            "labeler_name": "Actavis Pharma, Inc.",
            "brand_name": "CLOBETASOL PROPIONATE",
            "active_ingredients": [
                {
                    "name": "CLOBETASOL PROPIONATE",
                    "strength": ".05 g/mL"
                }
            ],
            "finished": True,
            "packaging": [
                {
                    "package_ndc": "0591-4039-46",
                    "description": "1 BOTTLE in 1 CARTON (0591-4039-46)  / 59 mL in 1 BOTTLE",
                    "marketing_start_date": "20150828",
                    "marketing_end_date": "20250930",
                    "sample": False
                },
                {
                    "package_ndc": "0591-4039-74",
                    "description": "1 BOTTLE in 1 CARTON (0591-4039-74)  / 125 mL in 1 BOTTLE",
                    "marketing_start_date": "20150828",
                    "marketing_end_date": "20250930",
                    "sample": False
                }
            ],
            "openfda": {
                "manufacturer_name": ["Actavis Pharma, Inc."],
                "rxcui": ["861512"],
                "spl_set_id": ["907e425a-720a-4180-b97c-9e25008a3658"],
                "is_original_packager": [True],
                "unii": ["779619577M"]
            },
            "marketing_category": "NDA AUTHORIZED GENERIC",
            "dosage_form": "SPRAY",
            "spl_id": "33a56b8b-a9a6-4287-bbf4-d68ad0c59e07",
            "product_type": "HUMAN PRESCRIPTION DRUG",
            "route": ["TOPICAL"],
            "marketing_start_date": "20150828",
            "product_id": "0591-4039_33a56b8b-a9a6-4287-bbf4-d68ad0c59e07",
            "application_number": "NDA021835",
            "brand_name_base": "CLOBETASOL PROPIONATE",
            "pharm_class": [
                "Corticosteroid Hormone Receptor Agonists [MoA]",
                "Corticosteroid [EPC]"
            ]
        }
    ]
}

packaging_data = pd.json_normalize(
    data['results'], 
    record_path=["packaging"], 
    meta=['product_ndc', 'brand_name', 'generic_name']
)

active_ingredients_data = pd.json_normalize(
    data['results'], 
    record_path=["active_ingredients"], 
    meta=['product_ndc', 'brand_name', 'generic_name']
)

combined_data = pd.merge(
    packaging_data,
    active_ingredients_data,
    on=['product_ndc', 'brand_name', 'generic_name'],
    how='outer'
)

full download and load of JSON file gives a key error when using meta

import pandas as pd
import json
import requests, zipfile, io, os

cwd = os.getcwd()
zip_url = 'https://download.open.fda.gov/drug/ndc/drug-ndc-0001-of-0001.json.zip'
r = requests.get(zip_url)
z = zipfile.ZipFile(io.BytesIO(r.content))
z.extractall(cwd)

with open('drug-ndc-0001-of-0001.json', 'r') as file:
    data = json.load(file)

packaging_data = pd.json_normalize(
    data['results'], 
    record_path=["packaging"], 
    meta=['product_ndc', 'brand_name', 'generic_name']
)

active_ingredients_data = pd.json_normalize(
    data['results'], 
    record_path=["active_ingredients"], 
    meta=['product_ndc', 'brand_name', 'generic_name']
)

combined_data = pd.merge(
    packaging_data,
    active_ingredients_data,
    on=['product_ndc', 'brand_name', 'generic_name'],
    how='outer'
)
Tags:

2

Answers


  1. 0

    You have holes in your data. Some records don’t have a "brand_name", others are missing the "active_ingredients" list used to expand the table. For "brand_name" you can set errors='ignore' to fill a default value. If you don’t like that solution, you can prescan the list and modify those records yourself.

    "active_ingredients" is more problematic. That field needs to exist as a list in all records. You could scan those records and either add an empty list or remove the record completely, depending on how you want to solve the problem.

    Login or Signup to reply.
  2. 0

    You need to allow for missing meta keys and "active_ingredients". In the case of the meta keys I would just add "n/a" (not available) and in the latter case just use an empty list.

    When downloading large content, I prefer to stream.

    Therefore, I suggest:

    import requests
from zipfile import ZipFile
import json
from pathlib import Path
import pandas as pd

CHUNK = 4096
META = ["product_ndc", "brand_name", "generic_name"]
DEFAULTS = {
    "product_ndc": "n/a",
    "brand_name": "n/a",
    "generic_name": "n/a",
    "active_ingredients": []
}
url = "https://download.open.fda.gov/drug/ndc/drug-ndc-0001-of-0001.json.zip"
filename = Path(url.split("/")[-1])

with requests.get(url, stream=True) as response:
    response.raise_for_status()
    with filename.open("wb") as z:
        for chunk in response.iter_content(CHUNK):
            z.write(chunk)

with ZipFile(filename) as z:
    z.extractall()
    with Path(z.namelist()[0]).open() as j:
        data = json.load(j)
    for result in data["results"]:
        for key, value in DEFAULTS.items():
            result.setdefault(key, value)

packaging_data = pd.json_normalize(
    data["results"], record_path=["packaging"], meta=list(META) # call list() to satisfy mypy
)

active_ingredients_data = pd.json_normalize(
    data["results"], record_path=["active_ingredients"], meta=list(META) # call list() to satisfy mypy
)

combined_data = pd.merge(packaging_data, active_ingredients_data, on=META, how="outer")

print(combined_data.head())

    Output:

        package_ndc                                 description marketing_start_date sample marketing_end_date product_ndc brand_name   generic_name           name     strength
0  0002-0013-03   3 mL in 1 VIAL, MULTI-DOSE (0002-0013-03)            04-DEC-09    NaN                NaN   0002-0013        n/a  Insulin human  INSULIN HUMAN  100 [iU]/mL
1  0002-0013-10  10 mL in 1 VIAL, MULTI-DOSE (0002-0013-10)            27-JUN-83    NaN                NaN   0002-0013        n/a  Insulin human  INSULIN HUMAN  100 [iU]/mL
2  0002-0095-00            3 mL in 1 SYRINGE (0002-0095-00)            29-DEC-15    NaN                NaN   0002-0095        n/a  Insulin human  INSULIN HUMAN  500 [iU]/mL
3  0002-0096-00  20 mL in 1 VIAL, MULTI-DOSE (0002-0096-00)            06-JAN-97    NaN                NaN   0002-0096        n/a  Insulin human  INSULIN HUMAN  500 [iU]/mL
4  0002-0113-03   3 mL in 1 VIAL, MULTI-DOSE (0002-0113-03)            24-JUN-10    NaN                NaN   0002-0113        n/a  Insulin human  INSULIN HUMAN  100 [iU]/mL
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search