get dict data from dataframe column into new dataframe using dict keys as headers

Question

I have a dataframe where one column is full of dicts:

                  Col
Index1   {"A":1, "B":2, "C":3}
Index2   {"A":4, "B":5, "C":6}
.
.
.

I would like a new dataframe as such:

          A    B    C
Index1    1    2    3 
Index2    4    5    6
.
.
.

As you can see, the dict keys in the DF column should become the new headers. Ideally I would like to preserve the indexes.

I have attempted to this using from_dict to create a new Dataframe from that column, but it is not working.

I.e.

new_DF = pd.DataFrame.from_dict(old_DF[Col])

And I am getting the error: ValueError: If using all scalar values, you must pass an index

I have tried other methods as well to no avail. Any guidance would be appreciated.

U13-Forward · Accepted Answer · 2021-09-20 03:01:47Z

1

Use apply with pd.Series:

>>> df['Col'].apply(pd.Series)
        A  B  C
Index1  1  2  3
Index2  4  5  6
>>>

Or use pd.json_normalize:

>>> pd.json_normalize(df['Col']).set_axis(df.index)
        A  B  C
Index1  1  2  3
Index2  4  5  6
>>>

Or try adding tolist:

>>> pd.DataFrame(df['Col'].tolist(), index=df.index)
        A  B  C
Index1  1  2  3
Index2  4  5  6
>>>

Edit:

If your column is string type, try:

from ast import literal_eval
df['Col'].map(literal_eval).apply(pd.Series)

        A  B  C
Index1  1  2  3
Index2  4  5  6

answered Sep 20, 2021 at 2:49

U13-Forward

71.8k15 gold badges100 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.