I am trying to merge rows that have duplicated entries in the ID column, by retrieving the means of each column.
In dplyr::distinct()'s documentation it states that distinct can take a data-masking function as input and return new values for the resulting column.
I used an example from the help pages. However, I am unable to get it to work properly, and could use some help with the syntax.
library(dplyr)
a <- data.frame(
"ID" = rep(1:3, each = 2) %>% as.numeric(),
"x" = rep(1:3, each = 2) %>% as.numeric(),
"y" = 1:6 %>% as.numeric()
)
# Original dataframe
a
A data.frame: 6 × 3
ID x y
<dbl> <dbl> <dbl>
1 1 1
1 1 2
2 2 3
2 2 4
3 3 5
3 3 6
The example from the Help pages:
grouped_mean <- function(df, var, ...) {
df %>%
group_by(...) %>%
summarise(mean = mean({{ var }}, na.rm = TRUE))
}
distinct(a, ID, grouped_mean(a,ID), .keep_all = TRUE)
# Output
A data.frame: 3 × 4
ID x y mean
<dbl> <dbl> <dbl> <dbl>
1 1 1 2
2 2 3 2
3 3 5 2
Instead of evaluating column means for each ID, it creates a new column with called mean.
I tried adding a piece of code I have used earlier for calculating grouped column means in a function:
row_mean <- function(df, var, ...) {
summarise(
across(
.cols = everything(),
.fns = ~ mean(as.numeric(.x),
na.rm = TRUE)
)
)}
distinct(a, ID, row_mean(a,ID), .keep_all = TRUE)
A data.frame: 3 × 3
ID x y
<dbl> <dbl> <dbl>
1 1 1
2 2 3
3 3 5
However, it only returns the first row of each ID value, as the default function does.
What I want:
A data.frame: 3 × 3
ID x y
<dbl> <dbl> <dbl>
1 1 1.5 # <-- column means for each unique ID
2 2 3.5 # <--
3 3 5.5 # <--
Any suggestions?