根据列和行名称合并数据框，创建条件列

I have a data frame with monthly returns and their corresponding month.

我有一个包含月报表和相应月份的数据框。

Data <- read.csv("C:/Users/h/Desktop/overflow.csv", sep=";", dec=",")
Data$Date <- as.Date(as.character(Data$Date), format="%Y-%m-%d")

The data frame looks like this now:

数据框现在看起来像这样:

> Data
           Fund.A Fund.B Fund.C Fund.D
2012-01-01  -0.01   0.04   0.11   0.10
2012-02-01  -0.04  -0.06   0.08   0.11
2012-03-01  -0.04  -0.07   0.15  -0.03
2012-04-01   0.00  -0.08  -0.04   0.13
2012-05-01  -0.07   0.10   0.06   0.02
2012-06-01  -0.05   0.06   0.06  -0.02
2012-07-01   0.12  -0.06  -0.09  -0.06
2012-08-01   0.08  -0.03   0.05   0.13
2012-09-01   0.10   0.07  -0.02   0.15
2012-10-01  -0.08   0.14   0.00  -0.04
2012-11-01  -0.09   0.11  -0.07   0.12
2012-12-01  -0.01  -0.09   0.07  -0.02

Now I want to continue the time series with new returns from a new csv, by simply matching the new return with the appropriate Fund in "Data". My problem is that new assets might have been added, messing up the order.

现在,我想通过简单地将新回报与“数据”中的相应基金匹配来继续新csv的新回报时间序列。我的问题是可能已经添加了新资产,搞乱了订单。

import <- read.csv("C:/Users/h/Desktop/import.csv", sep=";", dec=",")
import
           2013-01-01
1  Funds:          NA
2  Fund A        0.04
3  Fund AA      -0.09
4  Fund C       -0.10
5  Fund D        0.03
6  Fund B        0.14

As you can see, the "import" csv has new assets (Fund AA) as well as assets seen in "Data" (Fund a to D), where the funds are in rows and not columns. How can I write a code, which matches and adds a row to "Data" where the values in "import" falls under the right column (Fund) in "Data"? And if a new asset have been added, creates a column for the new asset?

正如您所看到的,“进口”csv有新资产(基金AA)以及“数据”(基金a到D)中看到的资产,其中资金是行而不是列。如何编写代码,匹配并向“数据”添加一行,其中“导入”中的值属于“数据”中的右栏(基金)?如果添加了新资产,则为新资产创建一列?

As a bonus, the code would only add a row if the date in "import" is more recent date than the most recent one in "Data". To only import new returns.

作为奖励,如果“导入”中的日期比“数据”中的最新日期更新,则代码将仅添加一行。仅导入新的退货。

Appreciate it!

1 个解决方案

#1

For time series purpose, I would recommend using xts. It makes life a bit easier. Borrowing from Arun's usable data:

出于时间序列的目的,我建议使用xts。它让生活更轻松。借用Arun的可用数据:

olddata <- structure(list(Date = structure(c(15340, 15371, 15400, 15431,
  15461, 15492, 15522, 15553, 15584, 15614, 15645, 15675), class = "Date"), 
  Fund.A = c(-0.01, -0.04, -0.04, 0, -0.07, -0.05, 0.12, 0.08, 0.1, -0.08,
  -0.09, -0.01), Fund.B = c(0.04, -0.06, -0.07, -0.08, 0.1, 0.06, -0.06,
  -0.03, 0.07, 0.14, 0.11, -0.09), Fund.C = c(0.11, 0.08, 0.15, -0.04,
  0.06, 0.06, -0.09, 0.05, -0.02, 0, -0.07, 0.07), Fund.D = c(0.1, 0.11,
  -0.03, 0.13, 0.02, -0.02, -0.06, 0.13, 0.15, -0.04, 0.12, -0.02)),
  .Names = c("Date", "Fund.A", "Fund.B", "Fund.C", "Fund.D"),
  row.names = c(NA, 12L), class = "data.frame")

newimport <- structure(list(funds = c("Fund.A", "Fund.AA", "Fund.C",
  "Fund.D", "Fund.B"), `2013-01-01` = c(0.04, -0.09, -0.1, 0.03, 0.14)),
  .Names = c("funds", "2013-01-01"), row.names = c(NA, -5L),
  class = "data.frame")

Convert data to xts for easy datewise subsetting:

将数据转换为xts以便于日期子集化:

olddata <- xts(olddata[,-1], olddata$Date)
newdata <- xts(t(newimport[,-1]), as.Date(colnames(newimport)[-1]))
colnames(newdata) <- newimport[,1]

Merge data together while taking care of any new columns:

将数据合并在一起,同时处理任何新列:

cols <- names(newdata) %in% names(olddata)
combineData <- merge(rbind(olddata, newdata[,cols]), newdata[,!cols])
combineData
           Fund.A Fund.B Fund.C Fund.D Fund.AA
2012-01-01  -0.01   0.04   0.11   0.10      NA
2012-02-01  -0.04  -0.06   0.08   0.11      NA
2012-03-01  -0.04  -0.07   0.15  -0.03      NA
2012-04-01   0.00  -0.08  -0.04   0.13      NA
2012-05-01  -0.07   0.10   0.06   0.02      NA
2012-06-01  -0.05   0.06   0.06  -0.02      NA
2012-07-01   0.12  -0.06  -0.09  -0.06      NA
2012-08-01   0.08  -0.03   0.05   0.13      NA
2012-09-01   0.10   0.07  -0.02   0.15      NA
2012-10-01  -0.08   0.14   0.00  -0.04      NA
2012-11-01  -0.09   0.11  -0.07   0.12      NA
2012-12-01  -0.01  -0.09   0.07  -0.02      NA
2013-01-01   0.04   0.14  -0.10   0.03   -0.09

#1