How to create ascii-only tables as output in R, similar to MySQL style?

Question

I'm trying to find a function for R which outputs data.frame objects in MySQL style ascii tables like this

+----+------+------+
| id | var1 | var2 |
+----+------+------+
|  1 | asdf | g    |
|  2 | asdf | h    |
|  3 | asdf | j    |
+----+------+------+

Is there such function available? There is at least two tools for this

• csvfix asctii_table
• Format Text as a Table

but is there a way to do this directly from R?

Answer 1

Using your data:

d <- data.frame(id = 1:3, va1 = rep("asdf", 3), var2 = c("g","h","j"),
                stringsAsFactors = FALSE)

here is something to get you started, at least and might be sufficient for simple tables.

asciify <- function(df, pad = 1, ...) {
    ## error checking
    stopifnot(is.data.frame(df))
    ## internal functions
    SepLine <- function(n, pad = 1) {
        tmp <- lapply(n, function(x, pad) paste(rep("-", x + (2* pad)),
                                                collapse = ""),
                      pad = pad)
        paste0("+", paste(tmp, collapse = "+"), "+")
    }
    Row <- function(x, n, pad = 1) {
        foo <- function(i, x, n) {
            fmt <- paste0("%", n[i], "s")
            sprintf(fmt, as.character(x[i]))
        }
        rowc <- sapply(seq_along(x), foo, x = x, n = n)
        paste0("|", paste(paste0(rep(" ", pad), rowc, rep(" ", pad)),
                          collapse = "|"),
               "|")
    }
    ## convert everything to characters
    df <- as.matrix(df)
    ## nchar in data
    mdf <- apply(df, 2, function(x) max(nchar(x)))
    ## nchar in names
    cnames <- nchar(colnames(df))
    ## max nchar of name+data per elements
    M <- pmax(mdf, cnames)
    ## write the header
    sep <- SepLine(M, pad = pad)
    writeLines(sep)
    writeLines(Row(colnames(df), M, pad = pad))
    writeLines(sep)
    ## write the rows
    for(i in seq_len(nrow(df))) {
        ## write a row
        writeLines(Row(df[i,], M, pad = pad))
        ## write separator
        writeLines(sep)
    }
    invisible(df)
}

In use we get:

> asciify(d)
+----+------+------+
| id |  va1 | var2 |
+----+------+------+
|  1 | asdf |    g |
+----+------+------+
|  2 | asdf |    h |
+----+------+------+
|  3 | asdf |    j |
+----+------+------+

On something a bit more complex we get

> asciify(mtcars)
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
|  mpg | cyl |  disp |  hp | drat |    wt |  qsec | vs | am | gear | carb |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
|   21 |   6 |   160 | 110 |  3.9 |  2.62 | 16.46 |  0 |  1 |    4 |    4 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
|   21 |   6 |   160 | 110 |  3.9 | 2.875 | 17.02 |  0 |  1 |    4 |    4 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 22.8 |   4 |   108 |  93 | 3.85 |  2.32 | 18.61 |  1 |  1 |    4 |    1 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 21.4 |   6 |   258 | 110 | 3.08 | 3.215 | 19.44 |  1 |  0 |    3 |    1 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 18.7 |   8 |   360 | 175 | 3.15 |  3.44 | 17.02 |  0 |  0 |    3 |    2 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 18.1 |   6 |   225 | 105 | 2.76 |  3.46 | 20.22 |  1 |  0 |    3 |    1 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 14.3 |   8 |   360 | 245 | 3.21 |  3.57 | 15.84 |  0 |  0 |    3 |    4 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 24.4 |   4 | 146.7 |  62 | 3.69 |  3.19 |    20 |  1 |  0 |    4 |    2 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 22.8 |   4 | 140.8 |  95 | 3.92 |  3.15 |  22.9 |  1 |  0 |    4 |    2 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 19.2 |   6 | 167.6 | 123 | 3.92 |  3.44 |  18.3 |  1 |  0 |    4 |    4 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 17.8 |   6 | 167.6 | 123 | 3.92 |  3.44 |  18.9 |  1 |  0 |    4 |    4 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 16.4 |   8 | 275.8 | 180 | 3.07 |  4.07 |  17.4 |  0 |  0 |    3 |    3 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 17.3 |   8 | 275.8 | 180 | 3.07 |  3.73 |  17.6 |  0 |  0 |    3 |    3 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 15.2 |   8 | 275.8 | 180 | 3.07 |  3.78 |    18 |  0 |  0 |    3 |    3 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 10.4 |   8 |   472 | 205 | 2.93 |  5.25 | 17.98 |  0 |  0 |    3 |    4 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 10.4 |   8 |   460 | 215 |    3 | 5.424 | 17.82 |  0 |  0 |    3 |    4 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 14.7 |   8 |   440 | 230 | 3.23 | 5.345 | 17.42 |  0 |  0 |    3 |    4 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 32.4 |   4 |  78.7 |  66 | 4.08 |   2.2 | 19.47 |  1 |  1 |    4 |    1 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 30.4 |   4 |  75.7 |  52 | 4.93 | 1.615 | 18.52 |  1 |  1 |    4 |    2 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 33.9 |   4 |  71.1 |  65 | 4.22 | 1.835 |  19.9 |  1 |  1 |    4 |    1 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 21.5 |   4 | 120.1 |  97 |  3.7 | 2.465 | 20.01 |  1 |  0 |    3 |    1 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 15.5 |   8 |   318 | 150 | 2.76 |  3.52 | 16.87 |  0 |  0 |    3 |    2 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 15.2 |   8 |   304 | 150 | 3.15 | 3.435 |  17.3 |  0 |  0 |    3 |    2 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 13.3 |   8 |   350 | 245 | 3.73 |  3.84 | 15.41 |  0 |  0 |    3 |    4 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 19.2 |   8 |   400 | 175 | 3.08 | 3.845 | 17.05 |  0 |  0 |    3 |    2 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 27.3 |   4 |    79 |  66 | 4.08 | 1.935 |  18.9 |  1 |  1 |    4 |    1 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
|   26 |   4 | 120.3 |  91 | 4.43 |  2.14 |  16.7 |  0 |  1 |    5 |    2 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 30.4 |   4 |  95.1 | 113 | 3.77 | 1.513 |  16.9 |  1 |  1 |    5 |    2 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 15.8 |   8 |   351 | 264 | 4.22 |  3.17 |  14.5 |  0 |  1 |    5 |    4 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 19.7 |   6 |   145 | 175 | 3.62 |  2.77 |  15.5 |  0 |  1 |    5 |    6 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
|   15 |   8 |   301 | 335 | 3.54 |  3.57 |  14.6 |  0 |  1 |    5 |    8 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+
| 21.4 |   4 |   121 | 109 | 4.11 |  2.78 |  18.6 |  1 |  1 |    4 |    2 |
+------+-----+-------+-----+------+-------+-------+----+----+------+------+

