🎉Community Raffle - Win $25

An exclusive raffle opportunity for active members like you! Complete your profile, answer questions and get your first accepted badge to enter the raffle.
Join and Win

JSON to data and de-pivot for exampleset

User: "alex_fawzi"
alex_fawzi
New Altair Community Member
Updated by Jocelyn

I'm having a 'mare trying to get some data to de-pivot from json... 

 

The below process gets data from Poloniex in json format. The first row works fine and the exampleset is generated as I'd like, the other 2 (the orderbook and historical trades) I just can't get my head around. The Poloniex public api info is here https://poloniex.com/support/api/ but seems to be outdated. Any help would be appreciated.

 

I came across another post whilst trying to figure out how to use the json file which gave me the de-pivot, I also found one using the json -> xml & xpath way but it seems like json -> data and de-pivot should work.

<?xml version="1.0" encoding="UTF-8"?><process version="7.6.001">
  <operator activated="true" class="web:get_webpage" compatibility="7.3.000" expanded="true" height="68" name="Get Page" width="90" x="45" y="34">
    <parameter key="url" value="https://poloniex.com/public?command=returnChartData&amp;currencyPair=BTC_XMR&amp;end=9999999999&amp;period=7200&amp;start=1505057287"/>
    <parameter key="random_user_agent" value="false"/>
    <parameter key="connection_timeout" value="10000"/>
    <parameter key="read_timeout" value="10000"/>
    <parameter key="follow_redirects" value="true"/>
    <parameter key="accept_cookies" value="none"/>
    <parameter key="cookie_scope" value="global"/>
    <parameter key="request_method" value="GET"/>
    <list key="query_parameters"/>
    <list key="request_properties"/>
    <parameter key="override_encoding" value="false"/>
    <parameter key="encoding" value="SYSTEM"/>
    <description align="center" color="orange" colored="true" width="126">Get historical price data</description>
  </operator>
</process>
<?xml version="1.0" encoding="UTF-8"?><process version="7.6.001">
  <operator activated="true" class="text:json_to_data" compatibility="7.4.001" expanded="true" height="82" name="JSON To Data" width="90" x="179" y="34">
    <parameter key="ignore_arrays" value="false"/>
    <parameter key="limit_attributes" value="false"/>
    <parameter key="skip_invalid_documents" value="false"/>
  </operator>
</process>
<?xml version="1.0" encoding="UTF-8"?><process version="7.6.001">
  <operator activated="true" class="web:get_webpage" compatibility="7.3.000" expanded="true" height="68" name="Get Page (2)" width="90" x="45" y="187">
    <parameter key="url" value="https://poloniex.com/public?command=returnTradeHistory&amp;currencyPair=BTC_XMR"/>
    <parameter key="random_user_agent" value="false"/>
    <parameter key="connection_timeout" value="10000"/>
    <parameter key="read_timeout" value="10000"/>
    <parameter key="follow_redirects" value="true"/>
    <parameter key="accept_cookies" value="none"/>
    <parameter key="cookie_scope" value="global"/>
    <parameter key="request_method" value="GET"/>
    <list key="query_parameters"/>
    <list key="request_properties"/>
    <parameter key="override_encoding" value="false"/>
    <parameter key="encoding" value="SYSTEM"/>
    <description align="center" color="orange" colored="true" width="126">Get historical trade data (last month / 50,000 trades (API limit))</description>
  </operator>
</process>
<?xml version="1.0" encoding="UTF-8"?><process version="7.6.001">
  <operator activated="true" class="text:json_to_data" compatibility="7.4.001" expanded="true" height="82" name="JSON To Data (2)" width="90" x="179" y="187">
    <parameter key="ignore_arrays" value="false"/>
    <parameter key="limit_attributes" value="false"/>
    <parameter key="skip_invalid_documents" value="false"/>
  </operator>
</process>
<?xml version="1.0" encoding="UTF-8"?><process version="7.6.001">
  <operator activated="true" class="de_pivot" compatibility="7.6.001" expanded="true" height="82" name="De-Pivot (2)" width="90" x="313" y="187">
    <list key="attribute_name">
      <parameter key="amount" value="amount"/>
      <parameter key="date" value="date"/>
      <parameter key="globalTradeID" value="globalTradeID"/>
      <parameter key="rate" value="rate"/>
      <parameter key="total" value="total"/>
      <parameter key="tradeID" value="tradeID"/>
    </list>
    <parameter key="index_attribute" value="id"/>
    <parameter key="create_nominal_index" value="false"/>
    <parameter key="keep_missings" value="false"/>
  </operator>
</process>
<?xml version="1.0" encoding="UTF-8"?><process version="7.6.001">
  <operator activated="true" class="web:get_webpage" compatibility="7.3.000" expanded="true" height="68" name="Get Page (3)" width="90" x="45" y="340">
    <parameter key="url" value="https://poloniex.com/public?command=returnOrderBook&amp;currencyPair=BTC_XMR&amp;depth=10&amp;start=1505057287end=9999999999"/>
    <parameter key="random_user_agent" value="false"/>
    <parameter key="connection_timeout" value="10000"/>
    <parameter key="read_timeout" value="10000"/>
    <parameter key="follow_redirects" value="true"/>
    <parameter key="accept_cookies" value="none"/>
    <parameter key="cookie_scope" value="global"/>
    <parameter key="request_method" value="GET"/>
    <list key="query_parameters"/>
    <list key="request_properties"/>
    <parameter key="override_encoding" value="false"/>
    <parameter key="encoding" value="SYSTEM"/>
    <description align="center" color="orange" colored="true" width="126">Get orderbook data</description>
  </operator>
