Reading data in R from a single column text file

Question

my file named myfile.txt is set up as followed:

Column1
Column2
Column3
...
Column10
Row1
1
2
3
4
Row2
5
6
7
8
...

The rows eventually go to 100 and im having trouble with the read.table command. I'm not an experienced R user so I kinda just need to figure this out and be done with it.

I thought the col.names would look something like:

read.table("myfile.txt", col.names = 1:10)

But that didnt work

Answer 1

An example myfile.txt:

Column1
Column2
Column3
Column4
Row1
1
2
3
4
Row2
5
6
7
8

Read the file and create a matrix:

lin <- scan("myfile.txt", "") # read lines

lin2 <- grep("Column|Row", lin, value = TRUE, invert = TRUE) # values

matrix(as.numeric(lin2), ncol = sum(grepl("Column", lin)), byrow = TRUE)
  # create matrix

     [,1] [,2] [,3] [,4]
[1,]    1    2    3    4
[2,]    5    6    7    8

---

If the first lines are not named Column... but include the actual column names, you can use the following approach:

lin <- scan("myfile.txt", "") # read lines

idx <- grep("^Row", lin) # index of lines containing 'Row'

lin2 <- lin[-(c(seq(1, idx[1] - 1), idx))] # actual values

matrix(as.numeric(lin2), nrow = length(idx), 
       dimnames = list(NULL, lin[seq(1, idx[1] - 1)]), byrow = TRUE)

      Column1 Column2 Column3 Column4
 [1,]       1       2       3       4
 [2,]       5       6       7       8

Answer 2

1. sample data

X <- read.table(text=
"Column1
Column2
Column3
Column10
Row1
1
2
3
4
Row2
5
6
7
8
Row99
1
2
3
4
Row100
5
6
7
8", stringsAsFactors=FALSE)

2. Read in and process

# Some string that does not appear naturally in your data
dummyCollapse <- "\n"  # eg:  "zQz5Nsddfdfjjj"
## Make sure to properly escape the escape char.
dummyFind <- "\\n"  

flat  <- paste(unlist(X), collapse=dummyCollapse)
splat <- strsplit(flat, paste0("Row\\d+", dummyFind))

## strsplit returns a list.  You likely want just the first element
splat <- splat[[1]]

## weed out colnames 
cnms <- splat[[1]]  # now, the first element is the coloumn names from the weird data structure
# split them, also on dummyFind
cnms <- strsplit(cnms, dummyFind) 
# again, only want the first element
cnms <- cnms[[1]]  

## Weed out the rows
rows <- tail(splat, -1)

# split on dummy find
rows <- strsplit(rows, dummyFind)
## NOTE: This time, do NOT take just the first element from strsplit. You want them all

## Combine the data into a matrix
MyData <- do.call(cbind, rows)
## Coerce to data.frame, if you'd like
MyData <- as.data.frame(MyData)
## Add in Column names
colnames(MyData) <- cnms

3. Results

> MyData
  Column1 Column2 Column3 Column10
1       1       5       1        5
2       2       6       2        6
3       3       7       3        7
4       4       8       4        8

4. Send an email to whomever sent you the data in this format and smack them

This should help

Answer 3

Ricardo gives a hint, and here's a way to make it work:

x <- read.table(text="Column1
Column2
Column3
Column10
Row1
1
2
3
4
Row2
5
6
7
8")

Now insert newlines:

(combined <- paste(x[[1]], collapse='\n'))
[1] "Column1\nColumn2\nColumn3\nColumn10\nRow1\n1\n2\n3\n4\nRow2\n5\n6\n7\n8"

Split on Row\d+\n:

(comb.split <- strsplit(combined, 'Row\\d+\\n'))
[[1]]
[1] "Column1\nColumn2\nColumn3\nColumn10\n" "1\n2\n3\n4\n"                          "5\n6\n7\n8"                           

Split these elements on newlines:

(split.list <- strsplit(comb.split[[1]], '\\n'))
[[1]]
[1] "Column1"  "Column2"  "Column3"  "Column10"

[[2]]
[1] "1" "2" "3" "4"

[[3]]
[1] "5" "6" "7" "8"

Force numeric (if applicable):

(numeric.list <- lapply(split.list[-1], as.numeric))
[[1]]
[1] 1 2 3 4

[[2]]
[1] 5 6 7 8

Create the data frame:

dat <- do.call(rbind, numeric.list)
colnames(dat) <- split.list[[1]]
dat
     Column1 Column2 Column3 Column10
[1,]       1       2       3        4
[2,]       5       6       7        8

You do lose the row names here. If you know what they are, you can add them with rownames(dat)<- names.