NZFunApply

This corresponds to use-case 1. Applying a custom data transformation on each and every record of the database table.

Applying functions on each and every row of the table data

Sample scenario: convert the MAXTEMP column in the weather table from Celsius to Fahrenheit.

The user function that you want to run can assume two parameters by default, self (which represents the AE context) and x (which represents the row of the table). You can use x to operate on the columns you’d like to. In the example, the third column (x[3]) was retrieved and processed further to generate a new value. For the columns that you would want to generate as output, build them as a list and use self.output to populate the result. Alternatively, if you have only a single result, you can directly send it to self.output.

from nzpyida import IdaDataBase, IdaDataFrame
from nzpyida.ae import  NZFunApply

idadb = IdaDataBase('weather', 'admin', 'password', verbose=True)

idadf = IdaDataFrame(idadb, 'WEATHER')


def apply_fun(self, x):
            from math import sqrt
            max_temp = x[3]
            id = x[24]
            fahren_max_temp = (max_temp*1.8)+32
            row = [id, max_temp,  fahren_max_temp]
            self.output(row)

output_signature = {'ID': 'int', 'MAX_TEMP': 'float', 'FAHREN_MAX_TEMP': 'float'}
nz_apply = NZFunApply(df=idadf, fun_ref = code_str_apply, output_table="temp_conversion",output_signature=output_signature, merge_output_with_df=True)
result = nz_apply.get_result()
print(result)

In Notebook environments, where you cannot send the function as reference, wrap your function in quotes and assign it to a string variable. If your function code is being sent as a string, you have to mention the function name in the NZApply call.

Note: To generate indented AE code for the backend SQL function, you need to have the function name immediately after the quotes but not in the next line.

Example:

from nzpyida import IdaDataBase, IdaDataFrame
from nzpyida.ae import  NZFunApply

idadb = IdaDataBase('weather', 'admin', 'password', verbose=True)

idadf = IdaDataFrame(idadb, 'WEATHER')

code_str_apply = """def apply_fun(self, x):
    from math import sqrt
    max_temp = x[3]
    id = x[24]
    fahren_max_temp = (max_temp*1.8)+32
    row = [id, max_temp,  fahren_max_temp]
    self.output(row)
    """
output_signature = {'ID': 'int', 'MAX_TEMP': 'float', 'FAHREN_MAX_TEMP': 'float'}
nz_apply = NZFunApply(df=idadf, code_str=code_str_apply, fun_name='apply_fun', output_table="temp_conversion",output_signature=output_signature, merge_output_with_df=True)
result = nz_apply.get_result()
print(result)

Expected output:

  MAX_TEMP         FAHREN_MAX_TEMP   DATE               RISK_MM        RAINTOMORROW         ID
0       27.700001        81.860001         2008-12-16  ...     0.0            No                  448014
1       33.000000        91.400002         2008-12-22  ...     0.0            No  	         448020
2       32.700001        90.860001  	2008-12-28  ...     0.0            No  		  448026
3       28.799999        83.839996  	2009-01-03  ...     0.0            No  		  448032
4       28.400000        83.120003  	2009-01-09  ...     0.0            No  		  448038
...           ...              ...         ...  ...    ...           ...     ...
142188  32.900002        91.220001  	2015-12-02  ...     0.0            No  		  589631
142189  37.099998        98.779999  	2015-12-08  ...     0.2            No  		  589637
142190  39.500000       103.099998  	2015-12-14  ...     3.8           Yes  		  589643
142191  30.299999        86.540001  	2015-12-20  ...     4.8           Yes  		  589649
142192  36.299999        97.339996  	2016-02-06  ...     0.0            No  		  589692

[142193 rows x 27 columns]