I'm trying to split a transaction date column into 2 separate ones, 1 column for buy date, another for sell date. Likewise, I'd like to split a single transaction price column into sell price, and buy price. There is this post which is similar, but here I'd like to track every transaction date, instead of just imputing the earliest date as the buy and the latest date as the sell. So for example below is the current dataframe:
property = c('A','A','A','A','B','B','B')
transaction_dates = c("2011-03-09", "2013-06-06", "2015-08-28", "2016-07-18", "2016-12-13", "2018-10-29", "2019-11-30")
prices = c(750000, 830000, 820000,800000,825000,900000,600000)
proptx = data.frame(property,transaction_dates,prices)
property transaction_dates prices
1 A 2011-03-09 750000
2 A 2013-06-06 830000
3 A 2015-08-28 820000
4 A 2016-07-18 800000
5 B 2016-12-13 825000
6 B 2018-10-29 900000
7 B 2019-11-30 600000
I am trying to add columns (or rather perhaps generate a new data frame) that breaks the transaction date column and price column into separate "buy" and "sell" columns, like so
property buy_date buy_price sell_date sell_price
1 A 2011-03-09 750000 2013-06-06 830000
2 A 2013-06-06 830000 2015-08-28 820000
3 A 2015-08-28 820000 2016-07-18 800000
4 A 2016-07-18 800000 NA NA
5 B 2016-12-13 825000 2018-10-29 900000
5 B 2018-10-29 900000 2019-11-30 600000
6 B 2019-11-30 600000 NA NA
Ultimately what I would like to do is track the length of time that elapses between buy and sell dates, and then calculate the return to the seller. Rows 4 and 6 would represent that the property is not being/has not been sold. The actual data frame has hundreds of thousands of distinct properties, and I was hoping to do this sort of operation on each property.
Can this be done relatively easily?