Convert Nested JSON in to Dataframe using json_normalize

Question

Trying to flattened JSON data in to pandas dataframe generated from API response.

In below code, i flattened raw json data in to different columns using Json_normlize

raw_json = json_normalize(data["data"],sep="_")

Pages contains JSON data mentioned in col1 & col2.Using apply(pd.Series), split pages data in to 2 different columns named col1 & col2

raw_pages=raw_json['pages'].apply(pd.Series)

Now, I need help in extracting (id, choice_id,row_id,simple_text & heading) from col1 & col2.

Col1:

{
  'id': '124550266',
  'questions': [
    {
      'id': '471362125',
      'answers': [
        {
          'tag_data': [
            {
              'hexcolor': '671E75',
              'label': 'sm_processed_q',
              'tag_type': 'sentiment'
            },
            {
              'hexcolor': '671E75',
              'label': 'sm_processed_q',
              'tag_type': 'sentiment'
            }
          ],
          'simple_text': '9999999999'
        }
      ],
      'family': 'open_ended',
      'subtype': 'single',
      'heading': 'Please confirm your registered number with us'
    },
    {
      'id': '471362121',
      'answers': [
        {
          'choice_id': '3114700230',
          'row_id': '3114700224',
          'simple_text': '5'
        }
      ],
      'family': 'matrix',
      'subtype': 'rating',
      'heading': 'How likely is it that you would recommend xxxxx to a friend or colleague?'
    }
  ]
}

COl2:

{
  'id': '124550267',
  'questions': [
    {
      'id': '471362124',
      'answers': [
        {
          'choice_id': '3114700246',
          'row_id': '3114700251'
          
        },
        {
          'choice_id': '3114700247',
          'row_id': '3114700254'
          
        },
        {
          'choice_id': '3114700248',
          'row_id': '3114700255'
          
        },
        {
          'choice_id': '3114700248',
          'row_id': '3114700257'
          
        },
        {
          'choice_id': '3114700249',
          'row_id': '3114700259'
          
        }
      ],
      'family': 'matrix',
      'subtype': 'rating',
      'heading': 'Dear customer how much would you rate us)'
    },
    {
      'id': '471362122',
      'answers': [
        {
          'tag_data': [
            {
              'hexcolor': '05467E',
              'label': 'sm_positive',
              'tag_type': 'sentiment'
            }
          ],
          'simple_text': 'More variety  should be addwd'
        }
      ],
      'family': 'open_ended',
      'subtype': 'essay',
      'heading': 'Dear Customer,<br>Kindly help us, by providing your valuable suggestions or feedback'
    }
  ]
}

score 0 · Answer 1 · answered Jun 25 '20 at 13:03

Try this, d1 and d2 are the dicts you posted above:

df1 = pd.json_normalize(d1,
                        record_path=['questions', 'answers'],
                        meta=[['id'], ['questions', 'heading']],
                        meta_prefix='toplevel.').drop(columns=['tag_data'])
print(df1)

df2 = pd.json_normalize(d2,
                        record_path=['questions', 'answers'],
                        meta=[['id'], ['questions', 'heading']],
                        meta_prefix='toplevel.').drop(columns=['tag_data'])
print(df2)

df1:

  simple_text   choice_id      row_id toplevel.id                         toplevel.questions.heading
0  9999999999         NaN         NaN   124550266      Please confirm your registered number with us
1           5  3114700230  3114700224   124550266  How likely is it that you would recommend xxxx...

df2:

    choice_id      row_id                    simple_text toplevel.id                         toplevel.questions.heading
0  3114700246  3114700251                            NaN   124550267          Dear customer how much would you rate us)
1  3114700247  3114700254                            NaN   124550267          Dear customer how much would you rate us)
2  3114700248  3114700255                            NaN   124550267          Dear customer how much would you rate us)
3  3114700248  3114700257                            NaN   124550267          Dear customer how much would you rate us)
4  3114700249  3114700259                            NaN   124550267          Dear customer how much would you rate us)
5         NaN         NaN  More variety  should be addwd   124550267  Dear Customer,<br>Kindly help us, by providing...

Convert Nested JSON in to Dataframe using json_normalize

1 Answers1

Linked