| | |
- zeroifnull(column_expression)
- DESCRIPTION:
Function converts data from null to 0 to avoid cases where a null result creates an error.
PARAMETERS:
column_expression:
Required Argument.
Specifies a ColumnExpression of a numeric column containing null values to be converted to 0.
Format for the argument: '<dataframe>.<dataframe_column>.expression'.
Notes:
1. If the type of the column/argument is not FLOAT, column values are converted to FLOAT
based on implicit type conversion rules. If an argument cannot be converted, an
error is reported. For more information on implicit type conversion,
see Teradata Vantage™ Data Types and Literals, B035-1143.
2. Unsupported column types:
a. BYTE or VARBYTE
b. LOBs (BLOB or CLOB)
c. CHARACTER or VARCHAR if the server character set is GRAPHIC
NOTE:
Function accepts positional arguments only.
EXAMPLES:
# Load the data to run the example.
>>> load_example_data("dataframe","admissions_train")
>>>
# Create a DataFrame on 'admissions_train' table.
# We will process the data to introduce some null values in the dataframe, which we will convert to 0
# using "zeroifnull()" function.
>>> df = DataFrame('admissions_train')
>>> df1 = df[df.gpa == 4].select(['id', 'stats', 'masters', 'gpa'])
>>> df2 = df[df.gpa < 2].select(['id', 'stats', 'programming', 'admitted'])
# Let's concat both df1 and df2.
>>> cdf = df1.concat(df2)
>>> cdf
stats masters gpa programming admitted
id
29 Novice yes 4.0 None NaN
24 Advanced None NaN Novice 1.0
19 Advanced None NaN Advanced 0.0
15 Advanced yes 4.0 None NaN
13 Advanced no 4.0 None NaN
>>>
# Let's persist the cdf to Vantage.
>>> copy_to_sql(cdf, "zeroifnull_test_table")
>>> newdf = DataFrame("zeroifnull_test_table")
>>> newdf
id stats masters gpa programming admitted
0 19 Advanced None NaN Advanced 0.0
1 24 Advanced None NaN Novice 1.0
2 13 Advanced no 4.0 None NaN
3 29 Novice yes 4.0 None NaN
4 15 Advanced yes 4.0 None NaN
>>>
# Import func from sqlalchemy to execute zeroifnull() function.
>>> from sqlalchemy import func
# We are converting null values in 'admitted' column to 0 using 'zeroifnull()' function.
>>> rdf = newdf.assign(admitted_zero_if_null = func.zeroifnull(newdf.admitted.expression))
>>> print(rdf)
id stats masters gpa programming admitted admitted_zero_if_null
0 29 Novice yes 4.0 None NaN 0
1 24 Advanced None NaN Novice 1.0 1
2 19 Advanced None NaN Advanced 0.0 0
3 15 Advanced yes 4.0 None NaN 0
4 13 Advanced no 4.0 None NaN 0
>>>
|