Fill predefined pandas dataframe

user_1030099 0
Viewed 55

I have a predefined dataframe df1:

import pandas as pd

df1 = pd.DataFrame(columns = ["id", "col_a","col_b","col_c"])

I have a json file:

{'123': {'col_a': 5, 'col_b': "kuku", 'col_c': True}}

Or sometimes it can be:

{'123': {'col_a': 5, 'col_b': ["kuku", "bubu", "fooo"], 'col_c': True}}

Where '123' key is the id.

I want to add these json's data to my dataframe as new line/lines but if col_b is a list, I want it to be:

id    col_a col_b col_c
123   5     kuku  True
123   5     bubu  True
123   5     fooo  True

Or apply one hot encoder to col_b.

I am using pd.json_normalize but I want to also unnest the list and add the id value.

Please advise what is the best approach to do this?

pandas dataframe python-3.x python json
1 Answers

Not the cleanest, but you can do:

df1 = df1.append(pd.DataFrame(new_json).T
                   .explode('col_b')
                   .rename_axis('id')
                   .reset_index()
                )

Output:

    id col_a col_b col_c
0  123     5  kuku  True
1  123     5  bubu  True
2  123     5  fooo  True
user_42384080
Related