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.