nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Siemens Community Catalyst Program

The Siemens Community Catalyst program was co-created with our community to acknowledge technology leaders who consistently contribute to the Siemens Community. Nominations are accepted on a rolling basis.

Nominate Now

Count Price Changes

gianluca_scheid

Dear RapidMiner Community

I have a big data set with product name, retailer name and price at different points in time (time stamps).
My goal is to group the data by product and retailer name and count the number of price changes across all time stamps.

Is there an easy way to do this?

Thank you for your help

Best regards
GL

Find more posts tagged with

AI Studio

Aggregate

Accepted answers

MartinLiebig

Ahh!

The attached process should do the trick. It needs Operator Toolbox to run. Sadly i have no time to add additional comments right now. I hope this works for you.

<?xml version="1.0" encoding="UTF-8"?><process version="9.1.000"><br>  <context><br>    <input/><br>    <output/><br>    <macros/><br>  </context><br>  <operator activated="true" class="process" compatibility="9.1.000" expanded="true" name="Process"><br>    <parameter key="logverbosity" value="init"/><br>    <parameter key="random_seed" value="2001"/><br>    <parameter key="send_mail" value="never"/><br>    <parameter key="notification_email" value=""/><br>    <parameter key="process_duration_for_mail" value="30"/><br>    <parameter key="encoding" value="SYSTEM"/><br>    <process expanded="true"><br>      <operator activated="true" class="operator_toolbox:create_exampleset" compatibility="1.8.000-SNAPSHOT" expanded="true" height="68" name="Create ExampleSet" width="90" x="112" y="85"><br>        <parameter key="generator_type" value="comma_separated_text"/><br>        <parameter key="number_of_examples" value="100"/><br>        <parameter key="use_stepsize" value="false"/><br>        <list key="function_descriptions"/><br>        <parameter key="add_id_attribute" value="false"/><br>        <list key="numeric_series_configuration"/><br>        <list key="date_series_configuration"/><br>        <list key="date_series_configuration (interval)"/><br>        <parameter key="date_format" value="yyyy-MM-dd HH:mm:ss"/><br>        <parameter key="input_csv_text" value="product, retailer, price&#10;Product X, Retailer Y, 500&#10;Product X, Retailer Y, 500&#10;Product X, Retailer Y, 550&#10;Product X, Retailer Y, 600&#10;Product X, Retailer Y, 500&#10;Product X, Retailer Y, 600&#10;Product Y, Retailer Y, 500&#10;Product Y, Retailer Y, 500&#10;Product Y, Retailer Y, 550&#10;Product Y, Retailer Y, 600&#10;Product Y, Retailer Y, 500&#10;Product Y, Retailer Y, 600"/><br>        <parameter key="column_separator" value=","/><br>        <parameter key="parse_all_as_nominal" value="false"/><br>        <parameter key="decimal_point_character" value="."/><br>        <parameter key="trim_attribute_names" value="true"/><br>      </operator><br>      <operator activated="true" class="generate_concatenation" compatibility="9.1.000" expanded="true" height="82" name="Generate Concatenation" width="90" x="246" y="85"><br>        <parameter key="first_attribute" value="product"/><br>        <parameter key="second_attribute" value="retailer"/><br>        <parameter key="separator" value="_"/><br>        <parameter key="trim_values" value="false"/><br>      </operator><br>      <operator activated="true" class="operator_toolbox:group_into_collection" compatibility="1.8.000-SNAPSHOT" expanded="true" height="82" name="Group Into Collection" width="90" x="447" y="85"><br>        <parameter key="group_by_attribute" value="product_retailer"/><br>        <parameter key="group_by_attribute (numerical)" value=""/><br>        <parameter key="sorting_order" value="none"/><br>      </operator><br>      <operator activated="true" class="loop_collection" compatibility="9.1.000" expanded="true" height="82" name="Loop Collection" width="90" x="581" y="85"><br>        <parameter key="set_iteration_macro" value="false"/><br>        <parameter key="macro_name" value="iteration"/><br>        <parameter key="macro_start_value" value="1"/><br>        <parameter key="unfold" value="false"/><br>        <process expanded="true"><br>          <operator activated="true" class="operator_toolbox:generate_session_id" compatibility="1.8.000-SNAPSHOT" expanded="true" height="82" name="Generate Session ID" width="90" x="112" y="34"><br>            <parameter key="date_attribute" value="price"/><br>            <parameter key="gap_threshold" value="0.0"/><br>            <parameter key="gap_unit" value="none"/><br>            <parameter key="use_absolutes" value="false"/><br>          </operator><br>          <operator activated="true" class="remove_duplicates" compatibility="9.1.000" expanded="true" height="103" name="Remove Duplicates" width="90" x="246" y="34"><br>            <parameter key="attribute_filter_type" value="single"/><br>            <parameter key="attribute" value="Session id"/><br>            <parameter key="attributes" value=""/><br>            <parameter key="use_except_expression" value="false"/><br>            <parameter key="value_type" value="attribute_value"/><br>            <parameter key="use_value_type_exception" value="false"/><br>            <parameter key="except_value_type" value="time"/><br>            <parameter key="block_type" value="attribute_block"/><br>            <parameter key="use_block_type_exception" value="false"/><br>            <parameter key="except_block_type" value="value_matrix_row_start"/><br>            <parameter key="invert_selection" value="false"/><br>            <parameter key="include_special_attributes" value="true"/><br>            <parameter key="treat_missing_values_as_duplicates" value="false"/><br>          </operator><br>          <operator activated="true" class="aggregate" compatibility="9.1.000" expanded="true" height="82" name="Aggregate" width="90" x="447" y="34"><br>            <parameter key="use_default_aggregation" value="false"/><br>            <parameter key="attribute_filter_type" value="all"/><br>            <parameter key="attribute" value=""/><br>            <parameter key="attributes" value=""/><br>            <parameter key="use_except_expression" value="false"/><br>            <parameter key="value_type" value="attribute_value"/><br>            <parameter key="use_value_type_exception" value="false"/><br>            <parameter key="except_value_type" value="time"/><br>            <parameter key="block_type" value="attribute_block"/><br>            <parameter key="use_block_type_exception" value="false"/><br>            <parameter key="except_block_type" value="value_matrix_row_start"/><br>            <parameter key="invert_selection" value="false"/><br>            <parameter key="include_special_attributes" value="false"/><br>            <parameter key="default_aggregation_function" value="average"/><br>            <list key="aggregation_attributes"><br>              <parameter key="product" value="count"/><br>            </list><br>            <parameter key="group_by_attributes" value="retailer|product"/><br>            <parameter key="count_all_combinations" value="false"/><br>            <parameter key="only_distinct" value="false"/><br>            <parameter key="ignore_missings" value="true"/><br>          </operator><br>          <connect from_port="single" to_op="Generate Session ID" to_port="exa"/><br>          <connect from_op="Generate Session ID" from_port="exa" to_op="Remove Duplicates" to_port="example set input"/><br>          <connect from_op="Remove Duplicates" from_port="example set output" to_op="Aggregate" to_port="example set input"/><br>          <connect from_op="Aggregate" from_port="example set output" to_port="output 1"/><br>          <portSpacing port="source_single" spacing="0"/><br>          <portSpacing port="sink_output 1" spacing="0"/><br>          <portSpacing port="sink_output 2" spacing="0"/><br>        </process><br>      </operator><br>      <operator activated="true" class="append" compatibility="9.1.000" expanded="true" height="82" name="Append" width="90" x="715" y="85"><br>        <parameter key="datamanagement" value="double_array"/><br>        <parameter key="data_management" value="auto"/><br>        <parameter key="merge_type" value="all"/><br>      </operator><br>      <connect from_op="Create ExampleSet" from_port="output" to_op="Generate Concatenation" to_port="example set input"/><br>      <connect from_op="Generate Concatenation" from_port="example set output" to_op="Group Into Collection" to_port="exa"/><br>      <connect from_op="Group Into Collection" from_port="col" to_op="Loop Collection" to_port="collection"/><br>      <connect from_op="Loop Collection" from_port="output 1" to_op="Append" to_port="example set 1"/><br>      <connect from_op="Append" from_port="merged set" to_port="result 1"/><br>      <portSpacing port="source_input 1" spacing="0"/><br>      <portSpacing port="sink_result 1" spacing="0"/><br>      <portSpacing port="sink_result 2" spacing="0"/><br>    </process><br>  </operator><br></process><br><br>

