<body><script type="text/javascript"> function setAttributeOnload(object, attribute, val) { if(window.addEventListener) { window.addEventListener('load', function(){ object[attribute] = val; }, false); } else { window.attachEvent('onload', function(){ object[attribute] = val; }); } } </script> <div id="navbar-iframe-container"></div> <script type="text/javascript" src="https://apis.google.com/js/plusone.js"></script> <script type="text/javascript"> gapi.load("gapi.iframes:gapi.iframes.style.bubble", function() { if (gapi.iframes && gapi.iframes.getContext) { gapi.iframes.getContext().openChild({ url: 'https://www.blogger.com/navbar.g?targetBlogID\x3d6813476980165976394\x26blogName\x3dThe+Bayesian+Conspiracy\x26publishMode\x3dPUBLISH_MODE_BLOGSPOT\x26navbarType\x3dBLUE\x26layoutType\x3dCLASSIC\x26searchRoot\x3dhttp://bayesianconspiracy.blogspot.com/search\x26blogLocale\x3den\x26v\x3d2\x26homepageUrl\x3dhttp://bayesianconspiracy.blogspot.com/\x26vt\x3d642017532500428956', where: document.getElementById("navbar-iframe-container"), id: "navbar-iframe" }); } }); </script>

The Bayesian Conspiracy

The Bayesian Conspiracy is a multinational, interdisciplinary, and shadowy group of scientists. It is rumored that at the upper levels of the Bayesian Conspiracy exist nine silent figures known only as the Bayes Council.

This blog is produced by James Durbin, a Bayesian Initiate.

OnlineTable: Accessing csv files row at a time by column name.

Here is a handy class I use to simplify accessing a CSV or TAB file one line at a time. I feel like I've seen this somewhere else also. I hope it's not just in GINA! Anyway, sometimes the simplest things are the most handy so it bears repeating even if it is. Suppose you have a file, beer.csv, that has a header describing the columns followed by rows of data like:

    brand,price,calories,alcohol,type,domestic
    LeinenkugelsRed,4.79,160,5.0,1,1
    SamuelAdamsBoston,5.96,160,4.9,1,1
    GeorgeKilliansIrishRed,4.70,162,4.9,1,1
    RedWolf,4.11,157,5.5,1,1
    Becks,5.83,148,4.3,3,0
    PilsnerUrquell,7.80,160,4.1,3,0

Then you can use OnlineTable to parse it like:

new OnlineTable("beer.csv").eachRow{row->
  println "brand: "+row.brand
  println "calories:"+row.calories
}

Or, if you prefer the map notation, you can use it like:

new OnlineTable("beer.csv").eachRow{row->
  println "brand: "+row['brand']
}

This version automatically inspects header to see if it's a CSV or TAB delimited file. The class is shown below but is also included as part of durbinlib.jar The latter will be updated with additional features.

/***********************************
* Support for accessing a table one row at a time.
*
*/
class OnlineTable{      
  
      String fileName
    
      def OnlineTable(String f){
        fileName = f
      }     
      
      def eachRow(Closure c){
        new File(fileName).withReader{r->
          def headingStr = r.readLine()
          def sep = determineSeparator(headingStr)
          def headings = headingStr.split(sep)
          r.eachLine{rowStr->
            def rfields = rowStr.split(sep)
            def row = [:]
            rfields.eachWithIndex{f,i->row[headings[i]]=f}
            c(row)
          }
        }
      } 
            
      def determineSeparator(line){
        def sep;
        if (line.contains(",")) sep = ","
        else if (line.contains("\t")) sep = "\t"
        else {
          System.err.println "File does not appear to be a csv or tab file.";
          throw new RuntimeException();
        }
        return(sep);
      }   
}

Labels: , ,

You can leave your response or bookmark this post to del.icio.us by using the links below.

Post a Comment | Bookmark | Go to end |
  • Blogger Saager Mhatre:  

    I always thought OpenCSV filled up this use case nicely. And at just 28k it's light too! (December 10, 2012 at 11:23 PM) top

  • Blogger James Durbin:  

    OpenCSV strikes me as too complicated and not helpful enough at the same time. For example, I don't see any way that you can access fields by column name in OpenCSV. For that matter, it looks like OpenCSV has no knowledge of a heading for a csv file at all. Maybe it's helpful in Java, but all of the OpenCSV functionality seems easily replaced with built-in Groovy functionality. Consider this example:

    CSVReader reader = new CSVReader(new FileReader(ADDRESS_FILE));
    String [] nextLine;
    while ((nextLine = reader.readNext()) != null) {
    System.out.println("Name: [" + nextLine[0] +
    "]\nAddress: [" + nextLine[1] + "]\nEmail: [" + nextLine[2] + "]");
    }

    This can be done in Groovy like this:

    new File(ADDRESS_FILE).splitEachLine(","){fields->
    println "Name: ${fields[0]} Address: ${fields[1]} Email: ${fields[2]}"
    }

    What OnlineTable adds to this Groovy code is mapping the heading line to fields so you don't have to. The OnlineTable equivalent of this example is thus (assuming the csv file now has a heading):

    new OnlineTable(ADDRESS_FILE).eachRow{row->
    println "Name: $row.name Address $row.address Email: $row.email"
    }

    That is a big difference in simplicity and utility from the OpenCSV example, I think. (December 11, 2012 at 12:26 AM) top

  • Blogger Saager Mhatre:  

    My bad, I somehow incorrectly 'remembered' using OpenCSV with header rows. Now that I look at it again, it is missing quite a few pieces. There's even a feature request for it.

    Nevertheless, one of the things that attracted me to OpenCSV (even though I haven't used it a whole lot) is it's support for quoting and escaping. IMHO, that's one use case that can become a pain in fairly large datasets.

    But, I'll concede that your solution is much groovier in that it espouses internal iterators with block support.

    Good stuff and apologies again for the confusion. (December 11, 2012 at 10:02 AM) top