How do I create a new data frame when performing analysis on a pandas pivot table?

user_9519968 0
Viewed 49

I'm trying to clean up my data frame so I can create a graph that shows profit over time. I grouped my data frame by Symbol thinking it would be easier to correctly apply analysis but I'm not sure of the logic from here. I'd like to create a separate data frame like the example below.

Code:

import pandas as pd

data = {'Action': ['BUY_TO_OPEN', 'SELL_TO_CLOSE', 'BUY_TO_OPEN', 'SELL_TO_CLOSE', 'BUY_TO_OPEN', 'SELL_TO_CLOSE', 'BUY_TO_OPEN', 'SELL_TO_CLOSE', 'BUY_TO_OPEN', 'SELL_TO_CLOSE'],
        'Date': ['2020-07-23', '2020-07-29', '2020-06-04', '2020-06-24', '2020-07-17', '2020-07-21', '2020-05-28', '2020-05-28', '2020-06-29', '2020-07-20'],
        'Quantity': [200.0, 200.0, 130.0, 130.0, 100.0, 100.0, 100.0, 100.0, 120.0, 120.0],
        'Symbol': ['ACHV', 'ACHV', 'ACST', 'ACST', 'AGE', 'AGE', 'AIKI', 'AIKI', 'AIKI', 'AIKI'],
        'tot_value': [-96.16, 163.81, -100.2, 83.07, -100.08, 149.9, -74.08, 71.91, -100.9, 153.48]}

df_trade = pd.DataFrame(data)

df_trade.Date = pd.to_datetime(df_trade.Date)

df_trades = pd.pivot_table(df_trades, index=['Symbol', 'Date', 'Action', 'Quantity'], values=['tot_value'])

print(df_trades)

Pivot Table:

                                         tot_value
Symbol Date       Action        Quantity
ACHV   2020-07-23 BUY_TO_OPEN   200.0        -96.16
       2020-07-29 SELL_TO_CLOSE 200.0        163.81
ACST   2020-06-04 BUY_TO_OPEN   130.0       -100.20
       2020-06-24 SELL_TO_CLOSE 130.0         83.07
AGE    2020-07-17 BUY_TO_OPEN   100.0       -100.08
       2020-07-21 SELL_TO_CLOSE 100.0        149.90
AIKI   2020-05-28 BUY_TO_OPEN   100.0        -74.08
                  SELL_TO_CLOSE 100.0         71.91
       2020-06-29 BUY_TO_OPEN   120.0       -100.90
       2020-07-20 SELL_TO_CLOSE 120.0        153.48

Example of data frame I'm trying to achieve:

Where the date is the SELL_TO_CLOSE date and the Profit column is the sum of tot_value using each BUY_TO_OPEN and SELL_TO_CLOSE pair to calculate.

Symbol Date          Profit
ACHV   2020-07-29    67.65
ACST   2020-06-24   -17.13
AGE    2020-07-21    49.82
AIKI   2020-05-28    -2.17
AIKI   2020-07-20    52.58
pandas python
1 Answers

From your pivoted table, you can do it using the fact that your data is ordered and by pair, by grouping where Action is BUY_TO_OPEN and cumsum

print (df_trades.reset_index()
                .groupby((df_trades.index.get_level_values('Action')
                          =='BUY_TO_OPEN').cumsum())
                .agg({'Symbol':'first', 'Date':'last','tot_value':'sum'})
                .rename(columns={'tot_value':'Profit'})
      )
  Symbol        Date  Profit
1   ACHV  2020-07-29   67.65
2   ACST  2020-06-24  -17.13
3    AGE  2020-07-21   49.82
4   AIKI  2020-05-28   -2.17
5   AIKI  2020-07-20   52.58
user_92747320
Related