Transforming from dict to separate columns

Written by

Sorin Tudor

in

I queried, some time ago, trace route info for IP’s in a list and took the wrong decision to save it in the following form.

At first sight it’s not really an issue but if you want to retrieve it and create a dataframe it will look not as expected.

Here is the actual code to separate the payload column to column defined by keys in dictionary

import pandas as pd

# Assuming 'df' is your DataFrame and 'payload' is the column containing dictionaries
df_expanded = pd.json_normalize(trace_rt_df['payload'])

# Rename columns to match original keys (optional)
df_expanded.columns = df_expanded.columns.map(lambda x: x.split('.')[-1])

# Concatenate the expanded columns with the original DataFrame
df_final = pd.concat([trace_rt_df, df_expanded], axis=1)

# Drop the original 'payload' column (optional)
df_final.drop('payload', axis=1, inplace=True)

After the processing the dataframe will look like

That’s all.

Sorin

More posts