Populate next field in table with previous data if prior field is empty

Question

I have a CSV communicating directly with a series of points and lines in an application I'm developing out of QGIS 2.8. The CSV is a bit problematic, however. The hardware that provides the CSV data only provides location data every few rows. That is, there is an attribute table with designated X and Y columns whose fields are only filled only at key times. The rest is other still useful data, but location is only updated intermittently.

I have my point feature structured so that only the bottom-most (most recent) attribute row is used to display one point. Naturally, I need location data for that bottom-most row, otherwise the point disappears. Currently I'm not getting it but sporadically. My CSV -> point code looks like this currently (pyshp library used):

        cur_dir = os.path.dirname(os.path.realpath(__file__))

        file_location = os.path.join(cur_dir, "data", "mycsv.csv")
        out_file = os.path.join(cur_dir, "data", "mypoint.shp")
        idd, az, y, x = [], [], [], []

        with open(file_location, 'rb') as csvfile:
            r = list(csv.reader(csvfile, delimiter=','))
            reader = r[len(r) - 1:] #read only top row
            for i, row in reversed(list(enumerate(reader))): #reverse table
                idd.append(str(row[0]))
                az.append(float(row[2]))
                y.append(float(row[6]))
                x.append(float(row[7]))
                print "appended"


        w = shapefile.Writer(shapefile.POINT)
        w.field('ID', 'N')
        w.field('AZIMUTH', 'N', 12)
        w.field('Y', 'F', 10, 8)
        w.field('X', 'F', 10, 8)

        for j, k in enumerate(x): #write shapefile
            w.point(k, y[j])
            w.record(idd[j],az[j],y[j], k )
            print "recorded"

        w.save(out_file)

Here is a snippet of the CSV. You will see the chopped up columns on the right: https://i.stack.imgur.com/ULZOS.png

I have started by trying to add if x > 0: conditionals in various places below the multiple enumerators to possibly halt digitization until data is read, but I am given error min() arg is an empty sequence each time. I'm not even sure this is the right place to begin, as it would probably just put attributes out of sequence with each other. I really just need some sort of "if X or Y field is null, populate field with prior field's data" script.

I am using Python 2.7 and PyQt4, developing out of the QGIS 2.8 API, and running on Ubuntu 14.04,

score 1 · Accepted Answer · answered Oct 30 '15 at 17:01

1

You could cache the last valid values of the X and Y data points, which I'm assuming are in row[6] and row[7]

    with open(file_location, 'rb') as csvfile:
        r = list(csv.reader(csvfile, delimiter=','))
        reader = r[len(r) - 1:] #read only top row
        lastX = 0.
        lastY = 0.
        for i, row in reversed(list(enumerate(reader))): #reverse table
            idd.append(str(row[0]))
            az.append(float(row[2]))
            try:
                y.append(float(row[6]))
                lastY = float(row[6]))
            except (TypeError, ValueError) as e:
                if len(y) > 0:
                    y.append(lastY)

            try:
                x.append(float(row[7]))
                lastX = float(row[7]))
            except (TypeError, ValueError) as e:
                if len(x) > 0:
                    x.append(lastX)

            print "appended"

I'm assuming that the missing CSV values come through as empty strings or None. Both those values will raise an Exception when casting to float(). If the values are coming as 0, then ask the original dev to make the values empty because 0 could be a real coordinate instead of a missing coordinate.

If X and Y always come through at the same time (if you have one, you always have the other) then you could use a single try/except block to handle all cases.

answered Oct 30 '15 at 17:01

ryanday

2,506
18
25

Strangely, it throws `ERROR 1: Error in fread() reading object of size 28 at offset 100 from .shp file` when the empty field is noticed. Any idea what may be causing this? I tweaked it a little and set the `if`s to `if len(x) == 0:` to see if nothing was going to the `lastX`/`lastY` variables, and while the error disappeared, the coordinates were `0,0` every time. Is the append to lastX/lastY getting reverted to 0 each time the script runs? – Alex92 Oct 30 '15 at 19:05
Try removing the if statements all together then. I was trying to cut out the initial bad values by not appending anything unless a valid value had already been found. – ryanday Oct 30 '15 at 20:07
So, regardless of the if statements, I think there is some other issue going on, because I'm almost positive I've set the code to cache properly, but whenever it runs, I get that `ERROR 1: Error in fread() reading object of size 28 at offset 100 from .shp file` error each time. Either the coordinates default to 0,0 when `lastX` and `lastY` variables are set to `0.` like you wrote, or the above error throws when they are set to `[]`. My intuition is that setting them to `[]` is the right move, but this `fread()` function error I've never encountered throws instead. – Alex92 Nov 02 '15 at 16:20
I figured it out. I removed the if statements and made `lastX` and `lastY` class attributes at the beginning of my code. My best guess is that they originally weren't ever caching when they were local variables. If the fields were empty, the `try` would skip to the `except`, thus appending `0` no matter what. Thanks for pointing me in the right direction though! – Alex92 Nov 03 '15 at 16:53

Populate next field in table with previous data if prior field is empty

1 Answers1