How to flatten a nested JSON from the NASA Weather Insight API in Python

Question

Hello I am trying to display the mars weather from the Mars Insight API. The issue that is occurring is that the data is returning in JSON format and has three levels API Documentation. I can pull the primary key no problem with 3-4 different methods but when I try to get a secondary or tertiary key that is when it falls apart.

import requests
import json
import pandas as pd
from pandas.io.json import json_normalize

API_url = "https://api.nasa.gov/insight_weather/?api_key=nTal99zKlhGbl0N8F0V9iUofifMdcwyOHw64CrVm&feedtype=json&ver=1.0"
API_data = requests.get(API_url).json()

# define weather data attributes

#AT = {'AT':API_data['sol_keys'[1,2,3]]}
#PRE = {'PRE':API_data['sol_keys']}
#HWS = {'HWS':API_data['sol_keys']}
#Season= {'Season':API_data['sol_keys']}
#WD = {'WD':API_data['sol_keys']}
#most_common = {'most_common':API_data['sol_keys']}

context = {'sol_keys': API_data["sol_keys"]}

data =json_normalize(API_data, 'sol_keys', '301','AT')

print (data)

Snood

Answer 1

Use recursion to flatten the nested dicts

• Thinking Recursively in Python
• Flattening JSON objects in Python
• flatten
• The flatten_json function, will be used to flatten data

def flatten_json(nested_json: dict, exclude: list=['']) -> dict:
    """
    Flatten a list of nested dicts.
    """
    out = dict()
    def flatten(x: (list, dict, str), name: str='', exclude=exclude):
        if type(x) is dict:
            for a in x:
                if a not in exclude:
                    flatten(x[a], f'{name}{a}_')
        elif type(x) is list:
            i = 0
            for a in x:
                flatten(a, f'{name}{i}_')
                i += 1
        else:
            out[name[:-1]] = x

    flatten(nested_json)
    return out

import pandas as pd
from pandas.io.json import json_normalize
import requests

API_url = "https://api.nasa.gov/insight_weather/?api_key=nTal99zKlhGbl0N8F0V9iUofifMdcwyOHw64CrVm&feedtype=json&ver=1.0"
API_data = requests.get(API_url).json()

# create a list of dicts: these are the values of each sol_key
data = [API_data[x] for x in API_data['sol_keys']]

# if you also want the sol_key to be included in the data
# it needs to be added back in as a key: value pair
for i, value in enumerate(data, 301):
    value.update({'sol_key': i})

# expand all the values
df = pd.DataFrame([flatten_json(x) for x in data])

