BUG/ERR: read_csv(header=[0]) should raise/warn

[ischwabacher]

read_csv with a single-row header either breaks any names that might be on the index, or reads all data as NaN. This problem might exist because pd.read_csv hasn't caught up to #7589.

In [1]: import pandas as pd

In [2]: from StringIO import StringIO

In [3]: pd.read_csv(StringIO('''\
alpha,,A,B
foo,bar,,
this,x,1,2
this,y,5,6
that,x,3,4
that,y,7,8'''), header=0, index_col=[0,1])
Out[3]: 
                   A   B
alpha Unnamed: 1        
foo   bar        NaN NaN
this  x            1   2
      y            5   6
that  x            3   4
      y            7   8

In [4]: pd.read_csv(StringIO('''\
alpha,,A,B
foo,bar,,
this,x,1,2
this,y,5,6
that,x,3,4
that,y,7,8'''), header=[0], index_col=[0,1])
Out[4]: 
alpha       A    B
foo  bar          
this x    NaN  NaN
     y    NaN  NaN
that x    NaN  NaN
     y    NaN  NaN

In [5]: pd.read_csv(StringIO('''\
alpha,,A,B
beta,,Y,Z
foo,bar,,
this,x,1,2
this,y,5,6
that,x,3,4                                  
that,y,7,8'''), header=[0,1], index_col=[0,1])
Out[5]: 
alpha     A  B
beta      Y  Z
foo  bar      
this x    1  2
     y    5  6
that x    3  4
     y    7  8

[jreback]

use header=0
this should technically work but it violates the guarantees as if header is a list it must have len > 1
And is therefore looking for a multi index header (which of only a single level which is not valid)
so it's prob an easy fix

[ischwabacher]

Notice that header=0 has different semantics regarding the second row: with header=[0], it's taken as the result.index.names, whereas with header=0 you need to use skiprows=[1] and set the names of the index manually.

[jreback]

I am not sure what you have (in the first example) is a valid format. IOW you cannot generate it using to_csv to the same interpretation (IIRC this is a separate issue). The index name on a single column is lost

In [68]: df = DataFrame(np.arange(1,9).reshape(4,2),columns=Index(['A','B'],name='alpha'),index=MultiIndex.from_product([['this','that'],['x','y']],names=['foo','bar']))

In [69]: df.to_csv('test.csv',mode='w')

In [70]: !cat test.csv
foo,bar,A,B
this,x,1,2
this,y,3,4
that,x,5,6
that,y,7,8

[ischwabacher]

Ah, I get it now. I thought you could because of the appearance of 'alpha' in str(Out[4]), but after digging more it became clear that that's just a broken object anyway. I think there are three possible solutions to this:

• Make read_csv raise when index_col or header is a singleton
• Make read_csv silently unpack such singletons
• Allow Indexes to have names just like MultiIndexes (my preference-- I really dislike it when I'm stacking and unstacking levels and suddenly my names disappear).

[jreback]

• Index can have a name! (see index repr should show name(s) attribute #6482 as its not printing out, but its their).
• the issue is that header=[a_single_value] I think should raise (unless tupleize_cols=False, iow its a list of tuples, which is de-facto a MultiIndex and how things < 0.12 used to parse).
• alternatively, you could have it unpack it (and maybe show a warning) if tupleize_cols=True (the default), AND their is no ambiguity.

care to do a pull-request?

[ischwabacher]

If this raises, how should we handle the case where there's a single row index and a single column index, each of which has a name?

[jreback]

I don't think that is representable at the moment (except by a separate line, which to_csv doesn't generate properly)

somthing like

column_index_name,column_1
index_name
index_0,value_0

nice thing about tests is you can change code and see what if anything breaks.
Their are a lot of edge cases for this kind of thing.

[ischwabacher]

This will have to wait until I have some time, I think.