Note that the code doesn't handle aligning numeric data on their decimal points but the code for the internal Row() function could be modified to suit as all that requires is slightly different sprintf() call. Also, I realise I have right-aligned the strings which is not what you showed in the example table but not a terminal failure!

To get the output in a file, capture the output from asciify() using capture.output():

> capture.output(asciify(d), file = "asciified_d.txt")
> readLines("asciified_d.txt")
[1] "+----+------+------+" "| id |  va1 | var2 |" "+----+------+------+"
[4] "|  1 | asdf |    g |" "+----+------+------+" "|  2 | asdf |    h |"
[7] "+----+------+------+" "|  3 | asdf |    j |" "+----+------+------+"

(Note the output shown above is just a vector of character strings, each one a line in the captured file. The file looks like this on disk:

$ cat asciified_d.txt 
+----+------+------+
| id |  va1 | var2 |
+----+------+------+
|  1 | asdf |    g |
+----+------+------+
|  2 | asdf |    h |
+----+------+------+
|  3 | asdf |    j |
+----+------+------+

.)

I haven't checked this much and it will more than likely be shown to fail in a number of cases, but it works for basic data frames.

Update asciify() now handles data frames with factors as well as character and numeric data:

> require(ggplot2)
> asciify(head(diamonds))
+-------+-----------+-------+---------+-------+-------+-------+------+------+------+
| carat |       cut | color | clarity | depth | table | price |    x |    y |    z |
+-------+-----------+-------+---------+-------+-------+-------+------+------+------+
|  0.23 |     Ideal |     E |     SI2 |  61.5 |    55 |   326 | 3.95 | 3.98 | 2.43 |
+-------+-----------+-------+---------+-------+-------+-------+------+------+------+
|  0.21 |   Premium |     E |     SI1 |  59.8 |    61 |   326 | 3.89 | 3.84 | 2.31 |
+-------+-----------+-------+---------+-------+-------+-------+------+------+------+
|  0.23 |      Good |     E |     VS1 |  56.9 |    65 |   327 | 4.05 | 4.07 | 2.31 |
+-------+-----------+-------+---------+-------+-------+-------+------+------+------+
|  0.29 |   Premium |     I |     VS2 |  62.4 |    58 |   334 | 4.20 | 4.23 | 2.63 |
+-------+-----------+-------+---------+-------+-------+-------+------+------+------+
|  0.31 |      Good |     J |     SI2 |  63.3 |    58 |   335 | 4.34 | 4.35 | 2.75 |
+-------+-----------+-------+---------+-------+-------+-------+------+------+------+
|  0.24 | Very Good |     J |    VVS2 |  62.8 |    57 |   336 | 3.94 | 3.96 | 2.48 |
+-------+-----------+-------+---------+-------+-------+-------+------+------+------+