# Loop to extract json from dataframe and storing in a new dataframe

I have a dataframe (obtained from a csv saved from mysql) with several columns, and one of them consist of a string which is the representation of a json. The data looks like:

id   email_id     provider  raw_data       ts
1    aa@gmail.com    A      {'a':'A',   2019-23-08 00:00:00
'b':'B',
'c':'C'}


And what my desired output is:

email_id      a   b   c
aa@gmail.com  A   B   C


What I have coded so far is the following:

import pandas as pd
import ast

df1 = pd.DataFrame()

for i in range(len(df)):
dict_1 = ast.literal_eval(df['raw_content'][i])
df1 = df1.append(pd.Series(dict_1),ignore_index=True)

pd.concat([df['email_id'],df1])


This works but it has a very big problem: it is extremely low (it takes hours for 100k rows). How could I make this operation faster?

• Would json.loads be any faster? With a more limited structure it might. Another thing to consider is doing a string join on all those raw_data strings, and calling loads once. I haven't worked with json enough to know what's fast or slow in its parsing. Aug 26, 2019 at 20:32
• Yes, actually it is a little faster, thanks Aug 27, 2019 at 9:30
• Is the above exactly how your file looks? Aug 27, 2019 at 20:53
• I forgot to close the json, sorry Aug 28, 2019 at 9:05

Finally I got an amazing improvement thanks to stack overflow, regarding two things: https://stackoverflow.com/questions/10715965/add-one-row-to-pandas-dataframe https://stackoverflow.com/questions/37757844/pandas-df-locz-x-y-how-to-improve-speed

Also, as hpaulj pointed, changing to json.loads slightly increases the performance.

It went from 16 hours to 30 seconds

row_list = []

for i in range(len(df)):
dict1 = {}