Calculating a value from a different column and a different row using PETL

Question

I have a table that looks like this:

+---------------+------+-----------+
| EffectiveDate | TSV  | MTDReturn |
+===============+======+===========+
| '1994-01-30'  |  100 |      0.04 |
+---------------+------+-----------+
| '1995-02-28'  | None |      0.02 |
+---------------+------+-----------+
| '1992-03-31'  | None |      0.01 |
+---------------+------+-----------+
| '1974-08-30'  | None |      0.06 |
+---------------+------+-----------+

I'm using the PETL library to try and populate the TSV value. I'm able to do this by breaking the table down into a list of dicts and manipulating it, but I was asked to do it using PETL only.

The formula is pretty straight forward: TSV = previousTSV * (1 + MTDReturn)

I have looked into aggregation but there's nothing to group them by.

Perhaps I could join a table that has a column with previousTSV?

Would you include your source code and some input data (if it's large use github) so we can have a tinker please? — Stefan Collier, Jun 14 '18 at 16:01
@Splatmistro: question should be minimal, complete & verifiable, links aren't helpful — Azat Ibrakov, Jun 14 '18 at 16:45

swstephe · Answer 1 · 2020-10-23T12:39:42.023

0

This looks like a good fit for addfieldusingcontext. This will add the column value using a function which receives the previous row, (None for first row). I'm guessing you would use something like:

def calculate_tsv(prv, cur, nxt):
    if prv is not None:
        return prv.TSV * (1 + cur.MTDReturn)

etl.addfieldusingcontext(table1, 'TSV', calculate_tsv)

edited Oct 23 '20 at 12:39

answered Jun 18 '19 at 15:17

swstephe

1,840
11
17

Calculating a value from a different column and a different row using PETL

1 Answers1