0

I have a problem. I have one time series and I have the values from other data. Now I just want to add 2nd data values into the time series in the new column. for better understanding, I am attaching an example down. the data that I have

time     value     
09:00    14  
09:10    25
09:13    44
09:16    33
09:24    22
09:30    55
09:45    33
09:55    45
10:00    22
10:05    33

time    value
09:13    22
09:30    33
09:45    22

So, I have data like that but more than 2 thousand rows. I just want to know how we can use 2 different data and put them at the exact same time to get one data frame with 3 columns. for example, my output should be like that

time     value   values2 
09:00    14        0
09:10    25        0
09:13    44        22
09:16    33        0
09:24    22        0
09:30    55        33
09:45    33        22
09:55    45        0
10:00    22        0
10:05    33        0
CH_A_M
  • 33
  • 7
  • Try using the `merge()` function, e.g. df3 <- merge(df1, df2, all=T). Note that you should rename the `value`-column of your second data.frame before merging, e.g. `names(df2) <- c("time", "value2")`. – JKupzig Dec 29 '21 at 17:20
  • thanks. It worked. I was looking for that command. thanks a lot for your answer. – CH_A_M Dec 30 '21 at 10:52

1 Answers1

0

Base R:

df.merged <- merge(df1, df2, by = "time", all = TRUE)

 df.merged
     time value.x value.y
 1: 09:00    14     NA
 2: 09:10    25     NA
 3: 09:13    44     22
 4: 09:16    33     NA
 5: 09:24    22     NA
 6: 09:30    55     33
 7: 09:45    33     22
 8: 09:55    45     NA
 9: 10:00    22     NA
10: 10:05    33     NA

with dplyr:

library(dplyr)
df.joined <- df1 %>% 
  left_join(df2, by = "time")

 df.joined
     time value.x value.y
 1: 09:00    14     NA
 2: 09:10    25     NA
 3: 09:13    44     22
 4: 09:16    33     NA
 5: 09:24    22     NA
 6: 09:30    55     33
 7: 09:45    33     22
 8: 09:55    45     NA
 9: 10:00    22     NA
10: 10:05    33     NA

To match the names of your expected output, with base:

colnames(df2)[2] <- "value2" 
df.merged <- merge(df1, df2, by = "time", all = TRUE)

df.merged
     time value value2
 1: 09:00    14     NA
 2: 09:10    25     NA
 3: 09:13    44     22
 4: 09:16    33     NA
 5: 09:24    22     NA
 6: 09:30    55     33
 7: 09:45    33     22
 8: 09:55    45     NA
 9: 10:00    22     NA
10: 10:05    33     NA

or programmatically with dplyr:

df.joined <- df2 %>%
  rename_with(function(x) paste0(x, which(colnames(.)==x)), .cols = -time) %>% 
  right_join(df1, by = "time") %>% 
  arrange(time) %>% 
  select(time, value, everything())

df.joined

     time value value2
 1: 09:00    14     NA
 2: 09:10    25     NA
 3: 09:13    44     22
 4: 09:16    33     NA
 5: 09:24    22     NA
 6: 09:30    55     33
 7: 09:45    33     22
 8: 09:55    45     NA
 9: 10:00    22     NA
10: 10:05    33     NA
Mr.Rlover
  • 2,523
  • 3
  • 14
  • 32