Altair RISE

A program to recognize and reward our most engaged community members

Nominate Yourself Now!

Time rescale

Hi,
A question:
Each id has one or more attribute value(s) and attribute time(s)) (the moment the value was recorded). I would like to set the first recorded time for each id to zero which changes each recording time to the time between the current recording and the first recording time. Anyone any suggestion?
Thanks
Sven

Find more posts tagged with

AI Studio

Accepted answers

All comments

JEdward

Hi Sven,

Sorry I don't have time today to knock up a sample process, but the basic approach I'd take is.
(Assuming I understand correctly your description of the dataset.)

1. Loop values (on attribute)
2. Filter Examples to attribute
3. Loop values (on ID)
4. Filter Examples to ID
3. Sort by the attribute time.
4. Extract Macro value of first example time
5. Generate Attributes time diff: attributetime - macro

I'm assuming your dataset is in a format similar to:

ID | Attribute | Timestamp
1 | att1 | 26/2/1998 04:02:222
1 | att2 | 26/2/1998 04:02:222
1 | att1 | 26/2/1993 07:02:222
1 | att1 | 26/2/2013 02:42:232

DocMusher

Hi,
Thanks for the feedback, I will try it asap.
Cheers
Sven

DocMusher

Hi,
I have tried to follow your proposal. I think I do not fully understand it. Would it be possible to upload a XML of the process you proposed, this would help me a lot. Thanks +++
Sven

DocMusher

