I didn't have any experience with matrices in JMP, so I thought I'd
give it a try.
At the bottom of this message is a script that will make a
DataNewTable from the product of numeric data in the DataTable and a
column from a MultiplierTable. The columns in the DataNewTable will
have the same names as the corresponding columns in DataTable
concatenated with a SuffixString. Just set it to a null string ("")
if you don't want a suffix.
Names for all tables, the column number of the MultiplierTable and
the SuffixString are set at the beginning of the script.
The DataTable can contain non-Numeric columns; they will not show
up in the DataNewTable.
I included a section labeled "// Get rid of any earlier attempts".
If you don't want the script to delete older versions of the output
table, rename them or remove this section of the script.
I've included two versions of the script, with and without error
checking. The second is overkill for a onetime use, but I find that
problems have a way of showing up more than once. Both versions
worked on a small test table.
Again, this is my first time dealing with matrices, so check the
output carefully. No warranties or guarantees implied.
Let me know if it does or does not work for you or if I mis-
understood the problem. In any event, it was an interesting
exercise. I learned about matrix manipulation in scripts and I
learned more about the Throw() command.
Thanks, Kevan
----------------------------------------------------------------------
email: Kevan.Doyle@atmel.com
-----Original Message-----
From: jmp-l-owner@lists.biostat.wustl.edu [mailto:jmp-l-owner@lists.biostat.wustl.edu
] On Behalf Of Mark Bailey
Sent: Wednesday, March 19, 2008 7:21 PM
To: jmp-l@lists.biostat.wustl.edu
Subject: Re: [jmp-l] Multiplying columns
This problem might be really easy with a script. For example, if the
original data table contains only columns to be multiplied, and the
second table contains only the multiplier column, then it would be a
very short and direct script.
Let know if you want help.
Sent from my iPhone
On Mar 19, 2008, at 6:28 PM, "Paul E. Marek" <pm0623@ecu.edu> wrote:
Hi everybody,
I have about 600 columns (in one file) that I would like to multiply
by one column (in another file).
I would like to have, as a result, a new file with 600 new columns as
a product (of multiplying 600 columns X the one column).
I know this is a simple thing to do (I can do it easily in excel),
but
I just can't figure it out in JMP 7.
Thank you for your help.
Paul
--
Paul E. Marek
Biology Department
East Carolina University
Howell Science Complex/N404A
Greenville, NC 27858
(252) 328-2971
http://personal.ecu.edu/pm0623/
--------------------------------------------------
--------------------------------------------------
--------------------------------------------------
Simple Version
--------------------------------------------------
--------------------------------------------------
--------------------------------------------------
// Get table and column references ---------------
tblData = DataTable("Data");
tblMultiplier = DataTable("Multiplier");
colMultiplier = Column(tblMultiplier, 1);
strSuffix = "_Scaled";
// Get data in matrix form ---------------
matData = tblData << GetAsMatrix();
lngNumDataCols = NCol(matData);
lngNumDataRows = NRow(matData);
matMultiplier = colMultiplier << GetAsMatrix();
lngNumMultRows = NRow(matMultiplier);
// Multiply each row ---------------
// by the factor in the corresponding row
// of the Multiplier Column.
For(lngIndexRow = 1, lngIndexRow <= lngNumDataRows, lngIndexRow++,
matData[lngIndexRow, 0] = matData[lngIndexRow, 0] *
matMultiplier[lngIndexRow,1]
);
// Convert to a table
tblDataNew = AsTable(matData);
tblDataNew << SetName("DataNew");
// Add column names to new table. ---------------
// Only Numeric columns are affected.
lstColNames = tblData << GetColumnNames(Numeric);
lngNumNames = NItems(lstColNames);
// I found out that the dt << GetColumnNames() message
// returns the names as Expressions, not as Strings,
// so we have to convert.
For(lngIndexCol = 1, lngIndexCol <= lngNumNames, lngIndexCol++,
strName = Char(NameExpr(lstColNames[lngIndexCol])) || strSuffix;
Column(tblDataNew, lngIndexCol) << SetName(strName);
);
--------------------------------------------------
--------------------------------------------------
--------------------------------------------------
Version with Error Detection
--------------------------------------------------
--------------------------------------------------
--------------------------------------------------
/* debug step*/
// User Dependent Names
// Name the Data table.
strDataTableName = "Data";
// Name the Multiplier table.
strMultiplierTableName = "Multiplier";
// Name the output table.
strDataNewTableName = "DataNew";
// Specify which column in the Multiplier table has the factor.
lngMultiplierColNumber = 1;
// Add a suffix to the column names in the DataNew table.
strNewColSuffix = "_Scaled";
// Utility Function - MsgBox
MsgBox = Function({Msg, Cancel}, {Default Local},
strMsg = "\!r\!n" || Char(Msg) || "\!r\!n";
If(Cancel,
Dialog(strMsg ,Button("OK"), Button("Cancel"))
,
Dialog(strMsg ,Button("OK"));
);
); // Close Function MsgBox
// Get rid of any earlier attempts.
Try(
tblDataNew = DataTable(strDataNewTableName);
Close(tblDataNew, No Save)
,
Write("No previous DataNew table to delete.\!n")
);
// Get table and column references ---------------
Try(
tblData = DataTable(strDataTableName)
,
strErrMsg = "Table " || strDataTableName || " does not exist!";
MsgBox(strErrMsg,0); Throw();
);
Try(
tblMultiplier = DataTable(strMultiplierTableName);
,
strErrMsg = "Table " || tblMultiplier || " does not exist!";
MsgBox(strErrMsg,0); Throw();
);
Try(
colMultiplier = Column(tblMultiplier, lngMultiplierColNumber)
,
strErrMsg = "Column " || Char(lngMultiplierColNumber) ||
" of multiplier table \!"" || strMultiplierTableName || "\!"
does not exist!";
MsgBox(strErrMsg,0); Throw();
);
If((colMultiplier << GetDataType()) != "Numeric",
strErrMsg = "Multiplier Column is non-Numeric!";
MsgBox(strErrMsg,0); Throw();
);
// Get data in matrix form ---------------
matData = tblData << GetAsMatrix();
lngNumDataCols = NCol(matData);
lngNumDataRows = NRow(matData);
matMultiplier = colMultiplier << GetAsMatrix();
lngNumMultRows = NRow(matMultiplier);
If(lngNumDataRows != lngNumMultRows,
strErrMsg = "Number of Rows in Data table and Multiplier table
are not equal!" ||
"\!nStopping Script.";
MsgBox(strErrMsg,0); Throw();
);
// Multiply each row ---------------
// by the factor in the corresponding row
// of the Multiplier Column.
For(lngIndexRow = 1, lngIndexRow <= lngNumDataRows, lngIndexRow++,
matData[lngIndexRow, 0] = matData[lngIndexRow, 0] *
matMultiplier[lngIndexRow,1]
);
// Convert to a table
tblDataNew = AsTable(matData);
tblDataNew << SetName(strDataNewTableName);
// Add column names to new table. ---------------
// Only Numeric columns are affected.
lstColNames = tblData << GetColumnNames(Numeric);
lngNumNames = NItems(lstColNames);
If(lngNumNames != lngNumDataCols,
strErrMsg =
"Number of Names of Numeric Columns in Data table " ||
"does not equal Number of Columns in New Data table!";
MsgBox(strErrMsg,0); Throw();
);
// I found out that the dt << GetColumnNames() message
// returns the names as Expressions, not as Strings,
// so we have to convert.
For(lngIndexCol = 1, lngIndexCol <= lngNumNames, lngIndexCol++,
strName = Char(NameExpr(lstColNames[lngIndexCol])) ||
strNewColSuffix;
Column(tblDataNew, lngIndexCol) << SetName(strName);
);