ENH: add ujson support in pandas.io.json
State: closed

This is @wesm PR #3583 with this:

It builds now, and passes travis on py2 and py3, had 2 issues:
- clean was erasing the *.c files from ujson
- the module import didn't work because it was using the original init function

Converted to new io API: `to_json` / `read_json`

Docs added


Labels:

Comments:
yay!

This is pretty awesome.

One thing I think worth being explicit in the docs (am I right in saying this?) only works with _valid_ JSON.

the json routines read/write from strings
this is unlike any of the other io routines that pandas has
which all take a path_or_buf

is this typical of dealing with JSON data?

should we have a kw to do this? always do it?

@jreback That is an excellent point, this should work as all the other `read_`s. I don't _think_ this is necessarily typical to always have the string but at least it _does_ makes it clear the `read_json` takes the entire string at once rather than by chunks.

(The first thing I did was open a json_file ~~f.readlines().~~ `f.read()`)

It'd certainly be a useful feature is we could go `pd.from_json(datas_url)` and perhaps this would be a fairly standard use case.

We could either:
1. Have have a _string_ kwarg, filepath_or_buffer is first argument (this would be my preference).
2. Check if it's a filepath_or_buffer, if not there, treat as json string (seems like a can of worms)

(Also, to clarify previous point, from_json only _reads_ valid json :) )

do u have a URL that yields JSON?

Yoinked from [so](http://stackoverflow.com/questions/8890257/example-json-http-request-for-twitter-api):

http://search.twitter.com/search.json?q=blue%20angels&rpp=5&include_entities=true&result_type=mixed

... or http://search.twitter.com/search.json?q=pandas%20python

A more interesting example: https://api.github.com/repos/pydata/pandas/issues?per_page=100

parsed first try!

```
(Pdb) url_table
<class 'pandas.core.frame.DataFrame'>
Int64Index: 100 entries, 0 to 99
Data columns (total 19 columns):
assignee        6  non-null values
body            100  non-null values
closed_at       0  non-null values
comments        100  non-null values
comments_url    100  non-null values
created_at      100  non-null values
events_url      100  non-null values
html_url        100  non-null values
id              100  non-null values
labels          100  non-null values
labels_url      100  non-null values
milestone       75  non-null values
number          100  non-null values
pull_request    100  non-null values
state           100  non-null values
title           100  non-null values
updated_at      100  non-null values
url             100  non-null values
user            100  non-null values
dtypes: int64(3), object(16)
```

The one thing that trips is dates (you just have to to_datetime after), but that can be left for another day.

Whoop! :)

yeh....we'll see how this goes....in 0.12 can add `infer_types` directive, kind of like `read_html`

i wonder if there are any other similar libraries or systems that have this much io functionality in a _single_ package...

(Does infer_types work for unix time stamps? ...to get the roundtrip working. Anyway....)

doubtful since those are just integers...but i haven't tested

i tried 

``` sh
date +"%s" | python -c 'import sys; from dateutil.parser import parse; parse(sys.stdin.read())'
```

that doesn't work so i'm going to say no it won't work.

You can do `pd.to_datetime` on the column after reading.

yes an know....we have an open issue #3540 to make a better API for this, but look at #2015 

so if you know they are epoch time stamps (e.g. passing in as an option maybe), then its easy, we can convert them

`Timestamp` accepts a nanosecond based epoch timestamp (e.g. nanoseconds since 1970; so epoch time stamps are in seconds since 1970, so juse int(1e9) and it will work).....

but there is an issue because sometimes they are not in seconds...so have to disambiguate

Just saying as `to_json` exports timestamps to unix time.

oh yes `Timestamp` works...hm maybe should add to `read_html`...i doubt people are using html tables to store unix timestamp but who the hell knows? maybe i'll wait until the api is sorted out

could add a `parse_dates` arg that takes a list of fields to try to convert?

`read_html` much tougher because people are just reading it in ...and there is no standard...@hayd is right...since its a standard, could even try to convert an integer column (if they are in range)?

(obviously far too reckless to just `.applymap(pd.to_datetime)` lol)

what's a quick way to fix inconcistent space/tabs....something got screwed up...

M-x untabify

on a region i think prolly whole file works too

@hayd actually `.apply(pd.to_datetime)` will not change the column if all conversions fail, so it is `safe` sort of

try out: `convert_objects(convert_dates='coerce'))`

Quite slow though?

convert_objects is ok speed wise it operates on blocks using cython functions so it's gotta be faster than lambdas :)

@jreback correct me if i'm wrong here...

Well:

```
In [44]: %timeit with open('issues.json', 'r') as f: s = pd.read_json(f.read())
100 loops, best of 3: 8.83 ms per loop

In [45]: %timeit s.convert_objects(convert_dates='coerce')
1 loops, best of 3: 520 ms per loop
```