SOLVED
Thanks

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<process version="6.4.000">
<context>
<input/>
<output/>
<macros/>
</context>
<operator activated="true" class="process" compatibility="6.4.000" expanded="true" name="Process">
<parameter key="logfile" value="/Users/svenvanpoucke/Documents/BigData2015/RM/timerescale.log"/>
<parameter key="resultfile" value="/Users/svenvanpoucke/Documents/BigData2015/RM/timerescale.res"/>
<process expanded="true">
<operator activated="true" class="retrieve" compatibility="6.4.000" expanded="true" height="60" name="Retrieve RadoopMimic" width="90" x="33" y="46">
<parameter key="repository_entry" value="../data/RadoopMimic"/>
</operator>
<operator activated="false" class="sample" compatibility="6.4.000" expanded="true" height="76" name="Sample" width="90" x="146" y="110">
<parameter key="sample_size" value="10000"/>
<parameter key="sample_ratio" value="1.0"/>
<list key="sample_size_per_class"/>
<list key="sample_ratio_per_class"/>
<list key="sample_probability_per_class"/>
</operator>
<operator activated="true" class="filter_examples" compatibility="6.4.000" expanded="true" height="94" name="Filter Examples" width="90" x="268" y="118">
<list key="filters_list">
<parameter key="filters_entry_key" value="itemid.eq.50007"/>
<parameter key="filters_entry_key" value="itemid.eq.50428"/>
</list>
<parameter key="filters_logic_and" value="false"/>
</operator>
<operator activated="true" class="materialize_data" compatibility="6.4.000" expanded="true" height="76" name="Materialize Data" width="90" x="403" y="148"/>
<operator activated="true" class="select_attributes" compatibility="6.4.000" expanded="true" height="76" name="Select Attributes" width="90" x="536" y="148">
<parameter key="attribute_filter_type" value="subset"/>
<parameter key="attributes" value="icustay_expire_flg|charttime|itemid|valuenum|icustay_id_right"/>
<parameter key="include_special_attributes" value="true"/>
</operator>
<operator activated="true" class="set_role" compatibility="6.4.000" expanded="true" height="76" name="Set Role" width="90" x="666" y="149">
<parameter key="attribute_name" value="icustay_id_right"/>
<parameter key="target_role" value="id"/>
<list key="set_additional_roles">
<parameter key="icustay_expire_flg" value="label"/>
</list>
</operator>
<operator activated="true" class="nominal_to_date" compatibility="6.4.000" expanded="true" height="76" name="Nominal to Date" width="90" x="792" y="149">
<parameter key="attribute_name" value="charttime"/>
<parameter key="date_type" value="date_time"/>
<parameter key="date_format" value="YYYY-MM-DD HH:MM:SS"/>
</operator>
<operator activated="true" class="date_to_numerical" compatibility="6.4.000" expanded="true" height="76" name="Date to Numerical" width="90" x="929" y="148">
<parameter key="attribute_name" value="charttime"/>
<parameter key="time_unit" value="minute"/>
<parameter key="minute_relative_to" value="epoch"/>
<parameter key="day_relative_to" value="epoch"/>
</operator>
<operator activated="true" class="numerical_to_polynominal" compatibility="6.4.000" expanded="true" height="76" name="Numerical to Polynominal" width="90" x="1109" y="145">
<parameter key="attribute_filter_type" value="subset"/>
<parameter key="attributes" value="charttime"/>
</operator>
<operator activated="true" class="generate_attributes" compatibility="6.4.000" expanded="true" height="76" name="Generate Attributes (3)" width="90" x="1223" y="142">
<list key="function_descriptions">
<parameter key="test" value="str(itemid)"/>
<parameter key="patient" value="str(icustay_id_right)"/>
</list>
</operator>
<operator activated="true" class="select_attributes" compatibility="6.4.000" expanded="true" height="76" name="Select Attributes (2)" width="90" x="1350" y="144">
<parameter key="attribute_filter_type" value="subset"/>
<parameter key="attribute" value="test"/>
<parameter key="attributes" value="charttime|patient|test|valuenum"/>
</operator>
<operator activated="true" class="set_role" compatibility="6.4.000" expanded="true" height="76" name="Set Role (2)" width="90" x="1469" y="142">
<parameter key="attribute_name" value="patient"/>
<parameter key="target_role" value="id"/>
<list key="set_additional_roles">
<parameter key="icustay_expire_flg" value="label"/>
</list>
</operator>
<operator activated="true" class="select_attributes" compatibility="6.4.000" expanded="true" height="76" name="Select Attributes (4)" width="90" x="1599" y="141">
<parameter key="attribute_filter_type" value="subset"/>
<parameter key="attributes" value="charttime|icustay_expire_flg|patient|test|valuenum"/>
<parameter key="include_special_attributes" value="true"/>
</operator>
<operator activated="true" class="store" compatibility="6.4.000" expanded="true" height="60" name="Store" width="90" x="1753" y="132">
<parameter key="repository_entry" value="../data/radoopmimicstore"/>
</operator>
<operator activated="true" class="multiply" compatibility="6.4.000" expanded="true" height="76" name="Multiply" width="90" x="1886" y="122"/>
<operator activated="true" class="loop_values" compatibility="6.4.000" expanded="true" height="76" name="Loop Values" width="90" x="2008" y="122">
<parameter key="attribute" value="patient"/>
<process expanded="true">
<operator activated="true" class="filter_examples" compatibility="6.4.000" expanded="true" height="94" name="Filter Examples (2)" width="90" x="51" y="35">
<parameter key="parameter_string" value="patient = %{loop_value}"/>
<parameter key="condition_class" value="attribute_value_filter"/>
<list key="filters_list"/>
</operator>
<operator activated="true" class="aggregate" compatibility="6.4.000" expanded="true" height="76" name="Aggregate" width="90" x="209" y="44">
<list key="aggregation_attributes"/>
<parameter key="group_by_attributes" value="icustay_expire_flg|patient|charttime|test|valuenum"/>
</operator>
<operator activated="true" class="sort" compatibility="6.4.000" expanded="true" height="76" name="Sort" width="90" x="376" y="30">
<parameter key="attribute_name" value="charttime"/>
</operator>
<operator activated="true" class="extract_macro" compatibility="6.4.000" expanded="true" height="60" name="Extract Macro" width="90" x="493" y="32">
<parameter key="macro" value="mincharttime"/>
<parameter key="macro_type" value="data_value"/>
<parameter key="attribute_name" value="charttime"/>
<parameter key="example_index" value="1"/>
<list key="additional_macros"/>
</operator>
<operator activated="true" class="generate_attributes" compatibility="6.4.000" expanded="true" height="76" name="Generate Attributes (2)" width="90" x="375" y="130">
<list key="function_descriptions">
<parameter key="mincharttime" value="%{mincharttime}"/>
</list>
</operator>
<connect from_port="example set" to_op="Filter Examples (2)" to_port="example set input"/>
<connect from_op="Filter Examples (2)" 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="Aggregate" from_port="original" to_op="Generate Attributes (2)" to_port="example set input"/>
<connect from_op="Sort" from_port="example set output" to_op="Extract Macro" to_port="example set"/>
<connect from_op="Generate Attributes (2)" from_port="example set output" to_port="out 1"/>
<portSpacing port="source_example set" spacing="0"/>
<portSpacing port="sink_out 1" spacing="0"/>
<portSpacing port="sink_out 2" spacing="0"/>
</process>
</operator>
<operator activated="true" class="append" compatibility="6.4.000" expanded="true" height="76" name="Append" width="90" x="2144" y="124"/>
<operator activated="true" class="guess_types" compatibility="6.4.000" expanded="true" height="76" name="Guess Types" width="90" x="2290" y="120"/>
<operator activated="true" class="generate_attributes" compatibility="6.4.000" expanded="true" height="76" name="Generate Attributes" width="90" x="2415" y="120">
<list key="function_descriptions">
<parameter key="diffcharttime" value="(charttime-mincharttime)/60"/>
</list>
</operator>
<operator activated="true" class="select_attributes" compatibility="6.4.000" expanded="true" height="76" name="Select Attributes (3)" width="90" x="2539" y="119">
<parameter key="attribute_filter_type" value="subset"/>
<parameter key="attributes" value="patient|diffcharttime|test|valuenum|icustay_expire_flg"/>
<parameter key="include_special_attributes" value="true"/>
</operator>
<operator activated="true" class="free_memory" compatibility="6.4.000" expanded="true" height="76" name="Free Memory" width="90" x="46" y="230"/>
<connect from_port="input 1" to_op="Free Memory" to_port="through 1"/>
<connect from_op="Retrieve RadoopMimic" from_port="output" to_op="Filter Examples" to_port="example set input"/>
<connect from_op="Filter Examples" from_port="example set output" to_op="Materialize Data" to_port="example set input"/>
<connect from_op="Materialize Data" 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_op="Set Role" to_port="example set input"/>
<connect from_op="Set Role" from_port="example set output" to_op="Nominal to Date" to_port="example set input"/>
<connect from_op="Nominal to Date" from_port="example set output" to_op="Date to Numerical" to_port="example set input"/>
<connect from_op="Date to Numerical" from_port="example set output" to_op="Numerical to Polynominal" to_port="example set input"/>
<connect from_op="Numerical to Polynominal" from_port="example set output" to_op="Generate Attributes (3)" to_port="example set input"/>
<connect from_op="Generate Attributes (3)" from_port="example set output" to_op="Select Attributes (2)" to_port="example set input"/>
<connect from_op="Select Attributes (2)" from_port="example set output" to_op="Set Role (2)" to_port="example set input"/>
<connect from_op="Set Role (2)" from_port="example set output" to_op="Select Attributes (4)" to_port="example set input"/>
<connect from_op="Select Attributes (4)" from_port="example set output" to_op="Store" to_port="input"/>
<connect from_op="Store" from_port="through" to_op="Multiply" to_port="input"/>
<connect from_op="Multiply" from_port="output 1" to_op="Loop Values" to_port="example set"/>
<connect from_op="Loop Values" from_port="out 1" to_op="Append" to_port="example set 1"/>
<connect from_op="Append" from_port="merged set" to_op="Guess Types" to_port="example set input"/>
<connect from_op="Guess Types" 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 (3)" to_port="example set input"/>
<connect from_op="Select Attributes (3)" from_port="example set output" to_port="result 1"/>
<portSpacing port="source_input 1" spacing="0"/>
<portSpacing port="source_input 2" spacing="0"/>
<portSpacing port="sink_result 1" spacing="0"/>
<portSpacing port="sink_result 2" spacing="0"/>
</process>
</operator>
</process>

