"read in csv file skip first n lines"

josef_frankjosef_frank Member Posts: 3 Contributor I
edited May 2019 in Help

Hi,

when importing csv files, is there any method to skip lines in the beginning of a file?
Something that behaves like R's "read.table"-option "skip".

Quite some time I'd like to read in csv files that have a structure like windows' ini-files:

[Header]
some garbage here
some non-relevant info here
[Data]
Var1,Var2,Var3,...
0,1,0,...
....

I'd like to start reading the file in the line "Var1,Var2,.."
Just can't find any option to get this done :-(

Best
Josef
Tagged:

Best Answer

  • Telcontar120Telcontar120 Moderator, RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 1,635 Unicorn
    Solution Accepted

    @sgenzer So Martin posted a comment in the related product idea that I think showed me the solution.  You can use the "Edit Annotations" parameter to manually specify that certain lines are to be treated as comments (even if they don't have the comment character, which is where I was getting hung up before).  See the following:

    annotations.PNG

    That effectively skips those lines on import and it starts reading on the specified line, and interprets the correct number of columns!  And at that point, the extra work with the macros to rename the attributes isn't needed either, since you can specify the relevant line to be read as names--but I'm sure going to save that process anyways because it could come in handy in other more complex data files in the future.  Thanks again!

    Brian T.
    Lindon Ventures 
    Data Science Consulting from Certified RapidMiner Experts

Answers

  • JEdwardJEdward RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 578 Unicorn
    Hi Josef,

    I think step 3 of 5 of the import wizard offers this as you can set lines as name, comment or unit but I haven't used it before. 
    Give that a try and see if it does what you expect.

    Best,
    JEdward.

  • el_chiefel_chief Member Posts: 63 Contributor II
    ya, this has been annoying me as well. I usually delete the header line in notepad first.

    Simon, can you pleeeeeeeeeeeease fix this? And in Excel importer too

    josef.frank wrote:

    Hi,

    when importing csv files, is there any method to skip lines in the beginning of a file?
    Something that behaves like R's "read.table"-option "skip".

    Quite some time I'd like to read in csv files that have a structure like windows' ini-files:

    [Header]
    some garbage here
    some non-relevant info here
    [Data]
    Var1,Var2,Var3,...
    0,1,0,...
    ....

    I'd like to start reading the file in the line "Var1,Var2,.."
    Just can't find any option to get this done :-(

    Best
    Josef

  • JEdwardJEdward RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 578 Unicorn
    Hi guys,

    I gave a quick test with a csv with the following format:

    Line1- RandomText
    Line2 - [Var1], [Var2] (column headings)
    Line3 - RandomText
    Line4 - RandomText
    Line5 - RandomText
    Line6+ - [data1], [data2] (data)

    The below process worked fine for me.  What have I missed?
    <process version="5.1.006">
      <context>
        <input/>
        <output/>
        <macros/>
      </context>
      <operator activated="true" class="process" compatibility="5.1.006" expanded="true" name="Process">
        <process expanded="true" height="370" width="145">
          <operator activated="true" class="read_csv" compatibility="5.1.006" expanded="true" height="60" name="Read CSV" width="90" x="45" y="210">
            <parameter key="csv_file" value="C:\Users\jedward\Desktop\RapidiCSVTest.csv"/>
            <parameter key="column_separators" value=","/>
            <parameter key="skip_comments" value="true"/>
            <parameter key="first_row_as_names" value="false"/>
            <list key="annotations">
              <parameter key="0" value="Comment"/>
              <parameter key="1" value="Name"/>
              <parameter key="2" value="Comment"/>
              <parameter key="3" value="Comment"/>
              <parameter key="4" value="Comment"/>
              <parameter key="5" value="Comment"/>
            </list>
            <list key="data_set_meta_data_information"/>
          </operator>
          <connect from_op="Read CSV" from_port="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>
  • josef_frankjosef_frank Member Posts: 3 Contributor I
    JEdward wrote:

    Hi JEdward

    The below process worked fine for me.  What have I missed?
    The suggested method promising at a first glance
    (had already tried that before the original post).
    This procedure however has two drawbacks:
    1st: it gets quite unwieldy when 80 or 100 lines have
    to be annotated manually.
    2nd: It just doesn't work as intended:

    Maybe youl'd like to try another file:
    none1,none2,none3
    nothing
    var1,var2,var3,var4,var5
    1,2,3,4,5
    2,3,4,5,6
    3,4,5,6,7
    When annotating the first two lines as comments and the 3rd line
    with "Name", in the example above, the result set starts indeed with var1,
    but the number of fields that are read in is still determined by the first line,
    so this time the number of fields is determined by a comment :o
    Perhaps I'm just using it incorrectly. Could you give any hint?

    Best Josef
  • JEdwardJEdward RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 578 Unicorn
    Hi Josef,

    Point 1.- I agree that it's unwieldy.  One way around this would be using Excel & concatenate formulae to build the number of lines that you need and then pasting it into the XML of the process. 
    For example::
    A                         B         C                                 D
    1 <parameter key=" 0       " value="Comment"/> =CONCATENATE(A1,B1,C1)
    2 <parameter key=" =B1+1 " value="Comment"/> =CONCATENATE(A2,B2,C2)
    3 <parameter key=" =B2+1 " value="Comment"/> =CONCATENATE(A3,B3,C3)
    4 <parameter key=" =B3+1 " value="Comment"/> =CONCATENATE(A4,B4,C4)
    Not the most elegant solution & having an option to type a range of rows in RapidMiner would be very nice.
     
    Point 2. -Ah! I see the problem, that's a pain.
    The below worked for me after I named the required columns in "data_set_meta_data_information".  Try this.
    <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <process version="5.1.006">
      <context>
        <input/>
        <output/>
        <macros/>
      </context>
      <operator activated="true" class="process" compatibility="5.1.006" expanded="true" name="Process">
        <process expanded="true" height="370" width="279">
          <operator activated="true" class="read_csv" compatibility="5.1.006" expanded="true" height="60" name="Read CSV" width="90" x="179" y="165">
            <parameter key="csv_file" value="C:\Users\jedward\Desktop\TestFile.csv"/>
            <parameter key="column_separators" value=","/>
            <parameter key="skip_comments" value="true"/>
            <parameter key="first_row_as_names" value="false"/>
            <list key="annotations">
              <parameter key="0" value="Comment"/>
              <parameter key="1" value="Comment"/>
              <parameter key="2" value="Name"/>
            </list>
            <list key="data_set_meta_data_information">
              <parameter key="0" value="var1.true.integer.attribute"/>
              <parameter key="1" value="var2.true.integer.attribute"/>
              <parameter key="2" value="var3.true.integer.attribute"/>
              <parameter key="3" value="var4.true.integer.attribute"/>
              <parameter key="4" value="var5.true.integer.attribute"/>
            </list>
          </operator>
          <connect from_op="Read CSV" from_port="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>
  • Telcontar120Telcontar120 Moderator, RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 1,635 Unicorn
    So, is it really true that in 6 years, no one has found a better way to solve this problem??? I just added a product idea for the "skip n lines" for Read CSV.
    This appears to be the only relevant thread from the forum using the obvious search terms.
    In the meantime, if anyone else has found a more effective way to do this that doesn't involve going into csv files manually to add comment characters (or simply delete the header lines), I would love to hear about it. @sgenzer, any tricks up your sleeve here?
    Brian T.
    Lindon Ventures 
    Data Science Consulting from Certified RapidMiner Experts
  • sgenzersgenzer Administrator, Moderator, Employee, RapidMiner Certified Analyst, Community Manager, Member, University Professor, PM Moderator Posts: 2,959 Community Manager

    darn you, @Telcontar120 - now that you've thrown down the gauntlet I am going to tackle this.  You know my weakness for bizarre ETL puzzles.  :)  Stay tuned.

     

    Scott

     

  • sgenzersgenzer Administrator, Moderator, Employee, RapidMiner Certified Analyst, Community Manager, Member, University Professor, PM Moderator Posts: 2,959 Community Manager

    how's this?  test2.csv is attached

     

    <?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="set_macro" compatibility="7.6.001" expanded="true" height="68" name="Set Macro" width="90" x="45" y="34">
    <parameter key="macro" value="skip"/>
    <parameter key="value" value="4"/>
    <description align="center" color="transparent" colored="false" width="126">set n rows to %{skip}</description>
    </operator>
    <operator activated="true" class="read_csv" compatibility="7.6.001" expanded="true" height="68" name="Read CSV" width="90" x="179" y="34">
    <parameter key="csv_file" value="/Users/GenzerConsulting/Desktop/test2.csv"/>
    <parameter key="column_separators" value=","/>
    <parameter key="first_row_as_names" value="false"/>
    <list key="annotations"/>
    <list key="data_set_meta_data_information"/>
    </operator>
    <operator activated="true" class="extract_macro" compatibility="7.6.001" expanded="true" height="68" name="Extract Macro" width="90" x="313" y="34">
    <parameter key="macro" value="NEx"/>
    <list key="additional_macros"/>
    <description align="center" color="transparent" colored="false" width="126">get number of examples %{NEx}</description>
    </operator>
    <operator activated="true" class="generate_macro" compatibility="7.6.001" expanded="true" height="82" name="Generate Macro" width="90" x="447" y="34">
    <list key="function_descriptions">
    <parameter key="startRow" value="1+eval(%{skip})"/>
    </list>
    <description align="center" color="transparent" colored="false" width="126">%{startRow} = skip + 1</description>
    </operator>
    <operator activated="true" class="filter_example_range" compatibility="7.6.001" expanded="true" height="82" name="Filter Example Range" width="90" x="581" y="34">
    <parameter key="first_example" value="%{startRow}"/>
    <parameter key="last_example" value="%{NEx}"/>
    <description align="center" color="transparent" colored="false" width="126">filter startRow to NEx</description>
    </operator>
    <operator activated="true" class="rename_by_example_values" compatibility="7.6.001" expanded="true" height="82" name="Rename by Example Values" width="90" x="715" y="34"/>
    <connect from_op="Read CSV" from_port="output" to_op="Extract Macro" to_port="example set"/>
    <connect from_op="Extract Macro" from_port="example set" to_op="Generate Macro" to_port="through 1"/>
    <connect from_op="Generate Macro" from_port="through 1" to_op="Filter Example Range" to_port="example set input"/>
    <connect from_op="Filter Example Range" from_port="example set output" to_op="Rename by Example Values" to_port="example set input"/>
    <connect from_op="Rename by Example Values" 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>
  • Telcontar120Telcontar120 Moderator, RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 1,635 Unicorn

    Brilliant, I really appreciate you working on this @sgenzer!  Very creative use of macros to do this.  This approach is promising, but I think it only works if there are enough delimiters in the first line so it reads the correct number of columns.  If you modify the csv so the first line has only text (unfortunately more typical with title rows), then it only reads one column in, and the rest of the process works but only on that first column. And after a bit of testing it seems like it will generally read however many columns as it detects from the first row.  So if you can figure out how to tell RapidMiner how many columns to read (not based on the first row of data), then something like this should do the trick.  

    Happy Thanksgiving!

    Brian T.
    Lindon Ventures 
    Data Science Consulting from Certified RapidMiner Experts
  • sgenzersgenzer Administrator, Moderator, Employee, RapidMiner Certified Analyst, Community Manager, Member, University Professor, PM Moderator Posts: 2,959 Community Manager
    <?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="false" class="set_macro" compatibility="7.6.001" expanded="true" height="68" name="Set Macro" width="90" x="45" y="187">
    <parameter key="macro" value="skip"/>
    <parameter key="value" value="4"/>
    <description align="center" color="transparent" colored="false" width="126">set n rows to %{skip}</description>
    </operator>
    <operator activated="true" class="read_csv" compatibility="7.6.001" expanded="true" height="68" name="Read CSV" width="90" x="179" y="34">
    <parameter key="csv_file" value="/Users/genzerconsulting/Desktop/test2 mod.csv"/>
    <parameter key="first_row_as_names" value="false"/>
    <list key="annotations"/>
    <list key="data_set_meta_data_information"/>
    </operator>
    <operator activated="true" class="filter_examples" compatibility="7.6.001" expanded="true" height="103" name="Filter Examples" width="90" x="313" y="34">
    <list key="filters_list">
    <parameter key="filters_entry_key" value="att1.contains.,"/>
    </list>
    </operator>
    <operator activated="true" class="split" compatibility="7.6.001" expanded="true" height="82" name="Split" width="90" x="447" y="34"/>
    <operator activated="false" class="extract_macro" compatibility="7.6.001" expanded="true" height="68" name="Extract Macro" width="90" x="581" y="187">
    <parameter key="macro" value="NEx"/>
    <list key="additional_macros"/>
    <description align="center" color="transparent" colored="false" width="126">get number of examples %{NEx}</description>
    </operator>
    <operator activated="false" class="generate_macro" compatibility="7.6.001" expanded="true" height="82" name="Generate Macro" width="90" x="715" y="187">
    <list key="function_descriptions">
    <parameter key="startRow" value="1+eval(%{skip})"/>
    </list>
    <description align="center" color="transparent" colored="false" width="126">%{startRow} = skip + 1</description>
    </operator>
    <operator activated="false" class="filter_example_range" compatibility="7.6.001" expanded="true" height="82" name="Filter Example Range" width="90" x="849" y="187">
    <parameter key="first_example" value="%{startRow}"/>
    <parameter key="last_example" value="%{NEx}"/>
    <description align="center" color="transparent" colored="false" width="126">filter startRow to NEx</description>
    </operator>
    <operator activated="true" class="rename_by_example_values" compatibility="7.6.001" expanded="true" height="82" name="Rename by Example Values" width="90" x="983" y="34"/>
    <connect from_op="Read CSV" from_port="output" to_op="Filter Examples" to_port="example set input"/>
    <connect from_op="Filter Examples" from_port="example set output" to_op="Split" to_port="example set input"/>
    <connect from_op="Split" from_port="example set output" to_op="Rename by Example Values" to_port="example set input"/>
    <connect from_op="Extract Macro" from_port="example set" to_op="Generate Macro" to_port="through 1"/>
    <connect from_op="Generate Macro" from_port="through 1" to_op="Filter Example Range" to_port="example set input"/>
    <connect from_op="Rename by Example Values" 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>
  • sgenzersgenzer Administrator, Moderator, Employee, RapidMiner Certified Analyst, Community Manager, Member, University Professor, PM Moderator Posts: 2,959 Community Manager

    oh darn - of course I work on the next one and then see your note. Leave it to @mschmitz to one-up me.  :)  Perfect solution.

     

    Happy Thanksgiving.


    Scott

  • matt_yauchmatt_yauch Member Posts: 1 Contributor I

    I don't see how the macros can manage a blank row in the csv file. Looking to skip the first 2 lines (first is comment, second is blank) without editing the csv file. I didn't have luck with blank row via annotations, but perhaps I overlooked a setting. Any help with skipping blank lines between comment and column names? 

  • MartinLiebigMartinLiebig Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert, University Professor Posts: 3,503 RM Data Scientist

    Hi @matt_yauch,

     

    you can manually say in the operator parameters to skip first x lines and just read it. RM will then automatically detect the att names etc. 

     

    BR,

    Martin

    - Sr. Director Data Solutions, Altair RapidMiner -
    Dortmund, Germany
Sign In or Register to comment.