287854_zh-CN

liliconcom

数据质量检查和重新格式化

大家好， 

 

我的工作流程中一个常见的慢速过程是，客户向我们发送了一个电子表格，该电子表格的结果包含“ <0.01”或一些其他任意数量的低于检测极限的样品。我们有一个内部R代码来计算这些值的估算值，但是我们需要重新格式化数据集（此处附带示例）。从文件中，我们需要创建两个单独的CSV，其中一个将未检测到的CSV替换为零（附加检测文件），另一个将所有值都为零的未检测到的CSV替换为检测极限，且不带有“ <”符号（非检测附件）。 

有谁知道如何自动执行此过程以从数据集中创建这两个CSV？如果JMP具有此功能或任何人都可以提供常规脚本，那将感到非常兴奋。 

 

脚本编写回复：数据质量检查和重新格式化

对于JMP来说，这是一个相当简单的任务。  运行此代码（在编辑<您的路径>部分之后），然后查看它是否满足您的要求。  然后，您需要转到JMP文档库，并阅读《脚本指南》，这样您就可以了解下面的代码在做什么。

Names Default To Here( 1 );

// Open the Excel spreadsheet that has the issues
dt = Open( "<YOUR path="">\Sample Client Data.xlsx" );

// Get all of the character columns
colNames = dt << get column names( string, character );

// Go across all of the character columns, and if "<0.0" is found in one
// or more cells, then change those cells to 0.  Then convert the column
// to a numeric column
For( i = 1, i <= N Items( colNames ), i++,
 dt << select where( Contains( As Column( colNames[i] ), "<0.0" ) );
 selRows = dt << get selected rows;
 If( N Rows( selRows ) > 0,
  Column( dt, colNames[i] )[selRows] = "0";
  Column( dt, colNames[i] ) << data type( numeric ) << modeling type( continuous );
 );
);

// Save the Detect version of the data
dt << save( "<YOUR path="">\Detect.csv" );

Close( dt, nosave );

// Reopen the Excel spreadsheet that has issues
dt = Open( "<YOUR path="">\Sample Client Data.xlsx" );

// Get all of the character columns
colNames = dt << get column names( string, character );

// Go across all of the character columns, and if "<0.0" is found in one
// or more cells, then loop through the cells getting rid of the "<"  
// Then convert the column to a numeric column
For( i = 1, i <= N Items( colNames ), i++,
 dt << select where( Contains( As Column( colNames[i] ), "<0.0" ) );
 selRows = dt << get selected rows;
 If( N Rows( selRows ) > 0,
  For( k = 1, k <= N Rows( selRows ), k++,
   theRow = selRows[k];
   Column( dt, colNames[i] )[theRow] = Substr( Column( dt, colNames[i] )[theRow], 2 );
  );
  Column( dt, colNames[i] ) << data type( numeric ) << modeling type( continuous );
 );
);

// Save the NonDetect version of the data
dt << save( "<YOUR path="">\NonDetect.csv" );

Close( dt, nosave );</YOUR></YOUR></YOUR></YOUR>

回复：数据质量检查和重新格式化

吉姆，你好

我已经看过了，它缺少任务的第二部分。我们需要“非检测”脚本，以将所有值都更改为零。

 

我想运行循环，选择所有值> 0的单元格（具有“ <0.0” DL值的单元格除外）。 

您发送的代码的最后一部分效果很好，我只需要在其中插入“ NonDetectPreprocess.csv”即可。  

 

我尝试使用我制作的这个自定义脚本：

// Reopen the Excel spreadsheet that has issues
dt = Open( "D:\MD\Datto Workplace\Projects\Internal\JMP Scripts\Zero Imputation\Fake Client Data.csv" );

// Go across all of the character columns, and if concentration is greater than zero is found in one
// or more cells, then change those cells to 0.  Then convert the column
// to a numeric column
For( i = 1, i <= N Items( colNames ), i++,
 dt << select where( dt > 0 );
 selRows = dt << get selected rows;
 If( N Rows( selRows ) > 0,
  Column( dt, colNames[i] )[selRows] = "0";
  Column( dt, colNames[i] ) << data type( numeric ) << modeling type( continuous );
 );
);

//Save this sheet for next step

dt << save("D:\MD\Datto Workplace\Projects\Internal\JMP Scripts\Zero Imputation\NonDetectPreprocess.csv" )

Close( dt, nosave );