All comments

MartinLiebig

Hi @gianluca_scheid ,

i think you can just use the Aggregate operator. Group by product name and relatailer and use count on price to get the #.

BR,

Martin

gianluca_scheid

Hi Martin

Thank you for your suggestion.

I had already considered this option, however, it will only count the number of distinct prices but not the number of price changes:

For example:

t1: Product X, Retailer Y, 500USD
t2: Product X, Retailer Y, 500USD
t3: Product X, Retailer Y, 550USD
t4: Product X, Retailer Y, 600USD
t5: Product X, Retailer Y, 500USD
t6: Product X, Retailer Y, 600USD

When counting prices using the Aggregate function I'd get: Product X, Retailer Y, 3 distinct prices (500, 550, 600)
However, the price changed 4 times (500 to 550, 550 to 600, 600 to 500, 500 to 600)

Best regards
Gianluca

MartinLiebig

Ahh!

The attached process should do the trick. It needs Operator Toolbox to run. Sadly i have no time to add additional comments right now. I hope this works for you.

<?xml version="1.0" encoding="UTF-8"?><process version="9.1.000"><br>  <context><br>    <input/><br>    <output/><br>    <macros/><br>  </context><br>  <operator activated="true" class="process" compatibility="9.1.000" expanded="true" name="Process"><br>    <parameter key="logverbosity" value="init"/><br>    <parameter key="random_seed" value="2001"/><br>    <parameter key="send_mail" value="never"/><br>    <parameter key="notification_email" value=""/><br>    <parameter key="process_duration_for_mail" value="30"/><br>    <parameter key="encoding" value="SYSTEM"/><br>    <process expanded="true"><br>      <operator activated="true" class="operator_toolbox:create_exampleset" compatibility="1.8.000-SNAPSHOT" expanded="true" height="68" name="Create ExampleSet" width="90" x="112" y="85"><br>        <parameter key="generator_type" value="comma_separated_text"/><br>        <parameter key="number_of_examples" value="100"/><br>        <parameter key="use_stepsize" value="false"/><br>        <list key="function_descriptions"/><br>        <parameter key="add_id_attribute" value="false"/><br>        <list key="numeric_series_configuration"/><br>        <list key="date_series_configuration"/><br>        <list key="date_series_configuration (interval)"/><br>        <parameter key="date_format" value="yyyy-MM-dd HH:mm:ss"/><br>        <parameter key="input_csv_text" value="product, retailer, price&#10;Product X, Retailer Y, 500&#10;Product X, Retailer Y, 500&#10;Product X, Retailer Y, 550&#10;Product X, Retailer Y, 600&#10;Product X, Retailer Y, 500&#10;Product X, Retailer Y, 600&#10;Product Y, Retailer Y, 500&#10;Product Y, Retailer Y, 500&#10;Product Y, Retailer Y, 550&#10;Product Y, Retailer Y, 600&#10;Product Y, Retailer Y, 500&#10;Product Y, Retailer Y, 600"/><br>        <parameter key="column_separator" value=","/><br>        <parameter key="parse_all_as_nominal" value="false"/><br>        <parameter key="decimal_point_character" value="."/><br>        <parameter key="trim_attribute_names" value="true"/><br>      </operator><br>      <operator activated="true" class="generate_concatenation" compatibility="9.1.000" expanded="true" height="82" name="Generate Concatenation" width="90" x="246" y="85"><br>        <parameter key="first_attribute" value="product"/><br>        <parameter key="second_attribute" value="retailer"/><br>        <parameter key="separator" value="_"/><br>        <parameter key="trim_values" value="false"/><br>      </operator><br>      <operator activated="true" class="operator_toolbox:group_into_collection" compatibility="1.8.000-SNAPSHOT" expanded="true" height="82" name="Group Into Collection" width="90" x="447" y="85"><br>        <parameter key="group_by_attribute" value="product_retailer"/><br>        <parameter key="group_by_attribute (numerical)" value=""/><br>        <parameter key="sorting_order" value="none"/><br>      </operator><br>      <operator activated="true" class="loop_collection" compatibility="9.1.000" expanded="true" height="82" name="Loop Collection" width="90" x="581" y="85"><br>        <parameter key="set_iteration_macro" value="false"/><br>        <parameter key="macro_name" value="iteration"/><br>        <parameter key="macro_start_value" value="1"/><br>        <parameter key="unfold" value="false"/><br>        <process expanded="true"><br>          <operator activated="true" class="operator_toolbox:generate_session_id" compatibility="1.8.000-SNAPSHOT" expanded="true" height="82" name="Generate Session ID" width="90" x="112" y="34"><br>            <parameter key="date_attribute" value="price"/><br>            <parameter key="gap_threshold" value="0.0"/><br>            <parameter key="gap_unit" value="none"/><br>            <parameter key="use_absolutes" value="false"/><br>          </operator><br>          <operator activated="true" class="remove_duplicates" compatibility="9.1.000" expanded="true" height="103" name="Remove Duplicates" width="90" x="246" y="34"><br>            <parameter key="attribute_filter_type" value="single"/><br>            <parameter key="attribute" value="Session id"/><br>            <parameter key="attributes" value=""/><br>            <parameter key="use_except_expression" value="false"/><br>            <parameter key="value_type" value="attribute_value"/><br>            <parameter key="use_value_type_exception" value="false"/><br>            <parameter key="except_value_type" value="time"/><br>            <parameter key="block_type" value="attribute_block"/><br>            <parameter key="use_block_type_exception" value="false"/><br>            <parameter key="except_block_type" value="value_matrix_row_start"/><br>            <parameter key="invert_selection" value="false"/><br>            <parameter key="include_special_attributes" value="true"/><br>            <parameter key="treat_missing_values_as_duplicates" value="false"/><br>          </operator><br>          <operator activated="true" class="aggregate" compatibility="9.1.000" expanded="true" height="82" name="Aggregate" width="90" x="447" y="34"><br>            <parameter key="use_default_aggregation" value="false"/><br>            <parameter key="attribute_filter_type" value="all"/><br>            <parameter key="attribute" value=""/><br>            <parameter key="attributes" value=""/><br>            <parameter key="use_except_expression" value="false"/><br>            <parameter key="value_type" value="attribute_value"/><br>            <parameter key="use_value_type_exception" value="false"/><br>            <parameter key="except_value_type" value="time"/><br>            <parameter key="block_type" value="attribute_block"/><br>            <parameter key="use_block_type_exception" value="false"/><br>            <parameter key="except_block_type" value="value_matrix_row_start"/><br>            <parameter key="invert_selection" value="false"/><br>            <parameter key="include_special_attributes" value="false"/><br>            <parameter key="default_aggregation_function" value="average"/><br>            <list key="aggregation_attributes"><br>              <parameter key="product" value="count"/><br>            </list><br>            <parameter key="group_by_attributes" value="retailer|product"/><br>            <parameter key="count_all_combinations" value="false"/><br>            <parameter key="only_distinct" value="false"/><br>            <parameter key="ignore_missings" value="true"/><br>          </operator><br>          <connect from_port="single" to_op="Generate Session ID" to_port="exa"/><br>          <connect from_op="Generate Session ID" from_port="exa" to_op="Remove Duplicates" to_port="example set input"/><br>          <connect from_op="Remove Duplicates" from_port="example set output" to_op="Aggregate" to_port="example set input"/><br>          <connect from_op="Aggregate" from_port="example set output" to_port="output 1"/><br>          <portSpacing port="source_single" spacing="0"/><br>          <portSpacing port="sink_output 1" spacing="0"/><br>          <portSpacing port="sink_output 2" spacing="0"/><br>        </process><br>      </operator><br>      <operator activated="true" class="append" compatibility="9.1.000" expanded="true" height="82" name="Append" width="90" x="715" y="85"><br>        <parameter key="datamanagement" value="double_array"/><br>        <parameter key="data_management" value="auto"/><br>        <parameter key="merge_type" value="all"/><br>      </operator><br>      <connect from_op="Create ExampleSet" from_port="output" to_op="Generate Concatenation" to_port="example set input"/><br>      <connect from_op="Generate Concatenation" from_port="example set output" to_op="Group Into Collection" to_port="exa"/><br>      <connect from_op="Group Into Collection" from_port="col" to_op="Loop Collection" to_port="collection"/><br>      <connect from_op="Loop Collection" from_port="output 1" to_op="Append" to_port="example set 1"/><br>      <connect from_op="Append" from_port="merged set" to_port="result 1"/><br>      <portSpacing port="source_input 1" spacing="0"/><br>      <portSpacing port="sink_result 1" spacing="0"/><br>      <portSpacing port="sink_result 2" spacing="0"/><br>    </process><br>  </operator><br></process><br><br>

