Count specific symbol in one column

Question

I have a dataset with information about books. There is a column named author where you can see the author(s) of each book. Sometimes there is more than one author, they are separated by ","

Now I need to count how many authors each book has.

My first intention was to count the commas in the column, but I just don't know how this works in Rapidminer.

I tried Generate Attributes with an if statement, but it just counts to 1 because there is no loop.

I also tried to split the column, so I have each author in one column, but I don't know how to move on.

That's what I want to have in the end.

Author: Wiebke Krabbe                              Count Authors: 1
Author: Claudia Fischer, Ilona Butterer            Count Authors: 2
Author: Juliane Seidel, Christin Hopf, Paul Walsh  Count Authors: 3

I would be so thankful if anyone could help me.

Answer 1

Your suggestion to split the author attribute on the comma separator was indeed accurate. The next step involves creating a count by aggregating the splits once again using the 'Generate Aggregation' operator. This operator functions 'row-wise' and is capable of counting the occurrences within the newly created author-columns.

Example RapidMiner process:

<?xml version="1.0" encoding="UTF-8"?><process version="10.3.001">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="10.3.001" expanded="true" name="Process">
    <parameter key="logverbosity" value="init"/>
    <parameter key="random_seed" value="2001"/>
    <parameter key="send_mail" value="never"/>
    <parameter key="notification_email" value=""/>
    <parameter key="process_duration_for_mail" value="30"/>
    <parameter key="encoding" value="UTF-8"/>
    <process expanded="true">
      <operator activated="true" class="utility:create_exampleset" compatibility="10.3.001" expanded="true" height="68" name="Create ExampleSet" width="90" x="45" y="34">
        <parameter key="generator_type" value="comma separated text"/>
        <parameter key="number_of_examples" value="100"/>
        <parameter key="use_stepsize" value="false"/>
        <list key="function_descriptions"/>
        <parameter key="add_id_attribute" value="false"/>
        <list key="numeric_series_configuration"/>
        <list key="date_series_configuration"/>
        <list key="date_series_configuration (interval)"/>
        <parameter key="date_format" value="yyyy-MM-dd HH:mm:ss"/>
        <parameter key="time_zone" value="SYSTEM"/>
        <parameter key="input_csv_text" value="book;author&#10;book1;Author: Wiebke Krabbe&#10;book2;Author: Claudia Fischer, Ilona Butterer&#10;book3;Author: Juliane Seidel, Christin Hopf, Paul Walsh"/>
        <parameter key="column_separator" value=";"/>
        <parameter key="parse_all_as_nominal" value="false"/>
        <parameter key="decimal_point_character" value="."/>
        <parameter key="trim_attribute_names" value="true"/>
      </operator>
      <operator activated="true" class="blending:generate_id" compatibility="10.3.001" expanded="true" height="82" name="Generate ID" width="90" x="179" y="34">
        <parameter key="create_nominal_ids" value="false"/>
        <parameter key="offset" value="0"/>
        <description align="center" color="transparent" colored="false" width="126">id will help to join at the end. Not necessary, if id is already present in the real dataset.</description>
      </operator>
      <operator activated="true" class="multiply" compatibility="10.3.001" expanded="true" height="103" name="Multiply" width="90" x="313" y="34"/>
      <operator activated="true" class="split" compatibility="10.3.001" expanded="true" height="82" name="Split" width="90" x="447" y="136">
        <parameter key="attribute_filter_type" value="single"/>
        <parameter key="attribute" value="author"/>
        <parameter key="attributes" value=""/>
        <parameter key="use_except_expression" value="false"/>
        <parameter key="value_type" value="nominal"/>
        <parameter key="use_value_type_exception" value="false"/>
        <parameter key="except_value_type" value="file_path"/>
        <parameter key="block_type" value="single_value"/>
        <parameter key="use_block_type_exception" value="false"/>
        <parameter key="except_block_type" value="single_value"/>
        <parameter key="invert_selection" value="false"/>
        <parameter key="include_special_attributes" value="false"/>
        <parameter key="split_pattern" value=","/>
        <parameter key="split_mode" value="ordered_split"/>
        <description align="center" color="transparent" colored="false" width="126">split author column on comma as separator into new attributes</description>
      </operator>
      <operator activated="true" class="generate_aggregation" compatibility="10.3.001" expanded="true" height="82" name="Generate Aggregation" width="90" x="581" y="136">
        <parameter key="attribute_name" value="author_count"/>
        <parameter key="attribute_filter_type" value="regular_expression"/>
        <parameter key="attribute" value=""/>
        <parameter key="attributes" value=""/>
        <parameter key="regular_expression" value="author_.*"/>
        <parameter key="use_except_expression" value="false"/>
        <parameter key="value_type" value="attribute_value"/>
        <parameter key="use_value_type_exception" value="false"/>
        <parameter key="except_value_type" value="time"/>
        <parameter key="block_type" value="attribute_block"/>
        <parameter key="use_block_type_exception" value="false"/>
        <parameter key="except_block_type" value="value_matrix_row_start"/>
        <parameter key="invert_selection" value="false"/>
        <parameter key="include_special_attributes" value="false"/>
        <parameter key="aggregation_function" value="count"/>
        <parameter key="concatenation_separator" value="|"/>
        <parameter key="keep_all" value="false"/>
        <parameter key="ignore_missings" value="true"/>
        <parameter key="ignore_missing_attributes" value="false"/>
        <description align="center" color="transparent" colored="false" width="126">count the number of newly created attributes</description>
      </operator>
      <operator activated="true" class="concurrency:join" compatibility="10.3.001" expanded="true" height="82" name="Join" width="90" x="715" y="34">
        <parameter key="remove_double_attributes" value="true"/>
        <parameter key="join_type" value="left"/>
        <parameter key="use_id_attribute_as_key" value="true"/>
        <list key="key_attributes"/>
        <parameter key="keep_both_join_attributes" value="false"/>
        <description align="center" color="transparent" colored="false" width="126">join new author count column to original dataset</description>
      </operator>
      <connect from_op="Create ExampleSet" from_port="output" to_op="Generate ID" to_port="example set input"/>
      <connect from_op="Generate ID" from_port="example set output" to_op="Multiply" to_port="input"/>
      <connect from_op="Multiply" from_port="output 1" to_op="Join" to_port="left"/>
      <connect from_op="Multiply" from_port="output 2" to_op="Split" to_port="example set input"/>
      <connect from_op="Split" from_port="example set output" to_op="Generate Aggregation" to_port="example set input"/>
      <connect from_op="Generate Aggregation" from_port="example set output" to_op="Join" to_port="right"/>
      <connect from_op="Join" from_port="join" to_port="result 1"/>
      <portSpacing port="source_input 1" spacing="0"/>
      <portSpacing port="sink_result 1" spacing="0"/>
      <portSpacing port="sink_result 2" spacing="0"/>
    </process>
  </operator>
</process>