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
but is there a way to do this directly from R?
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 |
+-------+-----------+-------+---------+-------+-------+-------+------+------+------+
Another solution that is already more advance than the code I wrote over lunch is the pander package, which renders R objects in Pandoc markdown for subsequent conversion to a range of other formats.
The markup is not quite the same as the MySQL one shown, but the most similar is:
> pandoc.table(m, style = "grid")
+---------------------+-------+-------+--------+
| | mpg | cyl | disp |
+=====================+=======+=======+========+
| **Mazda RX4** | 21 | 6 | 160 |
+---------------------+-------+-------+--------+
| **Mazda RX4 Wag** | 21 | 6 | 160 |
+---------------------+-------+-------+--------+
As you can see, this has in-built handling of the row names (which mine doesn't), and may gain a lot more sugar over time.
There is some related discussion of this and further examples in this StackOverflow question.
If you're after a HTML result, perhaps you could harvest the power of googleVis package. You have more examples in the package demo().
library(googleVis)
x <- data.frame(matrix(runif(9), nrow = 3))
xg <- gvisTable(x, options=list(page='enable', height=300))
plot(xg)
The above chunk will produce a web page that looks something like this.
If you're looking for a tool for reporting, you could check out Markdown in RStudio.
