Load data
Assuming fd.txt contains the following
N: toto
Y: 2000
S: tata
N: titi
Y: 2004
S: tutu
N: toto
Y: 2000
S: tata2
N: toto
Y: 2000
S: tata3
N: tete
Y: 2002
S: tyty
N: tete
Y: 2002
S: tyty2
here is one solution in R:
tmp <- scan("fd.txt", what="character")
res <- data.frame(matrix(tmp[seq(2, length(tmp), by=2)], nc=3, byrow=TRUE))
The first command read everything a single vector of character, skipping blank lines; then we remove every odd elements ("N:", "S:", "Y:"); finally, we arrange them in a data.frame (this a convenient way to make each column a factor).
The output is
X1 X2 X3
1 toto 2000 tata
2 titi 2004 tutu
3 toto 2000 tata2
4 toto 2000 tata3
5 tete 2002 tyty
6 tete 2002 tyty2
Please note that if you have some GNU utilities on your machine, you can use awk
sed 's/[NYS]: //' fd.txt | awk 'ORS=(FNR%4)?FS:RS' > res.txt
The first command uses sed to filter the descriptor (replace by blank); then awk will produce its output (Output Record Separator) as follows: arrange each record using default Field Separator (space), and put a new Record Separator (new line) every 4 fields. Of note, we could filter the data using awk directly, but I like separating tasks a little.
The result is written in res.txt and can be imported into R using read.table():
toto 2000 tata
titi 2004 tutu
toto 2000 tata2
toto 2000 tata3
tete 2002 tyty
tete 2002 tyty2
Process and transform data
I didn't find a very elegant solution in R, but the following works:
library(plyr)
tmp <- ddply(res, .(X1,X2), mutate, S=list(X3))[,-3]
resf <- tmp[!duplicated(tmp[,1:2]),]
Then, resf has three columns, where column S list the levels of the X3 factor (siblings' name). So, instead of putting siblings in different columns, I concatenated them in a list. In other words,
as.character(resf$S[[1]])
gives you the name of tete's siblings, which are tyty and tyty2.
I'm pretty sure there's a better way to do this with plyr, but I didn't manage to get a nice solution for the moment.
With repeated "S:" motif, here is one possible quick and dirty solution. Say fd.txt now reads
N: toto
Y: 2000
S: tata
S: tata2
S: tata3
N: titi
Y: 2004
S: tutu
N: tete
Y: 2002
S: tyty
S: tyty2
then,
tmp <- read.table("fd.txt")
tmp$V1 <- gsub(":","",tmp$V1)
start <- c(which(tmp$V1=="N"), nrow(tmp)+1)
max.col <- max(diff(start))
res <- matrix(nr=length(start)-1, nc=max.col)
for (i in 1:(length(start)-1))
res[i,1:diff(start[i:(i+1)])] <- t(tmp[start[i]:(start[i+1]-1),])[2,]
res <- data.frame(res)
colnames(res) <- c("name","year",paste("S",1:(max.col-2),sep=""))
will produce
name year S1 S2 S3
1 toto 2000 tata tata2 tata3
2 titi 2004 tutu <NA> <NA>
3 tete 2002 tyty tyty2 <NA>
