Replace all values with first observation by group

user_10568575 0
Viewed 892

For each group, defined by 'id', I would like to select the value in the first row of columns 'x' and 'y', and replace all subsequent values with that first value.

Some data:

id    Visit   x        y
1      1      0        1
1      2      1        2
1      3      2        8
2      9      1       11
2      10     12      14

I want:

id    Visit   x        y
1      1      0        1
1      2      0        1  # <- x & y replaced with first values of 'id' 1 
1      3      0        1  # 
2      9      1        11  
2      10     1        11 # <- x & y replaced with first values of 'id' 2

I tried this:

df1 <- df %>%
  arrange(id, Visit) %>%
  group_by(id) %>%
  fill(x, y, 
       .direction = 'down',)

However, that doesn't seem to be doing it. Can someone help?

dplyr dataframe r
6 Answers

A base alternative using duplicated:

df[, c("x", "y")] = df[(i = !duplicated(df$id)), c("x", "y")][cumsum(i), ]
#   id Visit x  y
# 1  1     1 0  1
# 2  1     2 0  1
# 3  1     3 0  1
# 4  2     9 1 11
# 5  2    10 1 11

Using a data.table rolling join to "fill down" the first value within each group (fast on larger data):

library(data.table)
setDT(df)
df[ , c("x", "y") := df[!duplicated(id)][.SD, on = .(id, Visit), .(x, y), roll = Inf]]
df
#    id Visit x  y
# 1:  1     1 0  1
# 2:  1     2 0  1
# 3:  1     3 0  1
# 4:  2     9 1 11
# 5:  2    10 1 11
user_18517120

In case you want a base-R version of @akrun's awesome answer:

df[c("x","y")] <- lapply(df[c("x","y")], function(z) ave(z, df$id, FUN = function(y) y[1]))
df
#   id Visit x  y
# 1  1     1 0  1
# 2  1     2 0  1
# 3  1     3 0  1
# 4  2     9 1 11
# 5  2    10 1 11

(I purposefully avoided using dplyr::first or data.table::first, as that would defeat the point of using this base R version.)

Or a data.table variant:

library(data.table)
setDT(df)
df[, c("x","y") := lapply(.SD[,c("x","y")], first), by = .(id)]

As @Henrik just mentioned, it's (much!) better to use .SDcols here:

df[, c("x","y") := lapply(.SD, first), by = .(id), .SDcols = c("x","y")]
user_33582720

We can use first with across on mutate

library(dplyr)
df %>%
    arrange(id, Visit) %>% 
    group_by(id) %>% 
    mutate(across(c(x, y), first)) %>%
    ungroup
user_37322710

Another base R option

with(
  df,
  cbind(
    df[c("id", "Visit")],
    cbind(x, y)[ave(1:nrow(df), id, FUN = function(x) head(x, 1)), ]
  )
)

gives

  id Visit x  y
1  1     1 0  1
2  1     2 0  1
3  1     3 0  1
4  2     9 1 11
5  2    10 1 11
user_121587570

Using dplyr, head() can also be used

library(dplyr)
df %>%
    group_by(id) %>% 
    mutate(x = head(x, 1), y = head(x,1)) %>%
    ungroup
user_115124000

Another option in base R is to use the split-apply-combine paradigm via split(), lapply(), and do.call() with rbind():

df <- data.frame(id = c(1,1,1,2,2), Visit = c(1,2,3,9,10), x = c(0,1,2,1,12), y = c(1,2,8,11,14))

df <- do.call(rbind, 
        lapply(split(df, df$id), 
          function(z) {
                z$x <- z[1, "x"]
                z$y <- z[1, "y"]
                return(z)}))
user_43429010
Related