2
\$\begingroup\$

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

df = pd.read_csv('data.csv')
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?

\$\endgroup\$
  • 2
    \$\begingroup\$ 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. \$\endgroup\$ – hpaulj Aug 26 '19 at 20:32
  • \$\begingroup\$ Yes, actually it is a little faster, thanks \$\endgroup\$ – Javier López Tomás Aug 27 '19 at 9:30
  • 1
    \$\begingroup\$ Is the above exactly how your file looks? \$\endgroup\$ – C.Nivs Aug 27 '19 at 20:53
  • \$\begingroup\$ I forgot to close the json, sorry \$\endgroup\$ – Javier López Tomás Aug 28 '19 at 9:05
1
\$\begingroup\$

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 = {}
    dict1.update(json.loads(df.at[i,'raw_content']))
    row_list.append(dict1)

df1 = pd.DataFrame(row_list)

df2 = pd.concat([df['email_id'],df1],axis=1)
| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ "got an amazing improvement thanks to stack overflow" Do you have a link to that answer? \$\endgroup\$ – Mast Aug 28 '19 at 12:56
  • 1
    \$\begingroup\$ Yes, I have edited the answer to add those links \$\endgroup\$ – Javier López Tomás Aug 28 '19 at 13:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.