如何使用petl转换表中的值

Question

在表中转换多个值时，我遇到了一个奇怪的问题。

我有一个数据的table，如下所示：

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

然后我发现category_id是这样的：

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

然后像这样循环数据，以便将上面的category_id转换为目标类别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)

我似乎只得到了这个：

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

当它应该是

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

有什么建议吗？

Answer 1

etl.convert()在items_cat_id上创建一个新表，每次迭代都来自同一个未更改的源table。因此，在每个结果中只更改一行。修改代码如下：

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

现在你总是在处理最后的结果。