Error reading a JSON file into Pandas

Question

I am trying to read a JSON file into Pandas. It's a relatively large file (41k records) mostly text.

{"sanders": [{"date": "February 8, 2016 Monday", "source": "Federal News           
Service", "subsource": "MSNBC \"MSNBC Live\" Interview with Sen. Bernie 
Sanders (I-VT), Democratic", "quotes": ["Well, it's not very progressive to     
take millions of dollars from Wall Street as well.", "That's a very good 
question, and I wish I could give her a definitive answer. QUOTE SHORTENED FOR 
SPACE"]}, {"date": "February 7, 2016 Sunday", "source": "CBS News Transcripts", "subsource": "SHOW: CBS FACE THE NATION 10:30 AM EST", "quotes": 
["Well, John -- John, I think that`s a media narrative that goes around and 
around and around. I don`t accept that media narrative.", "Well, that`s what  
she said about Barack Obama in 2008. "]},

I tried:

quotes = pd.read_json("/quotes.json")

I expected it to read in cleanly because it was file created in python. However, I got this error:

ValueError                                Traceback (most recent call last)
<ipython-input-19-c1acfdf0dbc6> in <module>()
----> 1 quotes = pd.read_json("/Users/kate/Documents/99Antennas/Client\    
Files/Fusion/data/quotes.json")

/Users/kate/venv/lib/python2.7/site-packages/pandas/io/json.pyc in   
read_json(path_or_buf, orient, typ, dtype, convert_axes, convert_dates, 
keep_default_dates, numpy, precise_float, date_unit)
    208         obj = FrameParser(json, orient, dtype, convert_axes, 
convert_dates,
    209                           keep_default_dates, numpy, precise_float,
--> 210                           date_unit).parse()
    211 
    212     if typ == 'series' or obj is None:

/Users/kate/venv/lib/python2.7/site-packages/pandas/io/json.pyc in parse(self)
    276 
    277         else:
--> 278             self._parse_no_numpy()
    279 
    280         if self.obj is None:

/Users/kate/venv/lib/python2.7/site-packages/pandas/io/json.pyc in _   
parse_no_numpy(self)
    493         if orient == "columns":
    494             self.obj = DataFrame(
--> 495                 loads(json, precise_float=self.precise_float),   
dtype=None)
    496         elif orient == "split":
    497             decoded = dict((str(k), v)

ValueError: Expected object or value

After reading the documentation and stackoverflow, I also tried adding convert_dates=False to the parameters, but that did not correct the problem. I would welcome suggestions as to how to handle this error.

SPKoder · Accepted Answer · 2016-02-13 17:14:56Z

4

Try removing the forward slash in the filename. If you run this python code from the same directory where the file is sitting, it should work.

quotes = pd.read_json("quotes.json")

edited Feb 13, 2016 at 17:14

answered Feb 11, 2016 at 4:10

SPKoder

1,9231 gold badge16 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kate Stohr Over a year ago

tks SPKoder, but that doesn't seem to be the issue. The path is correct, and moving them into the same directory doesn't resolve this error. It seems to be a parsing issue with the date field. Some dates are not compliant. However, I need to get the data read in to clean it.

SPKoder Over a year ago

Kate Stohr - please provide a sample of the data with non-compliant date fields so that we can reproduce your problem. The sample data in the question loads without error. Also, what format do expect the result to be in? In the example you provided, I don't think Pandas would even attempt parsing dates. "Sanders" would be a column label and each entry in the associated list would be a row. Each cell contains a string with date, source, subsource, and quotes...

Kate Stohr Over a year ago

Tks SPKoder... I ended up getting a csv version and was able to strip the non-compliant dates. Not a great solution, but thank you.

user3023715 · Accepted Answer · 2017-04-12 12:17:59Z

1

SPKoder mentioned the forward slash. I was looking for an answer when I realized I hadn't added a / when combing filename and path (i.e. c:/path/herefile.json, instead of c:/path/here/file.json). Anyways the error I received was ...

ValueError: Expected object or value

Not a very intuitive error message, but that is what causes it.

answered Apr 12, 2017 at 12:17

user3023715

1,6192 gold badges13 silver badges14 bronze badges

1 Comment

KeithWM Over a year ago

Same issue here, somehow PyCharm (IDE) thought I wanted the data directory as the working directory. Obviously a relative path then fails...

Collectives™ on Stack Overflow

Error reading a JSON file into Pandas

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related