How to flatten a list of list of dicts in pandas?-CodePudding

I have an object like

l = [
{'id': 1, 'name': 'a', 'obj2': [{'a': 3, 'b': 6}, {'a':4, 'b': 5}], 'obj': [{'x': 6, 'y': 'p'}, {'x': 10, 'y': 'q', 'z': 'qqq'}]},
{'id': 2, 'name': 'b', 'obj': [{'x': 10, 'y': 'r'}], 'obj2': [{'a': 9, 'i': 's'}]}
]

and I want to make it a dataframe like:

id   name    a    i    b    x    y   z
1     a      3         6    6    p 
1     a      3         6    10   q   qqq
1     a      4         5    6    p 
1     a      4         5    10   q   qqq 
2     b      9    s         10   r

Inside the l, all keys will be the same. But I may have different l with different key name and different amount of objects with lists inside l[0]. Any help is much appreciated.

CodePudding user response：

This is a perfect use case for pd.json_normalize:

l = [{'id': 1, 'name': 'a', 'obj': [{'x': 6, 'y': 'p'}, {'x': 10, 'y': 'q', 'z': 'qqq'}]},
     {'id': 2, 'name': 'b', 'obj': [{'x': 10, 'y': 'r'}]}]

df = pd.json_normalize(l, 'obj', ['id', 'name'])
print(df)

# Output:
    x  y    z id name
0   6  p  NaN  1    a
1  10  q  qqq  1    a
2  10  r  NaN  2    b

Udpate:

I want to use the same code for every object that has that structure type, but maybe the id,name,obj will be named differently

keys = list(l[0].keys())
df = pd.json_normalize(l, keys[-1], keys[:-1])
print(df)

# Output:
    x  y    z id name
0   6  p  NaN  1    a
1  10  q  qqq  1    a
2  10  r  NaN  2    b