The Altair Community is migrating to a new platform to provide a better experience for you. In preparation for the migration, the Altair Community is on read-only mode from October 28 - November 6, 2024. Technical support via cases will continue to work as is. For any urgent requests from Students/Faculty members, please submit the form linked here
Crear nuevo atributo a partir de datos acumulados [Disaggregates cummulative values]
Contributor I
[Scroll down for English]
Estimados todos,
Agradezco su ayuda con el siguiente problema. Deseo calcular un nuevo atributo (columna) desagregando los datos acumulados de otro atributo que son los datos conocidos y disponibles. Por ejemplo, mi tabla tiene, entre muchos otros, los siguientes atributos: Product_ID, Orders_to_Date y Date.
Orders_to_Date tiene el acumulado de pedidos que ha recibido cada producto hasta la fecha (date). Lo que necesito es entonces, una formula para generar un atributo que me permita restar Orders_to_Date(t-1) de Orders_to_Date(t), donde Orders_to_Date(t-1) son los pedidos acumulados al día anterior y Orders_to_Date(t) son los pedidos acumulados a la fecha, de esta forma puedo tener el número diario de pedidos de cada producto.
En excel lo puedo hacer muy fácil (ver imagen) pero no lo he logrado con Rapidminer y hacerlo en excel y exportarlo luego a Rapidminer no es una opción porque el volumen de registros no lo puede manejar excel.
Espero sus sugerencias. Gracias a todos!
**********English post starts from here **********
Hello all!
I want to calculate an attribute (column) that disaggregates the cummulative values which are the known values. For instance, my table has the following fields (attributes): Product_ID, Orders_to_date, and Date.
Orders_to_date has the cummulative total of orders the product has acummulated until that date. Now I need the code to calculate the attribute that substracts Orders_to_date(t-1) from Orders_to_date(t) for each product and date in the source table. Hope the pic above helps to explains it better.
Hope to read your suggestions soon. Thanks!
Estimados todos,
Agradezco su ayuda con el siguiente problema. Deseo calcular un nuevo atributo (columna) desagregando los datos acumulados de otro atributo que son los datos conocidos y disponibles. Por ejemplo, mi tabla tiene, entre muchos otros, los siguientes atributos: Product_ID, Orders_to_Date y Date.
Orders_to_Date tiene el acumulado de pedidos que ha recibido cada producto hasta la fecha (date). Lo que necesito es entonces, una formula para generar un atributo que me permita restar Orders_to_Date(t-1) de Orders_to_Date(t), donde Orders_to_Date(t-1) son los pedidos acumulados al día anterior y Orders_to_Date(t) son los pedidos acumulados a la fecha, de esta forma puedo tener el número diario de pedidos de cada producto.
En excel lo puedo hacer muy fácil (ver imagen) pero no lo he logrado con Rapidminer y hacerlo en excel y exportarlo luego a Rapidminer no es una opción porque el volumen de registros no lo puede manejar excel.
Espero sus sugerencias. Gracias a todos!
**********English post starts from here **********
Hello all!
I want to calculate an attribute (column) that disaggregates the cummulative values which are the known values. For instance, my table has the following fields (attributes): Product_ID, Orders_to_date, and Date.
Orders_to_date has the cummulative total of orders the product has acummulated until that date. Now I need the code to calculate the attribute that substracts Orders_to_date(t-1) from Orders_to_date(t) for each product and date in the source table. Hope the pic above helps to explains it better.
Hope to read your suggestions soon. Thanks!
1
Best Answer
-
MarcoBarradas
Administrator, Employee-RapidMiner, RapidMiner Certified Analyst, Member Posts: 272 
Unicorn
Hola @juarente
Puedes usar LAG como te mencionaron o también puedes hacer uso de loops y Differentiate porque veo que tienes un ID de producto que se repite en el tiempo por lo que tu operación se basa en el conjunto de ID y de fechas para poder obtener el resultado que deseas.
Aquí te pongo un ejemplo. Construí info en RM para replicar un poco tu Data Set la puedes omitir y conectar a tus datos.<?xml version="1.0" encoding="UTF-8"?><process version="9.3.001"> <context> <input/> <output/> <macros/> </context> <operator activated="true" class="process" compatibility="9.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="SYSTEM"/> <process expanded="true"> <operator activated="true" class="subprocess" compatibility="9.3.001" expanded="true" height="68" name="Creates Sales Cumulative Data" width="90" x="112" y="34"> <process expanded="true"> <operator activated="true" class="generate_transaction_data" compatibility="9.3.001" expanded="true" height="68" name="Generate Transaction Data" width="90" x="45" y="34"> <parameter key="number_transactions" value="10000"/> <parameter key="number_customers" value="1000"/> <parameter key="number_items" value="10"/> <parameter key="number_clusters" value="10"/> <parameter key="use_local_random_seed" value="false"/> <parameter key="local_random_seed" value="1992"/> </operator> <operator activated="true" class="generate_attributes" compatibility="9.3.001" expanded="true" height="82" name="Generate Attributes" width="90" x="179" y="34"> <list key="function_descriptions"> <parameter key="Date" value="date_add(date_now(),rint(rand()*100),DATE_UNIT_DAY)"/> </list> <parameter key="keep_all" value="true"/> </operator> <operator activated="true" class="aggregate" compatibility="9.3.001" expanded="true" height="82" name="Aggregate" width="90" x="313" y="34"> <parameter key="use_default_aggregation" value="false"/> <parameter key="attribute_filter_type" value="all"/> <parameter key="attribute" value=""/> <parameter key="attributes" value=""/> <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="default_aggregation_function" value="average"/> <list key="aggregation_attributes"> <parameter key="Amount" value="sum"/> </list> <parameter key="group_by_attributes" value="Date|Item"/> <parameter key="count_all_combinations" value="false"/> <parameter key="only_distinct" value="false"/> <parameter key="ignore_missings" value="true"/> </operator> <operator activated="true" class="sort" compatibility="9.3.001" expanded="true" height="82" name="Sort" width="90" x="447" y="34"> <parameter key="attribute_name" value="Item"/> <parameter key="sorting_direction" value="increasing"/> </operator> <operator activated="true" class="concurrency:loop_values" compatibility="9.3.001" expanded="true" height="82" name="Loop Values" width="90" x="581" y="34"> <parameter key="attribute" value="Item"/> <parameter key="iteration_macro" value="loop_value"/> <parameter key="reuse_results" value="false"/> <parameter key="enable_parallel_execution" value="true"/> <process expanded="true"> <operator activated="true" class="filter_examples" compatibility="9.3.001" expanded="true" height="103" name="Filter Examples" width="90" x="45" y="34"> <parameter key="parameter_expression" value=""/> <parameter key="condition_class" value="custom_filters"/> <parameter key="invert_filter" value="false"/> <list key="filters_list"> <parameter key="filters_entry_key" value="Item.equals.%{loop_value}"/> </list> <parameter key="filters_logic_and" value="true"/> <parameter key="filters_check_metadata" value="true"/> </operator> <operator activated="true" class="generate_copy" compatibility="9.3.001" expanded="true" height="82" name="Generate Copy" width="90" x="246" y="34"> <parameter key="attribute_name" value="sum(Amount)"/> <parameter key="new_name" value="Cumulative"/> </operator> <operator activated="true" class="quantx1:cum_sums" compatibility="1.0.006" expanded="true" height="68" name="Cumulative Sums" width="90" x="380" y="34"> <parameter key="input attributes" value="Cumulative"/> </operator> <operator activated="true" class="rename" compatibility="9.3.001" expanded="true" height="82" name="Rename" width="90" x="514" y="34"> <parameter key="old_name" value="sum(Amount)"/> <parameter key="new_name" value="Day_Sales"/> <list key="rename_additional_attributes"/> </operator> <connect from_port="input 1" to_op="Filter Examples" to_port="example set input"/> <connect from_op="Filter Examples" from_port="example set output" to_op="Generate Copy" to_port="example set input"/> <connect from_op="Generate Copy" from_port="example set output" to_op="Cumulative Sums" to_port="example set input"/> <connect from_op="Cumulative Sums" from_port="example set output" to_op="Rename" to_port="example set input"/> <connect from_op="Rename" from_port="example set output" to_port="output 1"/> <portSpacing port="source_input 1" spacing="0"/> <portSpacing port="source_input 2" spacing="0"/> <portSpacing port="sink_output 1" spacing="0"/> <portSpacing port="sink_output 2" spacing="0"/> </process> </operator> <operator activated="true" class="append" compatibility="9.3.001" expanded="true" height="82" name="Append" width="90" x="313" y="136"> <parameter key="datamanagement" value="double_array"/> <parameter key="data_management" value="auto"/> <parameter key="merge_type" value="all"/> </operator> <operator activated="true" class="remember" compatibility="9.3.001" expanded="true" height="68" name="Remember" width="90" x="447" y="136"> <parameter key="name" value="Data"/> <parameter key="io_object" value="ExampleSet"/> <parameter key="store_which" value="1"/> <parameter key="remove_from_process" value="true"/> </operator> <connect from_op="Generate Transaction Data" from_port="output" to_op="Generate Attributes" to_port="example set input"/> <connect from_op="Generate Attributes" from_port="example set output" to_op="Aggregate" to_port="example set input"/> <connect from_op="Aggregate" from_port="example set output" to_op="Sort" to_port="example set input"/> <connect from_op="Sort" from_port="example set output" to_op="Loop Values" to_port="input 1"/> <connect from_op="Loop Values" from_port="output 1" to_op="Append" to_port="example set 1"/> <connect from_op="Append" from_port="merged set" to_op="Remember" to_port="store"/> <portSpacing port="source_in 1" spacing="0"/> <portSpacing port="sink_out 1" spacing="0"/> </process> <description align="center" color="transparent" colored="false" width="126">Disable if you already have cumulative data it uses the financial extension</description> </operator> <operator activated="true" class="recall" compatibility="9.3.001" expanded="true" height="68" name="Recall" width="90" x="112" y="340"> <parameter key="name" value="Data"/> <parameter key="io_object" value="ExampleSet"/> <parameter key="remove_from_store" value="false"/> <description align="center" color="transparent" colored="false" width="126">Replace for your data</description> </operator> <operator activated="true" class="select_attributes" compatibility="9.3.001" expanded="true" height="82" name="Removes sales by day from the ExampleSet I created" width="90" x="246" y="340"> <parameter key="attribute_filter_type" value="single"/> <parameter key="attribute" value="Day_Sales"/> <parameter key="attributes" value=""/> <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="true"/> <parameter key="include_special_attributes" value="false"/> </operator> <operator activated="true" class="concurrency:loop_values" compatibility="9.3.001" expanded="true" height="82" name="Loop over each product_id" width="90" x="380" y="340"> <parameter key="attribute" value="Item"/> <parameter key="iteration_macro" value="item_loop"/> <parameter key="reuse_results" value="false"/> <parameter key="enable_parallel_execution" value="true"/> <process expanded="true"> <operator activated="true" class="filter_examples" compatibility="9.3.001" expanded="true" height="103" name="Filter the data by item" width="90" x="112" y="34"> <parameter key="parameter_expression" value=""/> <parameter key="condition_class" value="custom_filters"/> <parameter key="invert_filter" value="false"/> <list key="filters_list"> <parameter key="filters_entry_key" value="Item.equals.%{item_loop}"/> </list> <parameter key="filters_logic_and" value="true"/> <parameter key="filters_check_metadata" value="true"/> </operator> <operator activated="true" class="sort" compatibility="9.3.001" expanded="true" height="82" name="Sort from closest first" width="90" x="246" y="34"> <parameter key="attribute_name" value="Day"/> <parameter key="sorting_direction" value="decreasing"/> </operator> <operator activated="true" class="time_series:differentiation" compatibility="9.3.001" expanded="true" height="68" name="Gets the difference between two examples" width="90" x="447" y="34"> <parameter key="attribute_filter_type" value="single"/> <parameter key="attribute" value="Cumulative"/> <parameter key="attributes" value=""/> <parameter key="use_except_expression" value="false"/> <parameter key="value_type" value="numeric"/> <parameter key="use_value_type_exception" value="false"/> <parameter key="except_value_type" value="real"/> <parameter key="block_type" value="value_series"/> <parameter key="use_block_type_exception" value="false"/> <parameter key="except_block_type" value="value_series_end"/> <parameter key="invert_selection" value="false"/> <parameter key="include_special_attributes" value="false"/> <parameter key="overwrite_attributes" value="false"/> <parameter key="new_attributes_postfix" value="_differentiated"/> <parameter key="lag" value="1"/> <parameter key="differentiation_method" value="subtraction"/> </operator> <connect from_port="input 1" to_op="Filter the data by item" to_port="example set input"/> <connect from_op="Filter the data by item" from_port="example set output" to_op="Sort from closest first" to_port="example set input"/> <connect from_op="Sort from closest first" from_port="example set output" to_op="Gets the difference between two examples" to_port="example set"/> <connect from_op="Gets the difference between two examples" from_port="example set" to_port="output 1"/> <portSpacing port="source_input 1" spacing="0"/> <portSpacing port="source_input 2" spacing="0"/> <portSpacing port="sink_output 1" spacing="0"/> <portSpacing port="sink_output 2" spacing="0"/> </process> <description align="center" color="transparent" colored="false" width="126">Product Id must be nominal</description> </operator> <operator activated="true" class="append" compatibility="9.3.001" expanded="true" height="82" name="Append the results by item and date" width="90" x="581" y="289"> <parameter key="datamanagement" value="double_array"/> <parameter key="data_management" value="auto"/> <parameter key="merge_type" value="all"/> </operator> <connect from_op="Recall" from_port="result" to_op="Removes sales by day from the ExampleSet I created" to_port="example set input"/> <connect from_op="Removes sales by day from the ExampleSet I created" from_port="example set output" to_op="Loop over each product_id" to_port="input 1"/> <connect from_op="Loop over each product_id" from_port="output 1" to_op="Append the results by item and date" to_port="example set 1"/> <connect from_op="Append the results by item and date" from_port="merged set" 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>
Saludos
7
Answers
You have to use the LAG operator.
The process :
<?xml version="1.0" encoding="UTF-8"?><process version="9.3.001"> <context> <input/> <output/> <macros/> </context> <operator activated="true" class="process" compatibility="9.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="SYSTEM"/> <process expanded="true"> <operator activated="true" breakpoints="after" class="utility:create_exampleset" compatibility="9.3.001" expanded="true" height="68" name="Create ExampleSet" width="90" x="112" y="136"> <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="Product_ID,Orders_to_date,Date 1,20,1/09/2019 2,15,1/09/2019 3,5,1/09/2019 1,22,2/09/2019 2,18,2/09/2019 3,5,2/09/2019 1,28,3/09/2019 2,22,3/09/2019 3,7,3/09/2019"/> <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="time_series:lag_series" compatibility="9.3.001" expanded="true" height="82" name="Lag" width="90" x="246" y="136"> <list key="attributes"> <parameter key="Orders_to_date" value="3"/> </list> <parameter key="overwrite_attributes" value="false"/> <parameter key="extend_exampleset" value="false"/> </operator> <operator activated="true" class="generate_attributes" compatibility="9.3.001" expanded="true" height="82" name="Generate Attributes" width="90" x="380" y="136"> <list key="function_descriptions"> <parameter key="Orders" value="Orders_to_date-[Orders_to_date-3]"/> </list> <parameter key="keep_all" value="true"/> </operator> <operator activated="true" class="select_attributes" compatibility="9.3.001" expanded="true" height="82" name="Select Attributes" width="90" x="514" y="136"> <parameter key="attribute_filter_type" value="single"/> <parameter key="attribute" value="Orders_to_date-3"/> <parameter key="attributes" value=""/> <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="true"/> <parameter key="include_special_attributes" value="false"/> </operator> <connect from_op="Create ExampleSet" from_port="output" to_op="Lag" to_port="example set input"/> <connect from_op="Lag" from_port="example set output" to_op="Generate Attributes" to_port="example set input"/> <connect from_op="Generate Attributes" from_port="example set output" to_op="Select Attributes" to_port="example set input"/> <connect from_op="Select Attributes" from_port="example set output" 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>Hope this helps,
Regards,
Lionel
Muchas gracias por tu sugerencia.
Estoy tratando de ejecutar tu código pero me sale un error de "Dummy Operator" en la línea 78. Parece que me hace falta alguna extensión que tu si tenias al crear el código. ¿Alguna idea de cuál pueda ser? Gracias.
Saludos!
[Edit: Disculpa, no había leido la nota que debía tener la "Financial Extension"]
Espero te haya sido útil y cualquier cosa por aquí estamos.
Saludos.