Mutate function-Adding columns

[Panagiotis_Emmanouil]

Hi,
I am trying to merge 2 tables in R using the merge function and keeping all columns in both tables:
SleepwithCalories<- merge(x=daily_activity_summary_all_KPI,y=sleepday,buy=daily_activity_summary_all_KPI$Id,all.x = TRUE)
The problem starts when I want to add 2 columns with average and median using the mutate function because the graph produced by existing variable does not make sense.
I use the following:
SleepwithCaloriesKPI<- SleepwithCalories%>%
group_by(Id)%>%
drop_na()%>%
mutatte(TotalMinutesAsleepAvg=mean(TotalMinutesAsleep),
TotalMinutesAsleepMedian=median(TotalMinutesAsleep))
The problem is that there is only 1 unique value . Could someone help with that issue?
Thanks
Panos

[rene_at_coco]

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}