How to properly read fixed-width format files

Question

Relatively new to R and R Studio and I'm trying to reformat a text file to run some analysis on the data within it. I'm currently trying to use read.fwf to tidy the data but seem to be doing something wrong, resulting in various errors. I've attempted to resolve these problems by referring to sources/guides but am still pretty stuck. Any suggestions? (code, examples of the information in the text file, and the desired format are below).

Current Code:

library(readr)
library(tidyr)
read.fwf("AK_JAN_2017_TMAS_", widths =c(1,2,6,1,1,2,2,2,2,2,3,4,2,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3), header = false, sep = "", row.names = c("Record Type", "FIPS", "Station ID", "Direction of Travel code", "Lane of Travel", "Year of Data", "Month of Data", "Day of Data", "Hour of Data", "Vehicle Class", "Open", "Total Weight of Vehicle", "Number of Axles", "A-axle Weight", "A-B Axle Spacing", "B-axle Weight", "B-C Axle Spacing", "C-axle Weight", "C-D Axle Spacing", "D-axle Spacing", "D-E Axle Spacing", "E-axle Weight", "E-F Axle Spacing", "F-axle Weight", "F-G Axle Spacing", "G-axle Weight", "G-H Axle Spacing", "H-axle Weight", "H-I Axle Spacing", "I-axle Weight", "I-J Axle Spacing", "J-axle Weight", "J-K Axle Spacing", "K-axle Weight", "K-L Axle Spacing", "L-axle Weight", "L-M Axle Spacing", "M-axle Weight"), col.names = NULL, n = -1, buffersize = 2000, fileencoding = "" )

Sample of the text file:

W02000103311701021610061031206057054056013054096054015053015038 W02000103311701021606055024403039038084005121 W02000103311701021609067028505040038054013065104062012064 W02000103311701021705073004302024043019 W02000103311701021710066045606055070075015088094085018086018067 W02000103311701021710080044706052069075015087096083018085018065 W02000103311701021805076007402034056040 W02000103311701021805076004802025043023 W02000103311701021905077002402010051014 W02000103311701021905072004702026042021 W02000103311701021906044020303068053067015068 W02000103311701022006066014803057045049014042 W02000103311701022006053012903058041038014033 W02000103311701022005060003702020043017 W02000103311701022006063009503046047023014026 W02000103311701022105072006602036060030 W02000103311701022206068017703045050059015073 W02000103311701022305065006902033037036 W02000103311701030005066008802032038056 W02000103311701030305066008202037063045

Desired format of the data:

Issues:

 library(readr)
 library(tidyr)
 AK_JAN_2017_TMAS_ <- read.table("~/R Studio Sessions/AK_JAN_2017_TMAS_.txt", quote="\"", comment.char="")
   View(AK_JAN_2017_TMAS_)
 left<-c(2,4,10,11,12,14,16,18,20,22,25,29,31,34,37,40,43,46,49,52,55,58,61,64,67,70,73,76,79,82,85,88,91,94,97,100,103)
 right<-c(3,9,10,11,13,15,17,19,21,24,28,30,33,36,39,42,45,48,51,54,57,60,63,66,69,72,75,78,81,84,87,90,93,96,99,102,105)
 df <- data.frame(matrix(numeric(length(x)*length(left)),ncol=length(left)))
Error in numeric(length(x) * length(left)) : object 'x' not found
 for (i in 1:length(input.set)) {
     stop <- nchar(x[i])
     for (j in 1:length(left)) {
         df[i,j] <- as.numeric(substr(input.set[i], left[j], right[j]))
         if (right[j] ==  stop) break
     }
 }
Error in length(input.set) : object 'input.set' not found
 df <- data.frame(AK_JAN_2017_TMAS_(numeric(length(x)*length(left)),ncol=length(left)))
Error in AK_JAN_2017_TMAS_(numeric(length(x) * length(left)), ncol = length(left)) : 
  could not find function "AK_JAN_2017_TMAS_"
 for (i in 1:length(input.set)) {
     stop <- nchar(x[i])
     for (j in 1:length(left)) {
         df[i,j] <- as.numeric(substr(input.set[i], left[j], right[j]))
         if (right[j] ==  stop) break
     }
 }

 df <- data.frame(matrix(numeric(length(x)*length(left)),ncol=length(left)))
Error in numeric(length(x) * length(left)) : object 'x' not found
 for (i in 1:length('AK_JAN_2017_TMAS_'.set)) {
Error: unexpected symbol in "for (i in 1:length('AK_JAN_2017_TMAS_'.set"
     stop <- nchar(x[i])
Error in nchar(x[i]) : object 'x' not found
     for (j in 1:length(left)) {
         df[i,j] <- as.numeric(substr(input.set[i], left[j], right[j]))
         if (right[j] ==  stop) break
     }
Error in substr(input.set[i], left[j], right[j]) : 
  object 'input.set' not found
 }
