transform a table?

10 views
Skip to first unread message

bbimber

unread,
Sep 9, 2009, 8:51:20 PM9/9/09
to MadUseR
hello everyone,

i'm a molecular biology grad student and found this forum off google.
i'm new to R, so i hope you dont mind a fairly basic R question.
Thanks in advance for any help or pointers.

we're using R to manipulate the results of SQL queries and create an
HTML output. I'm starting with a table that looks essentially like
this:

Name Field1 Field2
John value1 value2
Jane value3 value4

My table is stored as a dataframe. I'd like to efficiently produce an
output that iterates through each row, transposes it and outputs an
HTML table (one per row). like this:

Name: John
Field1: value1
Field2: value2

Name: Jane
Field1: value3
Field2: value4

I can accomplish this by looping through each row, then outputting
that row's table. This gets the job done, but it seems there must be
a better way. I'm going to need to do this sort of conversion a lot,
so the simpler the better. is there a better way to approach it than
the code below? is there a more general term for the sort of
transformation i'm trying to make that might help guide my searching?

i realize i need to look into better methods of outputting HTML tables
(like r2html).

here's the basic idea. 'labkey.data' is the data frame produced by my
SQL query:

D<-labkey.data
H<-colnames(D)
T<-t(D)
L<-length(D$id)

output <- ""

for(i in 1:L) {
R<-my.row <- D[i, ]
R<-t(R)
Len<-length(R)

output <- paste(output, "<table border=0>")
for(j in 1:Len) {
output <- paste(output,"<tr><td>", H[j],":</td><td>", R[j], "</td>")
}
output <- paste(output, "</table><p>")
}

write(output, file="${htmlout:output}")

Thanks for any help.

Douglas Bates

unread,
Sep 10, 2009, 9:15:11 AM9/10/09
to mad...@googlegroups.com, bbi...@gmail.com

It would help if you created a toy example so those who respond can
show the actual code.

A couple of comments on your code, using single-letter names as
identifiers is not recommended just because there are a number of
heavily-used functions that have single letter names. Name conflicts
are less of a problem now that packages have name spaces but it is
best not to tempt fate.

> D<-labkey.data
> H<-colnames(D)
> T<-t(D)
> L<-length(D$id)

You have the right idea of taking the data frame and transposing it.
During this process all the values will probably be converted to
character data, if they are not already character. Columns of a data
frame can be heterogeneous but the transpose of a data frame is an
array with homogeneous data types.

The best thing to do when developing code like this is to use the
str() function frequently. So

str(labkey.data) # shows the structure of the original data frame
str(t(labkey.data)) # shows the structure of the transpose

You can work on columns of the transposed table with loops and
indices, as shown below, but often it is more effective to write a
function that produces the desired result from one column then "apply"
the function to the columns. It would look like

cfunc <- function(cc) {
for (nm in names(cc)) output <- paste(output, "<tr><td>",
nm,":</td><td>", cc[nm], "</td>")
}

Then you can just
apply(t(labkey.data), 2, cfunc))

If you are doing this on large data frames you will probably find it
more convenient to write to a connection, rather than accumulating all
the output in a character string. See the help page for "file" or
"open".

bbimber

unread,
Sep 10, 2009, 9:38:10 AM9/10/09
to MadUseR
ok, thanks for the advice. i will post data in the future. your
suggestions on creating/applying a function make sense.

i have one question though. i believe i understand how to create a
file connection and the table rows to it. however, i'm not sure i
understand why this is more convenient that accumulating the data in
string variable (although it is not less convenient either). is a
file somehow handled more efficiently than a large string? is it
because files allow more manipulations (like iterating by line), than
strings? sorry if this is a dumb question, but i'm trying to be sure
i understand why i'm doing what i'm doing.

thanks again.



On Sep 10, 8:15 am, Douglas Bates <ba...@stat.wisc.edu> wrote:

Douglas Bates

unread,
Sep 10, 2009, 2:34:08 PM9/10/09
to mad...@googlegroups.com, bbi...@gmail.com
On Thu, Sep 10, 2009 at 8:38 AM, bbimber <bbi...@gmail.com> wrote:
>
> ok, thanks for the advice.  i will post data in the future.  your
> suggestions on creating/applying a function make sense.
>
> i have one question though.  i believe i understand how to create a
> file connection and the table rows to it.  however, i'm not sure i
> understand why this is more convenient that accumulating the data in
> string variable (although it is not less convenient either).  is a
> file somehow handled more efficiently than a large string?  is it
> because files allow more manipulations (like iterating by line), than
> strings?  sorry if this is a dumb question, but i'm trying to be sure
> i understand why i'm doing what i'm doing.
>
> thanks again.

Constructions like

output <- paste(output, "new stuff")

become inefficient in an interpreted language because appending to an
existing structure usually means that you need to allocate the new
structure, then copy the old value and append the new value. Files,
on the other hand, are designed to have contents appended to them and
the operating system takes care to do this efficiently.

Building structures by iterating and appending is discouraged in R. Compare

> sqrts <- numeric(0)
> system.time(for (i in 1:100000) sqrts <- c(sqrts, sqrt(i)))
user system elapsed
64.136 5.680 69.862
> sqrts <- numeric(100000)
> system.time(for (i in seq(along = sqrts)) sqrts[i] <- sqrt(i))
user system elapsed
1.04 0.00 1.04
> system.time(sqrts <- sqrt(1:100000))
user system elapsed
0.008 0.000 0.008

Each of these determines and stores the square roots of the first
100000 integers. The third one takes advantage of the vectorization
of arithmetic and mathematic functions and is the fastest. The second
one preallocates the storage and updates elements in a loop. It
suffers from the overhead of interpretation and looping but does not
do any copying. The first is appending to an existing structure in a
loop and is by far the slowest because of all the copying entailed.

ndiaye

unread,
Sep 13, 2009, 1:21:58 PM9/13/09
to MadUseR

Hi,
This is another alternative with reproducible example below. I
couldn't find a way to avoid the second looping.
I am still learning the subtilities of programming
However using hwritter() offers ways to improve the output design with
CSS etc...
Also consider the function reshape() if changing the direction of the
dataframe is your main goal.
Thank you
Ndiaye

###### Example starts ###############

## creating the dataframe
dat<-sample(1:5,15,replace=T)
dat<-as.data.frame(matrix(dat,5,3))
names(dat)<-c("test1","test2","test3")
name<-c("Jack","Sarah","Samba","Eve","Jean")
dat<-data.frame(name,dat)
dat
# install the following packages if not done yet
# install.packages("hwritter") #will format a html output
# install.packages("foreach") # a looping alternative

TransTable<-function(data){
# Function to transpose the tables and output them in html format
library(foreach)
nr<-nrow(data)
z<-foreach(i=1:nr) %do% t(data[i,])
library(hwriter)
p=openPage('tab.html')
# the html file will be in the working directory
# you could write a different location
# openPage will allow appending subsequent tables until closed
# writing first table then looping for the following
hwrite(z[[1]],col.names=F,p)
foreach(i=2:nr) %do% {
hwrite(z[[i]],col.names=F,p)
}
closePage(p)
}

TransTable(data=dat) # the file "tab.html" is created in the working
directory getwd()

###### Example ends #######
Reply all
Reply to author
Forward
0 new messages