</process>
<?xml version="1.0" encoding="UTF-8"?><process version="7.6.001">
  <operator activated="true" class="text:json_to_data" compatibility="7.4.001" expanded="true" height="82" name="JSON To Data (3)" width="90" x="179" y="340">
    <parameter key="ignore_arrays" value="false"/>
    <parameter key="limit_attributes" value="false"/>
    <parameter key="skip_invalid_documents" value="false"/>
  </operator>
</process>
<?xml version="1.0" encoding="UTF-8"?><process version="7.6.001">
  <operator activated="true" class="de_pivot" compatibility="7.6.001" expanded="true" height="82" name="De-Pivot" width="90" x="313" y="34">
    <list key="attribute_name">
      <parameter key="close" value="\[\d+\]\.close"/>
      <parameter key="high" value="\[\d+\]\.high"/>
      <parameter key="low" value="\[\d+\]\.low"/>
      <parameter key="open" value="\[\d+\]\.open"/>
      <parameter key="date" value="\[\d+\]\.date"/>
      <parameter key="volume" value="\[\d+\]\.volume"/>
      <parameter key="quoteVolume" value="\[\d+\]\.quoteVolume"/>
      <parameter key="weightedAverage" value="\[\d+\]\.weightedAverage"/>
    </list>
    <parameter key="index_attribute" value="id"/>
    <parameter key="create_nominal_index" value="false"/>
    <parameter key="keep_missings" value="false"/>
  </operator>
</process>
<?xml version="1.0" encoding="UTF-8"?><process version="7.6.001">
  <operator activated="true" class="de_pivot" compatibility="7.6.001" expanded="true" height="82" name="De-Pivot (3)" width="90" x="313" y="340">
    <list key="attribute_name">
      <parameter key="asks" value="asks"/>
      <parameter key="bids" value="bids"/>
    </list>
    <parameter key="index_attribute" value="id"/>
    <parameter key="create_nominal_index" value="false"/>
    <parameter key="keep_missings" value="false"/>
  </operator>
</process>

Thanks,

Alex

Find more posts tagged with

AI Studio
JSON
Pivot + De-Pivot
Sort by:
1 - 1 of 11
    User: "sgenzer"
    sgenzer
    Altair Employee
    Accepted Answer

    hello @alex_fawzi - ok that process came through fine.  The good news is that you're not really missing anything; the bad news is that second JSON array is just not that easy to work with in RapidMiner.  You can do what was suggested in that thread (JSON to XML, Write Document, Read XML) if you're just looking for one or two things.  If you really want to "unwrap" the array, you need to do some work.  Here is a quick-and-dirty for you that will give you the general idea (I probably don't have all the fields correct...):

     

    <?xml version="1.0" encoding="UTF-8"?><process version="7.6.001">
      <context>
        <input/>
        <output/>
        <macros/>
      </context>
      <operator activated="true" class="process" compatibility="7.6.001" expanded="true" name="Process">
        <process expanded="true">
          <operator activated="true" class="web:get_webpage" compatibility="7.3.000" expanded="true" height="68" name="Get Page (3)" width="90" x="45" y="34">
            <parameter key="url" value="https://poloniex.com/public?command=returnOrderBook&amp;currencyPair=BTC_XMR&amp;depth=10&amp;start=1506279150&amp;end=9999999999&amp;period=7200"/>
            <list key="query_parameters"/>
            <list key="request_properties"/>
          </operator>
          <operator activated="true" class="text:json_to_data" compatibility="7.5.000" expanded="true" height="82" name="JSON To Data (3)" width="90" x="179" y="34"/>
          <operator activated="true" class="transpose" compatibility="7.6.001" expanded="true" height="82" name="Transpose" width="90" x="313" y="34"/>
          <operator activated="true" class="filter_examples" compatibility="7.6.001" expanded="true" height="103" name="Filter Examples" width="90" x="447" y="34">
            <list key="filters_list">
              <parameter key="filters_entry_key" value="id.contains.["/>
            </list>
          </operator>
          <operator activated="true" class="replace" compatibility="7.6.001" expanded="true" height="82" name="Replace" width="90" x="581" y="34">
            <parameter key="attribute_filter_type" value="single"/>
            <parameter key="attribute" value="id"/>
            <parameter key="include_special_attributes" value="true"/>
            <parameter key="replace_what" value="\]"/>
          </operator>
          <operator activated="true" class="split" compatibility="7.6.001" expanded="true" height="82" name="Split" width="90" x="715" y="34">
            <parameter key="attribute_filter_type" value="single"/>
            <parameter key="attribute" value="id"/>
            <parameter key="include_special_attributes" value="true"/>
            <parameter key="split_pattern" value="\["/>
          </operator>
          <operator activated="true" class="pivot" compatibility="7.6.001" expanded="true" height="82" name="Pivot" width="90" x="849" y="34">
            <parameter key="group_attribute" value="id_2"/>
            <parameter key="index_attribute" value="id_1"/>
            <parameter key="consider_weights" value="false"/>
          </operator>
          <connect from_op="Get Page (3)" from_port="output" to_op="JSON To Data (3)" to_port="documents 1"/>
          <connect from_op="JSON To Data (3)" from_port="example set" to_op="Transpose" to_port="example set input"/>
          <connect from_op="Transpose" from_port="example set output" to_op="Filter Examples" to_port="example set input"/>
          <connect from_op="Filter Examples" from_port="example set output" to_op="Replace" to_port="example set input"/>
          <connect from_op="Replace" from_port="example set output" to_op="Split" to_port="example set input"/>
          <connect from_op="Split" from_port="example set output" to_op="Pivot" to_port="example set input"/>
          <connect from_op="Pivot" 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>

    Funny enough I have been working on a small project that will require a tool that will do exactly this much easier.  Stay tuned.  :)

     

    Scott

     

    View in context
Quick Links