gianluca_scheid

Hi Martin

Thank you for your help. Unfortunately, I don't know (yet) how to implement the xml code into my process...

Best regards
Gianluca

varunm1

@gianluca_scheid

You just need to open new process then go to menu bar View--> Show panel --> XML. Now you will get an XML window, delete code in XML window and just copy the complete code from @mschmitz and paste it there. Once you paste it click green tick mark so that you can see the processes in the process window. hope this helps

Thanks,
Varun

gianluca_scheid

@varunm1 and @mschmitz: Thank you so much for your help

Telcontar120

You can also use the Lag operator in the Finance and Economics extension to calculate the price difference between rows---and any non-zero value is then a price change. You could additionally aggregate on those if needed.

MartinLiebig

Hi @Telcontar120 ,

while this is of course technically possible, i would still recommend to use my options, since it is more memory efficient.

BR,

Martin

Telcontar120

Of course, @mschmitz, as is often the case I am simply pointing out another possible way of doing the same task. There are always so many options in RapidMiner!

Quick Links

Upcoming Events

Advancing power electronics design with Simcenter PSIM and High-Performance Computing
Join us for a community conversation on how High-Performance Computing (HPC) and Simcenter PSIM work together to meet the increasing demands of modern power electronics simulation. During this one-hour technical session, you'll discover how leveraging HPC can speed up simulations, improve scalability, and empower engineers…
Getting Started with Faster Motor Building in Simcenter Simlab
Join us for a community conversation on accelerating your motor simulation workflow with Simcenter Simlab. In this beginner-friendly session, we'll introduce the platform's automated model-building capabilities and show how easy it is to set up your first electric motor analysis. You'll see the basic steps of geometry…
Practical hybrid solver workflows in Simcenter Feko
Join us for a Community Conversation where we'll discuss practical approaches to analyzing complex electromagnetic models using hybrid solver techniques. This session will provide a general overview of how different computational methods can be combined within a single simulation to address models containing varied…

View all community events