How can I use the key and the value from a dictionary in columns

Question

I have the following dataframe:

print(df.head())

Line                        values
Line1                       {'ValueType1': 1}
Line2                       {'ValueType2': 3}
Line3                       {'ValueType3': 5, 'ValueType4': 6}

I want to have the following dataframe at the end:

Line                        Valuetype          value
Line1                       ValueType1         1
Line2                       ValueType2         3
Line3                       ValueType3         5
Line3                       ValueType4         6

How can I achieve this?

read: https://stackoverflow.com/questions/13575090 – binaryescape Aug 01 '23 at 14:37 — binaryescape, Aug 01 '23 at 14:37

score 1 · Answer 1 · answered Aug 01 '23 at 14:45

Use json_normalize to convert the dictionaries to columns
join to the original DataFrame and drop unnecessary columns
melt the dataframe to unpivot from wide to long
dropna and reset_index to get the output

>>> (df.join(pd.json_normalize(df["values"]))
       .drop("values", axis=1)
       .melt(id_vars="Line")
       .dropna()
       .reset_index(drop=True)
       )

    Line   Valuetype  value
0  Line1  ValueType1    1.0
1  Line2  ValueType2    3.0
2  Line3  ValueType3    5.0
3  Line3  ValueType4    6.0

How can I use the key and the value from a dictionary in columns

1 Answers1