242207_zh-TW

liliconcom

分類等級摘要

我想取得一列值的摘要，使用另一列作為「按」群組。這就像 Summary() 對 N 個類別所做的那樣，但我想要一個實際值的列表，而不僅僅是它們的數量。以這張表為例：

我想要類似的東西：

 

我想我可以使用「類別」作為 Summary() 中的子群組，然後重新編碼並組合列() 來做一些事情？還有更好或更聰明的想法嗎？我在社區裡閒逛了一下，找不到任何類似的東西（令人驚訝？）也許有一個使用關聯數組的地方？這個範例表很小，我將在其上運行的表可能有幾百萬行，其中組列有數百個“級別”。

回覆：分類等級摘要您最好將所有為您提供正確答案的答案標記為解決方案，以便未來的讀者可以看到解決問題的不同方法。回覆：分類等級摘要

感謝兩位提供的解決方案 - 我最終用關聯數組做了一些事情。我會把它放在這裡，希望這三種方法之一適用於下一個人嘗試這樣做：

 

Names Default To Here( 1 );

// Create the beginning table
dt = New Table( "Example",
 Add Rows( 11 ),
 New Column( "Group",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [1, 1, 2, 3, 4, 5, 5, 5, 6, 7, 8] )
 ),
 New Column( "Category",
  Character,
  "Nominal",
  Set Values( {"A", "A", "B", "A", "D", "X", "Y", "Z", "A", "B", "C"} )
 )
);

// let Summary do N Categories() for us
dt_sum = dt << Summary( Group( :Group ), N Categories( Category ) );

// the name of the column for which we want a list of the categories
category_col = "Category";
sum_colname = "Value List("|| category_col || ")";

// new col in the summary table to hold the list of values
dt_sum << New Column(sum_colname, Character);

// dump the group col into a matrix for quicker access
sum_groupdat = dt_sum:Group << Get As Matrix();
main_groupdat = dt:Group << Get As Matrix();

For( ri = 1, ri <= N Rows( dt_sum ), ri++, 
 // rows in main table that match the 'group by' col
 inds = Loc( main_groupdat, sum_groupdat[ri] );
 
 tmpaa = Associative Array( Column( dt, category_col )[inds] );
 keys = tmpaa << Get Keys();
 // if the category_col is numeric, need to convert the numbers to chars!
 If( Is Number( keys[1] ),
  charkeys = {};
  For( ki = 1, ki <= N Items( keys ), ki++,
   charkeys[ki] = Char( keys[ki] );
  );
 ,
  charkeys = keys
 );<BR /> // stuff all the items together and dump it into this row
 valstr = Concat Items( charkeys, "," );
 Column( dt_sum, sum_colname )[ri] = valstr;
);

 

回覆：分類等級摘要

我現在看到吉姆已經提出了一個解決方案。

這是使用製表和組合列（多重回應列）的不同方法，希望也適用於完整資料集。

 

// Groups is the example table with 2 nominal columns
DT_GroupCategory = Data Table( "Groups" );

// creates a cross table
Tab_DT_GroupCategory = DT_GroupCategory << Tabulate(
 Add Table(
  Column Table( Grouping Columns( :Category ) ),
  Row Table( Grouping Columns( :Group ) )
 )
);

// make data table from it 
DT_GroupCategory_CrossTab = Tab_DT_GroupCategory << Make Into Data Table;
DT_GroupCategory_CrossTab << Add Multiple Columns( "N Categories", 1, after( 1 ), Numeric );
Names_DT_GroupCategory_CrossTab = DT_GroupCategory_CrossTab << Get Column Names;

// replace 0 by missing and everything else by column name (probably not the most efficient way to do this)
For( i=3, i<=N Items( Names_DT_GroupCategory_CrossTab ), i++,
 Column( DT_GroupCategory_CrossTab, i ) << Data Type( "Character" );
 For( j=1, j<=N Rows( DT_GroupCategory_CrossTab ), j++,
  If( Column( DT_GroupCategory_CrossTab, i )[j] == "0",
   Column( DT_GroupCategory_CrossTab, i )[j] = "",
   Column( DT_GroupCategory_CrossTab, i )[j] = Char( Names_DT_GroupCategory_CrossTab[i] );
  )
 )
);

// make multiple response column with Cols -> Utilities -> Combine Columns
DT_GroupCategory_CrossTab << Combine Columns(
 delimiter( "," ),
 Columns( 2::N Items( Names_DT_GroupCategory_CrossTab ) ),
 Selected Columns are Indicator Columns( 0 ),
 Column Name( "Values(Category)" )
);

// add formula to get N Categories. +1 is added for one less comma, then div by 2 to get number of categories
Column( DT_GroupCategory_CrossTab, "N Categories" ) << Formula( (Length( :Name( "Values(Category)" ) ) + 1) / 2 );

Tab_DT_GroupCategory << Close Window;

回覆：分類等級摘要

我認為開始的方法是對資料表進行拆分，然後從那裡開始工作。以下是我編寫的一個簡單腳本，旨在為您提供所需的結果。如果您需要以互動方式執行此操作，只需按照腳本中的步驟操作即可

Names Default To Here( 1 );

// Create the beginning table
dt = New Table( "Example",
 Add Rows( 11 ),
 New Column( "Group",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [1, 1, 2, 3, 4, 5, 5, 5, 6, 7, 8] )
 ),
 New Column( "Category",
  Character,
  "Nominal",
  Set Values( {"A", "A", "B", "A", "D", "X", "Y", "Z", "A", "B", "C"} )
 )
);

// Change the group column to character so
// split columns are blanks and not missing values
dt:Group << data type( character );

// Split the columns
dtSplit = dt << Split( Split By( :Category ), Split( :Group ), Group( :Group ), Sort by Column Property );

// Change the data type back
dt:Group << data type( numeric );
dtSplit:Group << data type( numeric );

// Convert all of the non blank entries to the value of the column name
For( i = 2, i <= N Cols( dtSplit ), i++,
 theRows = dtSplit << get rows where( As Column( dtsplit, i ) != "" );
 If( N Rows( theRows ) > 0,
  Column( dtSplit, i )[theRows] = Column( dtsplit, i ) << get name
 );
);

// get all of the character column names
namesList = dtSplit << get column names( character, string );

// Create the combined column
dtSplit << Combine Columns(
 delimiter( "," ),
 Columns( :a, :b, :c, :d, :X, :y, :z ),
 Column Name( "Values (Category)" )
);

// Count the number of combined values
dtSplit << New Column( "N Categories (Category)",
 Numeric,
 "Continuous",
 Format( "Best", 12 ),
 Formula(
  count = 1;
  While( Word( count, :Name( "Values (Category)" ), "," ) != "", count++ );
  count = count - 1;
 )
);

// get rid of the formula
dtSplit:Name("N Categories (Category)") << delete property(formula);

// delete the unnecessary columns
dtSplit << delete columns( namesList );