0

I am trying to convert data from long format to wide format using spread function in R, it works but not correctly and transforms dataset wrongly. 

dbv_fc1<-read.csv("dbtest_forum.csv",header = TRUE,na.strings=0)
data_new <- dbv_fc1 %>% spread(test, value)

My dataset contains four columns

original data

But when it does the transforming into wide format on the basis of just two columns, it moved values of tests with same dates into new rows rather than same row but in respective columns as shown below.

After transformation

It shows that in ID A6 where dates are same it moves values into next row of corresponding column which I don't want.

Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
Usman
  • 29
  • 5
  • 1
    Please read instructions at top of R tag page regarding how to post a question: [tag:r] – G. Grothendieck Nov 02 '19 at 12:10
  • I am not in front of my laptop but perhaps you need `reshape` instead of `spread`? Haven't tested but something like this: `reshape(dbv_fc1,v.names = "test", timevar = "value", id.var = "ID", direction = "wide")` – Vitali Avagyan Nov 02 '19 at 13:19
  • What is the result you are looking for? you could use `dput(head(dbv_fc1, 10))` to provide a minumum example – Derek Corcoran Nov 02 '19 at 13:20
  • Hi Vitali, thanks for the suggestion I will give it a try. (Uwe keim) As i said, code was moving rows with same date into next row. We call that partially working. – Usman Nov 02 '19 at 15:45
  • Hi Derek, I am looking to transform data from long format to wide but my code moves values of id A6 into new row where date is same. Ideally it should display it in first row under it respective column. – Usman Nov 02 '19 at 15:52
  • Hi Vitali, I applied following line as per your suggestion with slight change. In which I added wide1 <- reshape(dbv_fc1, idvar=c("id","tdate"), v.names = "value", timevar = "test", direction="wide") It transformed into right format but ignoring some information and gives some warnings – Usman Nov 02 '19 at 20:32
  • Warning messages: (few of them) 1: In reshapeWide(data, idvar = idvar, timevar = timevar, varying = varying, : there are records with missing times, which will be dropped. 2: In reshapeWide(data, idvar = idvar, timevar = timevar, varying = varying, : multiple rows match for test=1: first taken 3: In reshapeWide(data, idvar = idvar, timevar = timevar, varying = varying, : multiple rows match for test=3: first taken – Usman Nov 02 '19 at 20:33
  • In my dataset where a6 17MAR2011:00:00:00 8 0.09 it transforms till a6 where test=7 then jumps to 9 instead of 8 – Usman Nov 02 '19 at 20:33

0 Answers0