flatten_json is giving me a long list of columns and a single row instead of the required dataframe structure

You can use this example to parse the Json to dataframe:

import pandas as pd


sampleJson = {
       'record1': 
              {
                'text':[   ['A', 'fried', 'is', 'a', 'nice', 'companion', '.'],  
                           ['The', 'birds', 'are', 'flying', '.']],                           
                'values':[ [0, 1, 0, 0],  
                           [1, 1, 0, 1]],
                'pairs':[  [0, 2],  
                           [2, 1]]
             },
  'record2': 
             {
                'text':[   ['We', 'can', 'work', 'hard', 'together', '.'], 
                           ['Let', 'the', 'things', 'happen', '.'], 
                           ['There', 'is', 'always', 'a', 'way', 'out', '.']],
               'values':[   [0, 1, 0, 0], 
                           [0, 1, 1, 1], 
                           [1, 1, 0, 1]],
               'pairs':[   [0, 2],  
                           [3, 4], 
                           [2, 1]]
             },
       }

all_data = []
for k, v in sampleJson.items():
    texts, values, pairs = v['text'], v['values'], v['pairs']
    for t, val, p in zip(texts, values, pairs):
        all_data.append({
            'record': k,
            'text': ' '.join(t),
            'pairs': p,
            **{'val_{}'.format(i): val_ for i, val_ in enumerate(val, 1)}
        })

df = pd.DataFrame(all_data)
print(df)

Prints this dataframe:

    record                           text   pairs  val_1  val_2  val_3  val_4
0  record1  A fried is a nice companion .  [0, 2]      0      1      0      0
1  record1         The birds are flying .  [2, 1]      1      1      0      1
2  record2    We can work hard together .  [0, 2]      0      1      0      0
3  record2        Let the things happen .  [3, 4]      0      1      1      1
4  record2    There is always a way out .  [2, 1]      1      1      0      1

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top