287854_zh-TW

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>