read_table fails with MultiIndex input and delim_whitespace=True

[mcwitt]

Related #6889

Example:

In [4]: text = """                      A       B       C       D        E
one two three   four
a   b   10.0032 5    -0.5109 -2.3358 -0.4645  0.05076  0.3640
a   q   20      4     0.4473  1.4152  0.2834  1.00661  0.1744
x   q   30      3    -0.6662 -0.5243 -0.3580  0.89145  2.5838"""

In [5]: pd.read_table(StringIO(text), delim_whitespace=True)
---------------------------------------------------------------------------
CParserError                              Traceback (most recent call last)
. . .
CParserError: Error tokenizing data. C error: Expected 6 fields in line 3, saw 9

This (partially) works if delim_whitespace=True is replaced with sep='\s+', engine='python' (although columns A-D are lost):

In [6]: pd.read_table(StringIO(text), sep='\s+', engine='python')
Out[6]: 
                           E
one two three   four        
a   b   10.0032 5     0.3640
    q   20.0000 4     0.1744
x   q   30.0000 3     2.5838

[3 rows x 1 columns]

[jreback]

@mcwitt can you address for 0.14?

[mcwitt]

Yep, I've made some progress on this. Should have a PR soon...

[mcwitt]

OK, I've fixed the bug in PythonParser. The C parser seems to have its own code for this, and I'm not sure yet whether it had been designed to support specifying a MultiIndex in this way.

[jreback]

hmm. its only the header detection code which is pretty straightforward (e.g. you have to read the header line-by-line based on the spec header=[0,1] whatever, one odd thing is you may or may not have an extra row for the row names depending on how it was written), then turning that to a MI is all the same for both parsers (and is python code)

[mcwitt]

hmm. its only the header detection code

I think this is a separate issue from header detection. The original title of this issue was wrong, this doesn't have to do with MI columns. This has to do with automatically setting the index columns to the 2nd row when the sum of the 1st and 2nd rows equals the 3rd, i.e.

In [8]: text = """                      A       B       C       D        E
one two three   four
a   b   10.0032 5    -0.5109 -2.3358 -0.4645  0.05076  0.3640
a   q   20      4     0.4473  1.4152  0.2834  1.00661  0.1744
x   q   30      3    -0.6662 -0.5243 -0.3580  0.89145  2.5838"""

In [9]: pd.read_table(StringIO(text), sep='\s+', engine='python')
Out[9]: 
                           A       B       C        D       E
one two three   four                                         
a   b   10.0032 5    -0.5109 -2.3358 -0.4645  0.05076  0.3640
    q   20.0000 4     0.4473  1.4152  0.2834  1.00661  0.1744
x   q   30.0000 3    -0.6662 -0.5243 -0.3580  0.89145  2.5838

[3 rows x 5 columns]

The code for dealing with this in the python parser is in _get_index_name, but the C parser doesn't seem to have anything similar yet.

[mcwitt]

Made a PR for the fix for the python parser. The fix for the C parser will be a bit more involved so I think it would be good to do this in a separate PR. Essentially I think we need to duplicate the functionality in PythonParser._get_index_name in CParserWrapper initially, and then merge as much of the code as possible into a common function for both parsers.

[jreback]

can you detect the condition in c-parser and raise?