'Formatting Pandas values and creating data sheets using mathematical models
I want to do a group type of Transaction Type where Buy and Sell are separate from Short and Cover. I want to modify the function g in the code below where it separates buy and Sell and Short and Cover. The Gains/Loss and Percentage Return works for Buy and Sell, however it does not work for Short and Cover. I want to modify the code so that it works for that.
There is a column that tracks the Gains/Loss of that stock that being it will subtract the buy value from sell value (Buy - Sell) like (2360.15-2160.36) + (1897-1936.2) since META was bought and sold twice on 2 different occasions the value is gonna be like that. For Short and Cover it will be -(13.60 - 21.60) if the second value 21.60 is higher then first value 13.60 then it will be a positive value as the output.
The % Gain/Loss that is calculated by the equation (Buy-Sell)/Buy * 100 so for the META the equation would be like
((2366.15-2160.36)/2360.15 + (1897-1936.2)/1897)* 100). For Short and Cover it will be -(Cover - Short)/Short So it will be (-(13.60 - 21.60)/21.60) * 100. Some of the code has been achieved from this link .
How would I be able to modify the table below to get the expected Outputs?
import pandas as pd
a = pd.DataFrame({
'Date': {0: '2/4/2022 1:33:40 PM', 1: '2/7/2022 3:09:46 PM',
2: '2/11/2022 9:35:44 AM',3: '2/12/2022 12:16:30 PM', 4: '2/14/2022 2:55:33 PM',
5: '2/15/2022 3:55:33 PM', 6: '2/15/2022 9:15:33 PM', 7:'3/1/2022 10:16:40 AM'},
'TransactionType': {0: 'Buy', 1: 'Buy', 2: 'Sell', 3:'Short', 4: 'Sell', 5: 'Buy', 6:'Sell', 7:'Cover'},
'Symbol': {0: 'META', 1: 'BABA', 2:'META', 3:'RDFN', 4: 'BABA',5: 'META', 6: 'META', 7:'RDFN'},
'Price': {0: 12.79, 1: 116.16, 2: 12.93, 3:21.81, 4: 121.82, 5: 13.55, 6:13.83, 7:1853.85},
'Amount': {0: -2366.15, 1: -2439.36, 2: 160.0, 3:21.65 , 4: 2558.22, 5:-1897, 6:1936.2, 7:13.60}})
out = df.groupby(['Symbol','TransactionType'])['TransactionType'].count().unstack().add_prefix('Number of ').add_suffix('s')
g = df.groupby(['Symbol', df['TransactionType'].eq('Buy').groupby(df['Symbol']).cumsum()])['Amount']
out['Gains/Losses'] = g.sum().groupby(level=0).sum()
out['Percentage change'] = g.pct_change().groupby(df['Symbol']).sum()
out = out.reset_index().rename_axis([None], axis=1)
Expected Output:
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|


