mirror of
https://github.com/johnkerl/miller.git
synced 2026-07-20 18:10:07 +00:00
177 lines
No EOL
10 KiB
HTML
177 lines
No EOL
10 KiB
HTML
|
||
<!DOCTYPE html>
|
||
|
||
<html>
|
||
<head>
|
||
<meta charset="utf-8" />
|
||
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
|
||
<title>CSV, with and without headers — Miller 6.0.0-alpha documentation</title>
|
||
|
||
<link rel="stylesheet" href="_static/scrolls.css" type="text/css" />
|
||
<link rel="stylesheet" href="_static/pygments.css" type="text/css" />
|
||
<link rel="stylesheet" href="_static/print.css" type="text/css" />
|
||
|
||
<script id="documentation_options" data-url_root="./" src="_static/documentation_options.js"></script>
|
||
<script src="_static/jquery.js"></script>
|
||
<script src="_static/underscore.js"></script>
|
||
<script src="_static/doctools.js"></script>
|
||
<script src="_static/language_data.js"></script>
|
||
<script src="_static/theme_extras.js"></script>
|
||
<link rel="index" title="Index" href="genindex.html" />
|
||
<link rel="search" title="Search" href="search.html" />
|
||
<link rel="next" title="Shapes of data" href="shapes-of-data.html" />
|
||
<link rel="prev" title="How to contribute" href="contributing.html" />
|
||
</head><body>
|
||
<div id="content">
|
||
<div class="header">
|
||
<h1 class="heading"><a href="index.html"
|
||
title="back to the documentation overview"><span>CSV, with and without headers</span></a></h1>
|
||
</div>
|
||
<div class="relnav" role="navigation" aria-label="related navigation">
|
||
<a href="contributing.html">« How to contribute</a> |
|
||
<a href="#">CSV, with and without headers</a>
|
||
| <a href="shapes-of-data.html">Shapes of data »</a>
|
||
</div>
|
||
<div id="contentwrapper">
|
||
<div id="toc" role="navigation" aria-label="table of contents navigation">
|
||
<h3>Table of Contents</h3>
|
||
<ul>
|
||
<li><a class="reference internal" href="#">CSV, with and without headers</a><ul>
|
||
<li><a class="reference internal" href="#headerless-csv-on-input-or-output">Headerless CSV on input or output</a></li>
|
||
<li><a class="reference internal" href="#headerless-csv-with-duplicate-field-values">Headerless CSV with duplicate field values</a></li>
|
||
<li><a class="reference internal" href="#regularizing-ragged-csv">Regularizing ragged CSV</a></li>
|
||
</ul>
|
||
</li>
|
||
</ul>
|
||
|
||
</div>
|
||
<div role="main">
|
||
|
||
<div class="section" id="csv-with-and-without-headers">
|
||
<h1>CSV, with and without headers<a class="headerlink" href="#csv-with-and-without-headers" title="Permalink to this headline">¶</a></h1>
|
||
<div class="section" id="headerless-csv-on-input-or-output">
|
||
<h2>Headerless CSV on input or output<a class="headerlink" href="#headerless-csv-on-input-or-output" title="Permalink to this headline">¶</a></h2>
|
||
<p>Sometimes we get CSV files which lack a header. For example (<a class="reference external" href="./data/headerless.csv">data/headerless.csv</a>):</p>
|
||
<div class="highlight-none notranslate"><div class="highlight"><pre><span></span><span class="hll"> cat data/headerless.csv
|
||
</span> John,23,present
|
||
Fred,34,present
|
||
Alice,56,missing
|
||
Carol,45,present
|
||
</pre></div>
|
||
</div>
|
||
<p>You can use Miller to add a header. The <code class="docutils literal notranslate"><span class="pre">--implicit-csv-header</span></code> applies positionally indexed labels:</p>
|
||
<div class="highlight-none notranslate"><div class="highlight"><pre><span></span><span class="hll"> mlr --csv --implicit-csv-header cat data/headerless.csv
|
||
</span> 1,2,3
|
||
John,23,present
|
||
Fred,34,present
|
||
Alice,56,missing
|
||
Carol,45,present
|
||
</pre></div>
|
||
</div>
|
||
<p>Following that, you can rename the positionally indexed labels to names with meaning for your context. For example:</p>
|
||
<div class="highlight-none notranslate"><div class="highlight"><pre><span></span><span class="hll"> mlr --csv --implicit-csv-header label name,age,status data/headerless.csv
|
||
</span> name,age,status
|
||
John,23,present
|
||
Fred,34,present
|
||
Alice,56,missing
|
||
Carol,45,present
|
||
</pre></div>
|
||
</div>
|
||
<p>Likewise, if you need to produce CSV which is lacking its header, you can pipe Miller’s output to the system command <code class="docutils literal notranslate"><span class="pre">sed</span> <span class="pre">1d</span></code>, or you can use Miller’s <code class="docutils literal notranslate"><span class="pre">--headerless-csv-output</span></code> option:</p>
|
||
<div class="highlight-none notranslate"><div class="highlight"><pre><span></span><span class="hll"> head -5 data/colored-shapes.dkvp | mlr --ocsv cat
|
||
</span> color,shape,flag,i,u,v,w,x
|
||
yellow,triangle,1,11,0.6321695890307647,0.9887207810889004,0.4364983936735774,5.7981881667050565
|
||
red,square,1,15,0.21966833570651523,0.001257332190235938,0.7927778364718627,2.944117399716207
|
||
red,circle,1,16,0.20901671281497636,0.29005231936593445,0.13810280912907674,5.065034003400998
|
||
red,square,0,48,0.9562743938458542,0.7467203085342884,0.7755423050923582,7.117831369597269
|
||
purple,triangle,0,51,0.4355354501763202,0.8591292672156728,0.8122903963006748,5.753094629505863
|
||
</pre></div>
|
||
</div>
|
||
<div class="highlight-none notranslate"><div class="highlight"><pre><span></span><span class="hll"> head -5 data/colored-shapes.dkvp | mlr --ocsv --headerless-csv-output cat
|
||
</span> yellow,triangle,1,11,0.6321695890307647,0.9887207810889004,0.4364983936735774,5.7981881667050565
|
||
red,square,1,15,0.21966833570651523,0.001257332190235938,0.7927778364718627,2.944117399716207
|
||
red,circle,1,16,0.20901671281497636,0.29005231936593445,0.13810280912907674,5.065034003400998
|
||
red,square,0,48,0.9562743938458542,0.7467203085342884,0.7755423050923582,7.117831369597269
|
||
purple,triangle,0,51,0.4355354501763202,0.8591292672156728,0.8122903963006748,5.753094629505863
|
||
</pre></div>
|
||
</div>
|
||
<p>Lastly, often we say “CSV” or “TSV” when we have positionally indexed data in columns which are separated by commas or tabs, respectively. In this case it’s perhaps simpler to <strong>just use NIDX format</strong> which was designed for this purpose. (See also <a class="reference internal" href="file-formats.html"><span class="doc">File formats</span></a>.) For example:</p>
|
||
<div class="highlight-none notranslate"><div class="highlight"><pre><span></span><span class="hll"> mlr --inidx --ifs comma --oxtab cut -f 1,3 data/headerless.csv
|
||
</span> 1 John
|
||
3 present
|
||
|
||
1 Fred
|
||
3 present
|
||
|
||
1 Alice
|
||
3 missing
|
||
|
||
1 Carol
|
||
3 present
|
||
</pre></div>
|
||
</div>
|
||
</div>
|
||
<div class="section" id="headerless-csv-with-duplicate-field-values">
|
||
<h2>Headerless CSV with duplicate field values<a class="headerlink" href="#headerless-csv-with-duplicate-field-values" title="Permalink to this headline">¶</a></h2>
|
||
<p>Miller is (by central design) a mapping from name to value, rather than integer position to value as in most tools in the Unix toolkit such as <code class="docutils literal notranslate"><span class="pre">sort</span></code>, <code class="docutils literal notranslate"><span class="pre">cut</span></code>, <code class="docutils literal notranslate"><span class="pre">awk</span></code>, etc. So given input <code class="docutils literal notranslate"><span class="pre">Yea=1,Yea=2</span></code> on the same input line, first <code class="docutils literal notranslate"><span class="pre">Yea=1</span></code> is stored, then updated with <code class="docutils literal notranslate"><span class="pre">Yea=2</span></code>. This is in the input-parser and the value <code class="docutils literal notranslate"><span class="pre">Yea=1</span></code> is unavailable to any further processing. The following example line comes from a headerless CSV file and includes 5 times the string (value) <code class="docutils literal notranslate"><span class="pre">'NA'</span></code>:</p>
|
||
<div class="highlight-none notranslate"><div class="highlight"><pre><span></span><span class="hll"> ag '0.9' nas.csv | head -1
|
||
</span> 2:-349801.10097848,4537221.43295653,2,1,NA,NA,NA,NA,NA
|
||
</pre></div>
|
||
</div>
|
||
<p>The repeated <code class="docutils literal notranslate"><span class="pre">'NA'</span></code> strings (values) in the same line will be treated as fields (columns) with same name, thus only one is kept in the output.</p>
|
||
<p>This can be worked around by telling <code class="docutils literal notranslate"><span class="pre">mlr</span></code> that there is no header row by using <code class="docutils literal notranslate"><span class="pre">--implicit-csv-header</span></code> or changing the input format by using <code class="docutils literal notranslate"><span class="pre">nidx</span></code> like so:</p>
|
||
<div class="highlight-none notranslate"><div class="highlight"><pre><span></span>ag '0.9' nas.csv | mlr --n2c --fs "," label xsn,ysn,x,y,t,a,e29,e31,e32 then head
|
||
</pre></div>
|
||
</div>
|
||
</div>
|
||
<div class="section" id="regularizing-ragged-csv">
|
||
<h2>Regularizing ragged CSV<a class="headerlink" href="#regularizing-ragged-csv" title="Permalink to this headline">¶</a></h2>
|
||
<p>Miller handles compliant CSV: in particular, it’s an error if the number of data fields in a given data line don’t match the number of header lines. But in the event that you have a CSV file in which some lines have less than the full number of fields, you can use Miller to pad them out. The trick is to use NIDX format, for which each line stands on its own without respect to a header line.</p>
|
||
<div class="highlight-none notranslate"><div class="highlight"><pre><span></span><span class="hll"> cat data/ragged.csv
|
||
</span> a,b,c
|
||
1,2,3
|
||
4,5
|
||
6,7,8,9
|
||
</pre></div>
|
||
</div>
|
||
<div class="highlight-none notranslate"><div class="highlight"><pre><span></span><span class="hll"> mlr --from data/ragged.csv --fs comma --nidx put '
|
||
</span><span class="hll"> @maxnf = max(@maxnf, NF);
|
||
</span><span class="hll"> @nf = NF;
|
||
</span><span class="hll"> while(@nf < @maxnf) {
|
||
</span><span class="hll"> @nf += 1;
|
||
</span><span class="hll"> $[@nf] = ""
|
||
</span><span class="hll"> }
|
||
</span><span class="hll"> '
|
||
</span> a,b,c
|
||
1,2,3
|
||
4,5
|
||
6,7,8,9
|
||
</pre></div>
|
||
</div>
|
||
<p>or, more simply,</p>
|
||
<div class="highlight-none notranslate"><div class="highlight"><pre><span></span><span class="hll"> mlr --from data/ragged.csv --fs comma --nidx put '
|
||
</span><span class="hll"> @maxnf = max(@maxnf, NF);
|
||
</span><span class="hll"> while(NF < @maxnf) {
|
||
</span><span class="hll"> $[NF+1] = "";
|
||
</span><span class="hll"> }
|
||
</span><span class="hll"> '
|
||
</span> a,b,c
|
||
1,2,3
|
||
4,5
|
||
6,7,8,9
|
||
</pre></div>
|
||
</div>
|
||
</div>
|
||
</div>
|
||
|
||
|
||
</div>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="footer" role="contentinfo">
|
||
© Copyright 2021, John Kerl.
|
||
Created using <a href="https://www.sphinx-doc.org/">Sphinx</a> 3.2.1.
|
||
</div>
|
||
</body>
|
||
</html> |