topic Re: replace null values with 0 in Geoprocessing Questions

replace null values with 0

JulianeStruve1 — Fri, 27 Dec 2013 18:42:33 GMT

I am trying to replace <Null> values with 0. I have selected these rows by attribute [field] "is null" but can't seem to find a way to actually replace the entries.

Can someone tell me how I can replace the values ? I can't just remove the rows, I need the 0 information in them for a field calculation.

Thanks a lot !

Re: replace null values with 0

TimWitt — Fri, 27 Dec 2013 18:59:39 GMT

Did you try and calculate them via the field calculator?

Right-click the column heading and click "Field Calculator..." and in the box write "0" (without the quotation marks).

Re: replace null values with 0

JulianeStruve1 — Fri, 27 Dec 2013 19:26:45 GMT

Did you try and calculate them via the field calculator?

Right-click the column heading and click "Field Calculator..." and in the box write "0" (without the quotation marks).

I did. The field calculator is grayed when I try.

Re: replace null values with 0

RichardFairhurst — Fri, 27 Dec 2013 20:18:21 GMT

I did. The field calculator is grayed when I try.

if you are working on a joined feature class/table you cannot calculate values into the joined table or create records to replace the Null values without first exporting the joined data to a new feature class/table. If that is not applicable what is the data source location and type? How was the data created? Did you create it or are you just trying to access it?

Re: replace null values with 0

JulianeStruve1 — Fri, 27 Dec 2013 20:56:33 GMT

The data were joined and the null values are non-matching records.

Re: replace null values with 0

RichardFairhurst — Sun, 12 Dec 2021 01:52:38 GMT

The data were joined and the null values are non-matching records.

There is no record existing to calculate a value into with a non-matching record join, which is why you cannot calculate a value into those (non-existent) records no matter what you might want. The Field Calculator never creates records, it only calculates to existing records. In any case, joins do not allow calculations ever on the joined table, since joins do not exist as real feature classes or tables except in memory which cannot be altered directly with a calculation. (because these joined fields only exist in memory the calculation has no where it can store the value of the calculation to disk and joins do not know if they are in reality based on a 1-1, M-1, 1-M, or M-M relationship). You would have to calculate the value into the original joined table directly to fill in a Null value in any real records.

However, in this case you can't even calculate to the original table, since those records do not even exist for you to calculate anything into. You have to create these records somehow. Either you have to insert the unmatched join records into the original join data table so that they are no longer unmatched (and there is no easy way to do that) or you have to export the joined table to create a new table with all of the fields in real no longer joined records so that you can now calculate to the newly created Null fields in an actual table. So use Copy, Feature Class to Feature Class, Copy Features or some similar tool to create a new table from the joined table.

Either that or create a calculation that uses the Null values they are with a code block calculation. Something like:

Parser: VB Script

Use Code Block: Checked

Pre-Logic Script Code

If IsNull([JoinedObjectIDField]) Then
  Output = "SomeValueWhenRecordsAreUnmatched"
Else
  Output = [OriginalValueInOutputField]
End If

Expression: Output

If the ObjectID field of the Joined table in Null the record is unmatched by definition and with the conditional calculation it does not matter if it has a value in it or not since you can process unmatched records differently from the way you process matched records.

Re: replace null values with 0

isabelapi — Tue, 06 Jun 2023 06:53:58 GMT

This is a very old thread, but the fact that I found this in 2023 means that maybe someone else will also find it now. So, the way to do this on field calculator is:

def rmnull(x):
    if x is None:
        return 0
    else:
        return x

Hopefully, this helps someone else 🙂