Hi Kevan,
Thanks for the script. I tried it out today and it worked excellent.
It did exactly what I was interested in.
Thanks again!
Paul
On 3/21/08 3:34 PM, "Doyle, Kevan" <Kevan.Doyle@atmel.com> wrote:
>
> 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);
> );
>
--
Paul E. Marek
Biology Department
East Carolina University
Howell Science Complex/N404A
Greenville, NC 27858
(252) 328-2971
http://personal.ecu.edu/pm0623/
|