How to convert values in table using petl

Question

I am having a weird issue with converting multiple values in a table.

I have a table of data like so:

+-------------+--------------+-------------+
| id          | name         | category_id |
+=============+==============+=============+
|         1   |       Horse  | 5           |
+-------------+--------------+-------------+
|         2   |        Cow   | 5           |
+-------------+--------------+-------------+
|         3   |        Pig   | 2           |
+-------------+--------------+-------------+
|         4   |     Chicken  | 3           |
+-------------+--------------+-------------+

I am then finding by category_id like so:

# find the item category id
items_cat_id = etl.values(table, 'category_id')

I then loop over the data like so so I can convert the category_id from the above to the target category id:

    for item in items_cat_id:
        """ 
        fetch target mongo collection. 
        source_name is the category name to look up in the target, 
        if we get a match convert the table category_id value to 
        the mongo id.
        """
        target_category_id = target_db.collection.find_one({ 'name': source_name })

        converted_table = etl.convert(table, 'category_id', 
            lambda _: target_category_id.get('_id'), 
            where=lambda x: x.category_id == item)

I seem to be only getting this:

+-------------+--------------+-----------------------------+
| id          | name         | category_id                 |
+=============+==============+=============================+  
|         1   |       Horse  | 5                           |
+-------------+--------------+-----------------------------+
|         2   |        Cow   | 5                           |
+-------------+--------------+-----------------------------+
|         3   |        Pig   | 2                           |
+-------------+--------------+-----------------------------+
|         4   |     Chicken  | QnicP3f4njL54HRqu           |
+-------------+--------------+-----------------------------+

when it should be

+-------------+--------------+-----------------------------+
| id          | name         | category_id                 |
+=============+==============+=============================+
|         1   |       Horse  | 5                           |
+-------------+--------------+-----------------------------+
|         2   |        Cow   | 5                           |
+-------------+--------------+-----------------------------+
|         3   |        Pig   | yrDku5Yqkc2MKZZkD           |
+-------------+--------------+-----------------------------+
|         4   |     Chicken  | QnicP3f4njL54HRqu           |
+-------------+--------------+-----------------------------+

Any suggestions?

score 1 · Accepted Answer · answered Dec 19 '19 at 20:55

1

etl.convert() creates a new table with every iteration over items_cat_id from the same unchanged source table. Therefore only one row is changed in every result. Change your code like so:

for x in y:
   table = etl.convert(table, ...)

Now you're always working with the last result.

answered Dec 19 '19 at 20:55

timo.rieber

3,727
3
32
47

Yep, this was asked a while ago but I found out that convert creates a new table each time :) – jeffci Dec 20 '19 at 21:17

How to convert values in table using petl

1 Answers1