345584_zh-TW

liliconcom

獲取具有特定值的列名

你好，

 

我剛開始在工作中使用jmp並嘗試學習JSL。 我正在嘗試編寫一個腳本來獲取行值=1 的列名稱。 例如，該表在不同的 IMCX 列下顯示 0 和 1 的值，我想找出每行中哪一列的值 = 1 並存儲在名為 IMC 的新列下。 在第一行中，IMC 4 列有 1，因此我在 IMC 下手動輸入 IMC4。 表中有超過 5000 行。 感謝您的幫助。

 

 

自動化和腳本編寫回复：獲取具有特定值的列名

嗨伊恩，
花一些時間閱讀幫助>書籍部分並將關鍵字從 loc 更改為 contains 現在有效。 謝謝噸！

// Get a list of the column names
colList = dt << getColumnNames( "String" );

// Get the '0's and '1's into a matrix
mat = dt << getAsMatrix;

// Value to search for
searchVal = 1;

// Add an 'expression' column and loop rowise . . .
newCol = dt << New Column( "Value " || Char( searchVal ) || " found in Column:", Expression );
for( r = 1, r <= N Row( dt ), r++,
    newCol[r] = colList[Contains( mat[r, 0], searchVal )]
);

回复：獲取具有特定值的列名感謝您的時間和幫助。 我無法成功運行該腳本。 它創建了一個 IMC 列，但其中沒有任何價值。 我將調試並嘗試更好地理解代碼。回复：獲取具有特定值的列名嗨伊恩，感謝您的時間和解釋。 我能理解你的代碼。 但是，當我運行將 dt 設置為當前表的腳本時，結果表達式列中出現空（）。 我在這裡學習還有很長的路要走。 感謝大家的意見和建議回复：獲取具有特定值的列名感謝您的時間和幫助。 很抱歉我沒有把問題說清楚。 在每一行中，一列下只有一個 1 的實例，我需要選擇列名稱並保存在新列下。回复：獲取具有特定值的列名太感謝了。 有效！回复：獲取具有特定值的列名

關於 ”我剛開始在工作中使用 jmp 並嘗試學習 JSL “（並且已經暗示）有很多方法可以完成工作。

 

因此，本著這種精神，請找到另一個細微的變化。 使用“幫助>書籍>腳本指南”來了解它是如何工作的。

Names Default To Here( 1 );

// Create a sample data table
dt = New Table( "Example",
 Add Rows( 2 ),
 New Column( "IMCO", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC1", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 0] ) ),
 New Column( "IIMC2", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC3", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 1] ) ),
 New Column( "IMC4", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 0] ) ),
 New Column( "IMC5", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC6", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 1] ), Set Display Width( 46 ) ),
 New Column( "IMC7", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC8", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC9", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC10", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC11", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
);

// Get a list of the column names
colList = dt << getColumnNames("String");

// Get the '0's and '1's into a matrix
mat = dt << getAsMatrix;

// Value to search for
searchVal = 1;

// Add an 'expression' column and loop rowise . . .
newCol = dt << NewColumn("Value "||Char(searchVal)||" found in Column:", Expression);
for (r=1, r<=NRow(dt), r++,
 newCol[r] = colList[Loc(mat[r, 0], searchVal)];
);

 

回复：獲取具有特定值的列名

我的解決方案（使用與 txnelson 相同的示例數據表）：

Names Default To Here( 1 );

// Create a sample data table
dt = New Table( "Example",
 Add Rows( 2 ),
 New Column( "IMCO", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC1", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IIMC2", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC3", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC4", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 0] ) ),
 New Column( "IMC5", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC6", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 1] ), Set Display Width( 46 ) ),
 New Column( "IMC7", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC8", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC9", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC10", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC11", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "RESIDUAL", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [32.3201037, 20.54947] ) ),
 New Column( "TEST", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [., .] ) )
);

//get wanted columns. Assumes that IMC columns start from column 1 and all IMC columns are one after another
colNames = dt << Get Column Names("String");
For(i = N Items(colNames), i > 0 , i--,
 If(!Starts With(colNames[i], "IMC"),
  Remove From(colNames, i),
  break(); //we can break on first occurance
 );
);

//new column with IMC with value 1
dt << New Column("IMC", Character, 
 << Set Each Value(
  rowValues = dt[Row(), 0][1::N Items(colNames)]; //get values of row as matrix
  colIndex = Contains(rowValues, 1); //get index of
  If(colIndex > 0, //handle 
   colNames[colIndex], //get column name with the index
   ""; //when no 1 found use ""
  );
 );
);

我喜歡第一個解決方案@tx尼爾森由於兩（三）個原因而提供：

