title: Pandas - expand nested json array within column in dataframe
body: <p>I have a json data (coming from mongodb) containing thousands of records (so an array/list of json object) with a structure like the below one for each object:</p>
  
  <pre><code>{
     "id":1,
     "first_name":"Mead",
     "last_name":"Lantaph",
     "email":"mlantaph0@opensource.org",
     "gender":"Male",
     "ip_address":"231.126.209.31",
     "nested_array_to_expand":[
        {
           "property":"Quaxo",
           "json_obj":{
              "prop1":"Chevrolet",
              "prop2":"Mercy Streets"
           }
        },
        {
           "property":"Blogpad",
           "json_obj":{
              "prop1":"Hyundai",
              "prop2":"Flashback"
           }
        },
        {
           "property":"Yabox",
           "json_obj":{
              "prop1":"Nissan",
              "prop2":"Welcome Mr. Marshall (Bienvenido Mister Marshall)"
           }
        }
     ]
  }
  </code></pre>
  
  <p>When loaded in a dataframe the "nested_array_to_expand" is a string containing the json (I do use "json_normalize" during loading). The expected result is to get a dataframe with 3 row (given the above example) and new columns for the nested objects such as below:</p>
  
  <pre><code>index   email first_name gender  id      ip_address last_name  \
  0  mlantaph0@opensource.org       Mead   Male   1  231.126.209.31   Lantaph   
  1  mlantaph0@opensource.org       Mead   Male   1  231.126.209.31   Lantaph   
  2  mlantaph0@opensource.org       Mead   Male   1  231.126.209.31   Lantaph   
  
    test.name                                      test.obj.ahah test.obj.buzz  
  0     Quaxo                                      Mercy Streets     Chevrolet  
  1   Blogpad                                          Flashback       Hyundai  
  2     Yabox  Welcome Mr. Marshall (Bienvenido Mister Marshall)        Nissan  
  </code></pre>
  
  <p>I was able to get that result with the below function but it extremely slow (around 2s for 1k records) so I would like to either improve the existing code or find a completely different approach to get this result.</p>
  
  <pre><code>def expand_field(field, df, parent_id='id'):
      all_sub = pd.DataFrame()
      # we need an id per row to be able to merge back dataframes
      # if no id, then we will create one based on index of rows
      if parent_id not in df:
          df[parent_id] = df.index
  
      # go through all rows and create a new dataframe with values
      for i, row in df.iterrows():
          try:
              sub = json_normalize(df[field].values[i])
              sub = sub.add_prefix(field + '.')
              sub['parent_id'] = row[parent_id]
              all_sub = all_sub.append(sub)
          except:
              print('crash')
              pass
      df = pd.merge(df, all_sub, left_on=parent_id, right_on='parent_id', how='left')
      #remove old columns
      del df["parent_id"]
      del df[field]
      #return expanded dataframe
      return df
  </code></pre>
  
  <p>Many thanks for your help.</p>
  
  <p>===== EDIT for answering comment ==== </p>
  
  <p>The data loaded from mongodb is an array of object.
  I load it with the following code:</p>
  
  <pre><code>data = json.loads(my_json_string)
  df = json_normalize(data)
  </code></pre>
  
  <p>The output give me a dataframe with df["nested_array_to_expand"] as dtype object (string) </p>
  
  <pre><code>0    [{'property': 'Quaxo', 'json_obj': {'prop1': '...
  Name: nested_array_to_expand, dtype: object
  </code></pre>