-1

Below is an example of the two data frames I would like to merge.

           portfolio_cost portfolio_value portfolio_return holding_period  closedate stock
2013-11-19          36.21           37.58      0.037834852              7 2013-11-29  MSFT
2013-12-12          36.68           36.90      0.005997819              9 2013-12-26  MSFT
2014-03-14          37.44           39.27      0.048878205              2 2014-03-18  MSFT
2014-04-10          39.08           40.58      0.038382805             11 2014-04-28  MSFT
2014-05-02          39.41           40.42      0.025628013              7 2014-05-13  MSFT

           portfolio_cost portfolio_value portfolio_return holding_period  closedate stock
2013-11-07          72.37           74.59       0.03067569              5 2013-11-14  AAPL
2013-12-18          77.78           80.51       0.03509900              3 2013-12-23  AAPL
2014-01-03          76.40           79.23       0.03704188              5 2014-01-10  AAPL
2014-04-04          75.55           77.42       0.02475182              1 2014-04-07  AAPL
2014-05-09          83.65           86.37       0.03251644              6 2014-05-19  AAPL
2014-06-20          90.91           92.93       0.02221978              6 2014-06-30  AAPL

What I would like to do is join the two dataframes together in a way that would result in something like this...

    portfolio_cost  portfolio_value portfolio_return    holding_period  closedate   stock
11/7/2013   72.37   74.59   0.03067569  5   11/14/2013  AAPL
11/19/2013  36.21   37.58   0.037834852 7   11/29/2013  MSFT
12/12/2013  36.68   36.9    0.005997819 9   12/26/2013  MSFT
12/18/2013  77.78   80.51   0.035099    3   12/23/2013  AAPL
1/3/2014    76.4    79.23   0.03704188  5   1/10/2014   AAPL
3/14/2014   37.44   39.27   0.048878205 2   3/18/2014   MSFT
4/4/2014    75.55   77.42   0.02475182  1   4/7/2014    AAPL
4/10/2014   39.08   40.58   0.038382805 11  4/28/2014   MSFT
5/2/2014    39.41   40.42   0.025628013 7   5/13/2014   MSFT
5/9/2014    83.65   86.37   0.03251644  6   5/19/2014   AAPL
6/20/2014   90.91   92.93   0.02221978  6   6/30/2014   AAPL

If there are two common dates, I would like it to do something like this...

    portfolio_cost  portfolio_value portfolio_return    holding_period  closedate   stock
11/7/2013   72.37+36.21 74.59+37.58 VALUE   5   11/14/2013  AAPL-MSFT
Jaap
  • 81,064
  • 34
  • 182
  • 193
road_to_quantdom
  • 1,341
  • 1
  • 13
  • 20

1 Answers1

1

Here is a data.table solution. I called those data frames df1 and df2. I changed the date of the first df2 entry to the same as the first entry in df1, otherwise there are no common dates. To account for cases where the close date is different the result pastes each close date separated by ,

library(data.table)
df1$Date <- rownames(df1)
df2$Date <- rownames(df2)
newdf <- rbind(df1,df2)
DT <- data.table(newdf)
DT[,list("portfolio_cost"=sum(portfolio_cost), "portfolio_value"=sum(portfolio_return), "holding_period"=sum(holding_period), "closedate"=paste(closedate,collapse=","), "stock"=paste(stock,collapse="-")),by=Date]

"holding_period"=paste(holding_period,collapse=",") might be more informative.

JeremyS
  • 3,497
  • 1
  • 17
  • 19
  • I was wondering what the resulting object is? Is it a data frame? For some reason I cannot manipulate the resulting object the way I normally manipulate a dataframe. I am trying to change the rownames back to the Dates, and remove the Dates column (just personal preference). Thank you so much again! This helps so much – road_to_quantdom Jul 09 '14 at 08:02
  • It's a data.table, you can change back to a data.frame using `data.frame(DT)` – JeremyS Jul 09 '14 at 08:08
  • also, how would you sort the entire table by Dates? – road_to_quantdom Jul 09 '14 at 08:10