How to flatten a pandas dataframe with some columns as json?
<p>I have a dataframe <code>df</code> that loads data from a database. Most of the columns are json strings while some are even list of jsons. For example:</p>

<pre><code>id     name     columnA                               columnB
1     John     {"dist": "600", "time": "0:12.10"}    [{"pos": "1st", "value": "500"},{"pos": "2nd", "value": "300"},{"pos": "3rd", "value": "200"}, {"pos": "total", "value": "1000"}]
2     Mike     {"dist": "600"}                       [{"pos": "1st", "value": "500"},{"pos": "2nd", "value": "300"},{"pos": "total", "value": "800"}]
...
</code></pre>

<p>AS you can see, all the rows don't have the same number of elements in the json strings for a column. </p>

<p>What I need to do is keep the normal columns like <code>id</code> and <code>name</code>, as it is and flatten the json columns like so</p>

<pre><code>id    name   columnA.dist   columnA.time   columnB.pos.1st   columnB.pos.2nd   columnB.pos.3rd     columnB.pos.total
1     John   600            0:12.10        500               300               200                 1000 
2     Mark   600            NaN            500               300               Nan                 800 
</code></pre>

<p>I have tried using <code>json_normalize</code> like so</p>

<pre><code>from pandas.io.json import json_normalize
json_normalize(df)
</code></pre>

<p>But there seems to be some problems with <code>keyerror</code>. What is the correct way of doing this?</p>

<p>create a custom function to flatten <code>columnB</code> then use <code>pd.concat</code></p>

<pre><code>def flatten(js):
    return pd.DataFrame(js).set_index('pos').squeeze()

pd.concat([df.drop(['columnA', 'columnB'], axis=1),
           df.columnA.apply(pd.Series),
           df.columnB.apply(flatten)], axis=1)
</code></pre>

<p><a href="http://i.stack.imgur.com/FVzRP.png" rel="nofollow"><img src="http://i.stack.imgur.com/FVzRP.png" alt="enter image description here"></a></p>

python json pandas flatten