260160_zh-TW

liliconcom

根據另一列的內容連接一列中的字符串

你好，

 

我有一個包含 2 列“第 1 列”和“第 2 列”的表格。我想找到一個腳本，其中第 2 列中的行根據第 1 列中的項目（見下面的第二個表）連接起來並放在一個新表中

 

第 1 列	第 2 欄
第 1 項	第 1 項
第 1 項	第 2 項
第 2 項	第 3 項
第 2 項	第 1 項

 

 

第 1 列	第 2 欄
第 1 項	第 1 項 |第 2 項
第 2 項	第 3 項 |第 1 項

 

任何建議將不勝感激。

 

謝謝

回复：根據另一列的內容連接一列中的字符串奇蹟般有效。

多謝

塞巴斯蒂安回复：根據另一列的內容連接一列中的字符串

是的，可變數量的蔬菜會使這更複雜。 在腳本而不是公式中構建連接值可能更容易。

 

看看這個版本的腳本，看看我會怎麼做。

 

orig = New Table( "Fruit vs Veggies",
 Add Rows( 48 ),
 New Column( "Fruit",
  Character,
  "Nominal",
  Set Values(
   {"Polb", "Polb", "Polb", "Polb", "Polb", "Polb", "Polb", "Polb", "Polb", "Polb",
   "Polb", "Polb", "Polb", "Polb", "Polb", "Polb", "Polb", "Polb", "Polb", "Polb",
   "Polb", "Polb", "Polb", "Polb", "Polb", "Polb", "Polb", "Chrnb2", "Chrnb2", "Chrnb2",
   "Chrnb2", "Chrnb2", "Chrnb2", "Chrnb2", "Chrnb2", "Chrnb2", "Chrnb2", "Chrnb2",
   "Chrnb2", "Chrnb2", "Chrnb2", "Chrnb2", "Chrnb2", "Chrnb2", "Chrnb2", "Chrnb2",
   "Chrnb2", "Chrnb2"}
  )
 ),
 New Column( "Vegetable",
  Character,
  "Nominal",
  Set Values(
   {"nuclear chromosome", "cellular_component", "intracellular", "cell", "nucleus",
   "chromosome", "organelle", "immune system process", "biological_process",
   "anatomical structure development", "DNA metabolic process",
   "cellular nitrogen compound metabolic process", "molecular_function", "cytoplasm",
   "cytoskeleton", "DNA binding", "nucleotidyltransferase activity",
   "biosynthetic process", "response to stress", "cell death", "signal transduction",
   "cytoskeletal protein binding", "lyase activity", "enzyme binding",
   "homeostatic process", "ion binding", "protein complex",
   "cellular component assembly", "cellular nitrogen compound metabolic process",
   "transmembrane transport", "ion binding", "cell proliferation",
   "immune system process", "nervous system process", "response to stress",
   "cell-cell signaling", "molecular_function", "cell", "cell morphogenesis",
   "biological_process", "anatomical structure development", "cell differentiation",
   "transport", "signal transduction", "plasma membrane", "cellular_component",
   "signal transducer activity", "transmembrane transporter activity"}
  )
 ),
 New Column( "Vegetable number",
  Numeric,
  "Nominal",
  Format( "Best", 9 ),
  Formula( Col Rank( :Vegetable, :Fruit ) )
 )
);

split = orig << Split(
 Split By( :Vegetable number ),
 Split( :Vegetable ),
 Group( :Fruit ),
 Sort by Column Property
);

vc = {};

For( i = 1, i <= N Row( split ), i++,
 vc[i] = Column( 2 )[i];
 For( j = 3, j <= N Col( split ), j++,
  If( Column( j )[i] != "",
   vc[i] = vc[i] || ", " || Column( j )[i]
  )
 );
);

split << New Column( "Veggies_concat", character, set values( vc ) );

回复：根據另一列的內容連接一列中的字符串

嗨，傑夫，

 

非常感謝您的快速回复。你對問題的描述就此打住。

但是，蔬菜類型的數量可能會因每種水果而異，這將導致填充的列數不同。 這導致在末尾有額外的逗號/分隔符（可以清理）。 此外，一旦編寫了腳本，它就不允許在每次執行腳本時自動調整不同數量的不同項目（即蔬菜）。

 

任何關於能夠在拆分步驟後自動調整列數的建議將不勝感激。

 

多謝

 

塞巴斯蒂安

 

