[R] how to read a df like that and transform it?

[arun]

Hi,

If the `spaces` in "father", "mother", "num_daughter" columns needs to be replaced by the values in the previous row, 
 dat1<-read.table(text="
father, mother, num_daughter, daughter
291, 3906, 0, 
275, 4219, 0, 
273, 4236, 1, 49410
281, 4163, 1, 49408
274, 4226, 1, 49406
295, 3869, 2, 49403
   , ,,    49404
287, 4113, 0 
295, 3871, 1, 49401
292, 3895, 4, 49396
   ,,,    49397
   ,,,    49398
   ,,,    49399
291, 3900, 3, 49392
",sep=",",header=T,fill=TRUE)

library(zoo)
 dat2<-data.frame(na.locf(dat1[,1:3]),daughter=dat1[,4])
dat2Sub<-dat2[rep(which(dat2[,3]==3),2),1:3]
 dat2Sub$daughter<-NA
dat3<-rbind(dat2,dat2Sub)
dat3$num_daughter[dat3$num_daughter>1]<-ave(dat3$num_daughter[dat3$num_daughter>1],dat3$num_daughter[dat3$num_daughter>1],FUN=seq_along)
reshape(dat3,v.names="daughter",idvar=c("father","mother"),timevar="num_daughter",direction="wide")
 #  father mother daughter.0 daughter.1 daughter.2 daughter.3 daughter.4
#1     291   3906         NA         NA         NA         NA         NA
#2     275   4219         NA         NA         NA         NA         NA
#3     273   4236         NA      49410         NA         NA         NA
#4     281   4163         NA      49408         NA         NA         NA
#5     274   4226         NA      49406         NA         NA         NA
#6     295   3869         NA      49403      49404         NA         NA
#8     287   4113         NA         NA         NA         NA         NA
#9     295   3871         NA      49401         NA         NA         NA
#10    292   3895         NA      49396      49397      49398      49399
#14    291   3900         NA      49392         NA         NA         NA
A.K.





----- Original Message -----
From: Yao He <yao.h.1988 at gmail.com>
To: R help <r-help at r-project.org>
Cc: 
Sent: Wednesday, January 23, 2013 7:42 AM
Subject: [R] how to read a df like that and transform it?

Dear all

I have a data.frame like that :

father    mother    num_daughter    daughter
291    3906    0    NULL
275    4219    0    NULL
273    4236    1    49410
281    4163    1    49408
274    4226    1    49406
295    3869    2    49403
                    49404
287    4113    0    NULL
295    3871    1    49401
292    3895    4    49396
                    49397
                    49398
                    49399
291    3900    3    49392

How to read it into R and transform it like that:

father mother    num_daughter   daughter1  daughter2  daughter3 daughter4
291    3906    0    NULL
275    4219    0    NULL
273    4236    1    49410
281    4163    1    49408
274    4226    1    49406
295    3869    2    49403      49404
287    4113    0    NULL
295    3871    1    49401
292    3895    4    49396      49397    49398   49399
291    3900    3    49392

library (plyr) and library (reshape2) and other good packages are  OK for me.

Thanks a lot!

Yao He
—————————————————————————
Master candidate in 2rd year
Department of Animal genetics & breeding
Room 436,College of Animial Science&Technology,
China Agriculture University,Beijing,100193
E-mail: yao.h.1988 at gmail.com
——————————————————————————

______________________________________________
R-help at r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.