With no sample data provided, this is my best guess at what you're attempting to accomplish. This post has tips on how to ask your question more effectively: Welcome to the RStudio Community!
# package libraries
library(tidyverse)
#> Warning: package 'tidyverse' was built under R version 4.2.2
#> Warning: package 'ggplot2' was built under R version 4.2.3
#> Warning: package 'tibble' was built under R version 4.2.3
#> Warning: package 'tidyr' was built under R version 4.2.2
#> Warning: package 'readr' was built under R version 4.2.2
#> Warning: package 'purrr' was built under R version 4.2.2
#> Warning: package 'dplyr' was built under R version 4.2.3
#> Warning: package 'stringr' was built under R version 4.2.2
#> Warning: package 'forcats' was built under R version 4.2.2
#> Warning: package 'lubridate' was built under R version 4.2.2
# sample data
set.seed(12)
table_x <- tibble(
id = as.character(seq(1, 100, 1)),
tms = sample(x = seq(360, 480, 1),size = 100, replace = T) # total minutes asleep
)
table_x
#> # A tibble: 100 × 2
#> id tms
#> <chr> <dbl>
#> 1 1 425
#> 2 2 449
#> 3 3 439
#> 4 4 478
#> 5 5 450
#> 6 6 405
#> 7 7 428
#> 8 8 428
#> 9 9 451
#> 10 10 393
#> # ℹ 90 more rows
table_y <- tibble(
id = as.character(seq(1, 100, 1)),
tms = sample(x = seq(60, 180, 1),size = 100, replace = T) # total minutes asleep
)
table_y
#> # A tibble: 100 × 2
#> id tms
#> <chr> <dbl>
#> 1 1 135
#> 2 2 126
#> 3 3 64
#> 4 4 145
#> 5 5 101
#> 6 6 75
#> 7 7 118
#> 8 8 97
#> 9 9 77
#> 10 10 125
#> # ℹ 90 more rows
# using full_join instead of merge
merged_table <- full_join( # keep both x and y
x = table_x,
y = table_y,
by = "id" # join by
)
merged_table
#> # A tibble: 100 × 3
#> id tms.x tms.y
#> <chr> <dbl> <dbl>
#> 1 1 425 135
#> 2 2 449 126
#> 3 3 439 64
#> 4 4 478 145
#> 5 5 450 101
#> 6 6 405 75
#> 7 7 428 118
#> 8 8 428 97
#> 9 9 451 77
#> 10 10 393 125
#> # ℹ 90 more rows
# transform - mutate new variables
m_table_new_var <- merged_table %>%
group_by(id) %>% # mutate at level of id
mutate(
tms_mean = mean(c(tms.x, tms.y)), # calculate mean for variables specified
tms_med = median(c(tms.x, tms.y)) # calculate median for variables specified
) %>%
ungroup()
m_table_new_var
#> # A tibble: 100 × 5
#> id tms.x tms.y tms_mean tms_med
#> <chr> <dbl> <dbl> <dbl> <dbl>
#> 1 1 425 135 280 280
#> 2 2 449 126 288. 288.
#> 3 3 439 64 252. 252.
#> 4 4 478 145 312. 312.
#> 5 5 450 101 276. 276.
#> 6 6 405 75 240 240
#> 7 7 428 118 273 273
#> 8 8 428 97 262. 262.
#> 9 9 451 77 264 264
#> 10 10 393 125 259 259
#> # ℹ 90 more rows
Created on 2023-06-07 with reprex v2.0.2