Importing multiple lines of same observation in R

Question

I'm trying to import data that looks something like this

ID,time1,time2,time3,time4,time5,time6,time7,time8,time9,time10,
123456,
0.123425,0.543565,0.43543,0.34567,0.76543,12345,43567,43567,324567,324567,
87654,
0.14567,0.543123,0.435987,0.5675,0.58843,.5543,.567,.3567,.24567,.533367,
32156,

I've tried importing it like this:

tmp <- read.csv(file, header = TRUE, sep = ",")

I've tried read.table as well. However, once I try importing it, ID is given it's own observation with all other variables marked missing. The next line of data bumps time1 into ID, time2 into time1, and so on.

It comes out looking like:

ID       time1     time2    time3   .....
123456
0.123435 0.543565  0.43543  0.34567
87654
0.14567  0.543123  0.435987 0.5675

I want my output to look like this:

ID     time1    time2    time3   ......
123456 0.123425 0.543565 0.43543
87654  0.14567  0.543123 0.435987
32156

tmp <- read.csv(file, header = TRUE, sep = ",") I've tried read.table as well. — Such, Feb 04 '16 at 16:14
Is that the output of read.csv? Or would you like to show the output of the same? — dikesh, Feb 04 '16 at 16:19
The problem is with the data. It has a newline after the `ID` field. The newline must be *after* the complete data row. — Bhargav Rao, Feb 04 '16 at 16:25
Use Notepad++ to remove the \n http://stackoverflow.com/questions/2668678/importing-csv-with-line-breaks-in-excel-2007 — Scott, Feb 04 '16 at 17:09

score 2 · Accepted Answer · answered Feb 04 '16 at 16:40

You can make a workaround in R, assuming that all data are structured similarly. For this example, I have assumed that there is an even number of rows in the imported dataset (I have removed the fifth row for testing).

#remove empty column
dat <- dat[,1:11]

#create vector of identifiers to split by, each id repeated twice
ID2 <- rep(1:(nrow(dat)/2), each=2)

Then we split the data by our identifier, and extract the data we need. That is the first value from the first row, and all values from the second row (bar the final empty one). Then we give the vector the column names of the original data and return it.

res <- lapply(split(dat,ID2), function(x){
  res <- c(x[1,1],x[2,-ncol(x)]) #remove final empty column
  names(res) <- colnames(dat)
  res
})

Finally, we bind it together

output <- do.call(rbind,res)

> output
  ID     time1    time2    time3    time4   time5   time6  time7 time8  time9   time10  
1 123456 0.123425 0.543565 0.43543  0.34567 0.76543 12345  43567 43567  324567  324567  
2 87654  0.14567  0.543123 0.435987 0.5675  0.58843 0.5543 0.567 0.3567 0.24567 0.533367

Importing multiple lines of same observation in R

1 Answers1