Restructure data frame through aggregation

[veegee]

Hello,
I am pretty new to R but have lots of Python experience. I have a data frame that I need to restructure through aggregation. I do have a strategy for a function that will accomplish this. My question is, is there anything built into R, or one of the R packages that will do this kind of aggregation?

The data is historical stock information, the structure is such as:
Ticker, time, quantity, price, type
APPL, timestamp1, 100, 144, BUY
APPL, timestamp2, 100, 145, BUY
APPL, timestamp3, 50, 150, SELL
APPL, timestamp4, 150, 155, SELL

I need to convert this so it is a TRADE record, rather than a BUY/SELL record such as:
Ticker, enter_time, exit_time, enter_price,exit_price
APPL, timestamp1, timestamp4,144.5,153.75

Note that the enter price and exit price are the weighted average buy/sell price. Also, there can be many more entries for buy/sell. The "trade" is finished when everything that has been bought is sold. And of course there will be buying and selling of many tickers intertwined.

Thanks,
Vera

[andresrcs]

Since you have experience with python I think you are familiar with the concept of a minimal reproducible example, could you please turn this into one?

FAQ: What's a reproducible example (`reprex`) and how do I create one?

Why reprex? Getting unstuck is hard. Your first step here is usually to create a reprex, or reproducible example. The goal of a reprex is to package your code, and information about your problem so that others can run it and feel your pain. Then, hopefully, folks can more easily provide a solution. What's in a Reproducible Example? Parts of a reproducible example: background information - Describe what you are trying to do. What have you already done? complete set up - include any library() calls and data to reproduce your issue. data for a reprex: Here's a discussion on setting up data for a reprex make it run - include the minimal code required to reproduce your error on the data…

In the meanwhile, you can take a look into tibbletime package for time aggregation and, into tidyr package for gathering your data into a wide format.

[veegee]

Thank you. I am looking for a general suggestion such as "this function might help". I felt that my simple description of the data structure provided enough info. To rephrase, I am wondering if a custom function is the approach you ( an experienced user) would use or if there might be some built in function I have missed that might be able to restructure the data.

Much thanks.

[andresrcs]

As I said before, take a look into gather() function of the 'tidyr' package for transforming your data into a wide format, also if you need to perform time aggregation take a look into collapse_by() from tibbletime.

Tidy Messy Data

GitHub - business-science/tibbletime: Time-aware tibbles

[cderv]

For dealing with time series data inside the tidyverse, you also have tsibble and there was a talk about it at rstudio conf this year

Posit