[SOLVED] Transform Document-Term matrix to flat table?

RWingerter
RWingerter New Altair Community Member
edited November 5 in Community Q&A
A newbie question.

I have a simple process which uses „Data to Documents“, „Process Documents“ and „Tokenize“  to turn a list of strings into a wordlist.The second result is my ExampleSet turned into a Document-Term Matrix.

My question is: How can I transform the Document-Term matrix (Document_ID x Term) to a flat table with three attributes (Document_ID, Term, occurrences)?

Regards,

Roland
Tagged:

Answers

  • Skirzynski
    Skirzynski New Altair Community Member
    Hey Roland,

    It is more likely to get an answer by posting a (self-)process with a small chunk of your data. Currently I am not sure what you have and what you want.

    Best
      Marcin
  • RWingerter
    RWingerter New Altair Community Member
    Hi Marcin,

    thanks for your reply. Here is my example data and my simple process.

    The input is a list of user queries (query_id, query, frequency), which is processed with "Process Documents from Data". The result is a word list and a document-term matrix. In addition, I would like to get a term-document table with Term, Query_ID, and TF*IDF, e.g.

    Term Query_ID TF*IDF
    ---------------------------------
    Term1    1        0.34
    Term1    2        0.23
    Term2    3        1.00

    I tried various things without success. Maybe it's not difficult to do, but I didn't manage.

    Sample data: 

    Query_ID;Query;frequency
    1;hautarzt;103921
    2;zahnarzt;101684
    3;augenarzt;89233
    4;frauenarzt;75116
    5;arzt;70755
    6;ärzte;65176
    7;zahnärzte;57836
    8;allgemeinarzt;54111
    9;tierarzt;52387
    10;augenärzte;49855
    11;hautärzte;33141
    12;kinderarzt;32989
    13;kinderärzte;26377
    14;hno arzt;22984
    15;tierärzte;22090
    16;frauenärzte;20694
    17;lungenfacharzt;16468
    18;praktische ärzte;14175
    19;hno-ärzte;13290
    20;hausarzt;12595
    21;hautarztpraxen;12262
    22;allgemeinärzte;11906
    23;ärzte allgemeinmedizin und praktische ärzte;11781
    24;ärzte orthopädie;10833
    25;hals nasen ohrenärzte;5457
    26;hno ärzte;4607
    27;hals nasen ohren arzt;4319
    28;ärzte innere medizin;4053
    29;ärzte urologie;3886
    30;ärzte frauenheilkunde und geburtshilfe;3837
    Code: 

    <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <process version="5.3.008">
      <context>
        <input/>
        <output/>
        <macros/>
      </context>
      <operator activated="true" class="process" compatibility="5.3.008" expanded="true" name="Process">
        <process expanded="true">
          <operator activated="true" class="read_csv" compatibility="5.3.008" expanded="true" height="60" name="Read CSV" width="90" x="45" y="75">
            <parameter key="csv_file" value="C:\Users\retegniw\Documents\Office\Excel\Sample queries.csv"/>
            <parameter key="first_row_as_names" value="false"/>
            <list key="annotations">
              <parameter key="0" value="Name"/>
            </list>
            <parameter key="encoding" value="windows-1252"/>
            <list key="data_set_meta_data_information">
              <parameter key="0" value="Query_ID.true.integer.id"/>
              <parameter key="1" value="Query.true.text.attribute"/>
              <parameter key="2" value="frequency.true.integer.attribute"/>
            </list>
          </operator>
          <operator activated="true" class="set_role" compatibility="5.3.008" expanded="true" height="76" name="Set Role" width="90" x="179" y="75">
            <parameter key="attribute_name" value="Query_ID"/>
            <parameter key="target_role" value="id"/>
            <list key="set_additional_roles"/>
          </operator>
          <operator activated="true" class="nominal_to_text" compatibility="5.3.008" expanded="true" height="76" name="Nominal to Text" width="90" x="313" y="75"/>
          <operator activated="true" class="text:process_document_from_data" compatibility="5.3.000" expanded="true" height="76" name="Process Documents from Data" width="90" x="447" y="75">
            <parameter key="keep_text" value="true"/>
            <list key="specify_weights"/>
            <process expanded="true">
              <operator activated="true" class="text:tokenize" compatibility="5.3.000" expanded="true" height="60" name="Tokenize" width="90" x="45" y="30"/>
              <operator activated="true" class="text:filter_stopwords_german" compatibility="5.3.000" expanded="true" height="60" name="Filter Stopwords (German)" width="90" x="179" y="30"/>
              <connect from_port="document" to_op="Tokenize" to_port="document"/>
              <connect from_op="Tokenize" from_port="document" to_op="Filter Stopwords (German)" to_port="document"/>
              <connect from_op="Filter Stopwords (German)" from_port="document" to_port="document 1"/>
              <portSpacing port="source_document" spacing="0"/>
              <portSpacing port="sink_document 1" spacing="0"/>
              <portSpacing port="sink_document 2" spacing="0"/>
            </process>
          </operator>
          <connect from_op="Read CSV" from_port="output" to_op="Set Role" to_port="example set input"/>
          <connect from_op="Set Role" from_port="example set output" to_op="Nominal to Text" to_port="example set input"/>
          <connect from_op="Nominal to Text" from_port="example set output" to_op="Process Documents from Data" to_port="example set"/>
          <connect from_op="Process Documents from Data" from_port="example set" to_port="result 1"/>
          <connect from_op="Process Documents from Data" from_port="word list" to_port="result 2"/>
          <portSpacing port="source_input 1" spacing="0"/>
          <portSpacing port="sink_result 1" spacing="0"/>
          <portSpacing port="sink_result 2" spacing="0"/>
          <portSpacing port="sink_result 3" spacing="0"/>
        </process>
      </operator>
    </process>
    Any and all help welcome.

    Thank you

    Roland
  • Skirzynski
    Skirzynski New Altair Community Member
    The operator you are looking for is the "De-Pivot" which is indeed not easy to use (in my opinion). Unfortunately it cannot handle special attributes, thus, you have to explicitly exclude Query_ID in the "attribute_name" with a negative lookahead, which is not optimal, but it works. 

    <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <process version="5.3.009">
      <context>
        <input/>
        <output/>
        <macros/>
      </context>
      <operator activated="true" class="process" compatibility="5.3.009" expanded="true" name="Process">
        <process expanded="true">
          <operator activated="true" class="read_csv" compatibility="5.3.009" expanded="true" height="60" name="Read CSV" width="90" x="45" y="75">
            <parameter key="csv_file" value="/home/marcin/temp/forum-6582.csv"/>
            <parameter key="first_row_as_names" value="false"/>
            <list key="annotations">
              <parameter key="0" value="Name"/>
            </list>
            <parameter key="encoding" value="windows-1252"/>
            <list key="data_set_meta_data_information">
              <parameter key="0" value="Query_ID.true.integer.id"/>
              <parameter key="1" value="Query.true.text.attribute"/>
              <parameter key="2" value="frequency.true.integer.attribute"/>
            </list>
          </operator>
          <operator activated="true" class="set_role" compatibility="5.3.009" expanded="true" height="76" name="Set Role" width="90" x="179" y="75">
            <parameter key="attribute_name" value="Query_ID"/>
            <parameter key="target_role" value="id"/>
            <list key="set_additional_roles"/>
          </operator>
          <operator activated="true" class="nominal_to_text" compatibility="5.3.009" expanded="true" height="76" name="Nominal to Text" width="90" x="313" y="75"/>
          <operator activated="true" class="text:process_document_from_data" compatibility="5.3.001" expanded="true" height="76" name="Process Documents from Data" width="90" x="447" y="75">
            <parameter key="keep_text" value="true"/>
            <list key="specify_weights"/>
            <process expanded="true">
              <operator activated="true" class="text:tokenize" compatibility="5.3.001" expanded="true" height="60" name="Tokenize" width="90" x="45" y="30"/>
              <operator activated="true" class="text:filter_stopwords_german" compatibility="5.3.001" expanded="true" height="60" name="Filter Stopwords (German)" width="90" x="179" y="30"/>
              <connect from_port="document" to_op="Tokenize" to_port="document"/>
              <connect from_op="Tokenize" from_port="document" to_op="Filter Stopwords (German)" to_port="document"/>
              <connect from_op="Filter Stopwords (German)" from_port="document" to_port="document 1"/>
              <portSpacing port="source_document" spacing="0"/>
              <portSpacing port="sink_document 1" spacing="0"/>
              <portSpacing port="sink_document 2" spacing="0"/>
            </process>
          </operator>
          <operator activated="true" class="select_attributes" compatibility="5.3.009" expanded="true" height="76" name="Select Attributes" width="90" x="246" y="210">
            <parameter key="attribute_filter_type" value="subset"/>
            <parameter key="attributes" value="text|frequency"/>
            <parameter key="invert_selection" value="true"/>
            <parameter key="include_special_attributes" value="true"/>
          </operator>
          <operator activated="true" class="de_pivot" compatibility="5.3.009" expanded="true" height="76" name="De-Pivot" width="90" x="380" y="210">
            <list key="attribute_name">
              <parameter key="TF-IDF" value="^(?!Query_ID).*"/>
            </list>
            <parameter key="index_attribute" value="Term"/>
            <parameter key="create_nominal_index" value="true"/>
          </operator>
          <connect from_op="Read CSV" from_port="output" to_op="Set Role" to_port="example set input"/>
          <connect from_op="Set Role" from_port="example set output" to_op="Nominal to Text" to_port="example set input"/>
          <connect from_op="Nominal to Text" from_port="example set output" to_op="Process Documents from Data" to_port="example set"/>
          <connect from_op="Process Documents from Data" from_port="example set" to_op="Select Attributes" to_port="example set input"/>
          <connect from_op="Select Attributes" from_port="example set output" to_op="De-Pivot" to_port="example set input"/>
          <connect from_op="De-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>
  • RWingerter
    RWingerter New Altair Community Member
    Hi Marcin,

    thank you very much, it works like a charm.
    Marcin wrote:

    The operator you are looking for is the "De-Pivot" which is indeed not easy to use (in my opinion).
    I had looked at the "De-Pivot" operator, but I had no idea how to adress the attribute names. I am not saying I understand your code (that will certainly take a while), but for now I am just happy to have a solution. Thanks again.

    Kind regards

    Roland

Categories