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
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