How to expand dictionaries in rows of pandas dataframe with unique column names?-CodePudding

I have a dataframe with rows as dictionaries as below:

Col1                A      B
{'A': 1, 'B': 23}   apple  carrot 
{'A': 3, 'B': 35}   banana spinach

I want to expand Col1 such that the dataframe looks like this:

Col1.A Col2.B  A      B
1       23    apple  carrot
3       35    banana spinach

How can I do this using pandas in python? Please let me know if there is any other way as well.

I tried using pd.explode but the new column names are being duplicated. How to avoid this?

CodePudding user response：

df["Col1.A"] = df["Col1"].map(lambda x: x["A"])
df["Col1.B"] = df["Col1"].map(lambda x: x["B"])
df.drop("Col1", axis=1, inplace=True)

CodePudding user response：

As a generic method that doesn't require knowledge of the dictionary keys:

df = (pd.json_normalize(df.pop('Col1'))
        .add_prefix('Col1.').join(df)
      )

Or, if you don't want to alter df:

out = (pd.json_normalize(df['Col1'])
       .add_prefix('Col1.')
       .join(df.drop(columns='Col1'))
       )

Output:

   Col1.A  Col1.B               A       B
0       1      23           apple  carrot
1       3      35  banana spinach    None

CodePudding user response：

To convert them to columns, you can use:

Col1 = df['Col1'].apply(pd.Series)

Result:

    A   B
0   1   23
1   3   35

Then, if you want, you can add this to your dataframe like this:

Col1.join(df.drop(columns='Col1'),  lsuffix='_Col1')

Output:

     A_Col1 B_Col1  A       B
0    1      23      apple   carrot
1    3      35      banana  spinach