如何创建一个DataFrame从API json字典嵌套字典?

Question

我有以下代码，将从食品数据库API加载数据:

import requests
import pandas as pd
url = "https://api.edamam.com/api/nutrition-data?app_id=aba82731&app_key=793acdcce19384d28aa31dbd04ae2e42&nutrition-type=logging&ingr=grilled%20chicken%20with%20pasta"
response = requests.get(url)
data = response.json()

，其中data具有以下字典结构:

data = {'calories': 458,
'cautions': ['WHEAT', 'SULFITES', 'FODMAP'],
'dietLabels': ['LOW_CARB', 'LOW_SODIUM'],
'healthLabels': ['SUGAR_CONSCIOUS',
'LOW_POTASSIUM',
'KIDNEY_FRIENDLY',
'KETO_FRIENDLY',
'DAIRY_FREE',
'GLUTEN_FREE',
'EGG_FREE',
'MILK_FREE',
'PEANUT_FREE',
'TREE_NUT_FREE',
'SOY_FREE',
'FISH_FREE',
'SHELLFISH_FREE',
'PORK_FREE',
'RED_MEAT_FREE',
'CRUSTACEAN_FREE',
'CELERY_FREE',
'MUSTARD_FREE',
'SESAME_FREE',
'LUPINE_FREE',
'MOLLUSK_FREE',
'ALCOHOL_FREE'],
'totalDaily': {'CA': {'label': 'Calcium',
'quantity': 4.654618862939055,
'unit': '%'},
'CHOCDF': {'label': 'Carbs',
'quantity': 2.9428945911158464,
'unit': '%'},
'CHOLE': {'label': 'Cholesterol',
'quantity': 47.32843095227672,
'unit': '%'},
'ENERC_KCAL': {'label': 'Energy',
'quantity': 22.915961111755184,
'unit': '%'},
'FASAT': {'label': 'Saturated',
'quantity': 36.436110754453985,
'unit': '%'},
'FAT': {'label': 'Fat',
'quantity': 45.36119696773634,
'unit': '%'},
'FE': {'label': 'Iron',
'quantity': 11.657564638232902,
'unit': '%'},
'FIBTG': {'label': 'Fiber',
'quantity': 4.546503710404067,
'unit': '%'},
'FOLDFE': {'label': 'Folate equivalent (total)',
'quantity': 5.722928611449462,
'unit': '%'},
'K': {'label': 'Potassium',
'quantity': 11.999801311239468,
'unit': '%'},
'MG': {'label': 'Magnesium',
'quantity': 12.799190831778608,
'unit': '%'},
'NA': {'label': 'Sodium',
'quantity': 22.77388978712869,
'unit': '%'},
'NIA': {'label': 'Niacin (B3)',
'quantity': 92.83876603888321,
'unit': '%'},
'P': {'label': 'Phosphorus',
'quantity': 49.81229888591152,
'unit': '%'},
'PROCNT': {'label': 'Protein',
'quantity': 75.71512679724621,
'unit': '%'},
'RIBF': {'label': 'Riboflavin (B2)',
'quantity': 21.81596188323827,
'unit': '%'},
'THIA': {'label': 'Thiamin (B1)',
'quantity': 14.182969547335848,
'unit': '%'},
'TOCPHA': {'label': 'Vitamin E',
'quantity': 14.611366206631876,
'unit': '%'},
'VITA_RAE': {'label': 'Vitamin A',
'quantity': 9.60452805492508,
'unit': '%'},
'VITB12': {'label': 'Vitamin B12',
'quantity': 27.111025993055698,
'unit': '%'},
'VITB6A': {'label': 'Vitamin B6',
'quantity': 78.44815134302021,
'unit': '%'},
'VITC': {'label': 'Vitamin C',
'quantity': 17.34915621174754,
'unit': '%'},
'VITD': {'label': 'Vitamin D',
'quantity': 2.2057885855539054,
'unit': '%'},
'VITK1': {'label': 'Vitamin K',
'quantity': 22.188631573071397,
'unit': '%'},
'ZN': {'label': 'Zinc',
'quantity': 19.393529459172907,
'unit': '%'}},
'totalNutrients': {'CA': {'label': 'Calcium, Ca',
'quantity': 46.54618862939054,
'unit': 'mg'},
'CHOCDF': {'label': 'Carbohydrate, by difference',
'quantity': 8.828683773347539,
'unit': 'g'},
'CHOLE': {'label': 'Cholesterol',
'quantity': 141.98529285683017,
'unit': 'mg'},
'ENERC_KCAL': {'label': 'Energy',
'quantity': 458.31922223510367,
'unit': 'kcal'},
'FAMS': {'label': 'Fatty acids, total monounsaturated',
'quantity': 13.404892227852464,
'unit': 'g'},
'FAPU': {'label': 'Fatty acids, total polyunsaturated',
'quantity': 6.176365111399097,
'unit': 'g'},
'FASAT': {'label': 'Fatty acids, total saturated',
'quantity': 7.287222150890797,
'unit': 'g'},
'FAT': {'label': 'Total lipid (fat)',
'quantity': 29.48477802902862,
'unit': 'g'},
'FATRN': {'label': 'Fatty acids, total trans',
'quantity': 0.17224160325995616,
'unit': 'g'},
'FE': {'label': 'Iron, Fe',
'quantity': 2.0983616348819223,
'unit': 'mg'},
'FIBTG': {'label': 'Fiber, total dietary',
'quantity': 1.1366259276010167,
'unit': 'g'},
'FOLAC': {'label': 'Folic acid',
'quantity': 0.05846900826431348,
'unit': 'µg'},
'FOLDFE': {'label': 'Folate, DFE',
'quantity': 22.89171444579785,
'unit': 'µg'},
'FOLFD': {'label': 'Folate, food',
'quantity': 22.7922526689386,
'unit': 'µg'},
'K': {'label': 'Potassium, K',
'quantity': 563.990661628255,
'unit': 'mg'},
'MG': {'label': 'Magnesium, Mg',
'quantity': 53.75660149347015,
'unit': 'mg'},
'NA': {'label': 'Sodium, Na',
'quantity': 546.5733548910886,
'unit': 'mg'},
'NIA': {'label': 'Niacin',
'quantity': 14.854202566221312,
'unit': 'mg'},
'P': {'label': 'Phosphorus, P',
'quantity': 348.68609220138063,
'unit': 'mg'},
'PROCNT': {'label': 'Protein',
'quantity': 37.85756339862311,
'unit': 'g'},
'RIBF': {'label': 'Riboflavin',
'quantity': 0.2836075044820975,
'unit': 'mg'},
'SUGAR': {'label': 'Sugars, total',
'quantity': 4.178421492568704,
'unit': 'g'},
'SUGAR.added': {'label': 'Sugars, added',
'quantity': 1.9229706816388945,
'unit': 'g'},
'THIA': {'label': 'Thiamin',
'quantity': 0.17019563456803016,
'unit': 'mg'},
'TOCPHA': {'label': 'Vitamin E (alpha-tocopherol)',
'quantity': 2.1917049309947814,
'unit': 'mg'},
'VITA_RAE': {'label': 'Vitamin A, RAE',
'quantity': 86.44075249432571,
'unit': 'µg'},
'VITB12': {'label': 'Vitamin B-12',
'quantity': 0.6506646238333367,
'unit': 'µg'},
'VITB6A': {'label': 'Vitamin B-6',
'quantity': 1.019825967459263,
'unit': 'mg'},
'VITC': {'label': 'Vitamin C, total ascorbic acid',
'quantity': 15.614240590572786,
'unit': 'mg'},
'VITD': {'label': 'Vitamin D (D2 + D3)',
'quantity': 0.3308682878330858,
'unit': 'µg'},
'VITK1': {'label': 'Vitamin K (phylloquinone)',
'quantity': 26.626357887685675,
'unit': 'µg'},
'WATER': {'label': 'Water',
'quantity': 182.17056922349246,
'unit': 'g'},
'ZN': {'label': 'Zinc, Zn',
'quantity': 2.1332882405090197,
'unit': 'mg'}},
'totalNutrientsKCal': {'CHOCDF_KCAL': {'label': 'Calories from carbohydrates',
'quantity': 36,
'unit': 'kcal'},
'ENERC_KCAL': {'label': 'Energy',
'quantity': 459,
'unit': 'kcal'},
'FAT_KCAL': {'label': 'Calories from fat',
'quantity': 269,
'unit': 'kcal'},
'PROCNT_KCAL': {'label': 'Calories from protein',
'quantity': 154,
'unit': 'kcal'}},
'totalWeight': 261.3214436115425,
'uri': 'http://www.edamam.com/ontologies/edamam.owl#recipe_8a35ade4cf6a4cb98d7e0aeb8a5b4cdd'}

