ENH: support non default indexes in writing to Parquet
State: closed

- [x] closes #18581
- [x] tests added / passed
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry


Labels:
name: Enhancement
name: IO Parquet
description: parquet, feather

Comments:
We need to check how this interacts with the `columns` keyword when reading back in. Ideally selecting only a few columns to read will still preserve your index (I think?), but I guess this is currently not happening (at least with pyarrow).
# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/18629?src=pr&el=h1) Report
> Merging [#18629](https://codecov.io/gh/pandas-dev/pandas/pull/18629?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/2c903d594299b2441d4742e777a10e8c76557386?src=pr&el=desc) will **decrease** coverage by `0.01%`.
> The diff coverage is `90.9%`.

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

```diff
@@            Coverage Diff             @@
##           master   #18629      +/-   ##
==========================================
- Coverage   91.59%   91.58%   -0.02%     
==========================================
  Files         155      155              
  Lines       51255    51260       +5     
==========================================
- Hits        46949    46945       -4     
- Misses       4306     4315       +9
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `89.44% <90.9%> (ø)` | :arrow_up: |
| #single | `40.66% <0%> (-0.12%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/18629?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/io/parquet.py](https://codecov.io/gh/pandas-dev/pandas/pull/18629/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL3BhcnF1ZXQucHk=) | `66.26% <90.9%> (+0.88%)` | :arrow_up: |
| [pandas/io/gbq.py](https://codecov.io/gh/pandas-dev/pandas/pull/18629/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2dicS5weQ==) | `25% <0%> (-58.34%)` | :arrow_down: |
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/18629/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `97.81% <0%> (-0.1%)` | :arrow_down: |
| [pandas/core/indexes/datetimes.py](https://codecov.io/gh/pandas-dev/pandas/pull/18629/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvaW5kZXhlcy9kYXRldGltZXMucHk=) | `95.68% <0%> (+0.09%)` | :arrow_up: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/18629?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/18629?src=pr&el=footer). Last update [2c903d5...a14edbe](https://codecov.io/gh/pandas-dev/pandas/pull/18629?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/18629?src=pr&el=h1) Report
> Merging [#18629](https://codecov.io/gh/pandas-dev/pandas/pull/18629?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/37086a0d82a6492d9903ba763059948889638de7?src=pr&el=desc) will **decrease** coverage by `0.03%`.
> The diff coverage is `53.19%`.

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

```diff
@@            Coverage Diff             @@
##           master   #18629      +/-   ##
==========================================
- Coverage    91.6%   91.57%   -0.04%     
==========================================
  Files         153      153              
  Lines       51317    51363      +46     
==========================================
+ Hits        47011    47034      +23     
- Misses       4306     4329      +23
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `89.43% <53.19%> (-0.02%)` | :arrow_down: |
| #single | `40.73% <17.02%> (-0.1%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/18629?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/io/parquet.py](https://codecov.io/gh/pandas-dev/pandas/pull/18629/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL3BhcnF1ZXQucHk=) | `60.19% <53.19%> (-4.37%)` | :arrow_down: |
| [pandas/io/gbq.py](https://codecov.io/gh/pandas-dev/pandas/pull/18629/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2dicS5weQ==) | `25% <0%> (-58.34%)` | :arrow_down: |
| [pandas/plotting/\_converter.py](https://codecov.io/gh/pandas-dev/pandas/pull/18629/diff?src=pr&el=tree#diff-cGFuZGFzL3Bsb3R0aW5nL19jb252ZXJ0ZXIucHk=) | `64.78% <0%> (-1.74%)` | :arrow_down: |
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/18629/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `97.81% <0%> (-0.1%)` | :arrow_down: |
| [pandas/core/generic.py](https://codecov.io/gh/pandas-dev/pandas/pull/18629/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZ2VuZXJpYy5weQ==) | `95.9% <0%> (ø)` | :arrow_up: |
| [pandas/core/indexes/interval.py](https://codecov.io/gh/pandas-dev/pandas/pull/18629/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvaW5kZXhlcy9pbnRlcnZhbC5weQ==) | `93.8% <0%> (ø)` | :arrow_up: |
| [pandas/core/indexes/base.py](https://codecov.io/gh/pandas-dev/pandas/pull/18629/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvaW5kZXhlcy9iYXNlLnB5) | `96.43% <0%> (ø)` | :arrow_up: |
| [pandas/core/indexes/multi.py](https://codecov.io/gh/pandas-dev/pandas/pull/18629/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvaW5kZXhlcy9tdWx0aS5weQ==) | `96.29% <0%> (+0.01%)` | :arrow_up: |
| [pandas/core/indexes/datetimes.py](https://codecov.io/gh/pandas-dev/pandas/pull/18629/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvaW5kZXhlcy9kYXRldGltZXMucHk=) | `95.7% <0%> (+0.01%)` | :arrow_up: |
| ... and [8 more](https://codecov.io/gh/pandas-dev/pandas/pull/18629/diff?src=pr&el=tree-more) | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/18629?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/18629?src=pr&el=footer). Last update [37086a0...0fbdc14](https://codecov.io/gh/pandas-dev/pandas/pull/18629?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

> *`fastparquet` also supports writing the index, so no need to still raise the errors in that case.*

It seems it might just be easier all-round to just bump the pyarrow/fastparquet deps to the min required to support non-default indices - i.e. `>=0.7.0`  for pyarrow and ??? for fastparquet
would be ok with bumping pyarrow dep
i think fp is ok as is (i bumped it when doing this originally)
Hello @dhirschfeld! Thanks for updating the PR.





Cheers ! There are no PEP8 issues in this Pull Request. :beers: 

##### Comment last updated on December 11, 2017 at 17:00 Hours UTC
@jreback not merge on green, see my request for changes on which I got no reply yet

BTW, I think we can do this for the 0.21.1 milestone. The other enhancements to `to/read_parquet` also went into that one (seeing it as fixes on very new functionality)
@jorisvandenbossche - as of `fastparquet=0.1.3` it doesn't appear to support multi-indexes

![image](https://user-images.githubusercontent.com/881019/33709251-7f1d50f2-db88-11e7-98ef-f3700ca48b2c.png)


...at least reading them anyway. Interestingly, it seems to write them fine and the file written by fastparquet can then be read in perfectly by pyarrow.

Not sure what to do about that but since fastparquet throws a sensible error I'd be inclined to just leave it and a future update will likely add the missing functionality.


```python
from numpy.random import randn
import pandas as pd

dates = pd.date_range('01-Jan-2018', '01-Dec-2018', freq='MS')
df = pd.DataFrame(randn(2*len(dates), 3), columns=list('ABC'))
index = pd.MultiIndex.from_product([['Level1', 'Level2'], dates])
df.index = index

df.to_parquet('tmp_pa.pq', engine='pyarrow')
df.to_parquet('tmp_fp.pq', engine='fastparquet', compression='UNCOMPRESSED')

df1 = pd.io.parquet.read_parquet('tmp_pa.pq', engine='pyarrow')
df1.equals(df)
```
@jorisvandenbossche - you're right about the `columns` argument not preserving the index:

![image](https://user-images.githubusercontent.com/881019/33709668-c3f41fa2-db89-11e7-9d8f-628313b0a138.png)

The workaround, at the cost of efficiency, is simple enough:
```python
pd.io.parquet.read_parquet('tmp_pa.pq', engine='pyarrow')[['A','B']]
```
...but you have to know beforehand that the file was saved with a multi-index - i.e. it's a silent failure mode which isn't ideal.

If I name the index levels you can actually pull them out by name:
```python
df = pd.DataFrame(randn(2*len(dates), 3), columns=list('ABC'))
index = pd.MultiIndex.from_product([['Level1', 'Level2'], dates], names=['level','date'])
df.index = index
```
![image](https://user-images.githubusercontent.com/881019/33710127-5591168a-db8b-11e7-9b9c-868bd4494e24.png)
```python
pd.io.parquet.read_parquet('tmp_pa.pq', engine='pyarrow', columns=['A','B','level','date']).head()
```
![image](https://user-images.githubusercontent.com/881019/33710207-92ebbd96-db8b-11e7-82e0-886d1286afe8.png)

...so, ideally if pandas can tell if the file has a multi-index the index names could then always be prepended to the columns argument.



@jorisvandenbossche - were there other changes you wanted that I've missed?
> Not sure what to do about that but since fastparquet throws a sensible error I'd be inclined to just leave it and a future update will likely add the missing functionality.

It's a bit unfortunate it only comes up when reading it back in, so either we are ok with that, or either we need to to raise on writing MIs with fastparquet.

> If I name the index levels you can actually pull them out by name:

That will not always work. Eg if they have no names, and pyarrow master has also changed this and the index levels will never have the level names but a `__index_level_n_` pattern, so the workaround you show will not work anymore with the new release. 

The thing we can do is the following:

```
In [85]: import pyarrow.parquet as pq

In [86]: f = pq.ParquetFile("__test_mi.parquet")

In [87]: f.metadata.metadata[b'pandas']
Out[87]: b'{"columns": [{"name": "A", "pandas_type": "float64", "numpy_type": "float64", "metadata": null}, {"name": "B", "pandas_type": "float64", "numpy_type": "float64", "metadata": null}, {"name": "C", "pandas_type": "float64", "numpy_type": "float64", "metadata": null}, {"name": "__index_level_0__", "pandas_type": "unicode", "numpy_type": "object", "metadata": null}, {"name": "__index_level_1__", "pandas_type": "datetime", "numpy_type": "datetime64[ns]", "metadata": null}], "index_columns": ["__index_level_0__", "__index_level_1__"], "pandas_version": "0.22.0.dev0+288.g6b6cfb8"}'

In [88]: import json

In [89]: pandas_metadata = json.loads(f.metadata.metadata[b'pandas'].decode('utf8'))

In [90]: pandas_metadata['index_columns']
Out[90]: ['__index_level_0__', '__index_level_1__']
```

and then add those to the `columns` list. 
And something similar for `fastparquet`:

```
In [98]: import fastparquet

In [99]: f = fastparquet.ParquetFile("__test_mi.parquet")

In [100]: json.loads(f.key_value_metadata['pandas'])['index_columns']
Out[100]: ['__index_level_0__', '__index_level_1__']
```

Heh, beat me to it:
```python
import pyarrow.parquet as pq
filemetadata = pq.read_metadata('tmp_fp.pq')
metadata = eval(filemetadata.metadata.get(b'pandas'), None, dict(null=None))
metadata['index_columns']
```
...eval is pretty nasty!
I also think that *if* there is a default index (range-like index without name, the case that was allowed now), we should not write it to the parquet file, because that would just be waste of time and space. 
Yep, the `json.loads` is the better alternative :-)
> BTW, I think we can do this for the 0.21.1 milestone. The other enhancements to to/read_parquet also went into that one (seeing it as fixes on very new functionality)

since we are bumping the version I don't think this is a good idea. marked for 0.22

I am ok with this fix, @jorisvandenbossche 
For pyarrow the columns fix is as simple as setting `use_pandas_metadata=True`:
```python
    def read(self, path, columns=None, **kwargs):
        path, _, _ = get_filepath_or_buffer(path)
        parquet_file = self.api.parquet.ParquetFile(path)
        kwargs['use_pandas_metadata'] = True
        return parquet_file.read(columns=columns, **kwargs).to_pandas()
```

For fastparquet I would use:
```python
    def read(self, path, columns=None, **kwargs):
        path, _, _ = get_filepath_or_buffer(path)
        parquet_file = self.api.ParquetFile(path)
        metadata = json.loads(parquet_file.key_value_metadata['pandas'])
        if columns is not None:
            columns = set(chain(columns, metadata['index_columns']))
        return parquet_file.to_pandas(columns=columns, **kwargs)
```
...but since it doesnt' support multi-indexes anyway it's a moot point.
@martindurant can you weigh in here on the fp fix?
> ...but since it doesnt' support multi-indexes anyway it's a moot point.

It's not a moot point, as it still supports single level index, which is better than nothing?

The fastparquet fix is fine, the MultiIndex 'not implemented' error is just an enhancement that would be welcome in fastparquet.
> I am ok with this fix, @jorisvandenbossche

I am not yet fine with the current version, so please don't merge.

> since we are bumping the version I don't think this is a good idea. marked for 0.22

If we do that, it's 0.21.1 that will become 0.22 and 0.22 will become 0.23 no?
I've pushed some changes to handle the columns issue - at least with pyarrow. The test is marked as xfail for fastparquet but that can be easily changed if fastparquet adds support for multi-indexes.

I think I've covered everything but let me know if there are further changes you'd like...
> if we do that, it's 0.21.1 that will become 0.22 and 0.22 will become 0.23 no?

huh?

no 0.21.1 - should release this soon as a bugfix, 0.22 for major changes.


@dhirschfeld Thanks for the updates! Will review in a moment
> For fastparquet I haven't bothered reading the metadata to pull out the index columns 

Apparently it is doing this 'correctly' by default (`f` is a fastparquet ParquetFile):

```
In [142]: f.to_pandas()
Out[142]: 
    bar  baz  foo
zoo              
x     A    1  one
y     B    2  one
z     C    3  one
q     A    4  two
w     B    5  two
t     C    6  two

In [143]: f.to_pandas(columns=['bar'])
Out[143]: 
    bar
zoo    
x     A
y     B
z     C
q     A
w     B
t     C
```

So also there this "extracting metadata" is not needed
Yep, fastparquet works fine with a non-multi-index DataFrame. The code I posted above would *also* work but was moot in the sense that it didn't fix the multi-index problem.

In the unlikely event that fastparquet fix reading multi-index DataFrames but don't automatically handle the columns then it may be of some use so I posted it in case it was ever useful.
Ah, OK, then I understand your "moot" point :-)
Thanks for the reviews @jreback, @jorisvandenbossche & @jschendel!

Sorry it's taken a while to get here - I've usually only got a small window in the evenings when I can work on stuff not directly related to my *actual* work. That window is fast closing down this way so feel to push any changes in the interim. I'm also happy to pick it up again tomorrow though. No big rush if it's scheduled for 0.22...
Maybe not essential for this PR, but I think we should also think about:

- not writing the index if it is a default index (range-like index without name). It will be cheaper to just reconstruct it on reading (which is already done by both engines if there is no index column in the metadata)

- providing a `index=True/False` keyword similar to `to_csv` and others to control if you want to write the index or not (both pyarrow and fastparquet support this)

> Sorry it's taken a while to get here - I've usually only got a small window in the evenings when I can work on stuff not directly related to my actual work.

No problem! The PR is only open yet for 3 days :-) And it will be a very nice improvement.
@jreback to come back on the milestone. I personally would like to see this released as soon as possible, as it is an important change to make `read_parquet`/`to_parquet` as brand new functionality easier to use. This will impact how users use those functions, so let's change that as quick as possible. Since we are also including the addition of the `columns` keyword in the 0.21.1 release, I don't see why we wouldn't include this in the 0.21.1 release. 
(I can help with finishing this PR if time is a problem)

> since we are bumping the version I don't think this is a good idea. 

I don't fully understand what bumping the version has to do with it, but let's keep the discussion about version numbers for https://github.com/pandas-dev/pandas/issues/18244, and let's assume here that we want to release 0.21.1 in a couple of days (but as argued above, I would like to try to include it in that release).
well, almost all of the 0.21.1 fixes won't break your existing environment; that's the point.

This is actually something that will. that's why its 0.22.0. its an api breaking change. Now personally I agree that it would be fine to break this as its a new feature. If you want to be less strict on this because of that, then I am not opposed.