python - 如何使用petl转换表中的值

Question

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

我有一个数据表，如下所示:

+-------------+--------------+-------------+
| 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() 每次迭代都会从同一个未更改的源表 中创建一个新表。因此，每个结果中仅更改一行。像这样更改您的代码:

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

现在您始终使用最后的结果。