CODEWITHSUNDEEP
rmergerowsrepeat
onhalu
onhalu

Reputation: 745

Merge data with repeating rows in R

I have these two data frames

> df1<-data.frame(day=c(monday,monday,monday,tuesday,tuesday,tuesday,wednesday,wednesday,wednesday),time=c(06:00,06:15,06:30,06:00,06:30,07:00,06:00,06:30,06:45),
>        subject= c(ENG, GER, RUS, ENG, GER, RUS, ENG, GER, RUS))
> 
> 
> df2<-data.frame(time=c(06:00,06:15,06:30,06:45,07:00))





df1
    day       time      subject
    
    monday    06:00     ENG
    monday    06:15     GER
    monday    06:30     RUS
    tuesday   06:00     ENG
    tuesday   06:30     GER
    tuesday   07:00     RUS
    wednesday 06:00     ENG
    wednesday 06:30     GER
    wednesday 06:45     RUS

df2

time

06:00
06:15
06:30
06:45
07:00

And I would like to get this

 df3

 day      time     subject
 monday   06:00    ENG
 monday   06:15    GER
 monday   06:30    RUS
 monday   06:45    NA
 monday   07:00    NA
tuesday   06:00    ENG
tuesday   06:15    NA
tuesday   06:30    GER
tuesday   06:45    NA
tuesday   07:00    RUS
wednesday 06:00    ENG
wednesday 06:15    NA
wednesday 06:30    GER
wednesday 06:45    RUS
wednesday 07:00    NA

I have tried

merge(df1, df2, by = "time", all = TRUE, sort = FALSE)

But that was not the way.

Upvotes: 0

Views: 63

Answers (1)

Onyambu
Onyambu

Reputation: 79188

you could do:

library(tidyverse)

df1 %>% 
   nest_by(day) %>% 
   mutate(data = list(full_join(data, df2,'time'))) %>%
   unnest(data)

# A tibble: 15 x 3
# Groups:   day [3]
   day       time  subject
   <chr>     <chr> <chr>  
 1 monday    06:00 ENG    
 2 monday    06:15 GER    
 3 monday    06:30 RUS    
 4 monday    06:45 NA     
 5 monday    07:00 NA     
 6 tuesday   06:00 ENG    
 7 tuesday   06:30 GER    
 8 tuesday   07:00 RUS    
 9 tuesday   06:15 NA     
10 tuesday   06:45 NA     
11 wednesday 06:00 ENG    
12 wednesday 06:30 GER    
13 wednesday 06:45 RUS    
14 wednesday 06:15 NA     
15 wednesday 07:00 NA

Upvotes: 1

Related Questions