-1

here is my dataset enter image description here

What I am trying to do is transpose the last 4 columns every time there is a new Tube (or new Colony or new Sample) Therefore transforming into sth like this: enter image description here

I ve been trying to do this in R, but I am open to other suggestions, any help is greatly appreciated!

Panos
  • 179
  • 2
  • 13
  • sorry, let me clarify better: when I am saying "every time there is a new Tube (or new Colony or new Sample)" i mean everytime the value changes in the column named "Tube" or the column name "Colony" – Panos Feb 03 '19 at 10:53
  • 4
    You should give a minimal [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) which would make it much easier for others to help you. – markus Feb 03 '19 at 11:02
  • 1
    Also, what is your end objective with the new data structure? Perhaps that could be met with a simpler restructuring or perhaps no restructuring at all. – SteveM Feb 03 '19 at 11:05

1 Answers1

0

ok found a solution (for those who might have a similar problem in the future)

in the sample data below, I assume that columns 2 and 3 must remain, while columns 4-6 need to be transposed.

I used the "unstack" which made the task pretty easy, probable there is an even easier way around this, but this worked quite ok for me, so I ll just leave it there.

create data

x <- data.frame("SN" = 1:24, "Tube" = c(1,1,1,1,1,1,1,1,3,3,3,3,3,3,3,3,5,5,5,5,5,5,5,5), 
            "Name" = c("John","John","John","John","John","John","John","John","Dora","Dora","Dora","Dora","Dora","Dora","Dora","Dora","Nick","Nick","Nick","Nick","Nick","Nick","Nick","Nick"))
x$CP1 <- sample(40, size = nrow(x), replace = TRUE)
x$CP2 <- sample(40, size = nrow(x), replace = TRUE)
x$CP3 <- sample(40, size = nrow(x), replace = TRUE)

conversion

tmp1 <- data.frame(CP1=x$CP1, ind=rep(1:8, nrow(x)/8))
tmp2 <- data.frame(CP2=x$CP2, ind=rep(1:8, nrow(x)/8))
tmp3 <- data.frame(CP3=x$CP3, ind=rep(1:8, nrow(x)/8))
new1=unstack(tmp1, CP1~ind)
new2=unstack(tmp2, CP2~ind)
new3=unstack(tmp3, CP3~ind)

z=unique(x[2:3])
tmp1=cbind(z,new1)
tmp2=cbind(z,new2)
tmp3=cbind(z,new3)
new.data=rbind(tmp1,tmp2,tmp3)
Community
  • 1
  • 1
Panos
  • 179
  • 2
  • 13