title: Pivot and Transpose Pandas Dataframe dynamically from nested JSON
body: <p>I have a nested JSON looking like below:</p>
  <p>Input JSON:</p>
  <pre><code>data= {
    &quot;name&quot;: &quot;root&quot;,
    &quot;type&quot;: {&quot;sample&quot;:&quot;text&quot;},
    &quot;teacher&quot;: [
      {
        &quot;name&quot;: &quot;properties&quot;,
        &quot;type&quot;: &quot;feature&quot;,
        &quot;father&quot;: [
          {
            &quot;name&quot;: &quot;print&quot;,
            &quot;type&quot;: &quot;feature&quot;,
            &quot;children&quot;: [
              {
                &quot;name&quot;: &quot;graphic print&quot;,
                &quot;type&quot;: &quot;feature&quot;,
                &quot;inherits&quot;: &quot;true&quot;
              },
              {
                &quot;name&quot;: &quot;striped print&quot;,
                &quot;type&quot;: &quot;feature&quot;,
                &quot;inherits&quot;: &quot;true&quot;,
                &quot;sister&quot;: [
                  {
                    &quot;name&quot;: &quot;pinstriped&quot;,
                    &quot;type&quot;: &quot;feature&quot;,
                    &quot;inherits&quot;: &quot;true&quot;
                  },
                  {
                    &quot;name&quot;: &quot;light stripe&quot;,
                    &quot;type&quot;: &quot;feature&quot;,
                    &quot;inherits&quot;: &quot;true&quot;
                  },
                  {
                    &quot;name&quot;: &quot;wide stripe&quot;,
                    &quot;type&quot;: &quot;feature&quot;,
                    &quot;inherits&quot;: &quot;true&quot;
                  }
                ]
              }
            ]
          }
        ]
      },
      {
        &quot;name&quot;: &quot;colours&quot;,
        &quot;type&quot;: &quot;colour&quot;,
        &quot;father&quot;: [
          {
            &quot;name&quot;: &quot;main colours&quot;,
            &quot;type&quot;: &quot;colour&quot;,
            &quot;reacher&quot;: [
              {
                &quot;name&quot;: &quot;black&quot;,
                &quot;type&quot;: &quot;colour&quot;,
                &quot;sister&quot;: [
                  {
                    &quot;name&quot;: &quot;light black&quot;,
                    &quot;type&quot;: &quot;colour&quot;,
                    &quot;inherits&quot;: &quot;true&quot;
                  },
                  {
                    &quot;name&quot;: &quot;blue black&quot;,
                    &quot;type&quot;: &quot;colour&quot;,
                    &quot;inherits&quot;: &quot;true&quot;
                  }
                ]
              },
              {
                &quot;name&quot;: &quot;red&quot;,
                &quot;type&quot;: &quot;colour&quot;,
                &quot;teacher&quot;: [
                  {
                    &quot;name&quot;: &quot;bright red&quot;,
                    &quot;type&quot;: &quot;colour&quot;,
                    &quot;inherits&quot;: &quot;true&quot;
                  },
                  {
                    &quot;name&quot;: &quot;light red&quot;,
                    &quot;type&quot;: &quot;colour&quot;
                  }
                ]
              }
            ]
          }
        ]
      },
      {
        &quot;name&quot;: &quot;genders&quot;,
        &quot;type&quot;: &quot;gender&quot;,
        &quot;children&quot;: [
          {
            &quot;name&quot;: &quot;female&quot;,
            &quot;type&quot;: &quot;gender&quot;
          },
          {
            &quot;name&quot;: &quot;male&quot;,
            &quot;type&quot;: &quot;gender&quot;
          }
        ]
      }
    ]
  }
  </code></pre>
  <p>By creating tree traversal code i can fetch all the keys and value of the JSON but now cannot group columns as they are too much normalized</p>
  <p>I am using below code on my jSON:</p>
  <pre><code>import pandas as pd
  def nested_parser(indict, pre=None):
      pre = pre[:] if pre else []
      if isinstance(indict, dict):
          for key, value in indict.items():
              if isinstance(value, dict):
                  for d in nested_parser(value, pre + [key+'_dict']):
                      yield d
              elif isinstance(value, list) or isinstance(value, tuple):
                  for idx,v in enumerate(value):
                      for d in nested_parser(v, pre + [key+'_array_'+str(idx)]):
                          yield d
              else:
                  yield pre + [key, value]
      else:
          yield pre + [indict]
  lst = [i for i in nested_parser(data)]
  max_dept_elem = len(max(lst, key=len))
  padded_list = [i[:-1]+ [None] * (max_dept_elem-len(i)) + i[-1:] if len(i)&lt;max_dept_elem else i for i in lst]
  df = pd.DataFrame(padded_list)
  df_cols = [&quot;root_level_&quot;+str(col) for col in df.columns.values]
  df.columns = df_cols
  print(df)
  </code></pre>
  <p>mainly this is achieved by complex traversal of dictionary that has lots of list and this way it is working fast. I cannot flatten and explode much faster so using traversal for flattening of complex JSON</p>
  <pre><code>      root_level_0      root_level_1      root_level_2     root_level_3 root_level_4   root_level_5
  0              name              None              None             None         None           root
  1         type_dict            sample              None             None         None           text
  2   teacher_array_0              name              None             None         None     properties
  3   teacher_array_0              type              None             None         None        feature
  4   teacher_array_0    father_array_0              name             None         None          print
  5   teacher_array_0    father_array_0              type             None         None        feature
  6   teacher_array_0    father_array_0  children_array_0             name         None  graphic print
  7   teacher_array_0    father_array_0  children_array_0             type         None        feature
  8   teacher_array_0    father_array_0  children_array_0         inherits         None           true
  9   teacher_array_0    father_array_0  children_array_1             name         None  striped print
  10  teacher_array_0    father_array_0  children_array_1             type         None        feature
  11  teacher_array_0    father_array_0  children_array_1         inherits         None           true
  12  teacher_array_0    father_array_0  children_array_1   sister_array_0         name     pinstriped
  13  teacher_array_0    father_array_0  children_array_1   sister_array_0         type        feature
  14  teacher_array_0    father_array_0  children_array_1   sister_array_0     inherits           true
  15  teacher_array_0    father_array_0  children_array_1   sister_array_1         name   light stripe
  16  teacher_array_0    father_array_0  children_array_1   sister_array_1         type        feature
  17  teacher_array_0    father_array_0  children_array_1   sister_array_1     inherits           true
  18  teacher_array_0    father_array_0  children_array_1   sister_array_2         name    wide stripe
  19  teacher_array_0    father_array_0  children_array_1   sister_array_2         type        feature
  20  teacher_array_0    father_array_0  children_array_1   sister_array_2     inherits           true
  21  teacher_array_1              name              None             None         None        colours
  22  teacher_array_1              type              None             None         None         colour
  23  teacher_array_1    father_array_0              name             None         None   main colours
  24  teacher_array_1    father_array_0              type             None         None         colour
  25  teacher_array_1    father_array_0   reacher_array_0             name         None          black
  26  teacher_array_1    father_array_0   reacher_array_0             type         None         colour
  27  teacher_array_1    father_array_0   reacher_array_0   sister_array_0         name    light black
  28  teacher_array_1    father_array_0   reacher_array_0   sister_array_0         type         colour
  29  teacher_array_1    father_array_0   reacher_array_0   sister_array_0     inherits           true
  30  teacher_array_1    father_array_0   reacher_array_0   sister_array_1         name     blue black
  31  teacher_array_1    father_array_0   reacher_array_0   sister_array_1         type         colour
  32  teacher_array_1    father_array_0   reacher_array_0   sister_array_1     inherits           true
  33  teacher_array_1    father_array_0   reacher_array_1             name         None            red
  34  teacher_array_1    father_array_0   reacher_array_1             type         None         colour
  35  teacher_array_1    father_array_0   reacher_array_1  teacher_array_0         name     bright red
  36  teacher_array_1    father_array_0   reacher_array_1  teacher_array_0         type         colour
  37  teacher_array_1    father_array_0   reacher_array_1  teacher_array_0     inherits           true
  38  teacher_array_1    father_array_0   reacher_array_1  teacher_array_1         name      light red
  39  teacher_array_1    father_array_0   reacher_array_1  teacher_array_1         type         colour
  40  teacher_array_2              name              None             None         None        genders
  41  teacher_array_2              type              None             None         None         gender
  42  teacher_array_2  children_array_0              name             None         None         female
  43  teacher_array_2  children_array_0              type             None         None         gender
  44  teacher_array_2  children_array_1              name             None         None           male
  45  teacher_array_2  children_array_1              type             None         None         gender
  </code></pre>
  <p>I have also tried grouping and collecting as list but dont know how to proceed further to convert these columns to rows.</p>
  <pre><code>grouped_df = df.groupby(['root_level_0','root_level_1','root_level_2','root_level_3','root_level_4'])['root_level_5'].apply(list)
  print(grouped_df)
  
  
  root_level_0     root_level_1    root_level_2      root_level_3     root_level_4
  teacher_array_0  father_array_0  children_array_1  sister_array_0   inherits                [true]
                                                                      name              [pinstriped]
                                                                      type                 [feature]
                                                     sister_array_1   inherits                [true]
                                                                      name            [light stripe]
                                                                      type                 [feature]
                                                     sister_array_2   inherits                [true]
                                                                      name             [wide stripe]
                                                                      type                 [feature]
  teacher_array_1  father_array_0  reacher_array_0   sister_array_0   inherits                [true]
                                                                      name             [light black]
                                                                      type                  [colour]
                                                     sister_array_1   inherits                [true]
                                                                      name              [blue black]
                                                                      type                  [colour]
                                   reacher_array_1   teacher_array_0  inherits                [true]
                                                                      name              [bright red]
                                                                      type                  [colour]
                                                     teacher_array_1  name               [light red]
                                                                      type                  [colour]
  </code></pre>
  <p>I have tried multiple things but not able to achieve data frame like below:</p>
  <pre><code>   name type.sample teacher.name teacher.type  ... teacher.father.reacher.teacher.type teacher.father.reacher.teacher.inherits teacher.children.name teacher.children.type
  0  root        text   properties      feature  ...                                 NaN                                     NaN                   NaN                   NaN
  1  root        text   properties      feature  ...                                 NaN                                     NaN                   NaN                   NaN
  2  root        text   properties      feature  ...                                 NaN                                     NaN                   NaN                   NaN
  3  root        text   properties      feature  ...                                 NaN                                     NaN                   NaN                   NaN
  4  root        text      colours       colour  ...                                 NaN                                     NaN                   NaN                   NaN
  5  root        text      colours       colour  ...                                 NaN                                     NaN                   NaN                   NaN
  6  root        text      colours       colour  ...                              colour                                    true                   NaN                   NaN
  7  root        text      colours       colour  ...                              colour                                     NaN                   NaN                   NaN
  8  root        text      genders       gender  ...                                 NaN                                     NaN                female                gender
  9  root        text      genders       gender  ...                                 NaN                                     NaN                  male                gender
  </code></pre>
  <p>Any help will be great.</p>