CODEWITHSUNDEEP
pythonpandasdataframe
piyush daga
piyush daga

Reputation: 501

Unnest dataframes in a column pandas

I have dataframes in column in a dataframe. I would like to unnest them so that each df comes as rows.

Input df:

# dummy data
df = pd.DataFrame.from_dict(
    {
        'col1': [1, 2],
        'col2': [pd.DataFrame.from_dict(
            {'inner_col1': ['one', 'two', 'three'],
            'inner_col2': ['four', 'five', 'six']}),
                 pd.DataFrame.from_dict(
            {'inner_col1': ['seven', 'eight', 'nine'],
            'inner_col2': ['ten', 'eleven', 'twelve']})
                ]
    }
)

# Output
    col1    col2
0   1   inner_col1 inner_col2 0 one fou...
1   2   inner_col1 inner_col2 0 seven te...

Required Output:

    col1   inner_col1   inner_col2
0   1      one              four
1   1      two              five
2   1      three            six
3   2      seven            ten
4   2      eight            eleven
5   2      nine             twelve

What I have tried to do (but failed):

pd.concat(df.drop('col2', axis=1), df.col2)

Upvotes: 1

Views: 1213

Answers (1)

jezrael
jezrael

Reputation: 862591

Idea is create dictionary of DataFrames with index by col1 column, so possible concat together. Then reset-index is for removed original index values of each DataFrame and second for convert index to column:

df = (pd.concat(df.set_index('col1').pop('col2').to_dict())
       .rename_axis(('col1','new'))
       .reset_index(level=0)
       .reset_index(drop=True))
print (df)

   col1 inner_col1 inner_col2
0     1        one       four
1     1        two       five
2     1      three        six
3     2      seven        ten
4     2      eight     eleven
5     2       nine     twelve

Upvotes: 5

Related Questions