ENH: allow to_csv to write multi-index columns, read_csv to read with header=list arg
State: closed

In theory should close:
#3571, #1651, #3141

Works, but a couple of issues/caveats:
-  ~~index_col needs to be specified as an integer list (can be fixed)~~
- header is a list of rows to read that contain the multi-index,
  a row that is skipped (e.g. [0,1,3,5], will just be skipped, like a comment)
- the writing format might be a bit odd: the col names go in the first column,
  other index_cols are blanks (they are separated, just == '')
  The names of an multi-index on the index are after the columns and before the data,
  and are a full row (but blank after the row names).
- ~~I am not sure if we should allow `df.to_csv('path',index=False)` when have a multi-index columns, could just ban it I guess (mainly as it screws up the write format, and then where do you put the names?)~~
- ~~The `cols` argument needs testing and prob is broken when using multi-index on the columns (it really should be specified as a tuple I think, but that is work, so maybe just ban it when using multi-index columns)~~
- needs more testing 

```
In [14]: df = mkdf(5,3,r_idx_nlevels=2,c_idx_nlevels=4)

In [15]: df.to_csv('test.csv')

In [16]: !cat 'test.csv'
C0,,C_l0_g0,C_l0_g1,C_l0_g2
C1,,C_l1_g0,C_l1_g1,C_l1_g2
C2,,C_l2_g0,C_l2_g1,C_l2_g2
C3,,C_l3_g0,C_l3_g1,C_l3_g2
R0,R1,,,
R_l0_g0,R_l1_g0,R0C0,R0C1,R0C2
R_l0_g1,R_l1_g1,R1C0,R1C1,R1C2
R_l0_g2,R_l1_g2,R2C0,R2C1,R2C2
R_l0_g3,R_l1_g3,R3C0,R3C1,R3C2
R_l0_g4,R_l1_g4,R4C0,R4C1,R4C2

In [17]: res = read_csv('test.csv',header=[0,1,2,3],index_col=[0,1])

In [18]: res.index
Out[18]: 
MultiIndex
[(u'R_l0_g0', u'R_l1_g0'), (u'R_l0_g1', u'R_l1_g1'), (u'R_l0_g2', u'R_l1_g2'), (u'R_l0_g3', u'R_l1_g3'), (u'R_l0_g4', u'R_l1_g4')]

In [19]: res.columns
Out[19]: 
MultiIndex
[(u'C_l0_g0', u'C_l1_g0', u'C_l2_g0', u'C_l3_g0'), (u'C_l0_g1', u'C_l1_g1', u'C_l2_g1', u'C_l3_g1'), (u'C_l0_g2', u'C_l1_g2', u'C_l2_g2', u'C_l3_g2')]

In [20]: res
Out[20]: 
C0              C_l0_g0 C_l0_g1 C_l0_g2
C1              C_l1_g0 C_l1_g1 C_l1_g2
C2              C_l2_g0 C_l2_g1 C_l2_g2
C3              C_l3_g0 C_l3_g1 C_l3_g2
R0      R1                             
R_l0_g0 R_l1_g0    R0C0    R0C1    R0C2
R_l0_g1 R_l1_g1    R1C0    R1C1    R1C2
R_l0_g2 R_l1_g2    R2C0    R2C1    R2C2
R_l0_g3 R_l1_g3    R3C0    R3C1    R3C2
R_l0_g4 R_l1_g4    R4C0    R4C1    R4C2

In [21]: res.index.names
Out[21]: ['R0', 'R1']

In [22]: res.columns.names
Out[22]: ['C0', 'C1', 'C2', 'C3']
```


Labels:

Comments:
Might it be useful to be able to pass the index/header names if you know them a priori?

can u give an example?

Hm. Maybe it's not that useful since u can just read them in and then filter out what u don't want. I'll give an example anyway.

Say you had column index names stored somewhere e.g., a text file. You only want to read in the levels of the column index with those names, so you specify e.g., `header=['c0', 'c1']` and only the levels from `c0` and `c1` will be in the resulting index.