Error: unexpected '}' in "}"


And a few changes I attempted to make:

     df <- data.frame(matrix(numeric(length(x)*length(left)),ncol=length(left)))
    for (i in 1:length('AK_JAN_2017_TMAS_'.set)) {
        stop <- nchar(x[i])
        for (j in 1:length(left)) {
            df[i,j] <- as.numeric(substr(input.set[i], left[j], right[j]))
            if (right[j] ==  stop) break
        }
    }

Please provide a [reproducible example in r](//stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). The link I provided, will tell you how. Please take the [tour](https://stackoverflow.com/tour) and review [How to Ask](https://stackoverflow.com/help/how-to-ask), and then edit the question accordingly. You need to provide a [Minimal, Complete, and Verifiable Example](//stackoverflow.com/help/mcve) and show us some efforts. Cheers. — M--, Mar 26 '19 at 19:24
What error message did you get? Are you sure that you want row.names rather than column names and is `false` defined? — Richard Telford, Mar 26 '19 at 19:44
Do you mean `read_fwf` instead of `read.fwf`? Since you're loading `readr`.. — Axeman, Mar 26 '19 at 19:45
I've made some changes to the post reflecting (to a degree) the examples you linked. — TheIllusiveNick, Mar 26 '19 at 19:46

Edward Carney · Answer 1 · 2019-03-28T18:09:45.293

The varying line lengths are likely to be a problem. You can do this line by line as follows:

Construct two lists that show the value boundaries (we skip the "W"). These will be used to extract the substrings for the variables.

left<-c(2,4,10,11,12,14,16,18,20,22,25,29,31,34,37,40,
        43,46,49,52,55,58,61,64,67,70,73,76,79,82,85,88,91,94,97,100,103)

right<-c(3,9,10,11,13,15,17,19,21,24,28,30,33,36,39,42,
         45,48,51,54,57,60,63,66,69,72,75,78,81,84,87,90,93,96,99,102,105)

Loop on the input line generating the numbers from the substrings. You only want to process the last possible value for each to prevent NAs, so a sentinel variable (stop) is created from the number of characters in each data string.

df <- data.frame(matrix(numeric(length(input.set)*length(left)),ncol=length(left)))
for (i in 1:length(input.set)) {
    stop <- nchar(input.set[i])
    for (j in 1:length(left)) {
        df[i,j] <- as.numeric(substr(input.set[i], left[j], right[j]))
        if (right[j] ==  stop) break
    }
}

You can then add names for the columns.

nvals <- c("FIPS","StaID","Dir","Lane","Year","Month","Day","Hour","Class",
           "Open", "TotW", "Axles",
           "AW","ASp","BW","BSp","CW","CSp","DW","DSp",
           "EW","ESp","FW","FSp","GW","GSp","HW","HSp",
           "IW","ISp","JW","JSp","KW","KSp","LW","LSp","MW")
names(df) <- nvals

Here are a couple of lines from the resulting data frame:

  FIPS StaID Dir Lane Year Month Day Hour Class Open TotW Axles AW ASp BW BSp  CW
1    2   103   3    1   17     1   2   16    10   61  312     6 57  54 56  13  54
2    2   103   3    1   17     1   2   16     6   55  244     3 39  38 84   5 121
3    2   103   3    1   17     1   2   16     9   67  285     5 40  38 54  13  65
  CSp DW DSp EW ESp FW FSp GW GSp HW HSp IW ISp JW JSp KW KSp LW LSp MW
1  96 54  15 53  15 38   0  0   0  0   0  0   0  0   0  0   0  0   0  0
2   0  0   0  0   0  0   0  0   0  0   0  0   0  0   0  0   0  0   0  0
3 104 62  12 64   0  0   0  0   0  0   0  0   0  0   0  0   0  0   0  0

I am running into a few problems when trying to input the code above--It could be a simple mistake I am making, I am not sure. I have added my issues at the bottom of the post. — TheIllusiveNick, Mar 28 '19 at 17:20
Essentially, I am running into issues of the object 'x' not being defined in length(x) and input.set not being defined either. — TheIllusiveNick, Mar 28 '19 at 18:06
Sorry. I’ve edited it to change `x` to `input.set`; I failed to change all the occurrences. BTW, you can use the `readLines` function to get the file contents, as in `input.set <- readLines(“filename.txt”)`. — Edward Carney, Mar 28 '19 at 18:15

How to properly read fixed-width format files

1 Answers1