如何根据某些条件合并数据框架和更改元素值？ [英] How to merge data frames and change element values based on certain conditions?

问题描述

以下是我想合并的两个数据框的示例。

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

推荐答案

这是一个data.table解决方案。我调用了这些数据框架 df1 和 df2 。我将第一个df2条目的日期与df1中的第一个条目相同，否则没有常见的日期。为了解决关闭日期不同的情况，结果粘贴每个关闭日期与分开，

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 =，）可能会更丰富。

这篇关于如何根据某些条件合并数据框架和更改元素值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！