<jupyter_start><jupyter_text>SpaceX & Wine, Reading & Reshaping Nested Data  Table of Contents1. Introduction2. Install & Import Packages3. SpaceX Launches4. 10 of My Favorite Wines from the Past Few Years4a. Method 1: .to_list()4b. Method 2: .apply(pd.Series)  1. Introduction We often encounter nested data, including in json files or dataframes with nested data columns. Today, we'll walk through a couple of examples of how to read and reshape nested data. In the 1st example, we'll work with a complex json file of SpaceX launches found here (https://api.spacexdata.com/v3/capsules) that includes a nested list of dictionaries within a list of dictionaries. We'll set up the json_normalize function with the arguments record_path and meta, as well as set the index and data types to get a usable dataframe at the end. In the 2nd example, we'll work with a manually created dataframe of 10 of my favorite wines from the past few years that includes a column of dictionaries, including variety, region, country, and vintage. We'll walk through two methods to flatten out the dictionaries to get the dataframe of all the wine info we want. The purpose of this is to demonstrate how to read and reshape common types of nested data to create usable dataframes for further analysis.   2. Install & Import Packages<jupyter_code>import pandas as pd
from datetime import datetime
import json
from pandas import json_normalize
import requests 
import urllib.request<jupyter_output><empty_output><jupyter_text>3. SpaceX Launches In the 1st example, we'll work with a complex json file of SpaceX launches.<jupyter_code># Source: https://api.spacexdata.com/v3/capsules

# Load json data from url
with urllib.request.urlopen('https://api.spacexdata.com/v3/capsules') as url:
    spacex_json = json.loads(url.read().decode())
    
# Our json is a list of dictionaries that also includes a nested list of dictionaries under missions 
spacex_json
# If we simply call json_normalize, we see that missions is not flattened out and still includes a list
spacex = json_normalize(spacex_json, sep='_')
spacex
# Let's also check the shape. We have 19 observations. Since we want to flatten out the missions dictionary, we should expect this to increase in our final dataframe
# Note that since missions includes 3 empty lists, these will be lost in our next step - these are future missions that haven't yet happened
spacex.shape
# Since we have a complex json, under arguments, let's include record_path for missions and all the columns we want to keep under the argument meta. Let's set the index to name of the mission
spacex = json_normalize(spacex_json, sep='_', record_path='missions', meta=['capsule_serial','capsule_id', 'status', 'original_launch','details','reuse_count']).set_index('name')

# Let's change the original launch column to a datetime data type
spacex['original_launch'] = pd.to_datetime(spacex['original_launch'])

# Let's check. We have a new data frame with the missions dictionaries flattened out into columns and the name set as index 
spacex
# Checking the shape, we see that we have more observations as missions have been added (we've also lost some from the empty lists)
spacex.shape<jupyter_output><empty_output><jupyter_text>4. 10 of My Favorite Wines from the Past Few Years In the 2nd example, we'll manually create dataframe, including a column of nested data, of 10 of my favorite wines over the past few years and apply 2 methods to reshape it.<jupyter_code># Create list of producers of the wine I had
Producers = ['Descendientes de J Palacios','Petrusa','<NAME>','<NAME>','Quinta do Crasto','Bargylus','Dom do Bibei Lalama','Avignonesi','<NAME>ia','Alcyone']

# Create list of dictionaries containing info on each wine producer for the wine I had 
Info = [{'Variety': 'Mencia', 'Region':'Bierzo', 'Country':'Spain','Vintage':'2006'},
         {'Variety': 'Schioppetino', 'Region':'Colli Orientali del Friuli','Country':'Italy', 'Vintage':'2010'},
         {'Variety': 'Prokupac', 'Region':'West Morava','Country':'Serbia', 'Vintage':'2016'},
         {'Variety': 'Old Vines', 'Region':'Douro','Country':'Portugal', 'Vintage':'2015'},
         {'Variety': 'Plavac Mali', 'Region':'Hvar','Country':'Croatia', 'Vintage':'2014'},
         {'Variety': 'Syrah-Cabarnet-Merlot', 'Region':'Latakia','Country':'Syria', 'Vintage':'2010'},
         {'Variety': 'Mencia', 'Region':'Ribeira Sacra','Country':'Spain', 'Vintage':'2010'},
         {'Variety': 'Merlot', 'Region':'Toscana (IGT)','Country':'Italy', 'Vintage':'2010'},
         {'Variety': 'Tempranillo-Garnacha', 'Region':'Rioja', 'Country':'Spain','Vintage':'2005'},
         {'Variety': 'Tannat', 'Region':'Canelones','Country':'Uruguay', 'Vintage':'2016'}]

# Create dataframe calling dictionary constructor with keys as producrs and info
wines = pd.DataFrame(dict(Producers=Producers,Info=Info))

# Checking the dataframe - the info column contains dictionaries
wines<jupyter_output><empty_output><jupyter_text>4a. Method 1: .to_list() One method to convert the above dataframe with a column of dictionaries is to call .to_list() to flatten out dictionaries into a list, convert to a dataframe, and concatenate with producers.<jupyter_code># Let's check. we see that we have 1 column for each key
info = wines['Info'].to_list()

# Convert list into a dataframe
info = pd.DataFrame(info)

# Let's check - we have 1 column for each key
info
# Concatenate Producers column and wine info columns 
pd.concat([wines['Producers'], info], axis=1)<jupyter_output><empty_output><jupyter_text>4b. Method 2: .apply(pd.Series) Another method is to call .apply(pd.Series), drop the original nested column, and concatenate with producers.<jupyter_code># Flatten out dictionary. pd.Series is applied to each value in info. 
info = wines['Info'].apply(pd.Series)

# Let's check, we have 1 column for each key
info
# Drop original nested column
wines = wines.drop(columns='Info')

# Concatenate with info dataframe
pd.concat([wines,info], axis=1)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10490553529996685, 'fraction_numerical': 0.013258203513423931, 'mean_word_length': 5.366033755274262, 'pattern_counts': {'":': 0, '<': 24, '<?xml version=': 0, '>': 24, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '157120', 'license': 'MIT', 'n_tokens_mistral': 1869, 'n_tokens_neox': 1715, 'n_words': 720, 'path': 'SpaceX and Wine, Reading and Reshaping Nested Data.ipynb', 'repo_name': 'hrishipoola/SpaceX_Wine_Reading_Reshaping_Nested_Data', 'size': '6046'}