用pandas groupby求和并重命名旧列?

jia Jimmy :

如以下代码所示,我想按来对数据进行分组account_id,然后求和system_value并将其重命名为,total_value并同时保留每个日期数据。

s = [
{'account_id': '1166470734', 'entity': 'entity1', 'system_value': 10.2,  'date': "2010-01-02", 'sale': 'sale1'},
{'account_id': '1166470734', 'entity': 'entity1', 'system_value': 2.2, 'date': "2010-01-03", 'sale': 'sale1'},
{'account_id': '123232323', 'entity': 'entity2', 'system_value': 4.2,  'date': "2010-01-03", 'sale': 'sale2'},
{'account_id': '123232323', 'entity': 'entity2', 'system_value': 5.2, 'date': "2010-01-04", 'sale': 'sale2'},
{'account_id': '4342343', 'entity': 'entity3', 'system_value': 10.2,  'date': "2010-01-04", 'sale': 'sale3'},

]
import pandas as pd

df = pd.DataFrame.from_records(s)
print(df)


#    account_id   entity  system_value        date   sale
# 0  1166470734  entity1          10.2  2010-01-02  sale1
# 1  1166470734  entity1           2.2  2010-01-03  sale1
# 2   123232323  entity2           4.2  2010-01-03  sale2
# 3   123232323  entity2           5.2  2010-01-04  sale2
# 4     4342343  entity3          10.2  2010-01-04  sale3

预期输出为:


#    account_id   entity       2010-01-02   2010-01-03   2010-01-04  total_value     sale
# 0  1166470734  entity1         10.2          2.2                    12.4          sale1
# 1   123232323  entity2                       4.2         5.2        9.4           sale2
# 2     4342343  entity3                                   10.2       10.2          sale3

抱歉,我是新手,如何获得预期的结果?

根据@ Ch3steR的答案更新我的问题:

我尝试过并显示以下错误


import datetime
from decimal import Decimal
import pandas as pd

s = [

{'account_id': '21312312', 'entity': 'entityname1', 'ae': 'lwe', 'is_pc': 0, 'type': 2, 'medium': 0, 'our_side_entity': 3, 'settlement_title': 'settlementd', 'settlement_short_title': 'kim', 'settlement_type': 0, 'date': datetime.date(2020, 4, 9), 'sale': 'sale1' ,'system_value': Decimal('1038.36')},
{'account_id': '21312312', 'entity': 'entityname1', 'ae': 'lwe', 'is_pc': 0, 'type': 2, 'medium': 0, 'our_side_entity': 3, 'settlement_title': 'settlementd', 'settlement_short_title': 'kim', 'settlement_type': 0, 'date': datetime.date(2020, 4, 10), 'sale': 'sale1' ,'system_value': Decimal('1038.36')},
{'account_id': '21312312', 'entity': 'entityname1', 'ae': 'lwe', 'is_pc': 0, 'type': 2, 'medium': 0, 'our_side_entity': 3, 'settlement_title': 'settlementd', 'settlement_short_title': 'kim', 'settlement_type': 0, 'date': datetime.date(2020, 4, 11), 'sale': 'sale1' ,'system_value': Decimal('1038.36')},
{'account_id': '21312312', 'entity': 'entityname1', 'ae': 'lwe', 'is_pc': 0, 'type': 2, 'medium': 0, 'our_side_entity': 3, 'settlement_title': 'settlementd', 'settlement_short_title': 'kim', 'settlement_type': 0, 'date': datetime.date(2020, 4, 12), 'sale': 'sale1' ,'system_value': Decimal('1038.36')},
{'account_id': '21312312', 'entity': 'entityname1', 'ae': 'lwe', 'is_pc': 0, 'type': 2, 'medium': 0, 'our_side_entity': 3, 'settlement_title': 'settlementd', 'settlement_short_title': 'kim', 'settlement_type': 0, 'date': datetime.date(2020, 4, 13), 'sale': 'sale1' ,'system_value': Decimal('1038.36')},
]
df = pd.DataFrame.from_records(s)

df = df.pivot_table(index=['account_id', 'entity', 'ae', 'is_pc', 'type', 'medium', 'our_side_entity', 'settlement_title', 'settlement_short_title', 'settlement_type', 'sale'],columns='date',values='system_value').\
   assign(total_sum=lambda x:x.sum(axis=1)).\
   reset_index()
print(df)

# raise DataError("No numeric types to aggregate")
# pandas.core.base.DataError: No numeric types to aggregate


Ch3steR:

你可以用df.pivot_tabledf.assign

df.pivot_table(index=['account_id','entity','sale'],columns='date',values='system_value').\
   assign(total_sum=lambda x:x.sum(axis=1)).\
   reset_index()

date  account_id   entity   sale  2010-01-02  2010-01-03  2010-01-04  total_sum
0     1166470734  entity1  sale1        10.2         2.2         NaN       12.4
1      123232323  entity2  sale2         NaN         4.2         5.2        9.4
2        4342343  entity3  sale3         NaN         NaN        10.2       10.2

编辑:

经过调查df.dtypes system_value后才object打字。因此,将引发错误。

df.dtypes
account_id                object
entity                    object
.                            .
.                            .
.                            .
date                      object
sale                      object
system_value              object
dtype: object

设置dtypesystem_valuefloat

df = pd.DataFrame.from_records(s).astype({'system_value':'float'})

给出输出:

date account_id       entity   sale  2020-04-09  2020-04-10  2020-04-11  2020-04-12  2020-04-13  total_sum
0      21312312  entityname1  sale1     1038.36     1038.36     1038.36     1038.36     1038.36     5191.8

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章