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?

[jreback]

moving this to 0.14.1 (though we'll merge your pythonparse fix after release notes).

and if you can detect the condition in the c-parser would be great (and just raise for now)

[jreback]

@mcwitt any thoughts on fixing the c-parser for this?

[mcwitt]

I'm still thinking about this. I think I will try to implement the fix described above, but I will be too busy to look at it for the next two weeks (traveling to a conference). I should be able to get it done for 0.14.1 though.

[jreback]

gr8!

[mcwitt]

Update: I'm close to a fix for this! Hopefully can make a PR tomorrow.

[jreback]

Reverted #7591, moving to 0.15 as for the failing tests: #7623

[jreback]

thanks.....easier to revert this PR, can address in 0.15.0 when you have time!

[rmsilva1973]

Hey @jreback . Just received this bug for triage. It's still a reproducible issue as of '1.1.0.dev0+1247.g9c317322f'

[jreback]

and it’s an open issue if you want to submit a PR

[rmsilva1973]

So, I've been the last 4 days trying to figure out how this tokenizer function works. I've initially narrowed down the test case to a simples one:

import pandas as pd
from io import StringIO
text = """ A
one
c -13"""
pd.read_table(StringIO(text),delim_whitespace=True)

@jreback it doesn't seem related to MultiIndex specifically. Any ammount of indexes included on the data (is it called "implicit indexes"?) hits the bug. Do you think it's reasonable to change the bug title to something more appropriate?

[mroeschke]

delim_whitespace is deprecated and will be removed in pandas 3.0 so closing as wont fix