从这本字典中，我想提取以下信息:

<

• 卡路里/gh>
• <
• 饮食标签/gh>
• 总营养:
  • CA:
  • 胆:
  • 脂肪:
  • 铁:
  • FIBTG:
  • K:
  • MG:
  • 钠:
  • PROCNT:
  • 糖:
  • VITC:

---

我怎么能得到这个信息到DataFrame作为一行?以一种自动的方式，比如循环，而不是手动的方式。

Answer 1

# Create DataFrame from data dictionary:
# - set_index(0) makes the keys (column 0) the index values
# - .T takes the transpose and makes the index the columns
# .reset_index(drop=True) resets index to 0 (1 row DataFrame) and drops old index
df = pd.DataFrame(data.items()).set_index(0).T.reset_index(drop=True)
# Select first 3 columns to keep as is
df = df[['calories', 'cautions', 'dietLabels']]
# For loop logic to create all other columns
# Since not all keys from totalNutrients are wanted, they must be selected in col_list
col_list = ['CA', 'CHOLE', 'FAT', 'FE', 'FIBTG', 'K', 'MG', 'NA', 'PROCNT', 'SUGAR', 'VITC']
value_list = []
# For each key and value (dictionary) in data['totalNutrients']
for key, value in data['totalNutrients'].items():
# If key is in col_list
if key in col_list:
# Add value to value_list
value_list.append(value['quantity'])

# Create dictionary of columns and values using zip()
clean_data_dict = dict(zip(col_list, value_list))
# Concatenate df with new DataFrame on the same index. axis=1 to concatenate on columns
df = pd.concat([df, pd.DataFrame(clean_data_dict, index=[0])], axis=1)
print(df)

可选

# To run the API multiple times and store each run:
# - Try loading data.csv
# - Add new data row to main_df and overwrite
# - If the file is not found then save first run df
try:
main_df = pd.read_csv('data.csv')
main_df = pd.concat([main_df, df])
main_df.reset_index(drop=True, inplace=True)
print(main_df)
main_df.to_csv('data.csv', index=False)
except FileNotFoundError:
df.to_csv('data.csv', index=False)

可选

# To change the column names at the end you can repeat the zip process
new_col_list = ['calories', 'cautions', 'diet_labels', 'calcium', 'cholesterol', 'fat', 'iron', 'dietary_fiber', 'potasssium', 'magnesium', 'sodium', 'protein', 'sugar', 'vitamin_c']
col_dict = dict(zip(col_list, new_col_list))
# .rename() uses a dict to map old column names to new column names
df.rename(columns=col_dict)
print(df)