CODEWITHSUNDEEP
pythonjsonpandas
jonas
jonas

Reputation: 380

Pandas - Flatten irregular nested JSON

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

Answers (2)

Myrt
Myrt

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

Andre S.
Andre S.

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

Related Questions