Python - New row containing sum of other rows based on criteria

user_13503702 0
Viewed 67

I have a dataframe that looks like this (there are other columns which needs to be kept but untouched, they can be blank in the new row with assetClass = 99):

clientID  date         assetClass   value
10111     01-01-2020   1            10
10111     01-01-2020   2            20
10111     01-01-2020   3            30
10111     02-01-2020   1            15
10111     02-01-2020   2            25
10111     02-01-2020   3            35

There are several clients and asset classes from 1-18. This is raw output from a system.

I need to create a new assetClass, call it 99, which consists of the sum of 2 and 3 (in reality 11-16), grouped by date and clientID, creating a dataframe looking like this:

clientID  date         assetClass   value
10111     01-01-2020   1            10
10111     01-01-2020   2            20
10111     01-01-2020   3            30
10111     01-01-2020   99           50
10111     02-01-2020   1            15
10111     02-01-2020   2            25
10111     02-01-2020   3            35
10111     02-01-2020   99           60

I've googled like a mad man, but I think there's something lacking in my knowledge of what this process should be called.

I have gotten nowhere with my own attempts at solving it, so I have no relevant code to show - how do I do this?

python
3 Answers

Here's a short the working code that I've tested and you doesn't have to make a copy explicitly.

result = df.loc[df['assetClass'].isin([2,3])].groupby(['clientID','date']).sum() #first select rows from 'assetClass' having values 2 and 3. 
#Then groupby clientID and date and then sum
result['assetClass'] = 99 # equate assetClass to 99
df = df.append(result) # merge this to existing dataframe
user_102279580

I think you need to create a new dataframe with the extra asset class. E.g.:

df_new = df.loc[df['assetClass'].isin([2, 3])].copy()
df_new = df_new.groupby(['date', 'clientID'])['value'].sum().reset_index()
df_new['assetClass'] = 99

df = pd.concat([df, df_new])

I cannot find a more lean way to do it.

user_95761160

I think some Boolean masking is in place. Apologise for the formatting. On mobile.

mask_date = (df[‘date’]==date)
mask_assetClass = (df[‘assetClass’].isin(listOfAssets))
mask = (mask_date & mask_assetClass)
value_sum = df.loc[mask,’value’].sum()

I’m unsure if this is the proper way to add the new data.

df=df.append({‘clientID’:10111,’date’:date,‘assetClass’:99,’value’:value_sum})

user_119177300
Related