fill variable cells by id in long format data.table in r

user_13237975 0
Viewed 89

I have a table in long format where data that is constant over time appears just once by id, like this

dt<-data.table(
      ID=c(rep(1,3),rep(2,3)),
      Time=rep(1:3,2),
      Sex=c("M",NA,NA,"F",NA,NA),
      V1=c(1.1,1.2,1.3,2.1,2.2,2.3)
    )

dt
   ID Time  Sex  V1
1:  1    1    M 1.1
2:  1    2 <NA> 1.2
3:  1    3 <NA> 1.3
4:  2    1    F 2.1
5:  2    2 <NA> 2.2
6:  2    3 <NA> 2.3

and I want to fill the missing values in Sex with the values of Sex when Time==1, that is, to repeat values across Time moments by ID, so that dt become

   ID Time Sex  V1
1:  1    1   M 1.1
2:  1    2   M 1.2
3:  1    3   M 1.3
4:  2    1   F 2.1
5:  2    2   F 2.2
6:  2    3   F 2.3

How can I achieve that?

Thanks

data.table r
2 Answers

We can group by ID and fill the 'Sex' column

library(dplyr)
library(tidyr)
dt %>%
  group_by(ID) %>%
  fill(Sex)

Or with data.table, grouped by 'ID', subset the 'Sex' where 'Time' is 1 and assign (:=) it to the same column

library(data.table)
dt[,  Sex := Sex[Time == 1], ID]

-output

dt
#   ID Time Sex  V1
#1:  1    1   M 1.1
#2:  1    2   M 1.2
#3:  1    3   M 1.3
#4:  2    1   F 2.1
#5:  2    2   F 2.2
#6:  2    3   F 2.3
user_37322710

We could use na.locf altervatively from zoo package

library(zoo)
dt$Sex <- na.locf(dt$Sex)

Output:

   ID Time Sex  V1
1:  1    1   M 1.1
2:  1    2   M 1.2
3:  1    3   M 1.3
4:  2    1   F 2.1
5:  2    2   F 2.2
6:  2    3   F 2.3
user_133216470
Related