Output

 sol_key   AT_av   AT_ct    AT_mn   AT_mx             First_UTC  HWS_av  HWS_ct  HWS_mn  HWS_mx              Last_UTC   PRE_av  PRE_ct    PRE_mn    PRE_mx  Season  WD_1_compass_degrees WD_1_compass_point  WD_1_compass_right  WD_1_compass_up  WD_1_ct  WD_10_compass_degrees WD_10_compass_point  WD_10_compass_right  WD_10_compass_up  WD_10_ct  WD_11_compass_degrees WD_11_compass_point  WD_11_compass_right  WD_11_compass_up  WD_11_ct  WD_12_compass_degrees WD_12_compass_point  WD_12_compass_right  WD_12_compass_up  WD_12_ct  WD_13_compass_degrees WD_13_compass_point  WD_13_compass_right  WD_13_compass_up  WD_13_ct  WD_2_compass_degrees WD_2_compass_point  WD_2_compass_right  WD_2_compass_up  WD_2_ct  WD_3_compass_degrees WD_3_compass_point  WD_3_compass_right  WD_3_compass_up  WD_3_ct  WD_5_compass_degrees WD_5_compass_point  WD_5_compass_right  WD_5_compass_up  WD_5_ct  WD_6_compass_degrees WD_6_compass_point  WD_6_compass_right  WD_6_compass_up  WD_6_ct  WD_7_compass_degrees WD_7_compass_point  WD_7_compass_right  WD_7_compass_up  WD_7_ct  WD_8_compass_degrees WD_8_compass_point  WD_8_compass_right  WD_8_compass_up  WD_8_ct  WD_9_compass_degrees WD_9_compass_point  WD_9_compass_right  WD_9_compass_up  WD_9_ct  WD_most_common_compass_degrees WD_most_common_compass_point  WD_most_common_compass_right  WD_most_common_compass_up  WD_most_common_ct  WD_14_compass_degrees WD_14_compass_point  WD_14_compass_right  WD_14_compass_up  WD_14_ct  WD_0_compass_degrees WD_0_compass_point  WD_0_compass_right  WD_0_compass_up  WD_0_ct
     301 -69.684  342720 -103.886 -26.371  2019-10-01T11:46:39Z   4.630  158626   0.129  17.919  2019-10-02T12:26:13Z  727.941  153492  711.7187  743.1005  spring                  22.5                NNE            0.382683          0.92388      4.0                  225.0                  SW            -0.707107         -0.707107     26723                  247.5                 WSW             -0.92388         -0.382683     15528                  270.0                   W                 -1.0              -0.0      3136                  292.5                 WNW             -0.92388          0.382683       2.0                  45.0                 NE            0.707107         0.707107      6.0                  67.5                ENE             0.92388         0.382683      688                 112.5                ESE             0.92388        -0.382683     3387                 135.0                 SE            0.707107        -0.707107    40327                 157.5                SSE            0.382683         -0.92388    31608                 180.0                  S                 0.0             -1.0     8520                 202.5                SSW           -0.382683         -0.92388    28697                           135.0                           SE                      0.707107                  -0.707107              40327                    NaN                 NaN                  NaN               NaN       NaN                   NaN                NaN                 NaN              NaN      NaN
     302 -68.977  339696 -102.032 -25.338  2019-10-02T12:26:14Z   4.781  154660   0.208  20.153  2019-10-03T13:05:49Z  727.076  168657  710.8055  741.8326  spring                  22.5                NNE            0.382683          0.92388      1.0                  225.0                  SW            -0.707107         -0.707107     32482                  247.5                 WSW             -0.92388         -0.382683      1508                  270.0                   W                 -1.0              -0.0        27                    NaN                 NaN                  NaN               NaN       NaN                  45.0                 NE            0.707107         0.707107     16.0                  67.5                ENE             0.92388         0.382683     1757                 112.5                ESE             0.92388        -0.382683     2178                 135.0                 SE            0.707107        -0.707107    25516                 157.5                SSE            0.382683         -0.92388    36367                 180.0                  S                 0.0             -1.0    26800                 202.5                SSW           -0.382683         -0.92388    28008                           157.5                          SSE                      0.382683                  -0.923880              36367                    NaN                 NaN                  NaN               NaN       NaN                   NaN                NaN                 NaN              NaN      NaN
     303 -67.094  257650 -103.946 -26.523  2019-10-03T13:05:50Z   4.911  113599   0.131  19.147  2019-10-04T13:45:24Z  724.189  110794  711.2929  741.7360  spring                  22.5                NNE            0.382683          0.92388      6.0                  225.0                  SW            -0.707107         -0.707107     16663                  247.5                 WSW             -0.92388         -0.382683      5999                  270.0                   W                 -1.0              -0.0      8920                  292.5                 WNW             -0.92388          0.382683      23.0                  45.0                 NE            0.707107         0.707107     12.0                  67.5                ENE             0.92388         0.382683      507                 112.5                ESE             0.92388        -0.382683     1041                 135.0                 SE            0.707107        -0.707107    21889                 157.5                SSE            0.382683         -0.92388    29209                 180.0                  S                 0.0             -1.0     9400                 202.5                SSW           -0.382683         -0.92388    19919                           157.5                          SSE                      0.382683                  -0.923880              29209                  315.0                  NW            -0.707107          0.707107      11.0                   NaN                NaN                 NaN              NaN      NaN
     304 -68.042  308602 -104.325 -25.869  2019-10-04T13:45:25Z   4.959  140757   0.132  18.224  2019-10-05T14:25:00Z  724.808  152271  707.9475  741.3935  spring                  22.5                NNE            0.382683          0.92388      6.0                  225.0                  SW            -0.707107         -0.707107     18480                  247.5                 WSW             -0.92388         -0.382683      9226                  270.0                   W                 -1.0              -0.0     16455                  292.5                 WNW             -0.92388          0.382683      12.0                  45.0                 NE            0.707107         0.707107      2.0                  67.5                ENE             0.92388         0.382683     1006                 112.5                ESE             0.92388        -0.382683     1622                 135.0                 SE            0.707107        -0.707107    27717                 157.5                SSE            0.382683         -0.92388    36692                 180.0                  S                 0.0             -1.0    13210                 202.5                SSW           -0.382683         -0.92388    16329                           157.5                          SSE                      0.382683                  -0.923880              36692                    NaN                 NaN                  NaN               NaN       NaN                   NaN                NaN                 NaN              NaN      NaN
     305 -71.205  229742 -104.059 -27.287  2019-10-05T14:25:01Z   4.874  103937   0.128  22.241  2019-10-06T15:04:35Z  722.192  157557  708.6817  738.4189  spring                   NaN                NaN                 NaN              NaN      NaN                  225.0                  SW            -0.707107         -0.707107     15124                  247.5                 WSW             -0.92388         -0.382683      4252                  270.0                   W                 -1.0              -0.0      3027                  292.5                 WNW             -0.92388          0.382683      11.0                   NaN                NaN                 NaN              NaN      NaN                  67.5                ENE             0.92388         0.382683       71                 112.5                ESE             0.92388        -0.382683      712                 135.0                 SE            0.707107        -0.707107    15842                 157.5                SSE            0.382683         -0.92388    34545                 180.0                  S                 0.0             -1.0    13445                 202.5                SSW           -0.382683         -0.92388    16908                           157.5                          SSE                      0.382683                  -0.923880              34545                    NaN                 NaN                  NaN               NaN       NaN                   NaN                NaN                 NaN              NaN      NaN
     306 -72.664  215500 -102.655 -25.681  2019-10-06T15:04:36Z   4.437  101771   0.131  17.113  2019-10-07T15:44:09Z  720.791  125256  706.1014  740.7565  spring                  22.5                NNE            0.382683          0.92388      1.0                  225.0                  SW            -0.707107         -0.707107     16025                  247.5                 WSW             -0.92388         -0.382683      2200                  270.0                   W                 -1.0              -0.0      6820                  292.5                 WNW             -0.92388          0.382683      63.0                  45.0                 NE            0.707107         0.707107      3.0                  67.5                ENE             0.92388         0.382683      265                 112.5                ESE             0.92388        -0.382683      747                 135.0                 SE            0.707107        -0.707107    15702                 157.5                SSE            0.382683         -0.92388    20971                 180.0                  S                 0.0             -1.0    18328                 202.5                SSW           -0.382683         -0.92388    20646                           157.5                          SSE                      0.382683                  -0.923880              20971                    NaN                 NaN                  NaN               NaN       NaN                   NaN                NaN                 NaN              NaN      NaN
     307 -71.995  175881 -102.027 -26.828  2019-10-07T15:44:10Z   4.948   82571   0.206  18.374  2019-10-08T10:12:49Z  724.898   87860  704.6372  739.6598  spring                  22.5                NNE            0.382683          0.92388      7.0                  225.0                  SW            -0.707107         -0.707107     13459                  247.5                 WSW             -0.92388         -0.382683      9642                  270.0                   W                 -1.0              -0.0      6382                    NaN                 NaN                  NaN               NaN       NaN                  45.0                 NE            0.707107         0.707107      3.0                  67.5                ENE             0.92388         0.382683      171                 112.5                ESE             0.92388        -0.382683      655                 135.0                 SE            0.707107        -0.707107    12847                 157.5                SSE            0.382683         -0.92388    19655                 180.0                  S                 0.0             -1.0    12628                 202.5                SSW           -0.382683         -0.92388     7121                           157.5                          SSE                      0.382683                  -0.923880              19655                    NaN                 NaN                  NaN               NaN       NaN                   0.0                  N                 0.0              1.0      1.0

Decrescent

Perfect answer. Thank you! – Snood