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": "/",
"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 = '.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'
)
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'
)
2 Answers
Reset to default 2You 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.
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
errors='ignore'to fill in null for brand_name but active_ingredients is tricky because its your path. Perhaps you could preprocess the records to fill in missing data with defaults. – tdelaney Commented Nov 19, 2024 at 4:01