you kind of want a `uselevels` argument analagous to `usecols`, need header to indicate which rows to use for the index in the first palce, e.g. `header=[0,1,2]`, which says you have 3 levels, then `uselevels=['c0','c1']` means only take the first 2...(and discard the 3rd) ?

@jreback right, right. sorry, yes header is for rows to use as headers. yeah or only the columns named c0, c1, ..., cN.

@y-p would be interested to here your take on this :)

It's consistent with the way the row index is handled, and as long as it doesn't try to do
auto-detection and keeps the user in charge it should work fine. The issues are just 
those of back-compat which you've already raised, so to reiterate:

1) is it necessary to change default behavior rather then add a flag? this will break someone's code,
as you mentioned. With the recent due colname mangling, we opted for an arg with a default
value the keeps back-compat, and added a note that it will flip in 0.12, giving people a heads-up
about the breaking change.
2) if I like the current behavior, or have code that depends on it (skips rows for example),
how can I ensure that's what my output looks like?
3) Same for input, if I have existing data with intentional tuples, how do I ensure I get
 the old behaviour?

Speaking of dupe cols, obviously a misuse since the save/load arguments don't match, 
but note the dupe unnamed columns, I don't think we meant that to happen:

```
In [1]: df = mkdf(5,3,r_idx_nlevels=3,c_idx_nlevels=4)
In [2]: df.to_csv('test.csv')
In [7]: res = pd.read_csv('test.csv',header=[0,1,2,3],index_col=[0,1])
In [10]: res.columns
Out[10]: 
MultiIndex
[(u'Unnamed: 2', u'Unnamed: 2', u'Unnamed: 2', u'Unnamed: 2'), (u'C_l0_g0', u'C_l1_g0', u'C_l2_g0', u'C_l3_g0'), (u'C_l0_g1', u'C_l1_g1', u'C_l2_g1', u'C_l3_g1'), (u'C_l0_g2', u'C_l1_g2', u'C_l2_g2', u'C_l3_g2')]

C0              Unnamed: 2 C_l0_g0 C_l0_g1 C_l0_g2
C1              Unnamed: 2 C_l1_g0 C_l1_g1 C_l1_g2
C2              Unnamed: 2 C_l2_g0 C_l2_g1 C_l2_g2
C3              Unnamed: 2 C_l3_g0 C_l3_g1 C_l3_g2
R0      R1                                        
R_l0_g0 R_l1_g0    R_l2_g0    R0C0    R0C1    R0C2
R_l0_g1 R_l1_g1    R_l2_g1    R1C0    R1C1    R1C2
R_l0_g2 R_l1_g2    R_l2_g2    R2C0    R2C1    R2C2
R_l0_g3 R_l1_g3    R_l2_g3    R3C0    R3C1    R3C2
R_l0_g4 R_l1_g4    R_l2_g4    R4C0    R4C1    R4C2

```

1) The question is are we 'forcing' a multi-index on columns to now be written in the new format, rather than the list of tuples. The answer is yes. I suppose could an an option not to do that, but I would argue that the default should be to do the new format.

the read code will work in either case (If you have list of tuples, then header=0 will interpret it, while header=[0,1,2] will work in the reg case).

So its only a back compat issue if you are actually using the file and depending on the list of tuples (and not reading with pandas, but using externally)

2) I don't understand, you _want_ to skip rows?

3) looks like a bug....

so I guess having 1 (or 2) more options

Can you have a sub-category? (e.g. `displays.csv.multi_index?`)

```
displays.write_csv_multi_index_columns=True
displays.read_csv_multi_index_as_tuples=True
```

would be the new behavior

Ok for you example of an underspecified `index_col` I now return this

