Graphing NONMEM output with EXCEL Macro
NONMEM Topic 10
Keywords: Graph, EXCEL, macro, output
Provided by: msale01@gumedlib.dml.georgetown.edu (Mark Sales) - 27 Jan 1994
I saw a questions recently about graphics with NONMEM. I have an excel
macro to make charts, divided by individual from NONMEM output that I would be
happy to share. Basically, it uses the parse command to put the data into
columns then the offset to select data for each individual. It wouldn't work
well as email, since there are many variables defined. But I'll send it to
anyone (unsupported of course) who sends a disc to
Mark Sale
3900 Reservoid Rd NW
Room NE403
Department of Pharmacology
Georgetown University
Washington DC 20007
or, if you want an encoded binary by email, send me mail to see if we have
compatible encoding software.
It may be helpful to those also working on a PC.
Mark
msale01@gumedlib.dml.georgetown.edu
Follow-up by msale01@gumedlib.dml.georgetown.edu (Mark Sales)
Dear Users,
The response to my offer of the EXCEL (Yes EXCEL, not SAS) macro has
been overwhelming. This macro makes an EXCEL graph of the data file output
from NONMEM ($TABLE FILE =). It isn't necessary to use NOHEADER since this
macro will delete any lines starting with text. But, I suggest that you only
put 3 items in the Table, the first of which should be the id. This macro
plots each id seperatly, on one plot and labels the first and last datum with
the id number. That is, a data set with 30 individuals will yield a plot with
30 lines on it. The second data item will end up the x axis and the third
the y axis coordinate. Additional items, if not deleted in EXCEL cause
problems. I haven't included a smoother, since I don't (yet) have one that
is better than the 2 available in EXCEL (moving average and an exponential
smoother). Instead of sending it to all as encoded binary, I'm
sending it over nmusers as text with instructions for defining the 5
functions that need to be defined. I hope this works:
There are 90 lines in this macro. The first 87 lines have only one column.
Lines 88-90 have 7 columns. I hope the tabs come through in lines 88 - 90.
If not they will have to be put in. Copy the text into a macro sheet in A1.
Five function need to be defined. To define a function, select Formula-
define name. Then type in the function name, and the cell locations. You
can use any name you like (and a control- key) for the main function,
appearing in A13. The other four function need the correct name, which
appears in that cell. For all function, the command box must be checked.
A13 define as command function, any name you like
A41 define as command function, delete_text
A53 define as command function, delzero
A65 define as command function, make_chart
A74 define as command function, put
Again, the id must be in column 1, x coordinate in column 2, y in column 3
and nothing beyond that. I have found this to be reasonable fast (500 data
from 137 individuals in 11 second on a Pentium) My Pentium with 16 Mbytes
ran out of memory at about 600 data. In general it took about as long to
parse the data as to make the chart.
To parse the data, open the add-in flatfile.xla and use smart-parse, with
the Remove extra blank spaces. The next letter will be the macro text.
Macro Text
"readme: To use this macro, the data must be parsed into columns"
"To parse data, use the parse or the smart parse command (in flatfile.xla)"
"Id number in column 1, x axis in column 2, y in column 3"
all lines with text will be deleted.
"if you chose to, all lines with 0 in column 2 (i.e., doses)"
will also be deleted from the data set.
"To use this macro, define the following cells as commands (formula,define nam
)"
"define A13 to be a command (any name, cntrl key if you like)"
"define A41 to be delete_text, a command"
"define A53 to be delzero, a command"
"define A65 to be make_chart, a command"
"define A74 to be put, a command"
make_nonmem_chart()
=ECHO(FALSE)
"=ALERT(""Warning, data set will be changed, lines with text will be deleted!"
,1,1)"
=DIALOG.BOX(A88:G90)
"=SET.NAME(""data"",GET.DOCUMENT(1))"
=delete_text()
"=IF(G89=TRUE,delzero())"
=make_chart()
"=SET.NAME(""curr_id"",!A1)"
"=SET.NAME(""which"",1)"
"=SET.NAME(""ids"",0)"
"=SET.NAME(""total_dat"",0)"
"=IF(ISBLANK(OFFSET(!A1,1,0)))"
"=ALERT(""Please delete first blank column (or run smart parse with """"Remove
extra blank spaces"""" checked)"",1,)"
=RETURN()
=END.IF()
"=WHILE(ISNUMBER(OFFSET(!A1,total_dat,0)))"
"=WHILE(OFFSET(!A1,total_dat,0)=curr_id)"
ids=ids+1
total_dat=total_dat+1
=NEXT()
=put()
=NEXT()
=ACTIVATE(chart)
"=SELECT(""S1"")"
"=PATTERNS(1,1,1,1,1,1,1,3,TRUE)"
=RETURN()
delete_text
"=SET.NAME(""position"",0)"
"=WHILE(NOT(ISBLANK(OFFSET(!A1,position,0))))"
"=IF(ISTEXT(OFFSET(!A1,position,0)))"
"=SELECT(OFFSET(!A1,position,0,1,6))"
=EDIT.DELETE(2)
position=position-1
=END.IF()
position=position+1
=NEXT()
=RETURN()
delzero
"=SET.NAME(""position"",0)"
"=WHILE(ISNUMBER(OFFSET(!A1,position,0)))"
"=IF(OFFSET(!A1,position,1)=0)"
"=SELECT(OFFSET(!A1,position,0,1,6))"
=EDIT.DELETE(2)
position=position-1
=END.IF()
position=position+1
=NEXT()
=RETURN()
make_chart
=SELECT(!I1)
"=NEW(2,2)"
"=SET.NAME(""chart"",GET.DOCUMENT(1))"
"=GALLERY.SCATTER(1,TRUE)"
=ACTIVATE(data)
=SELECT(!A1)
=RETURN()
put
"=COPY(OFFSET(!B1,total_dat-ids,0,ids,3))"
=ACTIVATE(chart)
"=PASTE.SPECIAL(2,FALSE,TRUE,FALSE)"
"=ATTACH.TEXT(4,which,1)"
=FORMULA(curr_id)
"=ATTACH.TEXT(4,which,ids)"
=FORMULA(curr_id)
"=SET.NAME(""which"",which+2)"
=ACTIVATE(data)
"=SET.NAME(""ids"",0)"
"=SET.NAME(""curr_id"",OFFSET(!A1,total_dat,0))"
=RETURN()
50 50 400 150 Delete Doses
13 50 70 400 60 delete rows with 0's in column 2? TRUE
1 30 30 50 30 OK
Do not use the above without making the corrections mentioned below.
Note the availability of this macro as an Excel file via WWW, gopher or ftp.
Corrections from msale01@gumedlib.dml.georgetown.edu (Mark Sale)
Sorry about the last message, my connection crashed. Last night I sent a
graphic macro for EXCEL. Two errors occured in transmission. First, an
extra line was added to the beginning. It should be deleted, the readme
should be in cell A1. Second, lines 88 - 90 lost the tabs between columns.
This should be spread out into 7 columns (A to G). Note that cell A88
should be blank, with a 13 in cell A89 and 1 in cell A 90. The only cell filled in
column G should be G89, with TRUE. The text (delete Doses, delete rows with
0's in column 2 and OK) goes in column F.
Response from nholford@ccu1.auckland.ac.nz (Nick Holford)
Thanks to Mark for offering the EXCEL macro. But he has obviously faced
some difficulty in distributing it. May I suggest that whenever possible
that this kind of 'it must be correct' transmission be sent as an
UUENCODED file so that the binary version can be readily sent. If there
is an error the corrected version (complete) should then be sent. This
saves a lot of confusion for those who must try and put the pieces
together.
If you are reading this and dont know about UUENCODE and UUDECODE then
ask you local computer support person. If you are still no wiser send me
a PC disk and I will send you a copy. Mac users will have to look
somewhere else :-(
Response from david-bourne@uokhsc.edu (David Bourne)
>If you are reading this and dont know about UUENCODE and UUDECODE then
>ask you local computer support person. If you are still no wiser send me
>a PC disk and I will send you a copy. Mac users will have to look
>somewhere else :-(
- no! no! Eudora (on the Mac) will uudecode, as will a number of other Mac
utilities. :-)
Response from nholford@ccu1.auckland.ac.nz (Nick Holford)
I wrote:
> >If you are reading this and dont know about UUENCODE and UUDECODE then
> >ask you local computer support person. If you are still no wiser send me
> >a PC disk and I will send you a copy. Mac users will have to look
> >somewhere else :-(
David Bourne replied:
> - no! no! Eudora (on the Mac) will uudecode, as will a number of other Mac
> utilities. :-)
>
What does he mean "no! no!" ?
I hope he meant "yes! yes! Eudora (on the Mac) will uudecode" :-)
Presumably Mac users should email David to find out how to get Eudora.
The real point of my original message is to encourage nmusers to "get
with it" and equip themselves with UUENCODE/DECODE so that we can share
files (including binaries) in an consistent and content checked manner
and avoid the messing about that Mark Sale went through with his EXCEL
graphs macro.
Response from david-bourne (David Bourne)
I have converted Mark Sales macro to an Excel file and then a UUENCODED file
which should be accessible via this WWW server, ftp, or gopher.
topic010xl.uu
topic010.xl
End of Topic - 12 Apr 94