title: Iterate over Pandas dataframe containing nested json dicts with arrays
body: <p>I have a list of more or less homogeneous json dicts, which I loaded into a Pandas dataframe. Any given dict can contain <strong>an arbitraty number</strong> of levels made only of another dicts or arrays, for example:</p>
  
  <p><code>[
      {"id": [0], "options": [{"name": "dhl", "price": 10}]},
      {"id": [0, 1], "options": [{"name": "dhl", "price": 50}, {"name": "fedex", "price": "100"}]},
  ]</code></p>
  
  <p>Now, I would like to be able to <strong>efficiently check particular fields</strong> - against a regex, compare the whole column between two dataframes, and etc. <code>id, options.name, options.price</code> are the fields in this example.</p>
  
  <p>I figured out one way to do it is to flatten a dataframe once, which allows us to use vectorized operations, such as <code>str.contains</code>.</p>
  
  <p><strong>Here's my recursive solution.</strong></p>
  
  <pre><code>def flatten_df(df, i=0, columns_map=None):
      if not columns_map:
          columns_map = {}
  
      for c in df.columns[i:]:
          flattened_columns = expand_column(df, c)
          if flattened_columns.empty:
              i += 1
              continue
  
          def name_column(x):
              new_name = f"{c}_{x}"
              if new_name in df.columns:
                  new_name = f"{c}_{uuid.uuid1().hex[:5]}"
  
              if c in columns_map:
                  columns_map[new_name] = columns_map[c]
              else:
                  columns_map[new_name] = c
              return new_name
  
          flattened_columns = flattened_columns.rename(columns=name_column)
          df = pd.concat([df[:], flattened_columns[:]], axis=1).drop(c, axis=1)
          columns_map.pop(c, None)
          return flatten_df(df, i, columns_map)
      return df, columns_map
  
  def expand_column(df, column):
      mask = df[column].map(lambda x: (isinstance(x, list) or isinstance(x, dict)))
      collection_column = df[mask][column]
      return collection_column.apply(pd.Series)
  </code></pre>
  
  <p>And here's the output:</p>
  
  <p><code>id_0  id_1 options_0_name  options_0_price options_1_name options_1_price
  0   0.0   NaN            dhl               10            NaN             NaN
  1   0.0   1.0            dhl               50          fedex             100</code></p>
  
  <p>Now I am able to perform vectorized methods and map the expanded columns to the original, if needed.</p>
  
  <p>However, since the size of the list can be huge - up to millions of dicts, this solution degrades in performance <strong>significantly</strong> with the increasing number of nested fields (i.e. with increasing number of recursions).</p>
  
  <p>I used <code>pandas.io.json.json_normalize</code> before, but it expands only dicts.</p>
  
  <p>Are there other efficient ways? The data can vary, however the number of operations on it are limited.</p>
  
  <p><strong>Update with performance stats:</strong></p>
  
  <p>These are <code>%prun</code> numbers for array of 200k of items with relatively low number of nested fields:</p>
  
  <pre><code>         101001482 function calls (100789761 primitive calls) in 79.717 seconds
  
     Ordered by: internal time
     List reduced from 478 to 20 due to restriction &lt;20&gt;
  
     ncalls  tottime  percall  cumtime  percall filename:lineno(function)
   22800000   10.062    0.000   16.327    0.000 &lt;ipython-input-8-786bcc78e0b9&gt;:56(&lt;lambda&gt;)
   53689789    9.168    0.000   10.769    0.000 {built-in method builtins.isinstance}
        139    6.827    0.049   44.534    0.320 {pandas._libs.lib.map_infer}
         25    4.134    0.165    6.469    0.259 internals.py:5074(_merge_blocks)
       26/1    3.525    0.136   79.574   79.574 &lt;ipython-input-8-786bcc78e0b9&gt;:1(flatten_df)
         28    2.958    0.106    2.958    0.106 {pandas._libs.algos.take_2d_axis0_object_object}
        217    2.416    0.011    2.416    0.011 {method 'copy' of 'numpy.ndarray' objects}
        100    2.355    0.024    2.355    0.024 {built-in method numpy.core.multiarray.concatenate}
     102236    2.223    0.000    2.784    0.000 generic.py:4378(__setattr__)
      66259    2.022    0.000    2.022    0.000 {pandas._libs.lib.maybe_convert_objects}
      66261    1.606    0.000    2.670    0.000 {method 'get_indexer' of 'pandas._libs.index.IndexEngine' objects}
      66510    1.413    0.000    3.235    0.000 cast.py:971(maybe_cast_to_datetime)
     133454    1.257    0.000    1.257    0.000 {built-in method numpy.core.multiarray.empty}
  69329/34771    1.232    0.000    5.796    0.000 base.py:255(__new__)
  101377/66756    1.178    0.000   21.435    0.000 series.py:166(__init__)
     468050    1.102    0.000    4.105    0.000 common.py:1688(is_extension_array_dtype)
    1850890    1.089    0.000    1.089    0.000 {built-in method builtins.hasattr}
     872564    1.044    0.000    2.070    0.000 &lt;frozen importlib._bootstrap&gt;:1009(_handle_fromlist)
      66400    1.005    0.000    8.859    0.000 algorithms.py:1548(take_nd)
  464282/464168    0.940    0.000    0.942    0.000 {built-in method numpy.core.multiarray.array}
  </code></pre>
  
  <p>I see that the significant time spent on checking the type of the data.</p>