Unnest a records column in a pandas dataframe

user_4303355 0
Viewed 172

I am wondering if there is a pandas way (in-built, or generally better) to unnest a column of records (where records are List[dict]) into a DataFrame.

Sample data:

import pandas as pd


expected = pd.DataFrame({
    'A': [1, 1, 2],
    'asset_id': ["aaa", "AAA", "bbb"],
    'another_prop': [2, 3, 4]
})

df = pd.DataFrame({
    'A':[1,2],
    'B':[
            [
                {"asset_id": "aaa", "another_prop": 2}, 
                {"asset_id": "AAA", "another_prop": 4}
            ],
            [
                {"asset_id": "bbb", "another_prop": 3}
            ]
        ]
    })

My attempt:

def unnest_records(df: pd.DataFrame, col: str) -> pd.DataFrame:
    """ Unnests a column of records into a DataFrame."""
    df_unnested = df.explode(col)  # unnest records
    records = df_unnested.pop(col)  # 1 row per record
    return pd.concat([df_unnested.reset_index(drop=True), pd.io.json.json_normalize(records)], axis=1)

Output:

>>> unnest_records(df, "B")
   A asset_id  another_prop
0  1      aaa             2
1  1      AAA             4
2  2      bbb             3
pandas dataframe python
2 Answers

IIUC explode pd.Series and set_index

df1 = df.set_index('A')['B'].explode().apply(pd.Series).reset_index()

   A asset_id  another_prop
0  1      aaa             2
1  1      AAA             4
2  2      bbb             3

or as @anky so kindly pointed out :

s = df['B'].explode()
df = df[['A']].join(pd.DataFrame(s.tolist(),index=s.index))

print(df)
   A asset_id  another_prop
0  1      aaa             2
0  1      AAA             4
1  2      bbb             3
user_93751020

Another option using itertools:

from itertools import chain

pd.DataFrame(chain(*df.B))
user_78913260
Related