I have some data that represents one event (a trade) that is broken up into two rows: Entry and Exit.
I would like to combine this into one row with details about each.
+---------+-------------+--------------------------+------------------+----------+-----------+-----------+----------+----------------+---------------+-----------+----------+-------------+------------+
| Trade # | Type | Signal | Date/Time | Price | Contracts | Profit C$ | Profit % | Cum. Profit C$ | Cum. Profit % | Run-up C$ | Run-up % | Drawdown C$ | Drawdown % |
+---------+-------------+--------------------------+------------------+----------+-----------+-----------+----------+----------------+---------------+-----------+----------+-------------+------------+
| 1 | Entry Long | Buy | 2021-09-20 18:04 | 0.006876 | 72706 | 14.01 | 2.8 | 14.01 | 28.01 | 17.59 | 3.52 | 4.95 | 0.99 |
| 1 | Exit Long | Close entry(s) order Buy | 2021-09-20 20:03 | 0.007077 | 72706 | 14.01 | 2.8 | 14.01 | 28.01 | 17.59 | 3.52 | 4.95 | 0.99 |
| 2 | Entry Short | Sell | 2021-09-24 14:05 | 0.007408 | 67476 | -5.66 | -1.13 | 8.34 | -8.85 | 0 | 0 | 5.36 | 1.07 |
| 2 | Exit Short | Exit Sell | 2021-09-24 14:50 | 0.007483 | 67476 | -5.66 | -1.13 | 8.34 | -8.85 | 0 | 0 | 5.36 | 1.07 |
+---------+-------------+--------------------------+------------------+----------+-----------+-----------+----------+----------------+---------------+-----------+----------+-------------+------------+
And here is desired output format:
+---------+-------------+--------------+------------------+-------------+-----------+--------------------------+------------------+------------+-----------------------+------------------+-----------+----------+----------------+---------------+-----------+----------+-------------+------------+
| Trade # | Type | Entry Signal | Entry Date/Time | Entry Price | Contracts | Exit Signal | Exit Date/Time | Exit Price | Price Change (Points) | Price Change (%) | Profit C$ | Profit % | Cum. Profit C$ | Cum. Profit % | Run-up C$ | Run-up % | Drawdown C$ | Drawdown % |
+---------+-------------+--------------+------------------+-------------+-----------+--------------------------+------------------+------------+-----------------------+------------------+-----------+----------+----------------+---------------+-----------+----------+-------------+------------+
| 1 | Entry Long | Buy | 2021-09-20 18:04 | 0.006876 | 72706 | Close entry(s) order Buy | 2021-09-20 20:03 | 0.007077 | 0.000201 | 2.92% | 14.01 | 2.8 | 14.01 | 28.01 | 17.59 | 3.52 | 4.95 | 0.99 |
| 2 | Entry Short | Sell | 2021-09-24 14:05 | 0.007408 | 67476 | Exit Sell | 2021-09-24 14:50 | 0.007483 | 0.000075 | 1.01% | -5.66 | -1.13 | 8.34 | -8.85 | 0 | 0 | 5.36 | 1.07 |
+---------+-------------+--------------+------------------+-------------+-----------+--------------------------+------------------+------------+-----------------------+------------------+-----------+----------+----------------+---------------+-----------+----------+-------------+------------+
You can also find a public Google Spreadsheet here showing the desired input and output, with some notes attached; noting this section in particular on the 'Desired Output' tab:
Also please note I would like to add a couple of new columns that would include calculated metrics, as shown in second table (Price Change (Points) and Price Change (%)).
I don't know if Orange3 has a widget(s) that could facilitate this transformation(?), and its usage?
(PS - I am currently using Orange3 by way of Anaconda Navigator. Perhaps if Orange3 isn't the right tool for this someone could recommend an alternative workflow? It could be in a comment if an answer isn't appropriate, given the current question. Or I could edit my question based on a comment. Kindly)
