write.fwf column names don't line up with values

Question

The following code produces a table whose column names don't line up with its values:

library( gdata )
test0 <- matrix(5:28, nrow = 4) 
row.names(test0) <- paste("r", 1:4, sep = "")
colnames(test0) <- paste("c", 1:6, sep = "")

test0[3, 2] <- 1234567890
test0[ , 3] <- 0.19412341293479123840214

test0 <- format(test0, digits = 5, trim = T, width = 10, scientific = T)

write.fwf(test0, file = paste("test", ".txt", sep = ""), width = 11, rowname = T, colname = T, quote = F)

How can I make column names line up with each column's values (in order to have the table to be readable by GAMS)?

What is the output suppose to look like? Column names aligned to the right of the column? — Roman Luštrik, Aug 15 '17 at 06:16
The output is supposed to be exactly like the table Simon made. I'm using this simple example to try out writing a large table (4000 by 6000, and containing large numbers) that needs to be lined up by column. — Huibin Chang, Aug 17 '17 at 02:51

score 3 · Accepted Answer · answered Aug 15 '17 at 06:21

3

Curious that the column names are not treated in the same way. A work-around could be to add the column names as a row in the table and then write the table without column names...

i.e.

test1 <- rbind( colnames(test0) , test0 )
write.fwf(test1, file = paste("test", ".txt", sep = ""),
          width = 11,
          rownames = T,
          colnames = F,  #Don't print the column names
          quote = F )

This looks like:

answered Aug 15 '17 at 06:21

Simon O'Hanlon

58,647
14
142
184

It worked! I also wonder that in "write.table", is there a way that could write tables containing very large numbers (with long "nchar")? Once there are large nchars, using "\t" in "write.table" produces a table that doesn't line up properly. – Huibin Chang Aug 17 '17 at 02:47

Bruno Adelé · Answer 2 · 2021-12-08T08:04:36.317

I created a small script for saving a dataframe to fwf format with columns aligned with values.

See the latest gist code

suppressPackageStartupMessages({
    library(gdata)
    library(stringr)
})

#' Generate automatically .fwf file (fixed width file) in R
#' @description This function creates automatically fixed width file
#' It align columns headers with datas
#' @param df dataframe   
#' @param filename filename   
#' @param nbspaces nb spaces for columns separator   
#' @param replace_na Empty/NA chain replacement   
#' @param rowname If it's defined, it convert rownames column to named column   
#' @examples write_fwf(mtcars, "carname", "/tmp/mtcars.fwf")
#'
#' # colnames: carname,mpg,cyl,disp,hp,drat,wt,qsec,vs,am,gear,carb
#' # cols: 22,7,6,8,6,7,8,8,5,5,7,7
#' carname                mpg   cyl    disp    hp   drat      wt    qsec   vs   am   gear   carb
#' Mazda RX4             21.0     6   160.0   110   3.90   2.620   16.46    0    1      4      4
#' Mazda RX4 Wag         21.0     6   160.0   110   3.90   2.875   17.02    0    1      4      4
#' Datsun 710            22.8     4   108.0    93   3.85   2.320   18.61    1    1      4      1
#' Hornet 4 Drive        21.4     6   258.0   110   3.08   3.215   19.44    1    0      3      1
write_fwf <- function(df, filename,rowname = FALSE,nbspaces = 3, replace_na = "NA") {
  # Convert rownames to column
  if (rowname) {
    df <- tibble::rownames_to_column(df, rowname)
  }

  # Convert all columns to character
  tmpdf = data.frame(df)
  tmpdf[] <- lapply(df, as.character)

  # Compute column size
  nasize=nchar(replace_na)
  maxwidthname <- nchar(colnames(tmpdf))
  maxwidthvalue <- sapply(tmpdf, function(x) max(nchar(x)))
  maxcols <- pmax(maxwidthname,maxwidthvalue,nasize)
  delta <- maxwidthvalue - maxwidthname 

  # Compute header
  header <- c()
  for (idx in seq(ncol(df))) {
    if (is.character(df[,idx])) {
      header <- append(header,paste0(colnames(df)[idx],strrep(" ",max(delta[idx],0))))
    } else {
      header <- append(header,paste0(strrep(" ",max(delta[idx],0)), colnames(df)[idx]))
    }
  }

  # Open file
  file <- file(filename, "w")
  
  # Write header
  writeLines(paste("# colnames:", paste(colnames(df), collapse=',')),file)
  writeLines(paste("# cols:", paste(unlist(maxcols+nbspaces), collapse=',')),file)
  writeLines(header,file, sep=strrep(" ",nbspaces))
  writeLines("", file, sep="\n")
  close(file)
  
  # Export data
  write.fwf(
    df,
    file=filename,
    append=TRUE, 
    width=maxcols,
    colnames=FALSE,
    na=replace_na, 
    sep=strrep(" ",nbspaces),
    justify="left"
  )
}

#' Read automatically .fwf file (fixed width file) in R
#' @description This function read and detect automatically fixed width file
#' @param maxsearchlines nb lines for the searching the columns metadata description   
#' @examples read_fwf("/tmp/mtcars.fwf")
read_fwf <- function(filename,maxsearchlines=100) {
  # Search columns informations
  file <- file(filename, "r")
  on.exit(close(file))

  lines <- readLines(file,n=maxsearchlines)

  idxname <- str_which(lines,"# colnames: ")
  colnames <- str_replace(lines[idxname], "# colnames: ", "")
  colnames <- unlist(str_split(colnames, ","))

  idxcols <- str_which(lines,"# cols: ")
  colwidths <- str_replace(lines[idxcols], "# cols: ", "")
  colwidths <- str_split(colwidths, ",")
  colwidths <- strtoi(unlist(colwidths))

  return(read.fwf(file=filename, skip=idxcols+1, col.names = colnames, widths=colwidths,strip.white=TRUE))
}

Sample utilization

write_fwf(mtcars, "carname", "/tmp/mtcars.fwf")

The result

# colnames: carname,mpg,cyl,disp,hp,drat,wt,qsec,vs,am,gear,carb
# cols: 22,7,6,8,6,7,8,8,5,5,7,7
carname                mpg   cyl    disp    hp   drat      wt    qsec   vs   am   gear   carb
Mazda RX4             21.0     6   160.0   110   3.90   2.620   16.46    0    1      4      4
Mazda RX4 Wag         21.0     6   160.0   110   3.90   2.875   17.02    0    1      4      4
Datsun 710            22.8     4   108.0    93   3.85   2.320   18.61    1    1      4      1
Hornet 4 Drive        21.4     6   258.0   110   3.08   3.215   19.44    1    0      3      1
Hornet Sportabout     18.7     8   360.0   175   3.15   3.440   17.02    0    0      3      2
Valiant               18.1     6   225.0   105   2.76   3.460   20.22    1    0      3      1
Duster 360            14.3     8   360.0   245   3.21   3.570   15.84    0    0      3      4

write.fwf column names don't line up with values

2 Answers2