How to explode a dict and keep the relation with a certain column-CodePudding

Given a dataframe:

id                      text
1   {'txt': 'en', 'longit': 0, 'latit': 0}
2   {'txt': 'fr', 'longit': 10, 'latit': 101, 'gross': 120.1}

I want to get this as output:

id     txt     longit    latit   gross
1       en       0         0      NaN
2       fr       10       101     120.1

What I did:

l=df.text.to_list()
c=pd.DataFrame(eval(str(l)))

The lines above works to create a dataframe but I am not get the right result as I want the id column together.

CodePudding user response：

Another possible solution, using pandas.json_normalize:

df['id'].to_frame().join(pd.json_normalize(df['text']))

Output:

   id txt  longit  latit  gross
0   1  en       0      0    NaN
1   2  fr      10    101  120.1

CodePudding user response：

You can use pandas.concat on axis=1 to keep the column id :

out = pd.concat([df["id"], c], axis=1)

Another solution :

out = pd.concat((df["id"], pd.DataFrame(df["text"].tolist())), axis=1)

print(out)
  id txt  longit  latit  gross
0  1  en       0      0    NaN
1  2  fr      10    101  120.1

CodePudding user response：

As an alternative using pd.Series:

df = df.join(df.pop('text').apply(pd.Series))
'''
   id txt  longit  latit  gross
0   1  en       0      0    NaN
1   2  fr      10    101  120.1
'''