</CODE></PRE><P>&nbsp;</P><P>&nbsp;</P></LINGO-BODY><LINGO-SUB id="lingo-sub-288098" slang="en-US">回复：数据质量检查和重新格式化</LINGO-SUB><LINGO-BODY id="lingo-body-288098" slang="en-US"><P>很好的尝试....但是我想这就是你想要的</P>
<PRE><CODE class=" language-jsl">// Reopen the Excel spreadsheet that has issues
dt = Open( "<YOUR path="">\Sample Client Data.xlsx" );

// Get all of the character columns
colNames = dt << get column names( string, character );

// Go across all of the character columns, and if "<0.0" is found in one
// or more cells, then loop through the cells getting rid of the "<"  
// Then convert the column to a numeric column
For( i = 3 i <= N Items( colNames ), i++,
 dt << select where( ! Contains( As Column( colNames[i] ), "<0.0" ) );
 
 selRows = dt << get selected rows;
 If( N Rows( selRows ) > 0,
  column( dt, colNames[i] )[selRows] = "0";
 );
 
 dt << invert row selection;
 selRows = dt << get selected rows;
 
 If( N Rows( selRows ) > 0,
  For( k = 1, k <= N Rows( selRows ), k++,
   theRow = selRows[k];
   Column( dt, colNames[i] )[theRow] = Substr( Column( dt, colNames[i] )[theRow], 2 );
  );
  Column( dt, colNames[i] ) << data type( numeric ) << modeling type( continuous );
 );
);

// Save the NonDetect version of the data
dt << save( "<YOUR path="">\NonDetect.csv" );

Close( dt, nosave );</YOUR></YOUR>

回复：数据质量检查和重新格式化

吉姆，你好 

我注意到您做了倒排选择。尽管这样做有帮助，但问题是仅选择了包含“ <0.0”的列。如何选择数据集中所有具有数字数据的列并进行更改？

谢谢您的帮助！ 

回复：数据质量检查和重新格式化

如果更清晰，只需跟进照片即可。黄色是已替换的检测极限“ <0.0”。这些列被零替换，但是其余的保持不变。 

 

回复：数据质量检查和重新格式化

嗯，我看到了这个问题.....并且我已经在下面更正了这个问题.....我相信。  但是首先，让我先澄清一下。  当JMP读取不同的列时，它将读取给定列的所有数据，并确定该列是数字列还是字符列。  列中的所有值都必须为数字值，才能将该列视为数字。  因此，当JMP找到具有非数字单元格（即<0.0005）的列时，它将声明该列为字符列。  我的代码利用了这一点，因为它允许代码查找已找到“ <”的列。  它还允许JSL使用诸如Substr（）之类的字符处理函数来摆脱“ <”并在单元格中保留有效数字。  但是，一旦所有“ <”都被删除，该列就可以更改为数字列了……<<数据类型（数字）。  这是我的新代码，在读入数据之后和处理“ <”列之前，将它找到的所有列都转换为数字。  我假设您要将数字列中的所有值更改为0，即使它是负值也是如此。  然后，代码继续纠正“ <”列，该列将所有数字单元格更改为0，但具有“ <”的单元格除外。

dt = Open( "C:\users\jim\documents\discussion group\Sample Client Data.xlsx" );

// Get all of the character columns
colNames = dt << get column names( string, character );
colNamesNum = dt << get column names( string, numeric );

// zero out all current numeric columns
For( i = 2, i <= N Items( colNamesNum ), i++,
 column( dt, colNamesNum[i] )[1::N Rows(dt)] = 0;
);

// Go across all of the character columns, and if "<0.0" is found in one
// or more cells, then loop through the cells getting rid of the "<"  
// Then convert the column to a numeric column
For( i = 1, i <= N Items( colNames ), i++,
 dt << select where( ! Contains( As Column( colNames[i] ), "<0.0" ) );
 
 selRows = dt << get selected rows;
 If( N Rows( selRows ) > 0,
  column( dt, colNames[i] )[selRows] = "0";
 );
 
 dt << invert row selection;
 selRows = dt << get selected rows;
 
 If( N Rows( selRows ) > 0,
  For( k = 1, k <= N Rows( selRows ), k++,
   theRow = selRows[k];
   Column( dt, colNames[i] )[theRow] = Substr( Column( dt, colNames[i] )[theRow], 2 );
  );
  Column( dt, colNames[i] ) << data type( numeric ) << modeling type( continuous );
 );
);

// Save the NonDetect version of the data
dt << save( "<YOUR path="">\NonDetect.csv" );

Close( dt, nosave );</YOUR>