How to create variable that is based on which data frame the values came from in R?

Question

I will merge the below two df's in ascending order by time, non-duplicating. My goal is to also have two new variables.

df1
   time   freq                  
1   1.5    1
2   3.5    1
3   4.5    2
4   5.5    1
5   8.5    2
6   9.5    1
7  10.5    1
8  11.5    1
9  15.5    1
10 16.5    1
11 18.5    1
12 23.5    1
13 26.5    1

df2
  time freq
1  0.5    6
2  2.5    2
3  3.5    1
4  6.5    1
5 15.5    1

Please help me with the code for creating the two new columns:

1. Where if the freq value corresponds to a time in df1, then a new variable (var1) would record the associated freq value, AND 0 if no such time value exists for df1.

2. Where if the freq value corresponds to a time in df2, then a second new variable (var2) would record that freq value from df2, AND 0 if no such time value exists for df2.

So I would have a table like this below:

time var1 var2
0.5   0    6
1.5   1    0
2.5   0    2
3.5   1    1
4.5   2    0
5.5   1    0
...

Answer 1

If I understood how your dataframe looks like correctly (something that would be created through:)

df1 = data.frame(time = c(1.5, 3.5, 4.5, 5.5, 8.5, 9.5, 10.5, 11.5, 15.5, 16.5, 18.5, 23.5, 26.5), freq = c(1, 1, 2, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1))
df2 = data.frame(time = c(0.5, 2.5, 3.5, 6.5, 15.5), freq = c(6, 2, 1, 1, 1))

Then you would get what you are looking for by:

df_new = data.frame(time = sort(unique(c(df1$time, df2$time))), var1 = sapply(sapply(time, function(x) {df1$freq[df1$time == x]}), function(x) {ifelse(length(x) == 0, 0, x)}), var2 = sapply((sapply(time, function(x) {df2$freq[df2$time == x]})), function(x) {ifelse(length(x) == 0, 0, x)}))

Hope this helps,

Answer 2

Code - base R

df3 <- merge(x = df1, df2, by.x = 'time', by.y = 'time', all = TRUE, sort = TRUE)
df3$freq.x[is.na(df3$freq.x)] <- 0
df3$freq.y[is.na(df3$freq.y)] <- 0

Code - data.table library

library('data.table')
setDT(df1)  
setkey(df1, time)
df3 <- merge(x = df1, df2, all = TRUE, sort = TRUE)
df3[is.na(freq.x), freq.x := 0 ]
df3[is.na(freq.y), freq.y := 0 ]

Output

df3
#    time freq.x freq.y
# 1:  0.5      0      6
# 2:  1.5      1      0
# 3:  2.5      0      2
# 4:  3.5      1      1
# 5:  4.5      2      0
# 6:  5.5      1      0
# 7:  6.5      0      1
# 8:  8.5      2      0
# 9:  9.5      1      0
# 10: 10.5      1      0
# 11: 11.5      1      0
# 12: 15.5      1      1
# 13: 16.5      1      0
# 14: 18.5      1      0
# 15: 23.5      1      0
# 16: 26.5      1      0

Data

df1 <- read.table(text = 
'time   freq                  
1   1.5    1
2   3.5    1
3   4.5    2
4   5.5    1
5   8.5    2
6   9.5    1
7  10.5    1
8  11.5    1
9  15.5    1
10 16.5    1
11 18.5    1
12 23.5    1
13 26.5    1', header = TRUE, stringsAsFactor = FALSE)

df2 <- read.table(text = 
'time freq
1  0.5    6
2  2.5    2
3  3.5    1
4  6.5    1
5 15.5    1', header = TRUE, stringsAsFactor = FALSE)

Answer 3

A more straightforward approach using tidyverse or dplyr:

library(tidyverse)

df1 <- tibble(time = c(1.5, 3.5, 4.5, 5.5), freq = c(1, 1, 2, 1))
df2 <- tibble(time = c(0.5, 2.5, 3.5, 6.5), freq = c(6, 2, 1, 1))

full_join(df1, df2, by = "time", suffix = c("_1", "_2")) %>% 
  mutate_all(~ .x %>% replace_na(0)) %>% 
  arrange(time)

# A tibble: 7 x 3
   time freq_1 freq_2
  <dbl>  <dbl>  <dbl>
1   0.5      0      6
2   1.5      1      0
3   2.5      0      2
4   3.5      1      1
5   4.5      2      0
6   5.5      1      0
7   6.5      0      1