```
(Pdb) result
C0              Unnamed: 2_level_0 C_l0_g0 C_l0_g1 C_l0_g2
C1              Unnamed: 2_level_1 C_l1_g0 C_l1_g1 C_l1_g2
C2              Unnamed: 2_level_2 C_l2_g0 C_l2_g1 C_l2_g2
C3              Unnamed: 2_level_3 C_l3_g0 C_l3_g1 C_l3_g2
R0      R1                                                
R_l0_g0 R_l1_g0            R_l2_g0    R0C0    R0C1    R0C2
R_l0_g1 R_l1_g1            R_l2_g1    R1C0    R1C1    R1C2
R_l0_g2 R_l1_g2            R_l2_g2    R2C0    R2C1    R2C2
R_l0_g3 R_l1_g3            R_l2_g3    R3C0    R3C1    R3C2
R_l0_g4 R_l1_g4            R_l2_g4    R4C0    R4C1    R4C2
```

Aside from what the default should be, is it still possible to specify that I want to have
the old behavior? that's writing out tuples, and reading in tuples as-is.

For people who have existing code, the bare minimum is to allow to them
to add a flag to keep the old behavior. Forcing everyone to rewrite their code
to a larger extent then that is a bad move IMO.

Here's to specify old behavior (option is a bit long, but wanted to be explicit),
default is for the _new_ behavior (which will still read the old style)

```
In [4]: df = mkdf(5,3,r_idx_nlevels=2,c_idx_nlevels=4)

In [5]: df.to_csv('test.csv',multi_index_columns_compat=True)

In [6]: !cat 'test.csv'
R0,R1,"('C_l0_g0', 'C_l1_g0', 'C_l2_g0', 'C_l3_g0')","('C_l0_g1', 'C_l1_g1', 'C_l2_g1', 'C_l3_g1')","('C_l0_g2', 'C_l1_g2', 'C_l2_g2', 'C_l3_g2')"
R_l0_g0,R_l1_g0,R0C0,R0C1,R0C2
R_l0_g1,R_l1_g1,R1C0,R1C1,R1C2
R_l0_g2,R_l1_g2,R2C0,R2C1,R2C2
R_l0_g3,R_l1_g3,R3C0,R3C1,R3C2
R_l0_g4,R_l1_g4,R4C0,R4C1,R4C2

In [7]: result = read_csv('test.csv',header=0,index_col=[0,1],multi_index_columns_compat=True)

In [8]: result
Out[8]: 
                ('C_l0_g0', 'C_l1_g0', 'C_l2_g0', 'C_l3_g0') ('C_l0_g1', 'C_l1_g1', 'C_l2_g1', 'C_l3_g1') ('C_l0_g2', 'C_l1_g2', 'C_l2_g2', 'C_l3_g2')
R0      R1                                                                                                                                            
R_l0_g0 R_l1_g0                                         R0C0                                         R0C1                                         R0C2
R_l0_g1 R_l1_g1                                         R1C0                                         R1C1                                         R1C2
R_l0_g2 R_l1_g2                                         R2C0                                         R2C1                                         R2C2
R_l0_g3 R_l1_g3                                         R3C0                                         R3C1                                         R3C2
R_l0_g4 R_l1_g4                                         R4C0                                         R4C1                                         R4C2

```

How about `tupleized_cols=bool` or similar?

`tupleized_columns=bool`

to be consistent with `mangle_dup_columns`

or maybe change that to `mangle_dup_cols` for consistency with `index_col`?

I guess default should be True for 0.11.1, then False (new behavior) in 0.12
so API available but not default yet ?

(+1) + (+1)

@y-p my mistake on `mange_dup_columns`, I had added to the parsers docstring and put in like that rather than the actual option name (which is correct), `mange_dup_cols`.

of course you can suggest, and I'll take it!

suggest you open a `note-to-selves` issue for the 0.12 miletsone, to remind us to flip the default 
and update the docstrings.

typos fixed

@y-p I can allow `index=False` in `to_csv` and then allow `index_col=None` in `read_csv`, BUT, then you lose any columns.names, or could disallow writing w/o an index

what do you think?

@y-p @cpcloud take a look at the csv as written on disk. There is an empty line where the row labels should go (and as they are none). Its very hard to parse this if this line is not there (because its ambiguous whether their are row labels or they should be part of the data). I could solve this with another option, but I don't like that solution.

Any thoughts?

