0

I've got the investment portfolio, see the example below:

portfolio <- data.frame(ISIN = c('ABC US Equity', 'DEF US Equity', 'GHI US Equity'),
             Buy.Date = c('22.10.2014', '02.05.2015', '07.08.2016'),
             Sale.Date = c('30.05.2015', '15.02.2016', '31.12.2017'),
             Buy.Price = c('107.1','101.3','97.8'),
             Sale.Price = c('85.69', '102.5', '100'),
             Position = c('200','250','300'))

> portfolio
ISIN           Buy.Date   Sale.Date Buy.Price Sale.Price Position
ABC US Equity 22.10.2014 30.05.2015 107.1        85.69      200
DEF US Equity 02.05.2015 15.02.2016 101.3        102.5      250
GHI US Equity 07.08.2016 31.12.2017 97.8         100        300

My aim is to obtain the single data.frame of the following view:

> portfolio    
ISIN           Buy.Date   Sale.Date Buy.Price Sale.Price Position
ABC US Equity 22.10.2014 30.05.2015 107.1        85.69      200
ABC US Equity 02.05.2015 30.05.2015 107.1        85.69      200
DEF US Equity 02.05.2015 15.02.2016 101.3        102.5      250
DEF US Equity 30.05.2015 15.02.2016 101.3        102.5      250
DEF US Equity 07.08.2016 15.02.2016 101.3        102.5      250
GHI US Equity 07.08.2016 31.12.2017 97.8         100        300
GHI US Equity 15.02.2016 31.12.2017 97.8         100        300

Namely, it should be a snapshot of portfolio holdings on single Buy and Sale Dates, i.e. actually it requires merging together the following data frames:

ISIN           Buy.Date   Sale.Date Buy.Price Sale.Price Position
ABC US Equity 22.10.2014 30.05.2015 107.1        85.69      200

ISIN           Buy.Date   Sale.Date Buy.Price Sale.Price Position
ABC US Equity 02.05.2015 30.05.2015 107.1        85.69      200
DEF US Equity 02.05.2015 15.02.2016 101.3        102.5      250

ISIN           Buy.Date   Sale.Date Buy.Price Sale.Price Position
DEF US Equity 30.05.2015 15.02.2016 101.3        102.5      250

ISIN           Buy.Date   Sale.Date Buy.Price Sale.Price Position
DEF US Equity 07.08.2016 15.02.2016 101.3        102.5      250
GHI US Equity 07.08.2016 31.12.2017 97.8         100        300

ISIN           Buy.Date   Sale.Date Buy.Price Sale.Price Position
GHI US Equity 15.02.2016 31.12.2017 97.8         100        300

For now I've just came up with two loop cycles which creates for me single data frames for each of the Buy and Sale Dates

  for(i in unique(portfolio$Buy.Date)) {
      nam <- paste("portfolio", i, sep = ".")
      assign(nam, portfolio[portfolio$Buy.Date==i,])
   }

  for(j in unique(portfolio$Sale.Date)) {
      nam <- paste("portfolio", j, sep = ".")
      assign(nam, portfolio[portfolio$Buy.Date==i,])
   }

Thus, the problem is now to merge all these data frames in a way so that it will correctly represent portfolio snapshots at different time points as I described above. Could R provide me the solution for this task?

Kindly appreciate all of your help, thank you!

P.S. I do have some current holdings in my portfolio which are not yet sold, that's why I have Sale Date = 31.12.2017. So, last rows should indicate that Date.

Spacedman
  • 92,590
  • 12
  • 140
  • 224
Nikita Aseev
  • 1
  • 1

1 Answers1

0

rbind should work, make sure all separate data.frames have the same column headings

df1<-data.frame(x=rep(1,5),y=rep(0.5))
df2<-data.frame(x=rep("A",5),y=rep("B",5))

rbind(df1,df2)
user_123
  • 62
  • 12
  • I am afraid that's not the case, cause I need the loop for many data frames and I have to change dates in these newly created data frames according to the time at which they were added (snapshots at single time point). – Nikita Aseev Jul 03 '17 at 15:11
  • Can you create a reproducible example https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example – user_123 Jul 03 '17 at 15:33
  • Yes, please, I added the code to replicate my 'portfolio' data frame – Nikita Aseev Jul 03 '17 at 16:06
  • It's still not clear which dates you want changed. You could `rbind` first to merge all the data.frames, then change the dates after. – user_123 Jul 03 '17 at 16:24