Reputation: 380
I have following JSON object and try to convert it into a DataFrame.
Data:
{
"data": {
"docs": [
{
"id": "1",
"col1": "foo",
"col2": "123",
"list": ["foo barr, fooo"]
},
{
"id": "2",
"col1": "abc",
"col2": "321",
"list": ["lirum epsum"]
},
{
"id": "3",
"col1": "foo",
"col2": "123",
"list": null
}
}
]
}
}
Ideally the list column should contain of strings instead of lists and look like this:
id col1 col2 list
1 foo 123 'foo barr, fooo'
2 abc 321 'lirum epsum'
3 foo 123 NaN
Following approach is throwing an exception (TypeError: 'NoneType' object is not iterable):
with open(path_to_json, encoding='utf-8') as json_file:
q= json.load(json_file)
df = json_normalize(q['data'], record_path=['docs', 'list'])
Upvotes: 0
Views: 231
Reputation: 34
I cannot add comment (yet) to complete the answer above but you can convert your column list to string using this code
df['list']=df['list'].apply(lambda x: str(x).strip('[\']'))
Upvotes: 2
Reputation: 518
json = {
"data": {
"docs": [
{
"id": "1",
"col1": "foo",
"col2": "123",
"list": ["foo barr, fooo"]
},
{
"id": "2",
"col1": "abc",
"col2": "321",
"list": ["lirum epsum"]
},
{
"id": "3",
"col1": "foo",
"col2": "123",
"list": np.nan
},
]
}
}
pd.DataFrame(json["data"]["docs"]).set_index("id")
gives you
id col1 col2 list
1 foo 123 [foo barr, fooo]
2 abc 321 [lirum epsum]
3 foo 123 NaN
Upvotes: 0