如何从带有嵌套词典的API JSON字典中创建数据框？

Question

，该代码将从a

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 = {'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'}

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

• 卡路里
• 注意
• 饮食标签
• 总营养素：
  • CA：
    • 数量
  • chole：
    • 数量
  • 脂肪：
    • 数量
  • fe：
    • 数量
  • fibtg：
    • 数量
  • k：
    • 数量
  • mg：
    • 数量
  • na：
    • 数量
  • procnt：
    • 数量
  • 糖：
    • 数量
  • vitc：
    • 数量

---

如何将这些信息作为一行将数据框架输入？以一种自动化的方式，例如循环，而不是手动是最好的。

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)