New Table( "Fruit vs Veggies",
 Add Rows( 48 ),
 New Script(
  "Source",
  Data Table( "mart_export (1)" ) << Subset(
   Selected Rows( 0 ),
   Rows(
    [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
    21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38,
    39, 40, 41, 42, 43, 44, 45, 46, 47, 48]
   ),
   columns( :Fruit, :Vegetable )
  )
 ),
 New Script(
  "Concatenate vegetables per fruits",
  dt = Data Table( "Fruit vs Veggies" );
  New Column( "Vegetable number",
   Character,
   "Nominal",
   formula( Col Rank( :Vegetable, :Fruit ) )
  );
  Data Table( "Fruit vs Veggies" ) << Split(
   Split By( :Vegetable number ),
   Split( :Vegetable ),
   Group( :Fruit ),
   Output Table( "Strange fruits" ),
   Sort by Column Property
  );
  Data Table( "Strange fruits" ) << New Column( "Vegggies_concat",
   Character,
   "Nominal",
   formula(
    :Name( "1" ) || ", " || :Name( "2" ) || ", " || :Name( "3" ) || ", " ||
    :Name( "4" ) || ", " || :Name( "5" ) || ", " || :Name( "6" ) || ", " ||
    :Name( "7" ) || ", " || :Name( "8" ) || ", " || :Name( "9" ) || ", " ||
    :Name( "10" ) || ", " || :Name( "11" ) || ", " || :Name( "12" ) || ", "
     || :Name( "13" ) || ", " || :Name( "14" ) || ", " || :Name( "15" ) ||
    ", " || :Name( "16" ) || ", " || :Name( "17" ) || ", " || :Name( "18" )
     || ", " || :Name( "19" ) || ", " || :Name( "20" ) || ", " ||
    :Name( "21" ) || ", " || :Name( "22" ) || ", " || :Name( "23" ) || ", "
     || :Name( "24" ) || ", " || :Name( "25" ) || ", " || :Name( "26" ) ||
    ", " || :Name( "27" )
   )
  );
  Data Table( "Strange fruits" ) << Subset(
   Output Table( "Strange fruits_clean" ),
   All rows,
   columns( :Fruit, :Vegggies_concat )
  ) << close window;
 ),
 New Column( "Fruit",
  Character,
  "Nominal",
  Set Values(
   {"Polb", "Polb", "Polb", "Polb", "Polb", "Polb", "Polb", "Polb", "Polb",
   "Polb", "Polb", "Polb", "Polb", "Polb", "Polb", "Polb", "Polb", "Polb",
   "Polb", "Polb", "Polb", "Polb", "Polb", "Polb", "Polb", "Polb", "Polb",
   "Chrnb2", "Chrnb2", "Chrnb2", "Chrnb2", "Chrnb2", "Chrnb2", "Chrnb2",
   "Chrnb2", "Chrnb2", "Chrnb2", "Chrnb2", "Chrnb2", "Chrnb2", "Chrnb2",
   "Chrnb2", "Chrnb2", "Chrnb2", "Chrnb2", "Chrnb2", "Chrnb2", "Chrnb2"}
  )
 ),
 New Column( "Vegetable",
  Character,
  "Nominal",
  Set Values(
   {"nuclear chromosome", "cellular_component", "intracellular", "cell",
   "nucleus", "chromosome", "organelle", "immune system process",
   "biological_process", "anatomical structure development",
   "DNA metabolic process", "cellular nitrogen compound metabolic process",
   "molecular_function", "cytoplasm", "cytoskeleton", "DNA binding",
   "nucleotidyltransferase activity", "biosynthetic process",
   "response to stress", "cell death", "signal transduction",
   "cytoskeletal protein binding", "lyase activity", "enzyme binding",
   "homeostatic process", "ion binding", "protein complex",
   "cellular component assembly",
   "cellular nitrogen compound metabolic process", "transmembrane transport",
   "ion binding", "cell proliferation", "immune system process",
   "nervous system process", "response to stress", "cell-cell signaling",
   "molecular_function", "cell", "cell morphogenesis", "biological_process",
   "anatomical structure development", "cell differentiation", "transport",
   "signal transduction", "plasma membrane", "cellular_component",
   "signal transducer activity", "transmembrane transporter activity"}
  )
 ),
 New Column( "Vegetable number",
  Numeric,
  "Nominal",
  Format( "Best", 9 ),
  Formula( Col Rank( :Vegetable, :Fruit ) )
 )
)

 

回复：根據另一列的內容連接一列中的字符串

@txnelson的解決方案可能是你想要的，但你的描述和例子並沒有讓我清楚你在尋找什麼。

 

我將重命名您表格中的一些內容，以明確我認為您想要的內容（不過我可能對您想要的內容有誤），然後向您展示如何獲得它：

 

你有什麼：

 

水果	蔬菜
蘋果	蘆筍
蘋果	西蘭花
香蕉	小洋白菜
香蕉	蘆筍

 

你想要什麼：

 

水果	蔬菜
蘋果	蘆筍 |西蘭花
香蕉	球芽甘藍 |蘆筍

 

要得到它可以使用表格->拆分得到一個數據表，但你還需要一列來確定每行的順序是哪個數字蔬菜。 所以，像這樣：

 

水果	蔬菜	蔬菜號
蘋果	蘆筍	1個
蘋果	西蘭花	2個
香蕉	小洋白菜	1個
香蕉	蘆筍	2個

 

如果您還沒有蔬菜號數據表中的列有幾種創建方法，具體取決於您的情況，因此請返回此處尋求有關此步驟的更多幫助。

 

然後使用表格->拆分拆分依據 =蔬菜號, 拆分列 =蔬菜, 組 =水果.

 

 

 

你會得到一個這樣的表：

 

水果	1個	2個
蘋果	蘆筍	西蘭花
香蕉	小洋白菜	蘆筍

 

然後，您只需使用公式編輯器連接第 1 列和第 2 列。

 

 

我希望這有幫助。 如果沒有，請明確說明您擁有什麼和想要什麼。

 

回复：根據另一列的內容連接一列中的字符串

for each row( :Column 2 = :Column 1 || :Column 2);

// or if you want the "|" between the values it would be

for each row( :Column 2 = :Column 1 || "|" || :Column 2);