Insert a value into a row with petl?

Question

I'm using petl and trying to figure out how to insert a value into a specific row.

I have a table that looks like this:

+----------------+---------+------------+
| Cambridge Data | IRR     | Price List |
+================+=========+============+
| '3/31/1989'    | '4.37%' |            |
+----------------+---------+------------+
| '4/30/1989'    | '5.35%' |            |
+----------------+---------+------------+

I want to set the price list to 100 on the row where Cambridge Data is 4/30/1989. This is what I have so far:

def insert_initial_price(self, table):
    import petl as etl
    initial_price_row = etl.select(table, 'Cambridge Data', lambda v: v == '3/31/1989')

That selects the row I need to insert 100 into, but i'm unsure how to insert it. petl doesn't seem to have an "insert value" function.

I think you might need to insert the data before transforming it with petl. — Ywapom, Jun 06 '18 at 20:01
`petl` seems to work on immutable data, you can add data more easily with `pandas` — Evgeny, Jun 06 '18 at 20:03
Oh really? Interesting, I basically need to add a new column to this table and produce a set of list prices by calculating each previous value * the IRR. Is that not doable with petl? — , Jun 06 '18 at 20:06
You can use .update on the row, however this will only return the data and you have to "somehow" update the table with those values. As others said, pandas or even a pure python iteration may be easier to operate, depends on your data source. initial_price_row.update('Price List', 100), however this will only return the row with the updated values, it will NOT update your table without further actions I do not really know from looking at petl for a short time. — d parolin, Jun 06 '18 at 20:15
Gotcha, thanks for replying. I'm actually going to populate the field itself with petl and then use `convert` and update the value with a lambda holding a conditional. — , Jun 06 '18 at 20:26

score 0 · Answer 1 · answered May 01 '20 at 18:23

I would advice not to use select.

To update the value of a field use convert.

See the docs with many examples: https://petl.readthedocs.io/en/stable/transform.html#petl.transform.conversions.convert

I have not tested it, but this should solve it:

import petl as etl
table2 = etl.convert(
    table,
    'Price List',
    100,
    where = lambda rec: rec["Cambridge Data"] == '4/30/1989',
)

Insert a value into a row with petl?

1 Answers1