CODEWITHSUNDEEP
rdataframetidyverse
bwilden
bwilden

Reputation: 21

How can I read in an ASCII data file in R where the rows are stacked columns?

I am trying to read in a weird ASCII data file that looks like this in the raw .txt:

01  1 55  0  0  0  1  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
02  1 55  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  7  0  
01  2 55  0 20  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
02  2 55  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 90

The second and third columns are the unique identifiers, and each other value (except the first column) should be its own variable. So in the end, I want the data to look like:

1 55  0  0  0  1  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  7  0
2 55  0 20  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0 90

I am trying to use tidyr::pivot_wider but not having much luck.

Upvotes: 2

Views: 130

Answers (1)

Allan Cameron
Allan Cameron

Reputation: 174348

It's not clear what format you want the end result in. It looks from your output that you want something like a two-row matrix with the id variables as the row names?

myfile <- 'D:\\mydata.txt'

data <- readLines(myfile)

vectors <- lapply(strsplit(data, '\\s+'), as.numeric)

ids <- sapply(vectors, function(x) paste(x[2], x[3]))

t(as.data.frame(lapply(split(vectors, ids), function(x) {
  do.call(c, lapply(x, function(y) y[-(1:3)]))}), check.names = FALSE))
#>      [,1] [,2] [,3] [,4] [,5] [,6] [,7] [,8] [,9] [,10] [,11] [,12] [,13] [,14]
#> 1 55    0    0    0    1    0    0    0    0    0     0     0     0     0     0
#> 2 55    0   20    0    0    0    0    0    0    0     0     0     0     0     0
#>      [,15] [,16] [,17] [,18] [,19] [,20] [,21] [,22] [,23] [,24] [,25] [,26]
#> 1 55     0     0     0     0     0     0     0     0     0     0     0     0
#> 2 55     0     0     0     0     0     0     0     0     0     0     0     0
#>      [,27] [,28] [,29] [,30] [,31] [,32] [,33] [,34] [,35] [,36] [,37] [,38]
#> 1 55     0     0     0     0     0     0     0     0     0     0     0     0
#> 2 55     0     0     0     0     0     0     0     0     0     0     0     0
#>      [,39] [,40] [,41] [,42] [,43]
#> 1 55     0     0     0     7     0
#> 2 55     0     0     0     0    90

Created on 2022-09-27 with reprex v2.0.2

Upvotes: 1

Related Questions