read_fwf colspecs autodetection could be more flexible

See #4955 (comment)

id foo 
1  a
1  a
1  a
123a

This works with the snippet in the orig #4488 but fails with the merged #4955 due to the last line.
It's a common example of a fwf file.

[jtratner]

So what should happen in this case? Are you thinking that detection should be entirely based on first line?

id foo 
123a
123a
123a
123a

[jtratner]

Also, as an aside, this is probably a clearer example (when it's at the end, seems like it's a special footer)

id foo 
1  a
123a
1  a
1  a

Not at all. #4488 handles the first case by looking for a pattern which is common to a majority of records,
#4955 fails if there's a single record in the whole dataset that occupies the whole column.

In your example, the first row is indeed the only hint as to the width of the columns,
and it may be interpreted as a single column with a header label of "id foo".

[jreback]

cc @alefnula

I think that if detection is 'infer' and "doesn't work", then should raise.

not sure if you can make an accurate determination in all cases though?

True, can't make it work for all cases. I think the first example is more common then yours.
For the case where there is a full single-character column seperating the fields, as required by #4955,
#4488 will (almost) always work.

[jreback]

cc @alefnula

I think you can do a percentage computation on the mask that you are computing. If they are say the same in say 90% of the lines then you can go with that (or even higher per). I think that would allow for some (but not most) no-delimeter lines

[alefnula]

Yes, this is a problem. The current implementation cannot detect the columns properly if they are not separated by the filler. But the suggested implementation in issue #4488 didn't work for any case I tested :( For the example you just wrote here it returns [8]. And even for simple cases like:

1 2 3
4 5 6
7 8 9

It returns [2, 3]...

But this is not the primary reason I haven't tried to fix it. The much bigger problem with the splitting approach is that it cannot handle the cases where columns are strings which can contain the filler, for example:

id  Firstname and Lastname  Age
1   Douglas Adams           44
1   Arthur Conan Doyle      55

The approach I used supports these cases.

So basically these are the reason why I chose to use the bitmask instead of splitting and counting. (and also it's more memory and time efficient - but this is not that important if we read only 100 lines of data). The only drawback is that it cannot handle the columns that are glued together, but that is clearly stated in the documentation.

Maybe an approach where the outliers will be discarded can be implemented. But I'll have to find a way to do it efficiently...

[jtratner]

Ultimately, you can still pass colspecs directly in ambiguous cases.

[alefnula]

Also I don't think this is a bug. The behavior of inferring columns is clearly documented, and the function behaves as the documentation states it does...
But, as everything, it can be improved :)

[jreback]

@alefnula can you squash the mas column wise and compare how many equal as per of total rows? (not sure if that is really useful.....) just a thought

Not true. There may be a bug in in #4488 (or not) but the underlying approach should handle strings
such as those just fine.
The issue is that it has a free parameter (the threshold) which has no "right" value, unless you know the distribution of all datasets out there (we don't).
It seems to me that #4955 is a private case of the threshold scheme in #4488, but with a threshold of 0%,
and I'm pretty sure whatever the "best" value is, it's not that.

Documented behaviour is great, but ultimately it's preferable to support more cases rather
then document the fact that you don't, no?

Removed bug label. It's a common case that isn't handled, which is a shame but not a bug.

[alefnula]

@jreback I dont actually think that this problem can be solved in a way that will satisfy everyone :( since it's too ambiguous. The provided example:

id foo 
1  a
1  a
123a

Can be split in two different (both valid) ways:

 id  foo      id  foo
  1    a       1    a
  1    a  or   1    a
123    a      12   3a

And here is an even worse case.

c  a  foo 
12 1  5
23 1  6
4561237

If we try to implement any kind of heuristic that will determine where to split, there will be a case when we will get it wrong. The user will, maybe, get the correct number of columns but the data will be incorrect. So he will get a false conclusion (especially if he has a large data set that he cannot skim through) that we correctly split the columns, but the data will not be correct.
In the current implementation, all these ambiguous cases will produce wrong number of column in the beginning, so the user will immediately know that something is not right...
My opinion is that it's better to get a completely wrong number of columns in the first place, then to get the right number of columns, but have incorrect data in them because of the ambiguous cases.

[jreback]

@alefnula agreed....false positive is bad here. I was just wondering if you can detect the false positive and raise a helpful error message?

[alefnula]

@jreback In the current implementation you wont get false positives. Either the number column will be correct and you will get valid data or the number of columns wont be correct...
The thing that I may be able to implement is to "suspect" that some lines are ambiguous and issue a warning:

Line 44 is suspicious. If the inferred columns are incorrect, check the line 44 or provide a correct colspecs argument.