如以下代码所示,我想按来对数据进行分组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
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
设置dtype
的system_value
对float
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] 删除。
我来说两句