Set a Calculated Field Programmatically

In this example, you will learn how you can use DataPipeline to add a calculated field to any dataset using logic compiled into your application.  This allows you to incorporate custom calculations and logic into your data processing workflows.  You can perform calculations based on existing data fields or other data in your application and add the results as a new field in the dataset.

Users in the finance domain can leverage this example to calculate interest rates, risk metrics, or scenario analysis based on existing data fields. 

This example uses a custom Transformer class with the logic implemented in its transform() method. Another option using the SetCalculatedField class can be found in the Set a Calculated Field at Runtime example.

Input CSV file

Account,LastName,FirstName,Balance,CreditLimit,AccountCreated,Rating
101,Reeves,Keanu,9315.45,10000.00,1/17/1998,A
312,Butler,Gerard,90.00,1000.00,8/6/2003,B
868,Hewitt,Jennifer Love,0,17000.00,5/25/1985,B
761,Pinkett-Smith,Jada,49654.87,100000.00,12/5/2006,A
317,Murray,Bill,789.65,5000.00,2/5/2007,C

Java Code Listing

package com.northconcepts.datapipeline.examples.cookbook;

import java.io.File;

import com.northconcepts.datapipeline.core.DataReader;
import com.northconcepts.datapipeline.core.Field;
import com.northconcepts.datapipeline.core.Record;
import com.northconcepts.datapipeline.core.StreamWriter;
import com.northconcepts.datapipeline.csv.CSVReader;
import com.northconcepts.datapipeline.job.Job;
import com.northconcepts.datapipeline.transform.Transformer;
import com.northconcepts.datapipeline.transform.TransformingReader;

public class SetACalculatedFieldProgrammatically {
    
    public static void main(String[] args) throws Throwable {
        DataReader reader = new CSVReader(new File("example/data/input/credit-balance-01.csv"))
            .setFieldNamesInFirstRow(true);
        
        TransformingReader transformingReader = new TransformingReader(reader);
        
        transformingReader.add(new Transformer() {
            public boolean transform(Record record) throws Throwable {
                // creates an 'AvailableCredit' field
                Field availableCredit = record.getField("AvailableCredit", true);
                
                Field creditLimit = record.getField("CreditLimit");
                Field balance = record.getField("Balance");
                
                // Since CSV fields are strings, they need to be parsed before subtraction
                availableCredit.setValue(
                        Double.parseDouble(creditLimit.getValueAsString()) -
                        Double.parseDouble(balance.getValueAsString())
                );
                
                return true;
            }
        });
        
        Job.run(transformingReader, new StreamWriter(System.out));
    }
    
}

Code walkthrough

1. CSVReader is created corresponding to the input file credit-balance-01.csv.
2. The CSVReader.setFieldNamesInFirstRow(true) method is invoked to specify that the names specified in the first row should be used as field names.
3. A TransformingReader is created to apply one or more changes to the incoming data sequentially.
4. Transformer object is added to hold the transformation logic. This class is an abstract class and transform() abstract method is implemented in the example.
5. A new field called "AvailableCredit" is created in the dataset.
6. String values in the "CreditLimit" and "Balance" fields are first parsed to double type and then subtracted from each other. The result is set to the value of the "AvailableCredit" field.
7.  Data is transferred from the transformingReader to the StreamWriter(System.out) via Job.run() method.

Output

-----------------------------------------------
0 - Record (MODIFIED) {
    0:[Account]:STRING=[101]:String
    1:[LastName]:STRING=[Reeves]:String
    2:[FirstName]:STRING=[Keanu]:String
    3:[Balance]:STRING=[9315.45]:String
    4:[CreditLimit]:STRING=[10000.00]:String
    5:[AccountCreated]:STRING=[1/17/1998]:String
    6:[Rating]:STRING=[A]:String
    7:[AvailableCredit]:DOUBLE=[684.5499999999993]:Double
}

-----------------------------------------------
1 - Record (MODIFIED) {
    0:[Account]:STRING=[312]:String
    1:[LastName]:STRING=[Butler]:String
    2:[FirstName]:STRING=[Gerard]:String
    3:[Balance]:STRING=[90.00]:String
    4:[CreditLimit]:STRING=[1000.00]:String
    5:[AccountCreated]:STRING=[8/6/2003]:String
    6:[Rating]:STRING=[B]:String
    7:[AvailableCredit]:DOUBLE=[910.0]:Double
}

-----------------------------------------------
2 - Record (MODIFIED) {
    0:[Account]:STRING=[868]:String
    1:[LastName]:STRING=[Hewitt]:String
    2:[FirstName]:STRING=[Jennifer Love]:String
    3:[Balance]:STRING=[0]:String
    4:[CreditLimit]:STRING=[17000.00]:String
    5:[AccountCreated]:STRING=[5/25/1985]:String
    6:[Rating]:STRING=[B]:String
    7:[AvailableCredit]:DOUBLE=[17000.0]:Double
}

-----------------------------------------------
3 - Record (MODIFIED) {
    0:[Account]:STRING=[761]:String
    1:[LastName]:STRING=[Pinkett-Smith]:String
    2:[FirstName]:STRING=[Jada]:String
    3:[Balance]:STRING=[49654.87]:String
    4:[CreditLimit]:STRING=[100000.00]:String
    5:[AccountCreated]:STRING=[12/5/2006]:String
    6:[Rating]:STRING=[A]:String
    7:[AvailableCredit]:DOUBLE=[50345.13]:Double
}

-----------------------------------------------
4 - Record (MODIFIED) {
    0:[Account]:STRING=[317]:String
    1:[LastName]:STRING=[Murray]:String
    2:[FirstName]:STRING=[Bill]:String
    3:[Balance]:STRING=[789.65]:String
    4:[CreditLimit]:STRING=[5000.00]:String
    5:[AccountCreated]:STRING=[2/5/2007]:String
    6:[Rating]:STRING=[C]:String
    7:[AvailableCredit]:DOUBLE=[4210.35]:Double
}

-----------------------------------------------
5 records