Formatting Pandas values and creating data sheets using mathematical models-CodePudding

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

Expected Output:

CodePudding user response：

I don't tend to use groupby although I am sure others who use it more regularly may be able to comment on its appropriateness in this situation.

It wasn't exactly clear how you calculate the gain - but I believe that this framework is easily editable to allow you to change the calculation

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
    }
})
print(a, '\n\n')

#### >>>>
#### ANSWER STARTS HERE ####
#### >>>>
results = {
    'Symbol': [],
    'Number of Buys': [],
    'Number of Sells': [],
    'Number of Shorts': [],
    'Number of Covers': [],
    'Gains/Loss ($)': [],
    'Percentage Return (%)': [],
}

for symbol in set(a['Symbol']):
    results['Symbol'].append(symbol)

    # now extract the rest of the data by isolating matching orders from the original
    # dataframe
    temp = a[a['Symbol'] == symbol]

    results['Number of Buys'].append(len(temp[temp['TransactionType']=='Buy']))
    results['Number of Sells'].append(len(temp[temp['TransactionType']=='Sell']))
    results['Number of Shorts'].append(len(temp[temp['TransactionType']=='Short']))
    results['Number of Covers'].append(len(temp[temp['TransactionType']=='Cover']))

    # do calculations

    # for buys and sells, multiply the price by the amount of the relevant rows
    buys = sum([
        a['Price'][i] * a['Amount'][i] for i in temp[temp['TransactionType']=='Buy'].index
    ])
    sells = sum([
        a['Price'][i] * a['Amount'][i] for i in temp[temp['TransactionType']=='Sell'].index
    ])

    # manage raw and percentage difference
    # set to 0 if either is 0 - this avoids any divide by zero errors
    gain = buys - sells
    if buys == 0.0 or gain == 0.0:
        percentage = 0.0
    else:
        percentage = 100 * gain / buys

    results['Gains/Loss ($)'].append(gain)
    results['Percentage Return (%)'].append(percentage)

# pass the dictionary to a dataframe and print
dataframe = pd.DataFrame(results)
print(dataframe)

This runs as follows:

                    Date TransactionType Symbol    Price   Amount
0    2/4/2022 1:33:40 PM             Buy   META    12.79 -2366.15
1    2/7/2022 3:09:46 PM             Buy   BABA   116.16 -2439.36
2   2/11/2022 9:35:44 AM            Sell   META    12.93   160.00
3  2/12/2022 12:16:30 PM           Short   RDFN    21.81    21.65
4   2/14/2022 2:55:33 PM            Sell   BABA   121.82  2558.22
5   2/15/2022 3:55:33 PM             Buy   META    13.55 -1897.00
6   2/15/2022 9:15:33 PM            Sell   META    13.83  1936.20
7   3/1/2022 10:16:40 AM           Cover   RDFN  1853.85    13.60 


  Symbol  Number of Buys  Number of Sells  Number of Shorts  Number of Covers  Gains/Loss ($)  Percentage Return (%)
0   META               2                2                 0                 0     -84813.8545             151.541507
1   RDFN               0                0                 1                 1          0.0000               0.000000
2   BABA               1                1                 0                 0    -594998.4180             209.982600