1
votes

I am trying to have Python read a JSON file and export it to a CSV. I am using Pandas for the conversion, but I am getting "ValueError: Expected object or value" when I run the code below.

import pandas as pd

df = pd.read_json ('contacts.json')

I am using Visual Studio Code for testing the script. When I run the above code, I get the message below in the Terminal window.

PS C:\Users\TaRan\tableau-wdc-tutorial-part-1> & "C:/Program Files/Python38/python.exe" "c:/Users/TaRan/Dropbox/Team Operational Resources/G. BI Internal/Testing/Hubspot/conversion.py"

Traceback (most recent call last):

File "c:/Users/TaRan/Dropbox/Team Operational Resources/G. BI Internal/Testing/Hubspot/conversion.py", line 3, in

df=pd.read_json('contacts.txt') File "C:\Program Files\Python38\lib\site-packages\pandas\util_decorators.py", line 199, in wrapper

return func(*args, **kwargs) File "C:\Program Files\Python38\lib\site-packages\pandas\util_decorators.py", line 296, in wrapper

return func(*args, **kwargs) File "C:\Program Files\Python38\lib\site-packages\pandas\io\json_json.py", line 618, in read_json

result = json_reader.read() File "C:\Program Files\Python38\lib\site-packages\pandas\io\json_json.py", line 755, in read

obj = self._get_object_parser(self.data) File "C:\Program Files\Python38\lib\site-packages\pandas\io\json_json.py", line 777, in _get_object_parser

obj = FrameParser(json, **kwargs).parse() File "C:\Program Files\Python38\lib\site-packages\pandas\io\json_json.py", line 886, in parse

self._parse_no_numpy() File "C:\Program Files\Python38\lib\site-packages\pandas\io\json_json.py", line 1119, in _parse_no_numpy

loads(json, precise_float=self.precise_float), dtype=None ValueError: Expected object or value

I thought it might be a problem with the JSON file, so I wrote a different one, but I still received the error. To me, it looks like something might be wrong with the Panadas package. I tried reinstalling it, but I still get the error.

EDIT

Here is a sample from the JSON file. I am displaying only one contact and changed the confidential information.

{"results":[{"id":"101","properties":{"createdate":"2020-06-05T15:18:37.746Z","email":"[email protected]","firstname":"First","hs_object_id":"101","lastmodifieddate":"2020-08-12T15:17:35.104Z","lastname":"Last"},"createdAt":"2020-06-05T15:18:37.746Z","updatedAt":"2020-08-12T15:17:35.104Z","archived":false}],"paging":{"next":{"after":"452","link":"https://api.hubapi.com/sampleurl.com"},"prev":null}}

I am getting the JSON file from the Hubspot API. I am not doing any kind of formatting before pulling it into Python for the conversion (nor do I want to - I am trying to automate this entire process). Please note that my JSON is all on one line. I am not sure if this matters or not.

2

2 Answers

1
votes

You have problems in your JSON format. e.g. in the posted part you have '[' but don't have ']'

1
votes

Are you sure your json file is correctly formatted? I wrote this json file and it seems to work fine for me.

{
  "Name": {
    "0": "John",
    "1": "Nick",
    "2": "Ali",
    "3": "Joseph"
  },
  "Gender": {
    "0": "Male",
    "1": "Male",
    "2": "Female",
    "3": "Male"
  },
  "Nationality": {
    "0": "UK",
    "1": "French",
    "2": "USA",
    "3": "Brazil"
  },
  "Age": {
    "0": 10,
    "1": 25,
    "2": 35,
    "3": 29
  }
}

I used the same code you wrote, but added a print statement to check the output and I was able to print out the head of the dataframe.

% python test.py
     Name  Gender Nationality  Age
0    John    Male          UK   10
1    Nick    Male      French   25
2     Ali  Female         USA   35
3  Joseph    Male      Brazil   29

EDIT: Using the JSON you provided it looks like it is malformed. The JSON you provided is missing a closing "]" and also there were some missing brackets in the second array item.

It should look like this depending on what you're trying to do.

{
  "results": [
    {
      "id": "101",
      "properties": {
        "createdate": "2020-06-05T15:18:37.746Z",
        "email": "[email protected]",
        "firstname": "First",
        "hs_object_id": "101",
        "lastmodifieddate": "2020-08-12T15:17:35.104Z",
        "lastname": "Last"
      },
      "createdAt": "2020-06-05T15:18:37.746Z",
      "updatedAt": "2020-08-12T15:17:35.104Z",
      "archived": false
    },
    {
      "paging": {
        "next": {
          "after": "452",
          "link": "https://api.hubapi.com/sampleurl.com"
        },
        "prev": null
      }
    }
  ]
}