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\$
4
  • 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, 2019 at 20:32
  • \$\begingroup\$ Yes, actually it is a little faster, thanks \$\endgroup\$ Aug 27, 2019 at 9:30
  • 1
    \$\begingroup\$ Is the above exactly how your file looks? \$\endgroup\$
    – C.Nivs
    Aug 27, 2019 at 20:53
  • \$\begingroup\$ I forgot to close the json, sorry \$\endgroup\$ Aug 28, 2019 at 9:05

1 Answer 1

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)
\$\endgroup\$
2
  • \$\begingroup\$ "got an amazing improvement thanks to stack overflow" Do you have a link to that answer? \$\endgroup\$
    – Mast
    Aug 28, 2019 at 12:56
  • 1
    \$\begingroup\$ Yes, I have edited the answer to add those links \$\endgroup\$ Aug 28, 2019 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.