JEdward

Hi Sven,

Glad you got it working. From looking at your example it seems my assumed dataset is pretty different from yours.

This is my example using my madeup data:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<process version="6.4.000">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="6.4.000" expanded="true" name="Process">
    <process expanded="true">
      <operator activated="true" class="subprocess" compatibility="6.4.000" expanded="true" height="76" name="Example1 Dataset" width="90" x="45" y="30">
        <process expanded="true">
          <operator activated="true" class="text:create_document" compatibility="6.4.001" expanded="true" height="60" name="Create Document" width="90" x="45" y="75">
            <parameter key="text" value="PatientID|Attribute|Timestamp&#10;1|att1|26/2/1998 04:02:222&#10;1|att2|26/2/1998 04:02:222&#10;1|att1|26/2/1993 07:02:222&#10;1|att1|26/2/2013 02:42:232"/>
          </operator>
          <operator activated="true" class="text:write_document" compatibility="6.4.001" expanded="true" height="76" name="Write Document" width="90" x="179" y="165"/>
          <operator activated="true" class="read_csv" compatibility="6.4.000" expanded="true" height="60" name="Read CSV" width="90" x="370" y="120">
            <parameter key="column_separators" value="|"/>
            <parameter key="date_format" value="dd/MM/yyyy HH:mm:ss"/>
            <list key="annotations"/>
            <list key="data_set_meta_data_information">
              <parameter key="0" value="PatientID.true.nominal.attribute"/>
              <parameter key="1" value="Attribute.true.nominal.attribute"/>
              <parameter key="2" value="TTimestamp.true.date_time.attribute"/>
            </list>
          </operator>
          <connect from_op="Create Document" from_port="output" to_op="Write Document" to_port="document"/>
          <connect from_op="Write Document" from_port="file" to_op="Read CSV" to_port="file"/>
          <connect from_op="Read CSV" from_port="output" to_port="out 1"/>
          <portSpacing port="source_in 1" spacing="0"/>
          <portSpacing port="sink_out 1" spacing="0"/>
          <portSpacing port="sink_out 2" spacing="0"/>
        </process>
      </operator>
      <operator activated="true" class="loop_values" compatibility="6.4.000" expanded="true" height="76" name="Loop Values" width="90" x="179" y="75">
        <parameter key="attribute" value="Attribute"/>
        <parameter key="iteration_macro" value="loop_value_att"/>
        <process expanded="true">
          <operator activated="true" class="filter_examples" compatibility="6.4.000" expanded="true" height="94" name="Filter Examples" width="90" x="45" y="30">
            <list key="filters_list">
              <parameter key="filters_entry_key" value="Attribute.equals.%{loop_value_att}"/>
            </list>
          </operator>
          <operator activated="true" class="loop_values" compatibility="6.4.000" expanded="true" height="76" name="Loop Values (2)" width="90" x="179" y="75">
            <parameter key="attribute" value="PatientID"/>
            <parameter key="iteration_macro" value="loop_value_pat"/>
            <process expanded="true">
              <operator activated="true" class="filter_examples" compatibility="6.4.000" expanded="true" height="94" name="Filter Examples (2)" width="90" x="45" y="30">
                <list key="filters_list">
                  <parameter key="filters_entry_key" value="PatientID.equals.%{loop_value_pat}"/>
                </list>
              </operator>
              <operator activated="true" class="sort" compatibility="6.4.000" expanded="true" height="76" name="Sort" width="90" x="179" y="30">
                <parameter key="attribute_name" value="TTimestamp"/>
              </operator>
              <operator activated="true" class="extract_macro" compatibility="6.4.000" expanded="true" height="60" name="Extract Macro" width="90" x="313" y="75">
                <parameter key="macro" value="firstTime"/>
                <parameter key="macro_type" value="data_value"/>
                <parameter key="attribute_name" value="TTimestamp"/>
                <parameter key="example_index" value="1"/>
                <list key="additional_macros"/>
              </operator>
              <operator activated="true" class="generate_attributes" compatibility="6.4.000" expanded="true" height="76" name="Generate Attributes" width="90" x="380" y="210">
                <list key="function_descriptions">
                  <parameter key="AttTime_New_in_milliseconds" value="date_diff(date_parse_custom(&quot;%{firstTime}&quot;,&quot;dd/MM/yyyy HH:mm:ss&quot;),TTimestamp)"/>
                </list>
              </operator>
              <connect from_port="example set" to_op="Filter Examples (2)" to_port="example set input"/>
              <connect from_op="Filter Examples (2)" from_port="example set output" to_op="Sort" to_port="example set input"/>
              <connect from_op="Sort" from_port="example set output" to_op="Extract Macro" to_port="example set"/>
              <connect from_op="Extract Macro" from_port="example set" to_op="Generate Attributes" to_port="example set input"/>
              <connect from_op="Generate Attributes" from_port="example set output" to_port="out 1"/>
              <portSpacing port="source_example set" spacing="0"/>
              <portSpacing port="sink_out 1" spacing="0"/>
              <portSpacing port="sink_out 2" spacing="0"/>
            </process>
          </operator>
          <operator activated="true" class="append" compatibility="6.4.000" expanded="true" height="76" name="Append" width="90" x="313" y="75"/>
          <connect from_port="example set" to_op="Filter Examples" to_port="example set input"/>
          <connect from_op="Filter Examples" from_port="example set output" to_op="Loop Values (2)" to_port="example set"/>
          <connect from_op="Loop Values (2)" from_port="out 1" to_op="Append" to_port="example set 1"/>
          <connect from_op="Append" from_port="merged set" to_port="out 1"/>
          <portSpacing port="source_example set" spacing="0"/>
          <portSpacing port="sink_out 1" spacing="0"/>
          <portSpacing port="sink_out 2" spacing="0"/>
        </process>
      </operator>
      <operator activated="true" class="append" compatibility="6.4.000" expanded="true" height="76" name="Append (2)" width="90" x="380" y="75"/>
      <connect from_op="Example1 Dataset" from_port="out 1" to_op="Loop Values" to_port="example set"/>
      <connect from_op="Loop Values" from_port="out 1" to_op="Append (2)" to_port="example set 1"/>
      <connect from_op="Append (2)" 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>