Pandas中的GROUP BY AND SUM不丢失列

问题描述

我有一个数据帧,如下所示:

--------------------------------------------------------------------
|TradeGroup | Fund Name | Contribution | From       | To           |
|  A        | Fund_1    |   0.20       | 2013-01-01 | 2013-01-02   |
|  B        | Fund_1    |   0.10       | 2013-01-01 | 2013-01-02   |
|  A        | Fund_1    |   0.05       | 2013-01-03 | 2013-01-04   |
|  B        | Fund_1    |   0.45       | 2013-01-03 | 2013-01-04   |
--------------------------------------------------------------------

基本上,这是一个行业团体每天向基金捐款。我想要做的是总结一个交易团每天的所有贡献,以供进一步分析。 我想看到的是:

--------------------------------------------------------------------
|TradeGroup | Fund Name | Contribution | From       | To           |
|  A        | Fund_1    |   0.25       | 2013-01-01 | 2013-01-04   |
|  B        | Fund_1    |   0.55       | 2013-01-01 | 2013-01-04   |
--------------------------------------------------------------------

我无法使用Dataframe解决此问题。我已经试过

df.groupby('TradeGroup')['Contribution'].sum()

但是,这不起作用。与此等效的SQL将为

Select SUM(Ctp) from Table Group By TradeGroup. 

任何帮助都将不胜感激。谢谢

sql

您需要确保贡献列是数字,而不是字符串,才能获得正确的匹配数字,就像在推荐答案中一样。我认为你收到的奇怪的"不"是因为你"投稿"栏目的字符串性质。则应执行以下操作:

import pandas as pd
import numpy as np
a=pd.DataFrame([['A','Fund_1','0.20','2013-01-01','2013-01-02'],
['B','Fund_1','0.10','2013-01-01','2013-01-02'],['A','Fund_1','0.05','2013-
01-03','2013-01-04'],['B','Fund_1','0.45','2013-01-03','2013-01-04']],
            columns=['TraderGroup', 'Fund Name','Contribution','From', 'To'])
print a
a['Contribution'] = pd.to_numeric(a['Contribution'], errors='coerce')
b=a.groupby(['TraderGroup','Fund Name']).agg({'Contribution':np.sum,
                                         'From':'min','To':'max'}).reset_index()
print b

相关文章