Transforming JSON data

Chances are, when reading data from JSON format into a dictionary, you'll probably have to apply some level of manual transformation to the data before it can be stored in a DataFrame. This is common when working with nested dictionaries, which you'll have the opportunity to explore in this exercise.

The "nested_school_scores.json" file has been read into a dictionary available in the raw_testing_scores variable, which takes the following form:

{
    "01M539": {
        "street_address": "111 Columbia Street",
        "city": "Manhattan",
        "scores": {
              "math": 657,
              "reading": 601,
              "writing": 601
        }
  }, ...
}

This exercise is part of the course

ETL and ELT in Python

View Course

Exercise instructions

Loop through both the keys and values of the raw_testing_scores dictionary.
Extract the "street_address" from each dictionary nested in the raw_testing_scores object.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

normalized_testing_scores = []

# Loop through each of the dictionary key-value pairs
for school_id, school_info in raw_testing_scores.____():
	normalized_testing_scores.append([
    	school_id,
    	school_info.____("____"),  # Pull the "street_address"
    	school_info.get("city"),
    	school_info.get("scores").get("math", 0),
    	school_info.get("scores").get("reading", 0),
    	school_info.get("scores").get("writing", 0),
    ])

print(normalized_testing_scores)

Edit and Run Code