BUG: parser can handle a common_format multi-column index (no row index cols), (GH4702)
State: closed

closes #4702, should help with #5254

Will handle the format generated by `to_csv` (which has a an 'extra' line for the index names)
and the more 'common' format

```
In [4]:         data = """,a,a,a,b,c,c
   ...: ,q,r,s,t,u,v
   ...: one,1,2,3,4,5,6
   ...: two,7,8,9,10,11,12"""

In [6]: read_csv(StringIO(data),header=[0,1],index_col=0)
Out[6]: 
     a         b   c    
     q  r  s   t   u   v
one  1  2  3   4   5   6
two  7  8  9  10  11  12
```

equiv to

```
df = DataFrame([[1,2,3,4,5,6],[7,8,9,10,11,12]],
                       index=['one','two'],
                       columns=MultiIndex.from_tuples([('a','q'),('a','r'),('a','s'),
                                                  ('b','t'),('c','u'),('c','v')]))
```

no index col

```
In [1]: data = """a,a,a,b,c,c
   ...: q,r,s,t,u,v
   ...: 1,2,3,4,5,6
   ...: 7,8,9,10,11,12"""

In [2]: read_csv(StringIO(data),header=[0,1],index_col=None)
Out[2]: 
   a         b   c    
   q  r  s   t   u   v
0  1  2  3   4   5   6
1  7  8  9  10  11  12
```


Labels:

Comments:
This is great! What is the expected behavior if first column has a label in
the first two rows? (say I explicitly tell it that 0 + 1 are headers and 0
is index col.

that is the example that is up there

@jreback your example starts out with a comma, so doesn't that mean the cell in the first two rows first column is empty?

@jtratner I put another example up, no index col specified

@jtratner what you were looking for?

I'm guessing this works, but I mean ambiguous-ish cases:

``` python
In [1]: data = """a,a,a,b,c,c
   ...: q,r,s,t,u,v
   ...: 1,2,3,4,5,6
   ...: 7,8,9,10,11,12"""

read_csv(StringIO(data),header=[0,1],index_col=0)
```

What should this do? Raise? Assume that a and q are labels for MI levels?

Similar question here

``` python
In [1]: data = """,a,a,b,c,c
   ...: q,r,s,t,u,v
   ...: 1,2,3,4,5,6
   ...: 7,8,9,10,11,12"""

read_csv(StringIO(data),header=[0,1],index_col=0)
```

(it takes my old mac a _long_ time to compile, so I'm trying to pull down your branch now).

And similarly what happens with MI header _and_ MI index - :smile: 

``` python
In [1]: data = """,a,a,b,c,c
   ...: q,r,s,t,u,v
   ...: 1,2,3,4,5,6
   ...: 7,8,9,10,11,12"""

read_csv(StringIO(data),header=[0,1],index_col=[0, 1])
```

@jtratner your examples

First one is user error

```
In [7]: In [1]: data = """a,a,a,b,c,c
   ...:    ...: q,r,s,t,u,v
   ...:    ...: 1,2,3,4,5,6
   ...:    ...: 7,8,9,10,11,12"""

In [13]: read_csv(StringIO(data),header=[0,1],index_col=0)
Out[13]: 
a  a      b   c    
q  r  s   t   u   v
1  2  3   4   5   6
7  8  9  10  11  12

In [14]: read_csv(StringIO(data),header=[0,1])
Out[14]: 
   a         b   c    
   q  r  s   t   u   v
0  1  2  3   4   5   6
1  7  8  9  10  11  12
```

2nd is user error, but the data is corrupt (missing first label in top-level).
maybe can detect; though 

```
In [15]: In [1]: data = """,a,a,b,c,c
   ....:    ...: q,r,s,t,u,v
   ....:    ...: 1,2,3,4,5,6
   ....:    ...: 7,8,9,10,11,12"""

In [16]: 

In [16]: read_csv(StringIO(data),header=[0,1],index_col=0)
Out[16]: 
   a      b   c    
q  r  s   t   u   v
1  2  3   4   5   6
7  8  9  10  11  12

In [17]: read_csv(StringIO(data),header=[0,1])
Out[17]: 
   Unnamed: 0_level_0  a      b   c    
                    q  r  s   t   u   v
0                   1  2  3   4   5   6
1                   7  8  9  10  11  12
```

3rd works fine (its wrong specification, but could be valid)

```
In [18]: In [1]: data = """,a,a,b,c,c
   ....:    ...: q,r,s,t,u,v
   ....:    ...: 1,2,3,4,5,6
   ....:    ...: 7,8,9,10,11,12"""

In [19]: 

In [19]: read_csv(StringIO(data),header=[0,1],index_col=[0, 1])
Out[19]: 
     a   b   c    
q    s   t   u   v
1 2  3   4   5   6
7 8  9  10  11  12
```

The second one is what you'd expect from something with two level
hierarchical columns and a single index column, no?
On Oct 24, 2013 7:53 AM, "jreback" notifications@github.com wrote:

> @jtratner https://github.com/jtratner your examples
> 
> First one is user error
> 
> In [7]: In [1]: data = """a,a,a,b,c,c
>    ...:    ...: q,r,s,t,u,v
>    ...:    ...: 1,2,3,4,5,6
>    ...:    ...: 7,8,9,10,11,12"""
> 
> In [13]: read_csv(StringIO(data),header=[0,1],index_col=0)
> Out[13]:
> a  a      b   c
> q  r  s   t   u   v
> 1  2  3   4   5   6
> 7  8  9  10  11  12
> 
> In [14]: read_csv(StringIO(data),header=[0,1])
> Out[14]:
>    a         b   c
>    q  r  s   t   u   v
> 0  1  2  3   4   5   6
> 1  7  8  9  10  11  12
> 
> 2nd is user error, but the data is corrupt (missing first label in
> top-level).
> maybe can detect; though
> 
> In [15]: In [1]: data = """,a,a,b,c,c
>    ....:    ...: q,r,s,t,u,v
>    ....:    ...: 1,2,3,4,5,6
>    ....:    ...: 7,8,9,10,11,12"""
> 
> In [16]:
> 
> In [16]: read_csv(StringIO(data),header=[0,1],index_col=0)
> Out[16]:
>    a      b   c
> q  r  s   t   u   v
> 1  2  3   4   5   6
> 7  8  9  10  11  12
> 
> In [17]: read_csv(StringIO(data),header=[0,1])
> Out[17]:
>    Unnamed: 0_level_0  a      b   c
>                     q  r  s   t   u   v
> 0                   1  2  3   4   5   6
> 1                   7  8  9  10  11  12
> 
> 3rd works fine.
> 
> In [18]: In [1]: data = """,a,a,b,c,c
>    ....:    ...: q,r,s,t,u,v
>    ....:    ...: 1,2,3,4,5,6
>    ....:    ...: 7,8,9,10,11,12"""
> 
> In [19]:
> 
> In [19]: read_csv(StringIO(data),header=[0,1],index_col=[0, 1])
> Out[19]:
>      a   b   c
> q    s   t   u   v
> 1 2  3   4   5   6
> 7 8  9  10  11  12
> 
> —
> Reply to this email directly or view it on GitHubhttps://github.com/pydata/pandas/pull/5298#issuecomment-26985834
> .

@jtratner I think the 2nd one is exactly what you got, 2-level column mi, and 1-level index named q. maybe not what the user expects, but it parses correctly according tot he rules.

Okay yeah that's what I'd expect. I was confused because you called it
malformed.

+1 to this!

ok...I mean malformed as in possibly not what the user expects

Thanks @jreback!