Data360 Analyze

 View Only

  • 1.  Date to date delta count for time series

    Posted 10-10-2023 05:38

    Hello, assume i have 3 dates and a count column per each date, is there a convenient way to calculate the delta for each 2 adjacent dates? starting from the oldest date:

    (In reality it will be around 30-60 dates)

    What i have:

         |Date|              |count|

    2023-01-02            1

    2023-01-03            8

    2023-01-04            9

    What i want:

     |Date|              |count|           |Delta|

    2023-01-02            1               NaN

    2023-01-03            8                 7

    2023-01-04            9                 1

    Best regards

    Henrik



    ------------------------------
    Henrik B
    E.ON Sverige
    ------------------------------


  • 2.  RE: Date to date delta count for time series

    Posted 10-11-2023 02:15

    I found that one solution is, solving the logic with SQL and then extracting that to a D360 Workflow.



    ------------------------------
    Henrik B
    E.ON Sverige
    ------------------------------



  • 3.  RE: Date to date delta count for time series

    Employee
    Posted 10-11-2023 07:29
    Edited by Adrian Williams 10-11-2023 07:38

    You can use a Python script in a transform node. The data should first be sorted by Date using a Sort node.

    The Transform node code is shown below:

    And the corresponding test data and results are as below:

    The details of the example scripts are as follows:

    ####ConfigureFields Script

    #Configure all fields from input 'in1' to be mapped
    #to the corresponding fields on the output 'out1'
    out1 += in1

    out1.Delta = int

    #### End ConfigureFields Script

    #### ProcessRecords Script

    #Copy all fields from input 'in1' to the corresponding output fields
    #in output 'out1'. Copies the values of the fields that have been setup
    #in the mapping defined in the ConfigureFields property
    out1 += in1

    if node.firstExec:
        LastVal = fields.count
        out1.Delta = Null
    else:
        if fields.count is Null:
            LastVal = 0
            out1.Delta = Null
        else:
            Diff = fields.count - LastVal
            out1.Delta = Diff
            LastVal = fields.count

    #### End ProcessRecords Script



    ------------------------------
    Adrian Williams
    Precisely Software Inc.
    ------------------------------