CODEWITHSUNDEEP
r
dzadi
dzadi

Reputation: 137

fill out missing value based on subject ID

I have a large data set like this:

SUB  DAY BASE  
 1    0    .    
 1    0    . 
 1    0    .   
 1    0    .    
 1    1    3.5   
 1    1    3.5  
 1    2    3.5   
 1    2    3.5    
 2    0    .
 2    0    .
 2    0    .
 2    1    2.3
 2    1    2.3
 2    2    2.3
 2    2    2.3
...

I want to fill the missing value in BASE with the value from that SUB. The expected output should look like this:

SUB  DAY BASE  
 1    0    3.5    
 1    0    3.5
 1    0    3.5   
 1    0    3.5    
 1    1    3.5   
 1    1    3.5  
 1    2    3.5   
 1    2    3.5    
 2    0    2.3
 2    0    2.3
 2    0    2.3
 2    1    2.3
 2    1    2.3
 2    2    2.3
 2    2    2.3
...

Does anyone have idea about realizing this?

Upvotes: 0

Views: 102

Answers (1)

Julien Navarre
Julien Navarre

Reputation: 7840

Using merge if your missing values are equals to NA (tricky, probably a better way) :

dat <- read.table(header = T, text = "SUB  DAY BASE  
1    0    NA   
1    0    NA
1    0    NA
1    0    NA   
1    1    3.5   
1    1    3.5  
1    2    3.5   
1    2    3.5    
2    0    NA
2    0    NA
2    0    NA
2    1    2.3
2    1    2.3
2    2    2.3
2    2    2.3")

> merge(subset(dat, select= -BASE), unique(dat[!is.na(dat$BASE),c("SUB", "BASE")]), by = "SUB")
   SUB DAY BASE
1    1   0  3.5
2    1   0  3.5
3    1   0  3.5
4    1   0  3.5
5    1   1  3.5
6    1   1  3.5
7    1   2  3.5
8    1   2  3.5
9    2   0  2.3
10   2   0  2.3
11   2   0  2.3
12   2   1  2.3
13   2   1  2.3
14   2   2  2.3
15   2   2  2.3

Upvotes: 2

Related Questions