make pandas.read_csv to not add lines with less columns delimiters than the main lines

user3313834 0
Viewed 28

Using pandas.read_csv with on_bad_lines='warn' option for lines with too many columns delimiters, it work well, bad lines are not loaded and stderr catch the bad lines numbers:

    import pandas as pd
    from io import StringIO
    data = StringIO("""
    nom,f,nb
    bat,F,52
    cat,M,66,
    caw,F,15
    dog,M,66,,
    fly,F,61
    ant,F,21""")
    df = pd.read_csv(data, sep=',', on_bad_lines='warn')

    # b'Skipping line 4: expected 3 fields, saw 4\nSkipping line 6: expected 3 fields, saw 5\n'

    df.head(10)
    #    nom  f  nb
    # 0  bat  F  52
    # 1  caw  F  15
    # 2  fly  F  61
    # 3  ant  F  21

But in case the number of delimiter (here sep=,) is less than the main, the line is added adding NaN.:

    import pandas as pd
    from io import StringIO
    data = StringIO("""
    nom,f,nb
    bat,F,52
    catM66,
    caw,F,15
    dog,M66
    fly,F,61
    ant,F,21""")
    df = pd.read_csv(data, sep=',', on_bad_lines='warn', dtype=str)
    df.head(10)

    #       nom    f   nb
    # 0     bat    F   52
    # 1  catM66  NaN  NaN            <==
    # 2     caw    F   15
    # 3     dog  M66  NaN            <==
    # 4     fly    F   61
    # 5     ant    F   21

Is there a way to make read_csv to not add lines with less columns delimiters than the main lines ?

Note: I'm in a context of loading real big data files (e.g. hundred of millions of lines, so the idea is not to propose any upfront grep/sed/awk processing but to take benefit of fast read_csv bulk_load)

pandas python
1 Answers

pd.read_csv() is a very nice function that performs a well-defined computation, but you desire a slightly different computation. You wish to filter out all rows containing fewer than K fields.

the idea is not to propose any upfront grep / sed / awk processing

You have rather constrained the solution space. Apparently speed (elapsed time) or power efficiency (watts dissipated) are motivating concerns.

You correctly observe that grep is quite fast and would be a natural pre-processing stage. One could store its filtered output to a temp file which we feed to .read_csv(), potentially costing extra disk I/O. A better solution would be to pipe its output using the subprocess library.

The original post mentions no grep timing results, so it is unclear if overhead due to an extra child process has been shown to be "too slow". There's no throughput specification of N rows / second, so it's unclear how this or any competing proposal should be evaluated.

Note that .read_csv() accepts a file-like object, which could be a python generator that inspects each row and only yields suitable rows.

Given that you're gung ho on calling .read_csv(), a function which doesn't quite compute what you want, it seems there's little for it but to post-process its output and hope for the best.

Filtering out all NaNs might do, but that's a little on the drastic side. There is some buggy generating process that produces "short" rows with fewer than K fields. If you know the minimum number of fields it's guaranteed to produce, you could at least do appropriate column-wise filtering to discard short rows. Then you get to preserve true NaNs in the first several columns. Good luck!

J_H0
Related