René:

I don't see the "two merges" way either. Here's the sqldf way. (SQLite, like
most SQL systems, doesn't like "." in table names and "from" is an SQL
keyword, so I made changes accordingly. I also hard-coded df_1.)

> install.packages("sqldf") # Output suppressed here
> library(sqldf)
> df_1 <-
data.frame(time=c(101,199,301,401,501,601,700,800,900,1000),value=NA)

> df_2 <- data.frame(start=c(99,500,799),end=c(303,702,950), value=c(1,3,5))

> df_3 <- sqldf("SELECT a.time, b.value FROM df_1 AS a LEFT JOIN df_2 AS b ON
a.time BETWEEN b.start AND b.end")> df_3   time value
1   101     1
2   199     1
3   301     1
4   401    NA
5   501     3
6   601     3
7   700     3
8   800     5
9   900     5
10 1000    NA



On Sat, May 14, 2011 at 1:06 PM, René Mayer <mayer@psychologie.tu-dresden.de
> wrote:

> thanks David and Ian,
> let me make a better example as the first one was flawed
>
> df.1=data.frame(round((1:10)*100+rnorm(10)), value=NA)
> names(df.1) = c("time", "value")
> df.1
>   time value
> 1   101    NA
> 2   199    NA
> 3   301    NA
> 4   401    NA
> 5   501    NA
> 6   601    NA
> 7   700    NA
> 8   800    NA
> 9   900    NA
> 10 1000    NA
>
> # from and to define ranges within time,
> # note that from and to may not match the numbers given in time
> df.2=data.frame(from=c(99,500,799),to=c(303,702,950), value=c(1,3,5))
> df.2
>  from  to value
> 1   99 303     1
> 2  500 702     3
> 3  799 950     5
>
> what I want is:
>   time value
> 1   101    1
> 2   199    1
> 3   301    1
> 4   401    NA
> 5   501    3
> 6   601    3
> 7   700    3
> 8   800    5
> 9   900    5
> 10 1000    NA
>
> @David I don't know what you mean by 2 merges,
> René
>
>
>
>
>
> Zitat von "David Winsemius" <dwinsemius@comcast.net>:
>
>
>
>> On May 14, 2011, at 9:16 AM, Ian Gow wrote:
>>
>>  If I assume that the third column in data.frame.2 is named "val" then in
>>> SQL terms it _seems_ you want
>>>
>>> SELECT a.time, b.val FROM data.frame.1 AS a LEFT JOIN data.frame.2 AS b
>>> ON
>>> a.time BETWEEN b.start AND b.end;
>>>
>>> Not sure how to do that elegantly using R subsetting/merge,
>>>
>>
>> Huh? It's just two merge()'s (... once you fix the error in the example.)
>>
>> --
>> David
>>
>>  but you might
>>> try a package that allows you to use SQL, such as sqldf.
>>>
>>>
>>> On 5/14/11 8:03 AM, "David Winsemius" <dwinsemius@comcast.net> wrote:
>>>
>>>
>>>> On May 14, 2011, at 8:12 AM, René Mayer wrote:
>>>>
>>>>  Hello,
>>>>> how can one merge
>>>>>
>>>>
>>>> And what happened when you typed:
>>>>
>>>> ?merge
>>>>
>>>>  two data frames when in the second data frame one column defines the
>>>>> start values
>>>>> and another defines the end value of the to be merged range.
>>>>> data.frame.1
>>>>> time ...
>>>>> 13
>>>>> 24
>>>>> 35
>>>>> 46
>>>>> 55
>>>>> ...
>>>>> data.frame.2
>>>>> start end
>>>>> 24 37 ?h? ?
>>>>> ...
>>>>>
>>>>> should result in this
>>>>> 13 NA
>>>>> 24 ?h?
>>>>> 35 ?h?
>>>>> 46 NA
>>>>> 55
>>>>> ?
>>>>>
>>>>
>>>> And _why_ would that be?
>>>>
>>>>
>>>>  thanks,
>>>>> René
>>>>>
>>>>> ______________________________________________
>>>>> R-help@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.
>>>>>
>>>>
>>>> David Winsemius, MD
>>>> West Hartford, CT
>>>>
>>>> ______________________________________________
>>>> R-help@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.
>>>>
>>>
>>>
>>>
>> David Winsemius, MD
>> West Hartford, CT
>>
>>
>>
>

	[[alternative HTML version deleted]]