1. 它使用直接從 JMP 找到的許多菜單項，您可以幾乎只需從數據表源中復制粘貼即可編寫腳本，無需任何 JSL 知識
2. 它使用非常類似於 JSL 的語法<<選擇在哪裡等等
3. （我認為解決方案中呈現的那種工作流程非常像 JMP）

回复：獲取具有特定值的列名

我對初始輸入的理解是，給定行上超過 1 列的值為 1。如果不是這種情況，則以下 2 個腳本不是要使用的方法。

這是執行此操作的一種方法

Names Default To Here( 1 );

// Create a sample data table
dt = New Table( "Example",
 Add Rows( 2 ),
 New Column( "IMCO", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC1", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IIMC2", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC3", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC4", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 0] ) ),
 New Column( "IMC5", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC6", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 1] ), Set Display Width( 46 ) ),
 New Column( "IMC7", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC8", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC9", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC10", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC11", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "RESIDUAL", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [32.3201037, 20.54947] ) ),
 New Column( "TEST", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [., .] ) )
);

// Generate a list of columns to be processed
colNamesList = dt << get column names(string);
remove from (colNamesList,13,2);

// Find the maximum( the ones ) for the columns in question
dtSum = dt<< Summary(
 Max(colNamesList),
 Freq( "None" ),
 Weight( "None" ),
 statistics column name format( "column" ),
 Link to original data table( 0 )
);

// Stack all of the summarized columns
dtStack = dtSum << Stack(
 columns(
  colNamesList
 ),
 Source Label Column( "IMC" ),
 Stacked Data Column( "Data" )
);

close( dtSum, nosave );

// Delete all data that do not have 1 
dtStack << select where(:Data !=1 );
dtStack << delete rows;

// Delete the unwanted columns from the summary table
dtStack << delete columns( {N Rows, data} );

// If there are more rows than in the original table, add rows
if( n rows( dt ) < N Rows( dtStack ),
 dt << add rows( N Rows( dtStack ) - N Rows( dt ) )
);

// Add the column names for the columns found with 1's in their data
dt << Update( With( dtStack ) );

close( dtStack, nosave );

這是第二種方法：在此示例中，給定行上的多個列的值為 1

Names Default To Here( 1 );

// Create a sample data table
dt = New Table( "Example",
 Add Rows( 2 ),
 New Column( "IMCO", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC1", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 0] ) ),
 New Column( "IIMC2", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC3", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 1] ) ),
 New Column( "IMC4", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 0] ) ),
 New Column( "IMC5", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC6", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 1] ), Set Display Width( 46 ) ),
 New Column( "IMC7", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC8", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC9", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC10", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "IMC11", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 0] ) ),
 New Column( "RESIDUAL", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [32.3201037, 20.54947] ) ),
 New Column( "TEST", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [., .] ) ),
 New Column( "IMC", Character, "Nominal", Set Values( {"IMC1", "IMC3"} ) )
);

// Generate a list of columns to be processed
colNamesList = dt << get column names( string );
Remove From( colNamesList, 13, 2 );
foundNamesList = {};

For( i = 1, i <= N Items( colNamesList ), i++,
 theRows = dt << get rows where( As Column( colNamesList[i] ) == 1 );
 If( N Rows( theRows ) > 0,
  Insert Into( foundNamesList, colNamesList[i] )
 );
);

dt << New Column( "IMC", character );

// If there are more rows than in the original table, add rows
If( N Rows( dt ) < N Items( foundNamesList ),
 dt << add rows( N Items( foundNamesList ) - N Rows( dt ) )
);

If( N Items( foundNamesList ) > 0,
 dt:IMC << set values( foundNamesList )
);

回复：獲取具有特定值的列名

你可以這樣做：

 

結果列中的公式如下：

If( :A == 0,
 "",
 Char( Column Name( 1 ) )
) || If( :B == 0,
 "",
 Char( Column Name( 2 ) )
) || If( :C == 0,
 "",
 Char( Column Name( 3 ) )
) || If(  == 0,
 "",
 Char( Column Name( 4 ) )
)

我用連接關鍵字檢查每一列是否為 1。

讓我知道它是否適合您！

回覆：取得具有特定值的列名

嗨，jthi，

如果我的資料類似於此範例，但所有列都是數字（連續資料），並且我想選擇帶有截斷的列名稱，例如> 3000（1 行中可能有多列> 3000），我應該在腳本上做什麼？
我使用以下腳本

colNames = dt <<取得列名稱( "String" );

對於(i = N 項(colNames), i > 3000 , i--,

）；

但它不起作用。

最好的