Mutate function-Adding columns

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

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

1 Like

This topic was automatically closed 42 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.