[R] Matching Problem: Want to match to data.frame with inexact matching identifier (one identifier has to be in the range of the other).

From: Christoph Jäckel <christoph.jaeckel_at_wi.tum.de>
Date: Sat, 16 Apr 2011 16:35:24 +0200


Hello R-Community,

I have the following matching problem: I have two data.frames, one with an observation every month (per company ID), and one with an observation every quarter (per company ID; note that quarter means fiscal quarter; therefore 1Q = Jan, Feb, Mar is not necessarily correct and also, a fiscal quarter is not necessarily 3 month long).

For every month and company, I want to get the correct value of that quarter. Consequently, several months have the same value for one quarter. As an example see the code below:

#Monthly data

d1 <- data.frame(cbind(c(rep("A",5),rep("B",5)),c(1:5,1:5)),stringsAsFactors=FALSE) names(d1) <- c("ID", "Month")
d1[,"Month"] <- as.integer(d1[,"Month"])

#Quarterly data, i.e. the value of every quarter has to be matched to
several months in d1
#However, I want to match fiscal quarters, which means that one
quarter is not necessarily 3 month long
d2 <- data.frame(cbind(c("A","A","B","B"),c(1,3,1,4),c(2,5,3,5),c("v1","v2","v3","V4")),stringsAsFactors=FALSE) names(d2) <- c("ID", "Min_Month", "Max_Month","Value") d2[,"Min_Month"] <- as.integer(d2[,"Min_Month"]) d2[,"Max_Month"] <- as.integer(d2[,"Max_Month"])

#Possible solution: Loop through every company and month and check in
which quarter (based on Min_Month and Max_Month) the current month is for (i in 1:nrow(d1)){
  for (j in 1:nrow(d2)){
    if ((d1[i,"ID"] == d2[j,"ID"]) & (d1[i,"Month"]>= d2[j,"Min_Month"]) &     (d1[i,"Month"]<= d2[j,"Max_Month"])) {break}   }
  d1[i,"Value"] <- d2[j,"Value"]
}

The solution works, but I was hoping that there would be a more efficient and elegant one. I checked "match" and "merge", but didn't figure out how I could use those to match without a unique identifier.

Any hints would be highly appreciated.

Christoph

--

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Christoph Jäckel (Dipl.-Kfm.)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------

Research Assistant
Chair for Financial Management and Capital Markets | Lehrstuhls für
Finanzmanagement und Kapitalmärkte
TUM School of Management | Technische Universität München

______________________________________________
R-help_at_r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Received on Sat 16 Apr 2011 - 14:38:35 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Tue 19 Apr 2011 - 16:30:31 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.

list of date sections of archive