How to flatten list of dictionaries in multiple columns of pandas dataframe

user_9555517 0
Viewed 1189

I have a dataframe and each record stores a list of dictionaries like this:

row prodect_id recommend_info
  0 XQ002      [{"recommend_key":"XXX567","recommend_point":50},
                {"recommend_key":"XXX236","recommend_point":20},
                {"recommend_key":"XXX090","recommend_point":35}]
  1 XQ003      [{"recommend_key":"XXX089","recommend_point":30},
                {"recommend_key":"XXX567","recommend_point":20}]

I would like to flatten lists of dictionaries, so that it will look like this

row prodect_id recommend_info_recommend_key recommend_info_recommend_point
  0 XQ002      XXX567                       50
  1 XQ002      XXX236                       20
  2 XQ002      XXX090                       35
  3 XQ003      XXX089                       30
  4 XQ003      XXX567                       20

I know how to convert only one list of dictionaries to a dataframe. like this:

d = [{"recommend_key":"XXX089","recommend_point":30},
     {"recommend_key":"XXX567","recommend_point":20}]

df = pd.DataFrame(d)

row recommend_key recommend_point
  0 XXX089        30
  1 XXX567        20

But I don't know how to do this to a dataframe when there is one column storing list of dicts, or there are multiple columns storing list of dicts

row  col_a  col_b                  col_c
  0  B001   [{"a":"b"},{"a":"c"}]  [{"y":11},{"a":"c"}]
  1  D009   [{"c":"o"},{"g":"c"}]  [{"y":11},{"a":"c"},{"l":"c"}]   
  2  G068   [{"c":"b"},{"a":"c"}]  [{"a":56},{"d":"c"}]
  3  C004   [{"d":"a"},{"b":"c"}]  [{"c":22},{"a":"c"},{"b":"c"}]
  4  F011   [{"h":"u"},{"d":"c"}]  [{"h":27},{"d":"c"}]
pandas flatten dataframe dictionary python
2 Answers

Try:

pd.concat([df.explode('recommend_info').drop(['recommend_info'], axis=1),
           df.explode('recommend_info')['recommend_info'].apply(pd.Series)],
          axis=1)

You can do the same thing over and over again with every column

Here is an example:

>>> df = pd.DataFrame({'a': [[{3: 4, 5: 6}, {3:8, 5: 1}],
...                          [{3:2, 5:4}, {3: 8, 5: 10}]],
...                    'b': ['X', "Y"]})
>>> df
                               a  b
0   [{3: 4, 5: 6}, {3: 8, 5: 1}]  X
1  [{3: 2, 5: 4}, {3: 8, 5: 10}]  Y
>>> df = pd.concat([df.explode('a').drop(['a'], axis=1),
...                 df.explode('a')['a'].apply(pd.Series)],
...                axis=1)
>>> df
   b  3   5
0  X  4   6
0  X  8   1
1  Y  2   4
1  Y  8  10
user_52961060

I had a data frame that contained several columns. One of the columns contained a list with one dictionary in each list. I needed the dictionary to be exploded and then appended to the same row it came from. Riccardo's answer mostly worked for me. I have generalized it a bit below:

def explode_column_from_list_dict(df_in, column_name_to_explode):
    df = df_in.copy()
    df = pd.concat(
        [
            df.explode(column_name_to_explode).drop([column_name_to_explode], axis=1),
            df.explode(column_name_to_explode)[column_name_to_explode].apply(pd.Series),
        ],
        axis=1,
    )
    return df
user_54032140
Related