Flatten JSON columns in a dataframe with lists-CodePudding

I have a JSON in a dataframe column as:

x = '''{"sections": 
[{
    "id": "12ab", 
    "items": [
        {"id": "34cd", 
        "isValid": true, 
        "questionaire": {"title": "blah blah", "question": "Date of Purchase"}
        },
        {"id": "56ef", 
        "isValid": true, 
        "questionaire": {"title": "something useless", "question": "Date of Billing"}
        }
    ]
}],
"ignore": "yes"}'''

I wanted the id, the internal id inside the items list and the question from the questionaire json:

I was able to extract the info using the below code:

df_norm = json_normalize(json.loads(x)['sections'])
df_norm = df_norm[['id', 'items']]
df1 = (pd.concat({k: pd.DataFrame(v) for k, v in df_norm.pop('items').items()}).reset_index(level=1, drop=True))
df = df_norm.join(df1, rsuffix='_').reset_index(drop=True)
df['child_id'] = df.pop('id_')
df = df[['id', 'child_id', 'questionaire']]
df.questionaire = df.questionaire.fillna({i: {} for i in df.index})
idx = df.set_index(['id', 'child_id']).questionaire.index
result = pd.DataFrame(df.
                      set_index(['id', 'child_id']).
                      questionaire.values.tolist(),index=idx).reset_index()
result = result[['id','child_id','question']]
result

Result DataFrame looks like this. You can run it to verify:

	id	child_id	question
0	12ab	34cd	Date of Purchase
1	12ab	56ef	Date of Billing

My problem is to make this work with a Dataframe where the json value shared above is a column in itself. The input I actually have looks like this:

id	name	location	flatten
1	xyz	new york	the json 'x' above

I am unable to tie it back when I have to do it for multiple such JSONs as a column value.

The final result DataFrame I would want is:

Masterid	name	location	id	child_id	question
1	xyz	new york	12ab	34cd	Date of Pruchase
1	xyz	new york	12ab	56ef	Date of Billing

CodePudding user response：

Idea is use dictionary comprehension with column flatten for i for index values, so after concat is possible join to original DataFrame:

x = '''{"sections": 
[{
    "id": "12ab", 
    "items": [
        {"id": "34cd", 
        "isValid": true, 
        "questionaire": {"title": "blah blah", "question": "Date of Purchase"}
        },
        {"id": "56ef", 
        "isValid": true, 
        "questionaire": {"title": "something useless", "question": "Date of Billing"}
        }
    ]
}],
"ignore": "yes"}'''


df = pd.DataFrame({'id':['1','2'], 'name':['xyz', 'abc'], 
                    'location':['new york', 'wien'], 'flatten':[x,x]})

#create default RangeIndex
df = df.reset_index(drop=True)

d = {i: pd.json_normalize(json.loads(x)['sections'],
                          'items', ['id'], 
                          record_prefix='child_')[['id','child_id','child_questionaire.question']]
                             .rename(columns={'child_questionaire.question':'question'})
     for  i, x in df.pop('flatten').items()}

df_norm = df.rename(columns={'id':'Masterid'}).join(pd.concat(d).reset_index(level=1, drop=True))

print (df_norm)
  Masterid name  location    id child_id          question
0        1  xyz  new york  12ab     34cd  Date of Purchase
0        1  xyz  new york  12ab     56ef   Date of Billing
1        2  abc      wien  12ab     34cd  Date of Purchase
1        2  abc      wien  12ab     56ef   Date of Billing