Fast convert JSON column into Pandas dataframe

Question

I'm reading data from a database (50k+ rows) where one column is stored as JSON. I want to extract that into a pandas dataframe. The snippet below works fine but is fairly inefficient and really takes forever when run against the whole db. Note that not all the items have the same attributes and that the JSON have some nested attributes.

How could I make this faster?

import pandas as pd import json  df = pd.read_csv('http://pastebin.com/raw/7L86m9R2', \                  header=None, index_col=0, names=['data'])  df.data.apply(json.loads) \        .apply(pd.io.json.json_normalize)\        .pipe(lambda x: pd.concat(x.values)) ###this returns a dataframe where each JSON key is a column 

Answer 1

json_normalize takes an already processed json string or a pandas series of such strings.

pd.io.json.json_normalize(df.data.apply(json.loads)) 

---

setup

import pandas as pd import json  df = pd.read_csv('http://pastebin.com/raw/7L86m9R2', \                  header=None, index_col=0, names=['data']) 

Answer 2

I think you can first convert string column data to dict, then create list of numpy arrays by values and last DataFrame.from_records:

df = pd.read_csv('http://pastebin.com/raw/7L86m9R2', \                  header=None, index_col=0, names=['data'])  a = df.data.apply(json.loads).values.tolist()  print (pd.DataFrame.from_records(a)) 

Another idea:

 df = pd.json_normalize(df['data'])