```
In [11]: 
df = DataFrame(np.random.randint(0,10,size=(3,3)), 
                                 columns=MultiIndex.from_tuples([('bah', 'foo'), 
                                                                 ('bah', 'bar'), 
                                                                 ('ban', 'baz')],
                                                                names=None))

In [13]: df.to_csv('test.csv',tupleize_cols=False)

In [14]: !cat 'test.csv'
,bah,bah,ban
,foo,bar,baz
,,,
0,2,0,6
1,4,0,5
2,1,4,2

In [16]: read_csv('test.csv',tupleize_cols=False,header=[0,1],index_col=0)
Out[16]: 
   bah       ban
   foo  bar  baz
0    2    0    6
1    4    0    5
2    1    4    2
```

Maybe adding a comment line to the right of the empty row so that new users aren't wondering what this is. That forces a specific comment syntax though... 

what about:

`Unnamed Index Label 0,,,`

(which the parser can then ignore, similar to what we do with unnamed columns)
?

should this throw on `header=list_that_is_too_long` or `header=list_that_contains_rows_not_in_header`, e.g. (using your `'test.csv'`),

``` python
if header not in parsed_rows_before_comma_line:
    raise ParserError("passed header rows not all in header when parsing MultiIndex")
```

when for example a user does something like:

``` python
read_csv('test.csv', tupleize_cols=False, header=range(10), index_col=0)
```

Slightly OT: allow `xrange` objects in `header` argument?

So I thing these are reasonable

```
In [3]: read_csv('test.csv', tupleize_cols=False, header=range(10), index_col=0)
CParserError: Passed header=0 but only 6 lines in file

In [4]: read_csv('test.csv', tupleize_cols=False, header=range(3), index_col=0)
Exception: Passed header=[0,1,2,3] are too many rows for this multi_index of columns
```

no real reason to support `xrange` this is a really short sequence (and we don't support it in
any other fields)

@wesm @y-p what do you think? 

my 2c:
- No need for xrange support. more generally, no need to add features just "because we can",
  there's always an associated cost.  
- Fine to have a row of missing values to make parsing easier on roundtrip,
  when the user specifies `tupleize_cols=True`.
-  disallowing `index=False` with `tupleize_cols=True` seems
  reasonable to me.
- Note that csv is just an export format, It's pushing things when you try to turn it
  into a dataframe serialization format. (the empty row labels line, the  index=False problem, 
  the inevitable request for control over including index names). 
- Slight dejavu of #2478, #2370, really.

@y-p 

I didn't disable `index=False` with `tupleize_cols=True`; just aded a test for it. Its not useful, but allowed. (This is allowed in 0.11.0 in fact)

So this I think is ready to go

`index=False` and `tupleize=True` (in 0.11.1), same as in prior versions

```
In [2]: df =  DataFrame(np.random.randint(0,10,size=(3,3)),columns=MultiIndex.from_tuples([('bah', 'foo'),('bah', 'bar'),('ban', 'baz')],names=['first','second']))

In [3]: df
Out[3]: 
first   bah       ban
second  foo  bar  baz
0         1    6    2
1         0    3    8
2         0    4    3

In [4]: df.to_csv('test.csv',tupleize_cols=True,index=False)

In [5]: !cat 'test.csv'
"('bah', 'foo')","('bah', 'bar')","('ban', 'baz')"
1,6,2
0,3,8
0,4,3

In [6]: read_csv('test.csv',header=0,tupleize_cols=True,index_col=None)
Out[6]: 
   ('bah', 'foo')  ('bah', 'bar')  ('ban', 'baz')
0               1               6               2
1               0               3               8
2               0               4               3

```

@jreback @y-p sorry about the `xrange` noise. abs no need for that there. want to avoid feature creep amap.

@y-p i agree that this is bordering on df srlztn but i think that if pandas supports mi's then it should support them as completely as possible.

@jreback i think this err msg is a bit cryptic `CParserError: Passed header=0 but only 6 lines in file`. why not something like "Elements of header list contain N but there are only M lines in file" subs N and M for associated values, because if there 6 lines in a file why is `header=0` invalid?