| <!DOCTYPE html> |
| <!--[if lt IE 7]> <html class="no-js lt-ie9 lt-ie8 lt-ie7"> <![endif]--> |
| <!--[if IE 7]> <html class="no-js lt-ie9 lt-ie8"> <![endif]--> |
| <!--[if IE 8]> <html class="no-js lt-ie9"> <![endif]--> |
| <!--[if gt IE 8]><!--> <html class="no-js"> <!--<![endif]--><head> |
| <meta charset='utf-8'/><meta http-equiv='X-UA-Compatible' content='IE=edge'/><meta name='viewport' content='width=device-width, initial-scale=1'/><meta name='keywords' content='groovy, cycling, duckdb, sql, ginq, gquery, csv'/><meta name='description' content='This post looks at processing the general classification results for the Critérium du Dauphiné using Groovy, GQuery and DuckDB.'/><title>The Apache Groovy programming language - Blogs - Processing Results for the Critérium du Dauphiné</title><link href='../img/favicon.ico' type='image/x-ico' rel='icon'/><link rel='stylesheet' type='text/css' href='../css/bootstrap.css'/><link rel='stylesheet' type='text/css' href='../css/font-awesome.min.css'/><link rel='stylesheet' type='text/css' href='../css/style.css'/><link rel='stylesheet' type='text/css' href='https://cdnjs.cloudflare.com/ajax/libs/prettify/r298/prettify.min.css'/> |
| </head><body> |
| <div id='fork-me'> |
| <a href='https://github.com/apache/groovy'> |
| <img style='position: fixed; top: 20px; right: -58px; border: 0; z-index: 100; transform: rotate(45deg);' src='/img/horizontal-github-ribbon.png'/> |
| </a> |
| </div><div id='st-container' class='st-container st-effect-9'> |
| <nav class='st-menu st-effect-9' id='menu-12'> |
| <h2 class='icon icon-lab'>Socialize</h2><ul> |
| <li> |
| <a href='https://groovy-lang.org/mailing-lists.html' class='icon'><span class='fa fa-envelope'></span> Discuss on the mailing-list</a> |
| </li><li> |
| <a href='https://twitter.com/ApacheGroovy' class='icon'><span class='fa fa-twitter'></span> Groovy on Twitter</a> |
| </li><li> |
| <a href='https://groovy-lang.org/events.html' class='icon'><span class='fa fa-calendar'></span> Events and conferences</a> |
| </li><li> |
| <a href='https://github.com/apache/groovy' class='icon'><span class='fa fa-github'></span> Source code on GitHub</a> |
| </li><li> |
| <a href='https://groovy-lang.org/reporting-issues.html' class='icon'><span class='fa fa-bug'></span> Report issues in Jira</a> |
| </li><li> |
| <a href='http://stackoverflow.com/questions/tagged/groovy' class='icon'><span class='fa fa-stack-overflow'></span> Stack Overflow questions</a> |
| </li><li> |
| <a href='http://groovycommunity.com/' class='icon'><span class='fa fa-slack'></span> Slack Community</a> |
| </li> |
| </ul> |
| </nav><div class='st-pusher'> |
| <div class='st-content'> |
| <div class='st-content-inner'> |
| <!--[if lt IE 7]> |
| <p class="browsehappy">You are using an <strong>outdated</strong> browser. Please <a href="http://browsehappy.com/">upgrade your browser</a> to improve your experience.</p> |
| <![endif]--><div><div class='navbar navbar-default navbar-static-top' role='navigation'> |
| <div class='container'> |
| <div class='navbar-header'> |
| <button type='button' class='navbar-toggle' data-toggle='collapse' data-target='.navbar-collapse'> |
| <span class='sr-only'></span><span class='icon-bar'></span><span class='icon-bar'></span><span class='icon-bar'></span> |
| </button><a class='navbar-brand' href='../index.html'> |
| <i class='fa fa-star'></i> Apache Groovy |
| </a> |
| </div><div class='navbar-collapse collapse'> |
| <ul class='nav navbar-nav navbar-right'> |
| <li class=''><a href='https://groovy-lang.org/learn.html'>Learn</a></li><li class=''><a href='https://groovy-lang.org/documentation.html'>Documentation</a></li><li class=''><a href='/download.html'>Download</a></li><li class=''><a href='https://groovy-lang.org/support.html'>Support</a></li><li class=''><a href='/'>Contribute</a></li><li class=''><a href='https://groovy-lang.org/ecosystem.html'>Ecosystem</a></li><li class=''><a href='/blog'>Blog posts</a></li><li class=''><a href='https://groovy.apache.org/events.html'></a></li><li> |
| <a data-effect='st-effect-9' class='st-trigger' href='#'>Socialize</a> |
| </li><li class=''> |
| <a href='../search.html'> |
| <i class='fa fa-search'></i> |
| </a> |
| </li> |
| </ul> |
| </div> |
| </div> |
| </div><div id='content' class='page-1'><div class='row'><div class='row-fluid'><div class='col-lg-3'><ul class='nav-sidebar'><li><a href='./'>Blog index</a></li><li class='active'><a href='#doc'>Processing Results for the Critérium du Dauphiné</a></li><li><a href='#_results_file' class='anchor-link'>Results file</a></li></ul><br/><ul class='nav-sidebar'><li style='padding: 0.35em 0.625em; background-color: #eee'><span>Related posts</span></li><li><a href='./reading-and-writing-csv-files'>Reading and Writing CSV files with Groovy</a></li><li><a href='./zipping-collections-with-groovy'>Zipping Collections with Groovy</a></li><li><a href='./comparators-and-sorting-in-groovy'>Comparators and Sorting in Groovy</a></li><li><a href='./groovy-list-processing-cheat-sheet'>Groovy List Processing Cheat Sheet</a></li><li><a href='./parsing-json-with-groovy'>Parsing JSON with Groovy</a></li><li><a href='./working-with-sql-databases-with'>Working with SQL databases with Groovy and GraalVM</a></li></ul></div><div class='col-lg-8 col-lg-pull-0'><a name='doc'></a><h1>Processing Results for the Critérium du Dauphiné</h1><p><span>Author: <i>Paul King</i></span><br/><span>Published: 2023-06-13 05:00PM</span></p><hr/><div id="preamble"> |
| <div class="sectionbody"> |
| <div class="paragraph"> |
| <p>The 2023 <a href="https://www.criterium-du-dauphine.fr/en/">Critérium du Dauphiné</a> |
| has just finished. Let’s examine the results using Groovy and DuckDB. |
| For the purposes of this post, we are interested in the overall top ten |
| riders in the general classification.</p> |
| </div> |
| </div> |
| </div> |
| <div class="sect1"> |
| <h2 id="_results_file">Results file</h2> |
| <div class="sectionbody"> |
| <div class="paragraph"> |
| <p>Our results are stored in a CSV file:</p> |
| </div> |
| <div class="paragraph"> |
| <p><span class="image"><img src="img/topten.png" alt="lines from CSV file"></span></p> |
| </div> |
| <div class="paragraph"> |
| <p>In a |
| <a href="https://groovy.apache.org/blog/reading-and-writing-csv-files">previous article</a>, |
| we looked at reading and writing CSV files using a number of CSV libraries. Today we will use a nice feature of |
| <a href="https://duckdb.org/">DuckDB</a> |
| which can read in CSV files on the fly.</p> |
| </div> |
| <div class="paragraph"> |
| <p>Our goal is very simple, just print out the information but grouped by each rider’s country.</p> |
| </div> |
| <div class="listingblock"> |
| <div class="content"> |
| <pre class="prettyprint highlight"><code data-lang="groovy">Sql.withInstance('jdbc:duckdb:', 'org.duckdb.DuckDBDriver') { sql -> |
| println "Country Riders Places Count" |
| sql.eachRow("""SELECT Country, |
| rpad(string_agg(Rider, ', '), 40, ' ') as Riders, |
| rpad(string_agg(Place, ', '), 10, ' ') as Places, |
| bar(count(Country), 0, 4, 30) as Count |
| FROM 'topten.csv' GROUP BY Country""") { row -> |
| row.with { |
| println "$Country $Riders$Places$Count" |
| } |
| } |
| }</code></pre> |
| </div> |
| </div> |
| <div class="paragraph"> |
| <p>Here we are making use of several built-in functions from DuckDB |
| including <code>rpad</code> to right pad the output, <code>string_agg</code> to aggregate |
| the riders (and their places) from the same country, and <code>bar</code> |
| to produce a pretty barchart.</p> |
| </div> |
| <div class="paragraph"> |
| <p>Running this code produces the following output:</p> |
| </div> |
| <div class="listingblock"> |
| <div class="content"> |
| <pre class="prettyprint highlight"><code data-lang="text">Country Riders Places Count |
| 🇩🇰 JONAS VINGEGAARD 1 ███████▌ |
| 🇬🇧 ADAM YATES 2 ███████▌ |
| 🇦🇺 BEN O’CONNOR, JAI HINDLEY, JACK HAIG 3, 4, 5 ██████████████████████▌ |
| 🇫🇷 GUILLAUME MARTIN, JULIAN ALAPHILIPPE 6, 10 ███████████████ |
| 🇿🇦 LOUIS DU BOUISSON MEINTJES 7 ███████▌ |
| 🇳🇴 TORSTEIN TRÆEN 8 ███████▌ |
| 🇪🇸 CARLOS RODRIGUEZ CANO 9 ███████▌</code></pre> |
| </div> |
| </div> |
| <div class="paragraph"> |
| <p>We can achieve a similar result using GQuery (AKA GINQ) using the following code:</p> |
| </div> |
| <div class="listingblock"> |
| <div class="content"> |
| <pre class="prettyprint highlight"><code data-lang="groovy">var f = 'topten.csv' as File |
| var lines = f.readLines()*.split(',') |
| var cols = lines[0].size() |
| var rows = lines[1..-1].collect{row -> |
| (0..<cols).collectEntries{ col -> [lines[0][col], row[col]] }} |
| var commaDelimited = Collectors.joining(', ') |
| var aggRiders = { it.stream().map(rec -> rec.r.Rider).collect(commaDelimited) } |
| var aggPlaces = { it.stream().map(rec -> rec.r.Place).collect(commaDelimited) } |
| println GQ { |
| from r in rows |
| groupby r.Country |
| select r.Country, |
| agg(aggRiders(_g)) as Riders, |
| agg(aggPlaces(_g)) as Places, |
| '██' * count(r.Country) as Count |
| }</code></pre> |
| </div> |
| </div> |
| <div class="paragraph"> |
| <p>We could use a CSV library to read in the data, but for this simple example |
| we’ll just use Groovy’s line/text processing capabilities. |
| GQuery doesn’t currently have built in equivalents to <code>bar</code> or <code>string_agg</code> |
| so we roll our own crude bar character function and aggregators, <code>aggRiders</code> |
| and <code>aggPlaces</code>.</p> |
| </div> |
| <div class="paragraph"> |
| <p>Running this code gives the following output:</p> |
| </div> |
| <div class="listingblock"> |
| <div class="content"> |
| <pre class="prettyprint highlight"><code data-lang="text">+---------+--------------------------------------+---------+--------+ |
| | Country | Riders | Places | Count | |
| +---------+--------------------------------------+---------+--------+ |
| | 🇦🇺 | BEN O’CONNOR, JAI HINDLEY, JACK HAIG | 3, 4, 5 | ██████ | |
| | 🇩🇰 | JONAS VINGEGAARD | 1 | ██ | |
| | 🇳🇴 | TORSTEIN TRÆEN | 8 | ██ | |
| | 🇿🇦 | LOUIS DU BOUISSON MEINTJES | 7 | ██ | |
| | 🇬🇧 | ADAM YATES | 2 | ██ | |
| | 🇪🇸 | CARLOS RODRIGUEZ CANO | 9 | ██ | |
| | 🇫🇷 | GUILLAUME MARTIN, JULIAN ALAPHILIPPE | 6, 10 | ████ | |
| +---------+--------------------------------------+---------+--------+</code></pre> |
| </div> |
| </div> |
| <div class="paragraph"> |
| <p>For Groovy 5, we are currently exploring the possibility of adding more |
| functions in GQuery, like <code>bar</code> and <code>stringAgg</code>. If you are interested in this |
| functionality, please get in touch via the |
| <a href="https://groovy-lang.org/mailing-lists.html">mailing lists</a> or |
| <a href="http://groovycommunity.com/">Slack</a>.</p> |
| </div> |
| </div> |
| </div></div></div></div></div><footer id='footer'> |
| <div class='row'> |
| <div class='colset-3-footer'> |
| <div class='col-1'> |
| <h1>Groovy</h1><ul> |
| <li><a href='https://groovy-lang.org/learn.html'>Learn</a></li><li><a href='https://groovy-lang.org/documentation.html'>Documentation</a></li><li><a href='/download.html'>Download</a></li><li><a href='https://groovy-lang.org/support.html'>Support</a></li><li><a href='/'>Contribute</a></li><li><a href='https://groovy-lang.org/ecosystem.html'>Ecosystem</a></li><li><a href='/blog'>Blog posts</a></li><li><a href='https://groovy.apache.org/events.html'></a></li> |
| </ul> |
| </div><div class='col-2'> |
| <h1>About</h1><ul> |
| <li><a href='https://github.com/apache/groovy'>Source code</a></li><li><a href='https://groovy-lang.org/security.html'>Security</a></li><li><a href='https://groovy-lang.org/learn.html#books'>Books</a></li><li><a href='https://groovy-lang.org/thanks.html'>Thanks</a></li><li><a href='http://www.apache.org/foundation/sponsorship.html'>Sponsorship</a></li><li><a href='https://groovy-lang.org/faq.html'>FAQ</a></li><li><a href='https://groovy-lang.org/search.html'>Search</a></li> |
| </ul> |
| </div><div class='col-3'> |
| <h1>Socialize</h1><ul> |
| <li><a href='https://groovy-lang.org/mailing-lists.html'>Discuss on the mailing-list</a></li><li><a href='https://twitter.com/ApacheGroovy'>Groovy on Twitter</a></li><li><a href='https://groovy-lang.org/events.html'>Events and conferences</a></li><li><a href='https://github.com/apache/groovy'>Source code on GitHub</a></li><li><a href='https://groovy-lang.org/reporting-issues.html'>Report issues in Jira</a></li><li><a href='http://stackoverflow.com/questions/tagged/groovy'>Stack Overflow questions</a></li><li><a href='http://groovycommunity.com/'>Slack Community</a></li> |
| </ul> |
| </div><div class='col-right'> |
| <p> |
| The Groovy programming language is supported by the <a href='http://www.apache.org'>Apache Software Foundation</a> and the Groovy community. |
| </p><div text-align='right'> |
| <img src='../img/asf_logo.png' title='The Apache Software Foundation' alt='The Apache Software Foundation' style='width:60%'/> |
| </div><p>Apache® and the Apache feather logo are either registered trademarks or trademarks of The Apache Software Foundation.</p> |
| </div> |
| </div><div class='clearfix'>© 2003-2023 the Apache Groovy project — Groovy is Open Source: <a href='http://www.apache.org/licenses/LICENSE-2.0.html' alt='Apache 2 License'>license</a>, <a href='https://privacy.apache.org/policies/privacy-policy-public.html'>privacy policy</a>.</div> |
| </div> |
| </footer></div> |
| </div> |
| </div> |
| </div> |
| </div><script src='../js/vendor/jquery-1.10.2.min.js' defer></script><script src='../js/vendor/classie.js' defer></script><script src='../js/vendor/bootstrap.js' defer></script><script src='../js/vendor/sidebarEffects.js' defer></script><script src='../js/vendor/modernizr-2.6.2.min.js' defer></script><script src='../js/plugins.js' defer></script><script src='https://cdnjs.cloudflare.com/ajax/libs/prettify/r298/prettify.min.js'></script><script>document.addEventListener('DOMContentLoaded',prettyPrint)</script><script> |
| (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){ |
| (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o), |
| m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m) |
| })(window,document,'script','//www.google-analytics.com/analytics.js','ga'); |
| |
| ga('create', 'UA-257558-10', 'auto'); |
| ga('send', 'pageview'); |
| </script> |
| </body></html> |