Handle blank/empty CSV cells

Question

I have two sets of geolocation data as lat/long in a csv file. The first two columns (0 and 1) comprise the latitude and longitude of the first set, and the next two columns (2 and 3) comprise the second set. The first set contains about 100 entries and the second set contains about 75. I want to plot both sets onto the same graph. I've loaded the csv file into Python using numpy.loadtxt.

The first set loads without any issue, but when I try to load the second set I get this error:

ValueError: could not convert string to float: ' '

After several hours of frustration, I finally determined that Python is reading blank csv cells in the second data set so that it will be equal with the first data set. This is literally the dumbest reason for anything I've ever heard of, because it is reading blank cells and expecting them to contain information, and then complains that there is no information.

Why is Python reading blank cells and how can I make it stop doing that?

To recreate the error, fill two columns with data and the next two with less data. If your first two columns have 10 entries, the next two have no more than 9. Save as a csv file and run the following code:

import numpy as np
import matplotlib.pyplot as plt

lat1 = np.loadtxt(r'C:\Users\XXXXX\Desktop\latlong.csv', usecols=0, delimiter=',')
long1 = np.loadtxt(r'C:\Users\XXXXX\Desktop\latlong.csv', usecols=1, delimiter=',')
lat2 = np.loadtxt(r'C:\Users\XXXXX\Desktop\latlong.csv', usecols=2, delimiter=',')
long2 = np.loadtxt(r'C:\Users\XXXXX\Desktop\latlong.csv', usecols=3, delimiter=',')

plt.plot(lat1, long1, 'o')
plt.plot(lat2, long2, '+')

You will get this error:

Traceback (most recent call last):

  File "C:\Users\XXXXX\AppData\Local\Programs\Spyder\pkgs\spyder_kernels\py3compat.py", line 356, in compat_exec
    exec(code, globals, locals)

  File "c:\users\XXXXX\XXXXX\XXXXX\XXXXX\XXXXX\untitled0.py", line 11, in <module>
    lat1 = np.loadtxt(r'C:\Users\XXXXX\Desktop\latlong.csv', usecols=0, delimiter=',')

  File "C:\Users\XXXXX\AppData\Local\Programs\Spyder\pkgs\numpy\lib\npyio.py", line 1163, in loadtxt
    chunk.append(packer(convert_row(words)))

  File "C:\Users\XXXXX\AppData\Local\Programs\Spyder\pkgs\numpy\lib\npyio.py", line 1142, in convert_row
    return [*map(_conv, vals)]

  File "C:\Users\XXXXX\AppData\Local\Programs\Spyder\pkgs\numpy\lib\npyio.py", line 725, in _floatconv
    return float(x)  # The fastest path.

ValueError: could not convert string to float: ''

Here is a sample of the data I'm trying to use:

41.878   -93.0977   43.78444   -88.7879
44.062   -114.742   38.58763   -80.4549
40.63313  -89.3985  43.07597   -107.29
40.55122  -85.6024
39.0119   -98.4842

The first two columns have two more lat/long entries than the second two columns. For reasons unknown to me, Python is reading an extra 4 blank cells in the second two columns, and then tries to use the blank cells as plotting data, giving me a ValueError. Why is Python reading blank data and trying to plot it? What is the solution to this?

