fill out missing value based on subject ID

Question

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?

Can you provide a reproducible example? http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — Roman Luštrik, Mar 31 '14 at 15:35
Hi Carl, yes, the BASE value for a given SUB value is always unique — dzadi, Mar 31 '14 at 16:43

score 2 · Accepted Answer · answered Mar 31 '14 at 16:02

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

This also worked with `.` by using `dat$BASE != '.'` – SethB Mar 31 '14 at 16:07 — SethB, Mar 31 '14 at 16:07

fill out missing value based on subject ID

1 Answers1