I have a large table with 80 columns and 10 million records already in a postgres database. Most of the data is really numbers there are some NAs where an external process encountered blanks in the dataset. I want to convert the data table to numeric format and to do that I need to get rid of the NA values, any suggestions on the transformer and whether I need to apply this to all fields one at time? I was thinking of using the Attribute Calculator.
Page 1 / 1
If "NA" means the Null, you can read only records except ones containing one or more NA values, with setting the "WHERE Clause" parameter in the POSTGRES reader feature type properties, like this.
field1 is not null and field2 is not null and field3 is not null ...This is efficient but there are 80 columns, so it would be troublesome to write the WHERE clause.
As a workaround, the PythonCaller with this script may be helpful.
def processFeature(feature):
for attr in feature.getAllAttributeNames():
isNull, isMissing, type = feature.getAttributeNullMissingAndType(attr)
if isNull:
feature.setAttribute('_flag', 0)
break
Expose "_flag" in the PythoCaller parameters dialog. Then, you can select valid features which don't have a value in the "_flag" attribute ("Attribute Is Missing" operator can be used in a Tester).
In FME 2016, a new transformer called AttributeValidator will be added, and it can be used in this case. Coming soon!
If "NA" means the Null, you can read only records except ones containing one or more NA values, with setting the "WHERE Clause" parameter in the POSTGRES reader feature type properties, like this.
field1 is not null and field2 is not null and field3 is not null ...This is efficient but there are 80 columns, so it would be troublesome to write the WHERE clause.
As a workaround, the PythonCaller with this script may be helpful.
def processFeature(feature):
for attr in feature.getAllAttributeNames():
isNull, isMissing, type = feature.getAttributeNullMissingAndType(attr)
if isNull:
feature.setAttribute('_flag', 0)
break
Expose "_flag" in the PythoCaller parameters dialog. Then, you can select valid features which don't have a value in the "_flag" attribute ("Attribute Is Missing" operator can be used in a Tester).
In FME 2016, a new transformer called AttributeValidator will be added, and it can be used in this case. Coming soon!
If it is possible, I would prefer the SQL-statement since it will filter the amount of features loaded into memory and therefore speed up your proces.
You can create a simple model to run only once and create the necessary SQL-statement that you can use in your 'real' model. You can do this by using the methodology described by takashi and then concatenate all the flagged features into one single SQLstring.
I think the NullAttributeMapper is what you are looking for.
This transformer allows you to replace Null values for multiple attribute at one go.
Below a screenshot of the settings:
