426270_zh-TW

liliconcom

JMP腳本：將多列中的非空值複製到新列

大家好，

目前使用JMP15，
需要協助將非空值從多列複製到不同的列，

不確定我在下面正確解釋的問題是我正在尋找的

這裡的列名和計數是可變的

每行只有 2 列，也有值

輸入：

預期產出

Column1和column2是輸出列

品質和工藝工程回复：JMP 腳本：將非空值從多列複製到新列

你好@jinsejoseph ,

建立在@txnelson在 s 方法中，可能有一個使用某些內置功能的速記腳本。這是通過最大限度地點擊和最小化腳本來實現的。

此方法使用兩個命令：合併和文本到列。 combine 的優點是它會忽略缺失值。在這種情況下，它用作沒有缺失值的連接。

然後，當我們想要單獨的列中的最終數據時，文本到列就派上用場了。

請讓我們知道它是否有效或需要進一步概括以供您使用。

羅恩

dt = New Table( "original data set",
 Add Rows( 36 ),
 New Column( "Data 01",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [1, 1, 1, ., ., ., ., ., ., ., ., ., ., ., ., 11, 11, 11, ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., .] )
 ),
 New Column( "Data 02",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [., ., ., 2, 2, 2, ., ., ., ., ., ., ., ., ., ., ., ., 12, 12, 12, ., ., ., ., ., ., ., ., ., ., ., ., ., ., .] )
 ),
 New Column( "Data 03",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [., ., ., ., ., ., 3, 3, 3, ., ., ., ., ., ., ., ., ., ., ., ., 13, 13, 13, ., ., ., ., ., ., ., ., ., ., ., .] )
 ),
 New Column( "Data 04",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [., ., ., ., ., ., ., ., ., 4, 4, 4, ., ., ., ., ., ., ., ., ., ., ., ., 14, 14, 14, ., ., ., ., ., ., ., ., .] )
 ),
 New Column( "Data 05",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [., ., ., ., ., ., ., ., ., ., ., ., 5, 5, 5, ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., .] ),
  Set Display Width( 59 )
 ),
 New Column( "Data 06",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [10, 10, 10, ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., .] )
 ),
 New Column( "Data 07",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [., ., ., 20, 20, 20, ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., 200, 200, 200, ., ., ., ., ., ., ., ., .] )
 ),
 New Column( "Data 08",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [., ., ., ., ., ., 30, 30, 30, ., ., ., ., ., ., ., ., ., ., ., ., 300, 300, 300, ., ., ., ., ., ., ., ., ., ., ., .] )
 ),
 New Column( "Data 09",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [., ., ., ., ., ., ., ., ., 40, 40, 40, ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., .] ),
  Set Display Width( 67 )
 ),
 New Column( "Data 10",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [., ., ., ., ., ., ., ., ., ., ., ., 50, 50, 50, ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., .] )
 )
);
// now we get to work
// Get the columns names to combine.
colNames = dt << get column names( string, continuous );


// Combine columns using list - here is the trick, it ignores missing values
dt << Combine Columns(
 columns( eval (colNames) ),
 Column Name( "combined" ),
 Delimiter( "," ),
 Multiple Response( 0 )
);

// Text to columns - now we can uncombine the data into as many columns there is actual data. this is robust to any number of events per row.
dt << Text to Columns(
 columns( :combined ),
 Delimiters( "," )
);

回复：JMP 腳本：將非空值從多列複製到新列

這裡有幾個解決方案，它們都是我對您之前討論的回复的修改。

Loc() 函數處理缺失值，就像處理零一樣。所以下面的硬連線 JSL 可以工作。不同之處在於 Delta 並未生產。相反，為第 12 列提取第一個值，為第 13 列提取第二個值

Names Default To Here( 1 );
dt = Current Data Table();

dt << New Column( "Column 12",
 Formula(
  x = :data[Row()] || :data1[Row()] || :data2[Row()] || :data3[Row()] || :data4[Row()] ||
  :data5[Row()] || :data6[Row()] || :data7[Row()] || :data8[Row()] || :data9[Row()];
  y = Loc( x );
  Try( x[y[1]], . );
 )
);

dt << New Column( "Column 13",
 Formula(
  x = :data[Row()] || :data1[Row()] || :data2[Row()] || :data3[Row()] || :data4[Row()] ||
  :data5[Row()] || :data6[Row()] || :data7[Row()] || :data8[Row()] || :data9[Row()];
  y = Loc( x );
  Try( x[y[2]], . );
 )
);

要將其擴展到列名未知且列數可能不同的情況，下面的代碼將起作用

Names Default To Here( 1 );
dt = Current Data Table();

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

// Build the first column and formula
theExpr = "dt<<NEW column=""></NEW>