title: Spark: How to flatten data frame with dynamic nested structs / arrays
body: <p>How to tidy data frame(json ) with dynamic nested structs / arrays in PySpark ?</p>
  <p>I have 10000 json files, each has static and dynamic fields as described below.</p>
  <ul>
  <li>Static names: <code>data</code>, <code>label</code>, <code>units</code>, <code>date</code>, <code>val</code>, <code>num</code> (can be hardcoded)</li>
  <li>Dynamic names:<code>data_1_a</code>, <code>data_1000_xyz</code> , <code>name_1a</code> , <code>name_1b</code>, <code>name_10000_xyz</code>, <code>A</code>, <code>B</code> (cannot be hardcoded as they are up to 10000 names / data sub categories)</li>
  </ul>
  <p>Input df:</p>
  <pre><code>root
   |-- data: struct (nullable = true)
   |    |-- data_1000_xyz: struct (nullable = true)
   |    |    |-- name_10000_xyz: struct (nullable = true)
   |    |    |    |-- label: string (nullable = true)
   |    |    |    |-- units: struct (nullable = true)
   |    |    |    |    |-- A: array (nullable = true)
   |    |    |    |    |    |-- element: struct (containsNull = true)
   |    |    |    |    |    |    |-- date: string (nullable = true)
   |    |    |    |    |    |    |-- num: string (nullable = true)
   |    |    |    |    |    |    |-- val: long (nullable = true)
   |    |    |-- name_1b: struct (nullable = true)
   |    |    |    |-- label: string (nullable = true)
   |    |    |    |-- units: struct (nullable = true)
   |    |    |    |    |-- B: array (nullable = true)
   |    |    |    |    |    |-- element: struct (containsNull = true)
   |    |    |    |    |    |    |-- date: string (nullable = true)
   |    |    |    |    |    |    |-- val: long (nullable = true)
   |    |-- data_1_a: struct (nullable = true)
   |    |    |-- name_1a: struct (nullable = true)
   |    |    |    |-- label: string (nullable = true)
   |    |    |    |-- units: struct (nullable = true)
   |    |    |    |    |-- A: array (nullable = true)
   |    |    |    |    |    |-- element: struct (containsNull = true)
   |    |    |    |    |    |    |-- date: string (nullable = true)
   |    |    |    |    |    |    |-- val: long (nullable = true)
   |-- id: long (nullable = true)
  
  
  +-----------------------------------------------------------------------------------------------------------------------------+---+
  |data                                                                                                                         |id |
  +-----------------------------------------------------------------------------------------------------------------------------+---+
  |{{{null, {[{2018, str, 4}, {2019, null, 5}, {2020, str, 6}]}}, {null, {[{2019, 2}, {2020, 3}]}}}, {{label_1, {[{2020, 1}]}}}}|1  |
  +-----------------------------------------------------------------------------------------------------------------------------+---+
  </code></pre>
  <p>Required df:</p>
  <pre><code>+---+--------------+----------------+---------+-------+------+-----+------+
  |id |level_1       |level_2         |level_3  |level_4| date | val | num  | 
  +---+--------------+----------------+---------+-------+------+-----+------+
  |1  |data_1_a      | name_1a        | unit    | A     | 2020 |  1  | null |
  |1  |data_1000_xyz | name_1b        | unit    | B     | 2019 |  2  | null |
  |1  |data_1000_xyz | name_1b        | unit    | B     | 2020 |  3  | null |
  |1  |data_1000_xyz | name_10000_xyz | unit    | A     | 2018 |  4  | str  |
  |1  |data_1000_xyz | name_10000_xyz | unit    | A     | 2019 |  5  | null |
  |1  |data_1000_xyz | name_10000_xyz | unit    | A     | 2020 |  6  | str  |
  +-------------------------------------------------------------------------+
  </code></pre>
  <p>To reproduce input df:</p>
  <pre><code>json_1 = &quot;&quot;&quot;{&quot;id&quot;:1,&quot;data&quot;:{&quot;data_1_a&quot;:{&quot;name_1a&quot;:{&quot;label&quot;:&quot;label_1&quot;,&quot;units&quot;:{&quot;A&quot;:[{&quot;date&quot;:&quot;2020&quot;,&quot;val&quot;:1}]}}},&quot;data_1000_xyz&quot;:{&quot;name_1b&quot;:{&quot;label&quot;:null,&quot;units&quot;:{&quot;B&quot;:[{&quot;date&quot;:&quot;2019&quot;,&quot;val&quot;:2},{&quot;date&quot;:&quot;2020&quot;,&quot;val&quot;:3}]}},&quot;name_10000_xyz&quot;:{&quot;label&quot;:null,&quot;units&quot;:{&quot;A&quot;:[{&quot;date&quot;:&quot;2018&quot;,&quot;val&quot;:4,&quot;num&quot;:&quot;str&quot;},{&quot;date&quot;:&quot;2019&quot;,&quot;val&quot;:5},{&quot;date&quot;:&quot;2020&quot;,&quot;val&quot;:6,&quot;num&quot;:&quot;str&quot;}]}}}}}&quot;&quot;&quot;
  df = spark.read.json(sc.parallelize([json_1]))
  </code></pre>
  <h2>Python Pandas solution:</h2>
  <pre><code>    import pandas as pd
      
      # 1) flatten json
      df = pd.json_normalize(json_1)
      df_dic = df.to_dict('records')
      
      # 2) split to levels
      data = []
      for row in df_dic:
          k={}
          for item in row.items():
              if item[0] == 'id':
                  id = item[1]
              else:  
                  keys  = item[0].split('.')
                  k = {i:s for i,s in enumerate(keys)}
                  k.update({'value':item[1]})
                  k.update({'id':id})
                  data.append(k)
      
      df = (pd.DataFrame(data)[['id',1,2,3,4,'value']]
      .rename(columns={1:'level_1',2:'level_2',3:'level_3',4:'level_4' }))
      df = df.loc[~df['level_4'].isnull()]
  
      # 3) explode
      dfe = df.explode('value', ignore_index=True)
      
      # 4) pop the value column and create a new dataframe from it then join the new frame with the exploded frame.
      output_df = dfe.join(pd.DataFrame([*dfe.pop('value')], index=dfe.index))
      
      
          id  level_1         level_2         level_3 level_4 date    val num
      0   1   data_1_a        name_1a         units   A      2020     1   NaN
      1   1   data_1000_xyz   name_1b         units   B      2019     2   NaN
      2   1   data_1000_xyz   name_1b         units   B      2020     3   NaN
      3   1   data_1000_xyz   name_10000_xyz  units   A      2018     4   str
      4   1   data_1000_xyz   name_10000_xyz  units   A      2019     5   NaN
      5   1   data_1000_xyz   name_10000_xyz  units   A      2020     6   str
  </code></pre>
  <p>Useful links:</p>
  <ul>
  <li><a href="https://stackoverflow.com/questions/68837951/how-to-flatten-nested-arrays-by-merging-values-in-spark">How to flatten nested arrays by merging values in spark</a></li>
  <li><a href="https://stackoverflow.com/questions/59870193/is-there-a-function-in-pyspark-dataframe-that-is-similar-to-pandas-io-json-json">Is there a function in pyspark dataframe that is similar to pandas.io.json.json_normalize</a></li>
  <li><a href="https://stackoverflow.com/questions/37471346/automatically-and-elegantly-flatten-dataframe-in-spark-sql/50156142#50156142">Automatically and Elegantly flatten DataFrame in Spark SQL</a></li>
  <li><a href="https://stackoverflow.com/questions/66476940/flatten-nested-array-in-spark-dataframe/66476985#66476985">Flatten nested array in Spark DataFrame</a></li>
  <li><a href="https://docs.microsoft.com/en-us/azure/synapse-analytics/how-to-analyze-complex-schema" rel="nofollow noreferrer">https://docs.microsoft.com/en-us/azure/synapse-analytics/how-to-analyze-complex-schema</a></li>
  <li><a href="https://stackoverflow.com/questions/62379748/flatten-a-dynamic-nested-struct-struct-inside-struct-in-pyspark">Flatten a dynamic nested struct (struct inside struct) in PySpark</a></li>
  <li><a href="https://towardsdatascience.com/nested-data-types-in-spark-3-1-663e5ed2f2aa" rel="nofollow noreferrer">https://towardsdatascience.com/nested-data-types-in-spark-3-1-663e5ed2f2aa</a></li>
  <li><a href="https://towardsdatascience.com/flattening-json-records-using-pyspark-b83137669def" rel="nofollow noreferrer">https://towardsdatascience.com/flattening-json-records-using-pyspark-b83137669def</a></li>
  <li><a href="https://docs.databricks.com/_static/notebooks/complex-nested-structured.html" rel="nofollow noreferrer">https://docs.databricks.com/_static/notebooks/complex-nested-structured.html</a></li>
  <li><a href="https://docs.databricks.com/delta/data-transformation/higher-order-lambda-functions.html" rel="nofollow noreferrer">https://docs.databricks.com/delta/data-transformation/higher-order-lambda-functions.html</a></li>
  <li><a href="https://docs.databricks.com/_static/notebooks/complex-nested-structured.html" rel="nofollow noreferrer">https://docs.databricks.com/_static/notebooks/complex-nested-structured.html</a></li>
  <li><a href="https://docs.databricks.com/_static/notebooks/transform-complex-data-types-python.html" rel="nofollow noreferrer">https://docs.databricks.com/_static/notebooks/transform-complex-data-types-python.html</a></li>
  <li><a href="https://databricks.com/blog/2017/02/23/working-complex-data-formats-structured-streaming-apache-spark-2-1.html" rel="nofollow noreferrer">https://databricks.com/blog/2017/02/23/working-complex-data-formats-structured-streaming-apache-spark-2-1.html</a></li>
  </ul>