How to read csv file with missing values and 'delim_whitespace=True'?

user_2329468 0
Viewed 1248

I would like to know if it is possible to simply drop any line that couses error instead of rising an exception.

My issue is connected to processing text file such this one:

111 aaa 222 bbb
1   a   2   b
11      22

Because of the varied number of whitespaces as separators, I am using option 'delim_whitespace=True' to read_csv function. I am however also explicitelly specifying data types by 'dtype' parameter.

It is natural that pandas shifts value 22 to second column for the third row (and I don't believe there is a way how to convince it that it actually bellongst to the third one). However since the second column is expected to be string it raises an exception.

I understand that this could be probably solved using 'converters' parameter, but I am worried about performance since the data file is quite large (millions of rows).

So is it possible to drop lines with lower number or columns (there is 'error_bad_lines' for higher) or drop any line which couses exception during retyping. Or do you have any other ideas?

pandas python
1 Answers

Use pandas.read_fwf to read file. This will fill empty string with NaN values.

=^..^=

import pandas as pd

data = pd.read_fwf('data.txt', header=None)
data.columns = ["c1", "c2", "c3", "c4"]

load:

    c1   c2   c3   c4
0  111  aaa  222  bbb
1    1    a    2    b
2   11  NaN   22  NaN

Next simply drop rows with NaN values:

out_data = data.dropna()

Output:

    c1   c2   c3   c4
0  111  aaa  222  bbb
1    1    a    2    b
user_115579740
Related