Maybe Python is reading blank cells because the file contains them? — mkrieger1, Aug 24 '22 at 22:16
May we please have a sample of the file, as well as the expected output. (Please update the question and include the sample as text, not as an image) — S3DEV, Aug 24 '22 at 22:18
Please provide enough code so others can better understand or reproduce the problem. — Community, Aug 24 '22 at 22:20
Somewhere in your numpy function, you've defined the schema of the columns... CSV files don't naturally have column types, so if you get "empty strings", then you need to have a function to post-process those after reading the file. See `dtype, default: float` and `converters` - https://numpy.org/doc/stable/reference/generated/numpy.loadtxt.html — OneCricketeer, Aug 24 '22 at 22:28
`genfromtxt` is bit better at loading missing data. `pandas` even better. But, I agree with others, we need to see a sample of the file to have much chance of helping. Oh, and show the full error traceback if possible. `loadtxt` loads columns as float by default (at least that's what the docs say). — hpaulj, Aug 24 '22 at 22:40
Unfortunately I cannot show the full traceback. It's too long by 265 characters. And if I could attach my csv file, I would, but there is no option to attach anything. — cat_herder, Aug 25 '22 at 00:36
"63.588753 -154.493062 43.804133 -120.554201 32.318231 -86.902298 41.203322 -77.194525 35.20105 -91.831833 18.220833 -66.590149 40.551217 -85.602364 39.011902 -98.484246" — cat_herder, Aug 25 '22 at 00:38
I just tried pasting a sample of the file as four columns of lat long data, but it doesn't display correctly. — cat_herder, Aug 25 '22 at 00:39
Edit the question to add the sample. We aren't interested in the actual numbers so much as the layout, delimiters and spacing. We need to see where are these 'blanks' — hpaulj, Aug 25 '22 at 00:54
I just edited the question. I'm new to this forum, so I'm still figuring out how it works. — cat_herder, Aug 25 '22 at 01:00
If the file is available online, add a link to it. If not you could use a service such as [0bin](https://0bin.net/) or pastebin to upload the file and post a link to that here — Martin Evans, Aug 25 '22 at 10:54
Pay close attention to the error traceback. It's having problem reading the first column. It hasn't even gotten to the short columns, much less the plotting. The sample does not use comma as delimiter. — hpaulj, Aug 25 '22 at 15:15
@cat_herder you could pre-parse the rows to ensure they they the correct number of values. I have added a possible workaround for you to try — Martin Evans, Aug 30 '22 at 09:11

score 0 · Answer 1 · answered Aug 24 '22 at 22:42

From the docs numpy.loadtxt

This function aims to be a fast reader for simply formatted files. The genfromtxt function provides more sophisticated handling of, e.g., lines with missing values.

A command along the lines of

np.genfromtxt('test.csv', delimiter=',', filling_values=np.nan)

should replace the empty values with nan. You could choose something else like 0.0 instead.

In conclusion: Not dumb.

Martin Evans · Accepted Answer · 2022-08-30T09:10:46.393

I suggest you read the whole file in at once rather than trying to load individual columns. As mentioned, np.genfromtxt() can be used to better handle rows with empty values.

The two sets of data could be plotted as follows:

import matplotlib.pyplot as plt
import numpy as np

latlong = np.genfromtxt('latlong.csv', delimiter=',', filling_values=np.nan)

plt.plot(latlong[:,0], latlong[:,1], 'ro')
plt.plot(latlong[:,2], latlong[:,3], 'g+')
plt.show()

For your sample data this would give you:

If the data does not have commas delimiting it, you could pre-parse the data using a Python CSV reader to ensure each row contains the correct number of values. These could then be loaded directly into a numpy array and converted to floats as follows:

import matplotlib.pyplot as plt
import numpy as np
import csv

data = []

with open('latlong.csv') as f_input:
    for row in csv.reader(f_input, delimiter=' ', skipinitialspace=True):
        row.extend([''] * (4 - len(row)))       # pad any short rows
        data.append(row)
        
latlong = np.array(data)
latlong[latlong == ''] = np.nan     # convert empty values into nan
latlong = latlong.astype(float)     # convert strings to floats

plt.plot(latlong[:,0], latlong[:,1], 'ro')
plt.plot(latlong[:,2], latlong[:,3], 'g+')
plt.show()

hpaulj · Answer 3 · 2022-08-25T15:18:06.617

With your sample:

In [1]: txt='''41.878   -93.0977   43.78444   -88.7879
   ...: 44.062   -114.742   38.58763   -80.4549
   ...: 40.63313  -89.3985  43.07597   -107.29
   ...: 40.55122  -85.6024
   ...: 39.0119   -98.4842'''

I get an error on the lat1 line, as you do, but for a different string:

In [2]: lat1 = np.loadtxt(txt.splitlines(), usecols=0, delimiter=',')
Traceback (most recent call last):
  Input In [2] in <cell line: 1>
    lat1 = np.loadtxt(txt.splitlines(), usecols=0, delimiter=',')
  File /usr/local/lib/python3.10/dist-packages/numpy/lib/npyio.py:1313 in loadtxt
    arr = _read(fname, dtype=dtype, comment=comment, delimiter=delimiter,
  File /usr/local/lib/python3.10/dist-packages/numpy/lib/npyio.py:979 in _read
    arr = _load_from_filelike(
ValueError: could not convert string '41.878   -93.0977   43.78444   -88.7879' to float64 at row 0, column 1.

There is a comma delimiter, so it tries to convert the whole line to float, with an obvious failure.

Letting it use the default white space delimiter:

In [3]: lat1 = np.loadtxt(txt.splitlines(), usecols=0)
In [4]: lat1
Out[4]: array([41.878  , 44.062  , 40.63313, 40.55122, 39.0119 ])
In [5]: np.loadtxt(txt.splitlines(), usecols=1)
Out[5]: array([ -93.0977, -114.742 ,  -89.3985,  -85.6024,  -98.4842])

But it fails when we ask it to load column 2 on the shorter lines:

In [6]: np.loadtxt(txt.splitlines(), usecols=2)
Traceback (most recent call last):
  Input In [6] in <cell line: 1>
    np.loadtxt(txt.splitlines(), usecols=2)
  File /usr/local/lib/python3.10/dist-packages/numpy/lib/npyio.py:1313 in loadtxt
    arr = _read(fname, dtype=dtype, comment=comment, delimiter=delimiter,
  File /usr/local/lib/python3.10/dist-packages/numpy/lib/npyio.py:979 in _read
    arr = _load_from_filelike(
ValueError: invalid column index 2 at row 4 with 2 columns

Trying to read the whole sample, both loadtxt and genfromtxt have problems with the short lines:

In [7]: np.loadtxt(txt.splitlines())
Traceback (most recent call last):
  Input In [7] in <cell line: 1>
    np.loadtxt(txt.splitlines())
  File /usr/local/lib/python3.10/dist-packages/numpy/lib/npyio.py:1313 in loadtxt
    arr = _read(fname, dtype=dtype, comment=comment, delimiter=delimiter,
  File /usr/local/lib/python3.10/dist-packages/numpy/lib/npyio.py:979 in _read
    arr = _load_from_filelike(
ValueError: the number of columns changed from 4 to 2 at row 4; use `usecols` to select a subset and avoid this error

In [8]: np.genfromtxt(txt.splitlines())
Traceback (most recent call last):
  Input In [8] in <cell line: 1>
    np.genfromtxt(txt.splitlines())
  File /usr/local/lib/python3.10/dist-packages/numpy/lib/npyio.py:2266 in genfromtxt
    raise ValueError(errmsg)
ValueError: Some errors were detected !
    Line #4 (got 2 columns instead of 4)
    Line #5 (got 2 columns instead of 4)

(I'm using the latest numpy version, which has a reword loadtxt, so its error messages may be different.)

Changing the text to use comma, including markers for the missing fields, genfromtxt works fine:

In [9]: txt='''41.878,   -93.0977,   43.78444,   -88.7879
   ...: 44.062 ,  -114.742 ,  38.58763 ,  -80.4549
   ...: 40.63313 , -89.3985,  43.07597,   -107.29
   ...: 40.55122 , -85.6024,,
   ...: 39.0119  , -98.4842,,'''
In [12]: np.genfromtxt(txt.splitlines(), delimiter=',')
Out[12]: 
array([[  41.878  ,  -93.0977 ,   43.78444,  -88.7879 ],
       [  44.062  , -114.742  ,   38.58763,  -80.4549 ],
       [  40.63313,  -89.3985 ,   43.07597, -107.29   ],
       [  40.55122,  -85.6024 ,        nan,        nan],
       [  39.0119 ,  -98.4842 ,        nan,        nan]])

Handle blank/empty CSV cells

3 Answers3

Linked