ENH: add to/from_parquet with pyarrow & fastparquet
State: closed

 xref https://github.com/dask/dask/issues/2127

TODO: these are fixed, waiting for release to update tests.

- [x] ``fastparquet``: [duplicate columns errors msg](https://github.com/dask/fastparquet/issues/118)
- [x] ``pyarrow 0.3``: [passing dataframe with non-string object columns](https://github.com/apache/arrow/pull/465)

This is a wrapper around ``pyarrow`` and ``fastparquet`` to provide seemless IO interop within pandas.

cc @wesm 
cc @martindurant
cc @mrocklin


Labels:
name: Enhancement
name: IO Data
description: IO issues that don't fit into a more specific label
name: IO Parquet
description: parquet, feather

Comments:
note that there are several specific tests that will need changing for pyarrow 0.3.0 (soon).
# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/15838?src=pr&el=h1) Report
> Merging [#15838](https://codecov.io/gh/pandas-dev/pandas/pull/15838?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/ab49d1fcda17cdb5571959a0d85d5ee872638b4c?src=pr&el=desc) will **decrease** coverage by `0.02%`.
> The diff coverage is `68.75%`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/15838/graphs/tree.svg?height=150&token=eZ4WkYLtcO&width=650&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/15838?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master   #15838      +/-   ##
==========================================
- Coverage   91.02%   90.99%   -0.03%     
==========================================
  Files         161      162       +1     
  Lines       49414    49494      +80     
==========================================
+ Hits        44980    45039      +59     
- Misses       4434     4455      +21
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `88.77% <68.75%> (-0.01%)` | :arrow_down: |
| #single | `40.26% <26.25%> (-0.09%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/15838?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/util/\_print\_versions.py](https://codecov.io/gh/pandas-dev/pandas/pull/15838?src=pr&el=tree#diff-cGFuZGFzL3V0aWwvX3ByaW50X3ZlcnNpb25zLnB5) | `15.71% <ø> (ø)` | :arrow_up: |
| [pandas/io/feather\_format.py](https://codecov.io/gh/pandas-dev/pandas/pull/15838?src=pr&el=tree#diff-cGFuZGFzL2lvL2ZlYXRoZXJfZm9ybWF0LnB5) | `85.71% <ø> (ø)` | :arrow_up: |
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/15838?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `97.66% <100%> (-0.1%)` | :arrow_down: |
| [pandas/io/api.py](https://codecov.io/gh/pandas-dev/pandas/pull/15838?src=pr&el=tree#diff-cGFuZGFzL2lvL2FwaS5weQ==) | `100% <100%> (ø)` | :arrow_up: |
| [pandas/core/config\_init.py](https://codecov.io/gh/pandas-dev/pandas/pull/15838?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvY29uZmlnX2luaXQucHk=) | `94.48% <100%> (+0.13%)` | :arrow_up: |
| [pandas/io/parquet.py](https://codecov.io/gh/pandas-dev/pandas/pull/15838?src=pr&el=tree#diff-cGFuZGFzL2lvL3BhcnF1ZXQucHk=) | `65.75% <65.75%> (ø)` | |
| [pandas/io/gbq.py](https://codecov.io/gh/pandas-dev/pandas/pull/15838?src=pr&el=tree#diff-cGFuZGFzL2lvL2dicS5weQ==) | `25% <0%> (-58.34%)` | :arrow_down: |
| [pandas/core/series.py](https://codecov.io/gh/pandas-dev/pandas/pull/15838?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvc2VyaWVzLnB5) | `95.04% <0%> (+0.09%)` | :arrow_up: |
| ... and [1 more](https://codecov.io/gh/pandas-dev/pandas/pull/15838?src=pr&el=tree-more) | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/15838?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/15838?src=pr&el=footer). Last update [ab49d1f...f553a5f](https://codecov.io/gh/pandas-dev/pandas/pull/15838?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

does parquet automatically infer compression from a filepath? 

e.g. say ``foo.pq.gz``, does this imply ``compression='gzip'`` or do you have to actually pass the parameter? (I can do either, because I actually DO infer this), just need to know the standard.
No, compression is an internal thing, and can vary between columns/chunks. You can typically (e.g., from spark) have files that are name.parquet.gz and have gzip compression throughout, but that's not the spec.
> No, compression is an internal thing, and can vary between columns/chunks. You can typically (e.g., from spark) have files that are name.parquet.gz and have gzip compression throughout, but that's not the spec.

ok, that's what I did already, the actual path/extension will not impact the ``compression`` argument which must be explicit.
added a few more tests cases for invalid columns, increased coverage of dtypes: bytes, nan and None in string arrays, and timedeltas.
@wesm thoughts on including this in 0.20.0 as is? (could also add but not advertise, e.g. mark as provision, and not put in ``pd`` namespace).

also will do a followup to require ``pyarrow >= 0.3.0`` I think.
@jreback I'd prefer to wait until the API and semantics of the Parquet implementations is more stable and consistent, and also that we have a strategy for dealing with multi-file datasets in pandas. Since we're still debating how to store nulls (https://github.com/dask/dask/issues/2177) I don't think this is ready yet
@wesm fair enough.
@martindurant coming back to this. with fp 0.0.6, pyarrow 0.3.0
cc @wesm 

This seems a bit odd to me.
```
In [1]: import fastparquet

In [2]: fastparquet.__version__
Out[2]: '0.0.6'

In [3]: import pyarrow

In [4]: pyarrow.__version__
Out[4]: '0.3.0'

In [5]: df = pd.DataFrame({'a': [True, None, False]})

In [6]: df.to_parquet('foo.parquet', engine='fastparquet', compression=None)

In [7]: pd.read_parquet('foo.parquet', engine='fastparquet')
Out[7]: 
     a
0  1.0
1  NaN
2  0.0

In [8]: pd.read_parquet('foo.parquet', engine='fastparquet').dtypes
Out[8]: 
a    float16
dtype: object
```

This fails my cross engine test (not the reverse works fine, e.g. writing by fp and reading by pyarrow). Note I am not using compression on purpose (I don't think this will affect things, but for consistency in the tests).

```
In [9]: df2 = pd.DataFrame({'A': [1, 2, 3], 'B': 'foo'})

In [10]: df2.to_parquet('bar.parquet', engine='pyarrow', compression=None)

In [11]: pd.read_parquet('bar.parquet', engine='pyarrow')
Out[11]: 
   A    B
0  1  foo
1  2  foo
2  3  foo

In [12]: pd.read_parquet('bar.parquet', engine='fastparquet')
---------------------------------------------------------------------------
---------------------------------------------------------------------------
NotADirectoryError                        Traceback (most recent call last)
/Users/jreback/miniconda3/envs/pandas/lib/python3.6/site-packages/fastparquet/api.py in __init__(self, fn, verify, open_with, sep)
     59                 self.fn = fn2
---> 60                 with open_with(fn2, 'rb') as f:
     61                     self._parse_header(f, verify)

/Users/jreback/miniconda3/envs/pandas/lib/python3.6/site-packages/fastparquet/util.py in default_open(f, mode)
     41 def default_open(f, mode='rb'):
---> 42     return open(f, mode)
     43 

NotADirectoryError: [Errno 20] Not a directory: 'bar.parquet/_metadata'

During handling of the above exception, another exception occurred:

IndexError                                Traceback (most recent call last)
<ipython-input-12-17a8b809ed63> in <module>()
----> 1 pd.read_parquet('bar.parquet', engine='fastparquet')

/Users/jreback/pandas/pandas/io/parquet.py in read_parquet(path, engine, **kwargs)
    177 
    178     impl = get_engine(engine)
--> 179     return impl.read(path)

/Users/jreback/pandas/pandas/io/parquet.py in read(self, path)
     96     def read(self, path):
     97         path, _, _ = get_filepath_or_buffer(path)
---> 98         return self.api.ParquetFile(path).to_pandas()
     99 
    100 

/Users/jreback/miniconda3/envs/pandas/lib/python3.6/site-packages/fastparquet/api.py in __init__(self, fn, verify, open_with, sep)
     64                 self.fn = fn
     65                 with open_with(fn, 'rb') as f:
---> 66                     self._parse_header(f, verify)
     67         if all(rg.columns[0].file_path is None for rg in self.row_groups):
     68             self.file_scheme = 'simple'

/Users/jreback/miniconda3/envs/pandas/lib/python3.6/site-packages/fastparquet/api.py in _parse_header(self, f, verify)
     93         self.head_size = head_size
     94         self.fmd = fmd
---> 95         self._set_attrs()
     96 
     97     def _set_attrs(self):

/Users/jreback/miniconda3/envs/pandas/lib/python3.6/site-packages/fastparquet/api.py in _set_attrs(self)
    106             for chunk in rg.columns:
    107                 self.group_files.setdefault(i, set()).add(chunk.file_path)
--> 108         self.schema = schema.SchemaHelper(self._schema)
    109         self.selfmade = self.created_by.split(' ', 1)[0] == "fastparquet-python"
    110         self._read_partitions()

/Users/jreback/miniconda3/envs/pandas/lib/python3.6/site-packages/fastparquet/schema.py in __init__(self, schema_elements)
     64         self.schema_elements_by_name = dict(
     65             [(se.name, se) for se in schema_elements])
---> 66         schema_tree(schema_elements)
     67 
     68     def __str__(self):

/Users/jreback/miniconda3/envs/pandas/lib/python3.6/site-packages/fastparquet/schema.py in schema_tree(schema, i)
     21     while len(root.children) < root.num_children:
     22         i += 1
---> 23         s = schema[i]
     24         root.children[s.name] = s
     25         if s.num_children is not None:

IndexError: list index out of range

```
[True, None, False] does indeed get stored as optional boolean as you expect. Loading to an object column, though, is unnecessarily wasteful in my opinion. Note that even pandas does not make [1, None, 0] into an object column. If you really wanted to make sure [True, None, False] became objects on load, you could always use json encoding...

I cannot check the second one right now, as I don't have the cutting edge pandas, and pyarrow segfaults for me on conda version 0.3.0-np111py35_1 (conda-forge). If the resultant file were posted, I could investigate how it looks from fastparquet's point of view.
@martindurant 

> [True, None, False] does indeed get stored as optional boolean as you expect. Loading to an object column, though, is unnecessarily wasteful in my opinion. Note that even pandas does not make [1, None, 0] into an object column. If you really wanted to make sure [True, None, False] became objects on load, you could always use json encoding...

these are reserialized as ``float16`` *that is what is weird*
```
In [8]: pd.read_parquet('foo.parquet', engine='fastparquet').dtypes
Out[8]: 
a    float16
dtype: object
```
I actually don't mind the floats, but float16 is not very well supported.

you can just ``conda install pyarrow -c conda-forge`` to test yourself (as its a released version)

In what way is float16 not supported? Any further processing on the column, and the dtype will be upcast as required, I don't see why to use four times the memory on load.

So the documentation says to call `table = pa.Table(..)` when writing, which causes segfault, but the docstring for Table says not to ever call it, but do Table.from_pandas instead. OK.

The error is a bug, or at least unexpected: the schema elements have num_children of 0 instead of NULL as you see elsewhere. @wesm , is this a conscious choice/preference? I can also correct this on my end.
> In what way is float16 not supported? Any further processing on the column, and the dtype will be upcast as required, I don't see why to use four times the memory on load.

we barely support *any* operations on it in pandas. it you want to cast this as float, then use float32. (pandas2 will fix this whole mess, but for now for compat this is the best soln). of course you could de-serialize this as an object array as well (will be pretty efficient actually as it only has True/False/np.nan).


What operation could you want to do beyond ==1, ==0, isnull, groupby? These all work; addition and multiplication, numpy trig all upcast as they should. That's all I've tried.
@martindurant as I said ``float16`` will fail in lots of cases, we don't test with it nor use it at all in pandas. So you risk incompatibility if you do this. Sure it might work for a while, but no guarantees. As i said, best bet would be to actually return an object array with True/False/np.nan, failing that float32.
@wesm this triggers for me locally (use this PR).

```
(pandas) bash-3.2$ pytest pandas/tests/io/test_parquet.py  pandas/tests/tseries/test_offsets.py 
=========================================================================================== test session starts ===========================================================================================
platform darwin -- Python 3.6.1, pytest-3.0.7, py-1.4.32, pluggy-0.4.0
rootdir: /Users/jreback/pandas, inifile: setup.cfg
plugins: cov-2.3.1, xdist-1.15.0
collected 341 items 

pandas/tests/io/test_parquet.py ...x...........ss..ss....s....
pandas/tests/tseries/test_offsets.py .......F..FF^C^C

================================================================================================ FAILURES =================================================================================================
```
Looking
Able to repro, trying to fix now
Fixed. Will wait for the builds to run and then work on getting a hotfix conda-forge release out this evening
@wesm pyarrow 0.3.0.post lgtm.
@wesm FYI: https://ci.appveyor.com/project/pandas-dev/pandas/build/1.0.1892/job/k0l3t0if9lumssy9

3.6 / windows with pyarrow 0.3.0.post 
Don't have the Parquet extension building in conda-forge on Windows yet. I've been meaning to test it out locally before adding to the build. You are welcome to open a PR on conda-forge to see if it passes
Just got this working in https://github.com/apache/arrow/pull/700, so this should drop in the 0.4.0 release (pretty soon)
so this is finally passing again now that ``numba`` released 0.34 (to work with np 1.13), required by fp).

@wesm note that I bumped pyarrow min to 0.4.1 (no real reason except that we are only testing with latest, AFAIK actually working with 0.3 previously).
Cool. We should try to sort out the pyarrow bug fixes ahead of the 0.5.0 release candidate (which should go out early next week)
@wesm What are the bugs in `pyarrow` that affect this PR?
@xhochy on closer inspection it looks like there isn't anything for us to do. @jreback can you confirm? Thanks!
this is all passing with 0.4.1, ex the compat test with fp indicated above (though that looks like an issue on fp side).
Great, I will be putting up 0.5.0.pre conda-forge packages this week so we should also validate against that  in case there are any regressions