CODEWITHSUNDEEP
pythonpandasdataframe
jia Jimmy
jia Jimmy

Reputation: 1848

Sum value with pandas groupby and rename old column？

As below codes showed, I want to group by the data by account_id, then sum the system_value and rename it as total_value and keep the every date data at same time.

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

Expected output is:


#    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

Sorry that I'm new to , how can I get the expected result?

Update for my question based on @Ch3steR's answer:

I tried and got error showed as below


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

Upvotes: 2

Views: 281

Answers (2)

Ch3steR
Ch3steR

Reputation: 20659

You can use df.pivot_table with df.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

EDIT:

After looking into df.dtypes system_value was object type. So, the error is raised.

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

Set the dtype of system_value to float

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

Gives the output:

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

Upvotes: 1

Quang Hoang
Quang Hoang

Reputation: 150785

An approach with groupby:

(df.groupby(['entity','date','sale']).system_value.sum()
   .unstack('date', fill_value=0)
   .assign(total_value=lambda x: x.sum(1))
   .reset_index()
)

Output:

date   entity   sale  2010-01-02  2010-01-03  2010-01-04  total_value
0     entity1  sale1        10.2         2.2         0.0         12.4
1     entity2  sale2         0.0         4.2         5.2          9.4
2     entity3  sale3         0.0         0.0        10.2         10.2

Upvotes: 1

Related Questions