How to split multiple dictionaries in row into new rows using Pandas-CodePudding

I have the following dataframe with multiple dictionaries in a list in the Rules column.

SetID      SetName             Rules
    0         Standard_1        [{'RulesID': '10', 'RuleName': 'name_abc'}, {'RulesID': '11', 'RuleName': 'name_xyz'}]   
    1         Standard_2        [{'RulesID': '12', 'RuleName': 'name_arg'}]

The desired output is:

SetID      SetName             RulesID        RuleName         
    0         Standard_1        10            name_abc
    0         Standard_1        11            name_xyz 
    1         Standard_2        12            name_arg

It might be possible that there are more than two dictionaries inside of the list.

I am thinking about a pop, explode or pivot function to build the dataframe but I have no clue how to start.

Each advice will be very appreciated!

EDIT: To build the dataframe you can use the follwing dataframe constructor:

# initialize list of lists
data = [[0, 'Standard_1', [{'RulesID': '10', 'RuleName': 'name_abc'}, {'RulesID': '11', 'RuleName': 'name_xyz'}]], [1, 'Standard_2', [{'RulesID': '12', 'RuleName': 'name_arg'}]]]
 
# Create the pandas DataFrame
df = pd.DataFrame(data, columns = ['SetID', 'SetName', 'Rules'])

CodePudding user response：

You can use explode:

tmp = df.explode('Rules').reset_index(drop=True)
df = pd.concat([tmp, pd.json_normalize(tmp['Rules'])], axis=1).drop('Rules', axis=1)

Output:

>>> df
   SetID     SetName RulesID  RuleName
0      0  Standard_1      10  name_abc
1      0  Standard_1      11  name_xyz
2      1  Standard_2      12  name_arg

One-liner version of the above:

df.explode('Rules').reset_index(drop=True).pipe(lambda x: pd.concat([tmp, pd.json_normalize(tmp['Rules'])], axis=1)).drop('Rules', axis=1)