Pandas按组中所有值的总和与另一列以逗号分隔 [英] Pandas Group by sum of all the values of the group and another column as comma separated

问题描述

我想按一列（标签）分组并总结相应的数量（数量）。相关参考号列应以逗号分隔。

I want to group by one column (tag) and sum up the corresponding quantites (qty). The related reference no. column should be separated by commas

import pandas as pd

tag = ['PO_001045M100960','PO_001045M100960','PO_001045MSP2526','PO_001045M870191', 'PO_001045M870191', 'PO_001045M870191']
reference= ['PA_000003', 'PA_000005', 'PA_000001', 'PA_000002', 'PA_000004', 'PA_000009']
qty=[4,2,2,1,1,1]

df = pd.DataFrame({'tag' : tag, 'reference':reference, 'qty':qty})

      tag           reference   qty
PO_001045M100960    PA_000003   4
PO_001045M100960    PA_000005   2
PO_001045MSP2526    PA_000001   2
PO_001045M870191    PA_000002   1
PO_001045M870191    PA_000004   1
PO_001045M870191    PA_000009   1

如果我使用df.groupby（'tag'）['qty' ] .sum（）。reset_index（），得到以下结果。

If I use df.groupby('tag')['qty'].sum().reset_index(), I am getting the following result.

         tag           qty
ASL_PO_000001045M100960 6
ASL_PO_000001045M870191 3
ASL_PO_000001045MSP2526 2

我需要附加一列，其参考编号为。在相应的标签下添加，例如，

I need an additional column where the reference no. are added under the respective tags like,

         tag           qty     refrence
ASL_PO_000001045M100960 6      PA_000003, PA_000005
ASL_PO_000001045M870191 3      PA_000002, PA_000004, PA_000009
ASL_PO_000001045MSP2526 2      PA_000001

我该如何实现？

谢谢。

推荐答案

使用 pandas.DataFrame。 groupby.agg ：

df.groupby('tag').agg({'qty': 'sum', 'reference': ', '.join})

输出：

                                        reference  qty
tag                                                   
PO_001045M100960             PA_000003, PA_000005    6
PO_001045M870191  PA_000002, PA_000004, PA_000009    3
PO_001045MSP2526                        PA_000001    2

注意：如果引用列为数字，' ，‘。join 将不起作用。在这种情况下，请使用 lambda x：’，’.join（str（i）for i in x）

Note: if reference column is numeric, ', '.join will not work. In such case, use lambda x: ', '.join(str(i) for i in x)

这篇关于Pandas按组中所有值的总和与另一列以逗号分隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！