Hhow do I combine dataframes of unequal length based on a condition

user_12232820 0
Viewed 62

I would like to know how to best combine the two following dataframes:

df1 <- data.frame(Date = c(1,2,3,4,5,6,7,8,9,10),
                  Altitude=c(100,101,101,102,103,99,98,99,89,70))

> df1
   Date Altitude
1     1      100
2     2      101
3     3      101
4     4      102
5     5      103
6     6       99
7     7       98
8     8       99
9     9       89
10   10       70

df2 <- data.frame(Start = c(1,4,8),Stop = c(3,7,10),Longitude=c(10,12,13))

> df2
  Start Stop Longitude
1     1    3        10
2     4    7        12
3     8   10        13

I would basically need a third column in df2, with the Longitude based on whether the Date is between Start and Stop, resulting in something like this:

   Date Altitude Longitude
1     1      100        10
2     2      101        10
3     3      101        10
4     4      102        12
5     5      103        12
6     6       99        12
7     7       98        12
8     8       99        13
9     9       89        13
10   10       70        13

I've been trying all kinds of subsetting, filtering, ... but I just can't figure it out. Any help would be appreciated!

Kind regards

r
3 Answers

An idea via dplyr is to complete the start:stop sequence, unnest and merge, i.e.

library(dplyr)

df2 %>% 
 mutate(Date = mapply(seq, Start, Stop)) %>% 
 tidyr::unnest() %>% 
 select(-c(1, 2)) %>% 
 right_join(df1, by = 'Date')

which gives,

   Longitude Date Altitude
1         10    1      100
2         10    2      101
3         10    3      101
4         12    4      102
5         12    5      103
6         12    6       99
7         12    7       98
8         13    8       99
9         13    9       89
10        13   10       70
user_56355800

Here is a tidyverse answer using the group_by and group_modify functions in the dplyr package (introduced in version 0.8.1 in May 2019).

library(dplyr)    
df1 %>% 
      group_by(Date, Altitude) %>%
      group_modify(~ data.frame(df2 %>% 
                                  filter(.x$Date >= Start, .x$Date <= Stop)) %>% 
                                  select(Longitude), 
                   keep = TRUE)

For each unique combination in df1 of date and altitude (i.e. for each row), this finds the longitude corresponding to the date range in df2.

The output is a tibble:

# A tibble: 10 x 3
# Groups:   Date, Altitude [10]
    Date Altitude Longitude
   <dbl>    <dbl>     <dbl>
 1     1      100        10
 2     2      101        10
 3     3      101        10
 4     4      102        12
 5     5      103        12
 6     6       99        12
 7     7       98        12
 8     8       99        13
 9     9       89        13
10    10       70        13
user_28546080

Base R solution:

ind <- apply(df2, 1, function(x) which(df1$Date >= x[1] & df1$Date <= x[2]))
df1$Longitude <- unlist(Map(function(x,y) rep(y, length(x)), ind, df2$Longitude))

Output

Date Altitude Longitude
1     1      100        10
2     2      101        10
3     3      101        10
4     4      102        12
5     5      103        12
6     6       99        12
7     7       98        12
8     8       99        13
9     9       89        13
10   10       70        13
user_119081070
Related