how to calculate widths for read.fwf if different column has different widths in r

Question

Trying to load file in to r (skipping first 4 lines) http://www.cpc.ncep.noaa.gov/data/indices/wksst8110.for

This is fixed width file and I don't know how to calculate width from file.

Can someone please tell how to load a fixed width file into R?

Answer 1

You can use dyplr::read_fwf

Fix the widths based on the field of vector you want to parse

nao <- read_fwf("https://www.cpc.ncep.noaa.gov/data/indices/wksst8110.for",
                fwf_widths(c(15, 4, 9, 4, 9, 4, 9, 4,4), 
                col_names = c("week",
                              "Nino1+2_sst",
                              "Nino1+2_ssta",
                              "Nino3_sst",
                              "Nino3_ssta",
                              "Nino34_sst",
                              "Nino34_ssta",
                              "Nino4_sst",
                              "Nino4_ssta")),
                skip =4)

Answer 2

I'm an entire newbie in R, so don't be too harsh. I got stuck doing this quiz too and searched for everything I could. Still, I couldn't find a function that would fully programmatically calculate this argument (for example, how would I know in the above comment that there are minuses that should deal with them?). So I wrote myself a simple function doing that. I considered that every new column in the file starting with a symbol, and if the number of symbols in some header is less than the width of the respective column, then empty spaces at the end of the header were added. I do not deny that it works, perhaps awkwardly, but for my task, it helped. Anyway, you are welcome to take a look at my "widths.R" and use it, correct it and so on if you feel so. // example url: https://d396qusza40orc.cloudfront.net/getdata%2Fwksst8110.for or (the same) http://www.cpc.ncep.noaa.gov/data/indices/wksst8110.for //

myurl <- "url"
l <- readLines(myurl)
head(l) ## looking for headers line number
myh <- NUMBER ## WRITE your headers line NUMBER (in my ex. myh <- 4)

widths.fwf <- function(url = myurl, h = myh) ## h: headers line number
    {
    x <- readLines(url, n = h)
    y <- strsplit(x[[h]], "") ## headers line, splitted into characters
    v <- as.vector(y[[1]]) ## vector of headers line characters 
    b <- ifelse(v[[1]] == " ", 0,1) ##binary var: empty (0) and filled (1) places in headers line
    p <- numeric() ## vector to find the places of every header start
    for (i in 2:length(b)) if (b[i] == 0 & b[i+1] == 1) p[i] <- i else p[i] <- 0
    pp <- which(p !=0) ## only places of every header start
    ppp <- numeric() ## to be vector of "widths"
    ppp[1] <- pp[1]
    for(i in 2:length(pp)) ppp[i] <- pp[i] - pp[i-1]
    ppp[length(pp)+1] <- length(p) - pp[length(pp)]
    return(ppp)}

library(foreign)
myppp <- widths.fwf()
t <- read.fwf(myurl, widths = myppp, skip = myh) ## our table ".for"
head(t)

Answer 3

Create a ruler on your console:

cat(">",paste0(rep(c(1:9,"+"),6),collapse=""))

Paste in the first line, then count:

> cat(">",paste0(rep(c(1:9,"+"),6),collapse=""))
> 123456789+123456789+123456789+123456789+123456789+123456789+
> 03JAN1990     23.4-0.4     25.1-0.3     26.6 0.0     28.6 0.3
Error: unexpected symbol in "03JAN1990"

If you look at the file you see that the only places where there are missing whitespaces are the columns with minus-signs. So another way would be to replace all the instances of "-" with " -" , i.e. to create whitespace where it is needed and then read with read.table:

dat <- read.table(text= gsub("\\-", " -", 
                             readLines(url("http://www.cpc.ncep.noaa.gov/data/indices/wksst8110.for"))),
                  skip=4)

> str(dat)
'data.frame':   1284 obs. of  9 variables:
 $ V1: Factor w/ 1284 levels "01APR1992","01APR1998",..: 98 394 689 984 1266 265 560 855 1150 279 ...
 $ V2: num  23.4 23.4 24.2 24.4 25.1 25.8 25.9 26.1 26.1 26.7 ...
 $ V3: num  -0.4 -0.8 -0.3 -0.5 -0.2 0.2 -0.1 -0.1 -0.2 0.3 ...
 $ V4: num  25.1 25.2 25.3 25.5 25.8 26.1 26.4 26.7 26.7 26.7 ...
 $ V5: num  -0.3 -0.3 -0.3 -0.4 -0.2 -0.1 0 0.2 -0.1 -0.2 ...
 $ V6: num  26.6 26.6 26.5 26.5 26.7 26.8 26.9 27.1 27.2 27.3 ...
 $ V7: num  0 0.1 -0.1 -0.1 0.1 0.1 0.2 0.3 0.3 0.2 ...
 $ V8: num  28.6 28.6 28.6 28.4 28.4 28.4 28.5 28.9 29 28.9 ...
 $ V9: num  0.3 0.3 0.3 0.2 0.2 0.3 0.4 0.8 0.8 0.7 ...

You could even skip only the first three lines and get headers:

> dat <- read.table(text= gsub("\\-", " -", readLines(url("http://www.cpc.ncep.noaa.gov/data/indices/wksst8110.for"))),
                   header=TRUE, skip=3)
> str(dat)
'data.frame':   1284 obs. of  9 variables:
 $ Week  : Factor w/ 1284 levels "01APR1992","01APR1998",..: 98 394 689 984 1266 265 560 855 1150 279 ...
 $ SST   : num  23.4 23.4 24.2 24.4 25.1 25.8 25.9 26.1 26.1 26.7 ...
 $ SSTA  : num  -0.4 -0.8 -0.3 -0.5 -0.2 0.2 -0.1 -0.1 -0.2 0.3 ...
 $ SST.1 : num  25.1 25.2 25.3 25.5 25.8 26.1 26.4 26.7 26.7 26.7 ...
 $ SSTA.1: num  -0.3 -0.3 -0.3 -0.4 -0.2 -0.1 0 0.2 -0.1 -0.2 ...
 $ SST.2 : num  26.6 26.6 26.5 26.5 26.7 26.8 26.9 27.1 27.2 27.3 ...
 $ SSTA.2: num  0 0.1 -0.1 -0.1 0.1 0.1 0.2 0.3 0.3 0.2 ...
 $ SST.3 : num  28.6 28.6 28.6 28.4 28.4 28.4 28.5 28.9 29 28.9 ...
 $ SSTA.3: num  0.3 0.3 0.3 0.2 0.2 0.3 0.4 0.8 0.8 0.7 ...