incorrect columns when reading text file in pandas

Question

2000       1    -999
2000       2    -999
2000       3    -999
2000       4    -999
2000       5    -999
2000       6    -999
2000       7    -999
2000       8    -999
2000       9    -999
2000      10    -999
2000      11    -999
2000      12    -999
2000      13    -999
2000      14    -999
2000      15    -999
2000      16    -999
2000      17    -999

I want to read a text file containing the data above into a pandas dataframe, I am using this command, but the output is not what I want:

df = pd.read_csv(path_file, names=['col_a', 'col_b', 'col_c'])

How do I fix this output:

                      col_a  col_b  col_c
0      2000       1    -999    NaN    NaN
1      2000       2    -999    NaN    NaN
2      2000       3    -999    NaN    NaN
3      2000       4    -999    NaN    NaN
4      2000       5    -999    NaN    NaN

score 3 · Answer 1 · answered Oct 29 '18 at 03:52

Do:

df = pd.read_csv(filename, names=['col_a', 'col_b', 'col_c'],sep='\s+')

Add an extra sep='\s+', because default it separates by comma, not space, so change it.

Or delim_whitespace:

df = pd.read_csv(filename, names=['col_a', 'col_b', 'col_c'], delim_whitespace=True)

incorrect columns when reading text file in pandas

1 Answers1