I have a LaTeX table generated by Excel. I want to place this table but not as a LaTeX code, but rather as a xtable. It is possible to convert LaTeX to xtable?
2 Answers
Set by step: Suppose that you have a file.xlsx
that look like:
To convert to R, first export to file.csv
:
,apples,tomatoes,babanas
Jinks,3,12,15
Pixie,4,5,6
Dixie,2,8,9
Then import as a data frame mytable
in R:
> mytable <- read.table("file.csv", header=TRUE, sep=",")
> mytable
X apples tomatoes babanas
1 Jinks 3 12 15
2 Pixie 4 5 6
3 Dixie 2 8 9
Or import directly from the file.xlsx
(need Perl installed on your system):
> library(gdata)
> mytable <- read.xls("file.xlsx")
The data frame obtained must be the same in any case. Just in case that first column are really row names as above:
> rownames(mytable) <- mytable[,1]
> mytable[,1] <- NULL
> mytable
apples tomatoes babanas
Jinks 3 12 15
Pixie 4 5 6
Dixie 2 8 9
For convert the dataframe mytable
as a xtable (that is, a chunk of LaTeX code):
> library(xtable)
> xtable(mytable)
% latex table generated in R 3.1.1 by xtable 1.7-4 package
% Wed Mar 11 06:11:44 2015
\begin{table}[ht]
\centering
\begin{tabular}{rrrr}
\hline
& apples & tomatoes & babanas \\
\hline
Jinks & 3 & 12 & 15 \\
Pixie & 4 & 5 & 6 \\
Dixie & 2 & 8 & 9 \\
\hline
\end{tabular}
\end{table}
So you can copy & paste the ouput of R in your LaTeX file to obtain:
But the good thing of LaTeX+R is that you can make all the above automatically inserting a R chunk in your file.tex
document (now is R noweb document, so save it as file.Rnw
) and compile it using Sweave
or knitr
from the command line (see
How to build Knitr document from the command line ) or simply making in Rstudio:
File > New file > R Sweave > Write some like:
\documentclass[twocolumn]{article}
\usepackage{titlecaps}
\usepackage{lipsum}
\title{Owners {\it \&} Fruits}
\author{Fran}
\begin{document}
\maketitle
<<mytable,echo=FALSE,warning=FALSE,message=FALSE,results='hide'>>=
library(gdata)
mytable <- read.xls("file.xlsx")
rownames(mytable) <- mytable[,1]
mytable[,1] <- NULL
library(xtable)
@
\section*{Introduction}
The cartoons (\Sexpr{row.names(mytable)}) are shooting fruits (\Sexpr{names(mytable)}).
\lipsum[2]
\section*{Cartoons have \Sexpr{sum(mytable$apples)} apples}
\lipsum[3]
<<xtable,echo=FALSE,warning=FALSE,message=FALSE,results='asis'>>=
print(xtable(mytable, caption="My Excel table"), caption.placement = "top")
@
\lipsum[4-15]
\end{document}
and finally, click on Compile PDF and you will obtain this file.pdf
:
More generally, if you have a latex table my_file.tex
such as:
% latex table generated in R 3.1.1 by xtable 1.7-4 package
% Wed Mar 11 06:11:44 2015
\begin{table}[ht]
\centering
\begin{tabular}{rrrr}
\hline
& apples & tomatoes & bananas \\
\hline
Jinks & 3 & 1 & 15 \\
Pixie & 4 & 5 & 6 \\
Dixie & 2 & 8 & 9 \\
\hline
\end{tabular}
\end{table}
You can import it into R directly, such as:
library("data.table") # loads the smart "fast" fread function
#import data into R
data <- fread("my_file.tex", skip=8, sep="&", data.table=F) # skips in compatible lines
names <- read.table("my_file.tex", skip=6, nrow=1, sep="&", stringsAsFactors=F)
#remove latex symbols from header
names<-gsub("\\\\", "", as.character(names))
names<-gsub(" ", "", as.character(names))
colnames(data) <- names
remove latex end of line from last column
data[,ncol(data)]<-gsub("\\\\", "", as.character(data[,ncol(data)]))
data[,ncol(data)]<-gsub(" ", "", as.character(data[,ncol(data)]))
data[,ncol(data)] <- as.numeric(data[,ncol(data)]) #change type as desired
This returns the following from calling data
in R:
NA apples tomatoes bananas
1 Jinks 3 1 15
2 Pixie 4 5 6
3 Dixie 2 8 9
Note: requires data.table 1.10.0 or higher
You can also export this from R to LaTeX:
library("xtable")
#function to export (filename defaults to object in R environment)
export_latex <- function(x, file) print(xtable(data.frame(x), type="latex", file=paste0(deparse(substitute(x)), ".tex"))
export_latex(data, file="my_file.tex")
.csv
text, assign to an object asmyexeceldata
, load thextable
library and then runxtable(myexeceldata)
to obtain the LateX code, or simply runmyexeceldata
to obtain the table in plain text.