260160_zh-CN

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);