mirror of
https://github.com/johnkerl/miller.git
synced 2026-07-18 17:04:50 +00:00
1010 lines
29 KiB
HTML
1010 lines
29 KiB
HTML
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
|
|
<html lang="en">
|
|
|
|
<!-- PAGE GENERATED FROM template.html and content-for-cookbook.html BY poki. -->
|
|
<!-- PLEASE MAKE CHANGES THERE AND THEN RE-RUN poki. -->
|
|
<head>
|
|
<meta http-equiv="Content-type" content="text/html;charset=UTF-8"/>
|
|
<meta name="description" content="Miller documentation"/>
|
|
<meta name="viewport" content="width=device-width, initial-scale=1.0"/> <!-- mobile-friendly -->
|
|
<meta name="keywords"
|
|
content="John Kerl, Kerl, Miller, miller, mlr, OLAP, data analysis software, regression, correlation, variance, data tools, " />
|
|
|
|
<title> Cookbook </title>
|
|
<link rel="stylesheet" type="text/css" href="css/miller.css"/>
|
|
<link rel="stylesheet" type="text/css" href="css/poki-callbacks.css"/>
|
|
</head>
|
|
|
|
<!-- ================================================================ -->
|
|
<script type="text/javascript">
|
|
var gaJsHost = (("https:" == document.location.protocol) ? "https://ssl." : "http://www.");
|
|
document.write(unescape("%3Cscript src='" + gaJsHost + "google-analytics.com/ga.js' type='text/javascript'%3E%3C/script%3E"));
|
|
</script>
|
|
<script type="text/javascript">
|
|
try {
|
|
var pageTracker = _gat._getTracker("UA-15651652-1");
|
|
pageTracker._trackPageview();
|
|
} catch(err) {}
|
|
</script>
|
|
|
|
<script type="text/javascript">
|
|
function toggle(divName) {
|
|
var eleDiv = document.getElementById(divName);
|
|
if (eleDiv != null) {
|
|
if (eleDiv.style.display == "block") {
|
|
eleDiv.style.display = "none";
|
|
} else {
|
|
eleDiv.style.display = "block";
|
|
}
|
|
}
|
|
}
|
|
</script>
|
|
|
|
<!--
|
|
The background image is from a screenshot of a Google search for "data analysis
|
|
tools", lightened and sepia-toned. Over this was placed a Mac Terminal app with
|
|
very light-grey font and translucent background, in which a few statistical
|
|
Miller commands were run with pretty-print-tabular output format.
|
|
-->
|
|
<body background="pix/sepia-overlay.jpg">
|
|
|
|
<!-- ================================================================ -->
|
|
<table width="100%">
|
|
<tr>
|
|
|
|
<!-- navbar -->
|
|
<td width="15%">
|
|
<!--
|
|
<img src="pix/mlr.jpg" />
|
|
<img style="border-width:1px; color:black;" src="pix/mlr.jpg" />
|
|
-->
|
|
|
|
<div class="pokinav">
|
|
<center><titleinbody>Miller</titleinbody></center>
|
|
|
|
<!-- PAGE LIST GENERATED FROM template.html BY poki -->
|
|
<br/><b>Overview:</b>
|
|
<br/>• <a href="index.html">About Miller</a>
|
|
<br/>• <a href="file-formats.html">File formats</a>
|
|
<br/>• <a href="feature-comparison.html">Miller features in the context of the Unix toolkit</a>
|
|
<br/>• <a href="record-heterogeneity.html">Record-heterogeneity</a>
|
|
<br/>• <a href="internationalization.html">Internationalization</a>
|
|
<br/><b>Using Miller:</b>
|
|
<br/>• <a href="reference.html">Reference</a>
|
|
<br/>• <a href="faq.html">FAQ</a>
|
|
<br/>• <a href="cookbook.html"><b>Cookbook</b></a>
|
|
<br/>• <a href="data-examples.html">Data examples</a>
|
|
<br/>• <a href="build.html">Installation, portability, dependencies, and testing</a>
|
|
<br/>• <a href="release-docs.html">Documents by release</a>
|
|
<br/><b>Background:</b>
|
|
<br/>• <a href="whyc.html">Why C?</a>
|
|
<br/>• <a href="etymology.html">Why call it Miller?</a>
|
|
<br/>• <a href="originality.html">How original is Miller?</a>
|
|
<br/>• <a href="performance.html">Performance</a>
|
|
<br/><b>Repository:</b>
|
|
<br/>• <a href="to-do.html">Things to do</a>
|
|
<br/>• <a href="contact.html">Contact information</a>
|
|
<br/>• <a href="https://github.com/johnkerl/miller">GitHub repo</a>
|
|
<br/> <br/> <br/> <br/> <br/> <br/> <br/> <br/> <br/> <br/> <br/> <br/>
|
|
<br/> <br/> <br/> <br/> <br/> <br/> <br/> <br/> <br/> <br/> <br/> <br/>
|
|
<br/> <br/> <br/> <br/> <br/> <br/>
|
|
</div>
|
|
</td>
|
|
|
|
<!-- page body -->
|
|
<td>
|
|
<!--
|
|
This is a visually gorgeous feature (here & in the CSS): it allows for
|
|
independent scroll of the nav and body panels. In particular the nav
|
|
stays on-screen as you scroll the body.
|
|
|
|
However, two problems:
|
|
|
|
(1) In Firefox & Chrome both I get janky end-of-body scrolls: there is
|
|
more content but I can't scroll down to it unless I repeatedly retry the
|
|
scrolldown. Which is weird.
|
|
|
|
(2) Worse, only the first page renders in PDF (again, Firefox & Chrome).
|
|
|
|
For now I'm disabling this separate-scroll feature. A frontender, I am
|
|
not ... maybe someday I'll find a config which gets *all* the features
|
|
I want; for now, it's a tradeoff.
|
|
-->
|
|
|
|
<!-- Implementation details: one bit is right here:
|
|
|
|
div style="overflow-y:scroll;height:1500px"
|
|
|
|
and the other bit is in css/poki-callbacks.css:
|
|
|
|
.pokinav {
|
|
display: inline-block;
|
|
background: #e8d9bc;
|
|
border: 1;
|
|
box-shadow: 0px 0px 3px 3px #C9C9C9;
|
|
margin: 10px;
|
|
padding-top: 10px;
|
|
padding-bottom: 10px;
|
|
padding-left: 10px;
|
|
padding-right: 10px;
|
|
overflow-y: scroll; < - - - - - - here
|
|
height: 1500px;
|
|
}
|
|
|
|
-->
|
|
<div>
|
|
<center> <titleinbody> Cookbook </titleinbody> </center>
|
|
<p/>
|
|
|
|
<!-- BODY COPIED FROM content-for-cookbook.html BY poki -->
|
|
<div class="pokitoc">
|
|
<center><b>Contents:</b></center>
|
|
• <a href="#Parsing_log-file_output">Parsing log-file output</a><br/>
|
|
• <a href="#Rectangularizing_data">Rectangularizing data</a><br/>
|
|
• <a href="#Bulk_rename_of_field_names">Bulk rename of field names</a><br/>
|
|
• <a href="#Regularizing_ragged_CSV">Regularizing ragged CSV</a><br/>
|
|
• <a href="#Two-pass_computation_of_percentages">Two-pass computation of percentages</a><br/>
|
|
• <a href="#Filtering_paragraphs_of_text">Filtering paragraphs of text</a><br/>
|
|
• <a href="#Doing_arithmetic_on_fields_with_currency_symbols">Doing arithmetic on fields with currency symbols</a><br/>
|
|
• <a href="#Program_timing">Program timing</a><br/>
|
|
• <a href="#Using_out-of-stream_variables">Using out-of-stream variables</a><br/>
|
|
• <a href="#Mean_without/with_oosvars">Mean without/with oosvars</a><br/>
|
|
• <a href="#Keyed_mean_without/with_oosvars">Keyed mean without/with oosvars</a><br/>
|
|
• <a href="#Variance_and_standard_deviation_without/with_oosvars">Variance and standard deviation without/with oosvars</a><br/>
|
|
• <a href="#Min/max_without/with_oosvars">Min/max without/with oosvars</a><br/>
|
|
• <a href="#Keyed_min/max_without/with_oosvars">Keyed min/max without/with oosvars</a><br/>
|
|
• <a href="#Delta_without/with_oosvars">Delta without/with oosvars</a><br/>
|
|
• <a href="#Keyed_delta_without/with_oosvars">Keyed delta without/with oosvars</a><br/>
|
|
• <a href="#Exponentially_weighted_moving_averages_without/with_oosvars">Exponentially weighted moving averages without/with oosvars</a><br/>
|
|
</div>
|
|
<p/>
|
|
|
|
<a id="Parsing_log-file_output"/><h1>Parsing log-file output</h1>
|
|
|
|
<p/>This, of course, depends highly on what’s in your log files. But, as
|
|
an example, suppose you have log-file lines such as
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
2015-10-08 08:29:09,445 INFO com.company.path.to.ClassName @ [sometext] various/sorts/of data {& punctuation} hits=1 status=0 time=2.378
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
I prefer to pre-filter with <tt>grep</tt> and/or <tt>sed</tt> to extract the structured text, then hand that to Miller. Example:
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
grep 'various sorts' *.log | sed 's/.*} //' | mlr --fs space --repifs --oxtab stats1 -a min,p10,p50,p90,max -f time -g status
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
<a id="Rectangularizing_data"/><h1>Rectangularizing data</h1>
|
|
|
|
<p/>Suppose you have a method (in whatever language) which is printing things of the form
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
outer=1
|
|
outer=2
|
|
outer=3
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
and then calls another method which prints things of the form
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
middle=10
|
|
middle=11
|
|
middle=12
|
|
middle=20
|
|
middle=21
|
|
middle=30
|
|
middle=31
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
and then, perhaps, that second method calls a third method which prints things of the form
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
inner1=100,inner2=101
|
|
inner1=120,inner2=121
|
|
inner1=200,inner2=201
|
|
inner1=210,inner2=211
|
|
inner1=300,inner2=301
|
|
inner1=312
|
|
inner1=313,inner2=314
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
with the result that your program’s output is
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
outer=1
|
|
middle=10
|
|
inner1=100,inner2=101
|
|
middle=11
|
|
middle=12
|
|
inner1=120,inner2=121
|
|
outer=2
|
|
middle=20
|
|
inner1=200,inner2=201
|
|
middle=21
|
|
inner1=210,inner2=211
|
|
outer=3
|
|
middle=30
|
|
inner1=300,inner2=301
|
|
middle=31
|
|
inner1=312
|
|
inner1=313,inner2=314
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
The idea here is that middles starting with a 1 belong to the outer value of 1,
|
|
and so on. (For example, the outer values might be account IDs, the middle
|
|
values might be invoice IDs, and the inner values might be invoice line-items.)
|
|
If you want all the middle and inner lines to have the context of which outers
|
|
they belong to, you can modify your software to pass all those through your
|
|
methods. Alternatively, you can use the following to rectangularize the data.
|
|
The idea is to use an out-of-stream variable to accumulate fields across
|
|
records. Clear that variable when you see an outer ID; accumulate fields; emit
|
|
output when you see the inner IDs.
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --from data/rect.txt put -q '
|
|
ispresent($outer) {
|
|
unset @r
|
|
}
|
|
for (k, v in $*) {
|
|
@r[k] = v
|
|
}
|
|
ispresent($inner1) {
|
|
emit @r
|
|
}'
|
|
outer=1,middle=10,inner1=100,inner2=101
|
|
outer=1,middle=12,inner1=120,inner2=121
|
|
outer=2,middle=20,inner1=200,inner2=201
|
|
outer=2,middle=21,inner1=210,inner2=211
|
|
outer=3,middle=30,inner1=300,inner2=301
|
|
outer=3,middle=31,inner1=312,inner2=301
|
|
outer=3,middle=31,inner1=313,inner2=314
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
<a id="Bulk_rename_of_field_names"/><h1>Bulk rename of field names</h1>
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ cat data/spaces.csv
|
|
a b c,def,g h i
|
|
123,4567,890
|
|
2468,1357,3579
|
|
9987,3312,4543
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --csv --rs lf rename -r -g ' ,_' data/spaces.csv
|
|
a_b_c,def,g_h_i
|
|
123,4567,890
|
|
2468,1357,3579
|
|
9987,3312,4543
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --csv --irs lf --opprint rename -r -g ' ,_' data/spaces.csv
|
|
a_b_c def g_h_i
|
|
123 4567 890
|
|
2468 1357 3579
|
|
9987 3312 4543
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
<p/>You can also do this with a for-loop but it puts the modified fields after the unmodified fields:
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --icsv --irs lf --opprint put -f data/bulk-rename-for-loop.mlr data/spaces.csv
|
|
def a_b_c g_h_i
|
|
4567 123 890
|
|
1357 2468 3579
|
|
3312 9987 4543
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
<a id="Regularizing_ragged_CSV"/><h1>Regularizing ragged CSV</h1>
|
|
|
|
<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="pokipanel">
|
|
<pre>
|
|
$ cat data/ragged.csv
|
|
a,b,c
|
|
1,2,3
|
|
4,5
|
|
6
|
|
7,8,9
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --from data/ragged.csv --fs comma --nidx put '
|
|
@maxnf = max(@maxnf, NF);
|
|
@nf = NF;
|
|
while(@nf < @maxnf) {
|
|
@nf += 1;
|
|
$[@nf] = ""
|
|
}
|
|
'
|
|
a,b,c
|
|
1,2,3
|
|
4,5,
|
|
6,,
|
|
7,8,9
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
or, more simply,
|
|
|
|
POKI_RUN_COMMAND(cat data/small)HERE
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --from data/ragged.csv --fs comma --nidx put '
|
|
@maxnf = max(@maxnf, NF);
|
|
while(NF < @maxnf) {
|
|
$[NF+1] = "";
|
|
}
|
|
'
|
|
a,b,c
|
|
1,2,3
|
|
4,5,
|
|
6,,
|
|
7,8,9
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
<a id="Two-pass_computation_of_percentages"/><h1>Two-pass computation of percentages</h1>
|
|
|
|
<p/>Miller is a streaming record processor; commands are performed once per
|
|
record. This makes Miller particularly suitable for single-pass algorithms,
|
|
allowing many of its verbs to process files that are (much) larger than the
|
|
amount of RAM present in your system. (Of course, Miller verbs such as
|
|
<tt>sort</tt>, </tt>tac</tt>, etc. all must ingest and retain all input records
|
|
before emitting any output records.)
|
|
|
|
<p/>You can also use out-of-stream variables to perform multi-pass computations.
|
|
For example, mapping numeric values down a column to the percentage between
|
|
their min and max values is two-pass: on the first pass you find the min and
|
|
max values, then on the second, map each record’s value to a percentage.
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --from data/small --opprint put -q '
|
|
# These are executed once per record, which is the first pass.
|
|
# The key is to use NR to index an out-of-stream variable to
|
|
# retain all the x-field values.
|
|
@x_min = min($x, @x_min);
|
|
@x_max = max($x, @x_max);
|
|
@x[NR] = $x;
|
|
|
|
# The second pass is in a for-loop in an end-block.
|
|
end {
|
|
for (nr, x in @x) {
|
|
@x_pct[nr] = 100 * (@x[nr] - @x_min) / (@x_max - @x_min);
|
|
}
|
|
emit (@x, @x_pct), "NR"
|
|
}
|
|
'
|
|
NR x x_pct
|
|
1 0.346790 25.661943
|
|
2 0.758680 100.000000
|
|
3 0.204603 0.000000
|
|
4 0.381399 31.908236
|
|
5 0.573289 66.540542
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
<a id="Filtering_paragraphs_of_text"/><h1>Filtering paragraphs of text</h1>
|
|
|
|
<p/>The idea is to use a record separator which is a pair of newlines. Then, if
|
|
you want each paragraph to be a record with a single value, use a
|
|
field-separator which isn’t present in the input data (e.g. a control-A
|
|
which is octal 001). Or, if you want each paragraph to have its lines as
|
|
separate values, use newline as field separator.
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ cat paragraphs.txt
|
|
The quick brown fox jumped over the lazy dogs. The quick brown fox jumped
|
|
over the lazy dogs. The quick brown fox jumped over the lazy dogs. The quick
|
|
brown fox jumped over the lazy dogs. The quick brown fox jumped over the
|
|
lazy dogs.
|
|
|
|
Now is the time for all good people to come to the aid of their country. Now
|
|
is the time for all good people to come to the aid of their country. Now is
|
|
the time for all good people to come to the aid of their country. Now is the
|
|
time for all good people to come to the aid of their country. Now is the
|
|
time for all good people to come to the aid of their country.
|
|
|
|
Sphynx of black quartz, judge my vow. Sphynx of black quartz, judge my vow.
|
|
Sphynx of black quartz, judge my vow. Sphynx of black quartz, judge my vow.
|
|
Sphynx of black quartz, judge my vow.
|
|
|
|
The rain in Spain falls mainly on the plain. The rain in Spain falls mainly
|
|
on the plain. The rain in Spain falls mainly on the plain. The rain in Spain
|
|
falls mainly on the plain. The rain in Spain falls mainly on the plain. The
|
|
rain in Spain falls mainly on the plain. The rain in Spain falls mainly on
|
|
the plain. The rain in Spain falls mainly on the plain.
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --from paragraphs.txt --nidx --rs '\n\n' --fs '\001' filter '$1 =~ "the"'
|
|
The quick brown fox jumped over the lazy dogs. The quick brown fox jumped
|
|
over the lazy dogs. The quick brown fox jumped over the lazy dogs. The quick
|
|
brown fox jumped over the lazy dogs. The quick brown fox jumped over the
|
|
lazy dogs.
|
|
|
|
Now is the time for all good people to come to the aid of their country. Now
|
|
is the time for all good people to come to the aid of their country. Now is
|
|
the time for all good people to come to the aid of their country. Now is the
|
|
time for all good people to come to the aid of their country. Now is the
|
|
time for all good people to come to the aid of their country.
|
|
|
|
The rain in Spain falls mainly on the plain. The rain in Spain falls mainly
|
|
on the plain. The rain in Spain falls mainly on the plain. The rain in Spain
|
|
falls mainly on the plain. The rain in Spain falls mainly on the plain. The
|
|
rain in Spain falls mainly on the plain. The rain in Spain falls mainly on
|
|
the plain. The rain in Spain falls mainly on the plain.
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --from paragraphs.txt --nidx --rs '\n\n' --fs '\n' cut -f 1,3
|
|
The quick brown fox jumped over the lazy dogs. The quick brown fox jumped
|
|
brown fox jumped over the lazy dogs. The quick brown fox jumped over the
|
|
|
|
Now is the time for all good people to come to the aid of their country. Now
|
|
the time for all good people to come to the aid of their country. Now is the
|
|
|
|
Sphynx of black quartz, judge my vow. Sphynx of black quartz, judge my vow.
|
|
Sphynx of black quartz, judge my vow.
|
|
|
|
The rain in Spain falls mainly on the plain. The rain in Spain falls mainly
|
|
falls mainly on the plain. The rain in Spain falls mainly on the plain. The
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
<a id="Doing_arithmetic_on_fields_with_currency_symbols"/><h1>Doing arithmetic on fields with currency symbols</h1>
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ cat sample.csv
|
|
EventOccurred,EventType,Description,Status,PaymentType,NameonAccount,TransactionNumber,Amount
|
|
10/1/2015,Charged Back,Reason: Authorization Revoked By Customer,Disputed,Checking,John,1,$230.36
|
|
10/1/2015,Charged Back,Reason: Authorization Revoked By Customer,Disputed,Checking,Fred,2,$32.25
|
|
10/1/2015,Charged Back,Reason: Customer Advises Not Authorized,Disputed,Checking,Bob,3,$39.02
|
|
10/1/2015,Charged Back,Reason: Authorization Revoked By Customer,Disputed,Checking,Alice,4,$57.54
|
|
10/1/2015,Charged Back,Reason: Authorization Revoked By Customer,Disputed,Checking,Jungle,5,$230.36
|
|
10/1/2015,Charged Back,Reason: Payment Stopped,Disputed,Checking,Joe,6,$281.96
|
|
10/2/2015,Charged Back,Reason: Customer Advises Not Authorized,Disputed,Checking,Joseph,7,$188.19
|
|
10/2/2015,Charged Back,Reason: Customer Advises Not Authorized,Disputed,Checking,Joseph,8,$188.19
|
|
10/2/2015,Charged Back,Reason: Payment Stopped,Disputed,Checking,Anthony,9,$250.00
|
|
|
|
$ mlr --icsv --opprint cat sample.csv
|
|
EventOccurred EventType Description Status PaymentType NameonAccount TransactionNumber Amount
|
|
10/1/2015 Charged Back Reason: Authorization Revoked By Customer Disputed Checking John 1 $230.36
|
|
10/1/2015 Charged Back Reason: Authorization Revoked By Customer Disputed Checking Fred 2 $32.25
|
|
10/1/2015 Charged Back Reason: Customer Advises Not Authorized Disputed Checking Bob 3 $39.02
|
|
10/1/2015 Charged Back Reason: Authorization Revoked By Customer Disputed Checking Alice 4 $57.54
|
|
10/1/2015 Charged Back Reason: Authorization Revoked By Customer Disputed Checking Jungle 5 $230.36
|
|
10/1/2015 Charged Back Reason: Payment Stopped Disputed Checking Joe 6 $281.96
|
|
10/2/2015 Charged Back Reason: Customer Advises Not Authorized Disputed Checking Joseph 7 $188.19
|
|
10/2/2015 Charged Back Reason: Customer Advises Not Authorized Disputed Checking Joseph 8 $188.19
|
|
10/2/2015 Charged Back Reason: Payment Stopped Disputed Checking Anthony 9 $250.00
|
|
|
|
$ mlr --csv put '$Amount = sub(string($Amount), "\$", "")' then stats1 -a sum -f Amount sample.csv
|
|
Amount_sum
|
|
1497.870000
|
|
|
|
$ mlr --csv --ofmt '%.2lf' put '$Amount = sub(string($Amount), "\$", "")' then stats1 -a sum -f Amount sample.csv
|
|
Amount_sum
|
|
1497.87
|
|
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
<a id="Program_timing"/><h1>Program timing</h1>
|
|
|
|
This admittedly artificial example demonstrates using Miller time and stats
|
|
functions to introspectly acquire some information about Miller’s own
|
|
runtime. The <tt>delta</tt> function computes the difference between successive
|
|
timestamps.
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ ruby -e '10000.times{|i|puts "i=#{i+1}"}' > lines.txt
|
|
|
|
$ head -n 5 lines.txt
|
|
i=1
|
|
i=2
|
|
i=3
|
|
i=4
|
|
i=5
|
|
|
|
mlr --ofmt '%.9le' --opprint put '$t=systime()' then step -a delta -f t lines.txt | head -n 7
|
|
i t t_delta
|
|
1 1430603027.018016 1.430603027e+09
|
|
2 1430603027.018043 2.694129944e-05
|
|
3 1430603027.018048 5.006790161e-06
|
|
4 1430603027.018052 4.053115845e-06
|
|
5 1430603027.018055 2.861022949e-06
|
|
6 1430603027.018058 3.099441528e-06
|
|
|
|
mlr --ofmt '%.9le' --oxtab \
|
|
put '$t=systime()' then \
|
|
step -a delta -f t then \
|
|
filter '$i>1' then \
|
|
stats1 -a min,mean,max -f t_delta \
|
|
lines.txt
|
|
t_delta_min 2.861022949e-06
|
|
t_delta_mean 4.077508505e-06
|
|
t_delta_max 5.388259888e-05
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
<a id="Using_out-of-stream_variables"/><h1>Using out-of-stream variables</h1>
|
|
|
|
<p/> One of Miller’s strengths is its compact notation: for example, given input of the form
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ head -n 5 ../data/medium
|
|
a=pan,b=pan,i=1,x=0.3467901443380824,y=0.7268028627434533
|
|
a=eks,b=pan,i=2,x=0.7586799647899636,y=0.5221511083334797
|
|
a=wye,b=wye,i=3,x=0.20460330576630303,y=0.33831852551664776
|
|
a=eks,b=wye,i=4,x=0.38139939387114097,y=0.13418874328430463
|
|
a=wye,b=pan,i=5,x=0.5732889198020006,y=0.8636244699032729
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
you can simply do
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --oxtab stats1 -a sum -f x ../data/medium
|
|
x_sum 4986.019682
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
or
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --opprint stats1 -a sum -f x -g b ../data/medium
|
|
b x_sum
|
|
pan 965.763670
|
|
wye 1023.548470
|
|
zee 979.742016
|
|
eks 1016.772857
|
|
hat 1000.192668
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
rather than the more tedious
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --oxtab put -q '
|
|
@x_sum += $x;
|
|
end {
|
|
emit @x_sum
|
|
}
|
|
' data/medium
|
|
x_sum 4986.019682
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
or
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --opprint put -q '
|
|
@x_sum[$b] += $x;
|
|
end {
|
|
emit @x_sum, "b"
|
|
}
|
|
' data/medium
|
|
b x_sum
|
|
pan 965.763670
|
|
wye 1023.548470
|
|
zee 979.742016
|
|
eks 1016.772857
|
|
hat 1000.192668
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
<p/> The former (<tt>mlr stats1</tt> et al.) has the advantages of being easier
|
|
to type, being less error-prone to type, and running faster.
|
|
|
|
<p/> Nonetheless, out-of-stream variables (which I whimsically call
|
|
<i>oosvars</i>), begin/end blocks, and emit statements give you the ability to
|
|
implement logic — if you wish to do so — which isn’t present
|
|
in other Miller verbs. (If you find yourself often using the same
|
|
out-of-stream-variable logic over and over, please file a request at <a
|
|
href="https://github.com/johnkerl/miller/issues">https://github.com/johnkerl/miller/issues</a>
|
|
to get it implemented directly in C as a Miller verb of its own.)
|
|
|
|
<p/> The following examples compute some things using oosvars which are already
|
|
computable using Miller verbs, by way of providing food for thought.
|
|
|
|
<a id="Mean_without/with_oosvars"/><h2>Mean without/with oosvars</h2>
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --opprint stats1 -a mean -f x data/medium
|
|
x_mean
|
|
0.498602
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --opprint put -q '
|
|
@x_sum += $x;
|
|
@x_count += 1;
|
|
end {
|
|
@x_mean = @x_sum / @x_count;
|
|
emit @x_mean
|
|
}
|
|
' data/medium
|
|
x_mean
|
|
0.498602
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
<a id="Keyed_mean_without/with_oosvars"/><h2>Keyed mean without/with oosvars</h2>
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --opprint stats1 -a mean -f x -g a,b data/medium
|
|
a b x_mean
|
|
pan pan 0.513314
|
|
eks pan 0.485076
|
|
wye wye 0.491501
|
|
eks wye 0.483895
|
|
wye pan 0.499612
|
|
zee pan 0.519830
|
|
eks zee 0.495463
|
|
zee wye 0.514267
|
|
hat wye 0.493813
|
|
pan wye 0.502362
|
|
zee eks 0.488393
|
|
hat zee 0.509999
|
|
hat eks 0.485879
|
|
wye hat 0.497730
|
|
pan eks 0.503672
|
|
eks eks 0.522799
|
|
hat hat 0.479931
|
|
hat pan 0.464336
|
|
zee zee 0.512756
|
|
pan hat 0.492141
|
|
pan zee 0.496604
|
|
zee hat 0.467726
|
|
wye zee 0.505907
|
|
eks hat 0.500679
|
|
wye eks 0.530604
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --opprint put -q '
|
|
@x_sum[$a][$b] += $x;
|
|
@x_count[$a][$b] += 1;
|
|
end{
|
|
for ((a, b), v in @x_sum) {
|
|
@x_mean[a][b] = @x_sum[a][b] / @x_count[a][b];
|
|
}
|
|
emit @x_mean, "a", "b"
|
|
}
|
|
' data/medium
|
|
a b x_mean
|
|
pan pan 0.513314
|
|
pan wye 0.502362
|
|
pan eks 0.503672
|
|
pan hat 0.492141
|
|
pan zee 0.496604
|
|
eks pan 0.485076
|
|
eks wye 0.483895
|
|
eks zee 0.495463
|
|
eks eks 0.522799
|
|
eks hat 0.500679
|
|
wye wye 0.491501
|
|
wye pan 0.499612
|
|
wye hat 0.497730
|
|
wye zee 0.505907
|
|
wye eks 0.530604
|
|
zee pan 0.519830
|
|
zee wye 0.514267
|
|
zee eks 0.488393
|
|
zee zee 0.512756
|
|
zee hat 0.467726
|
|
hat wye 0.493813
|
|
hat zee 0.509999
|
|
hat eks 0.485879
|
|
hat hat 0.479931
|
|
hat pan 0.464336
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
<a id="Variance_and_standard_deviation_without/with_oosvars"/><h2>Variance and standard deviation without/with oosvars</h2>
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --oxtab stats1 -a count,sum,mean,var,stddev -f x data/medium
|
|
x_count 10000
|
|
x_sum 4986.019682
|
|
x_mean 0.498602
|
|
x_var 0.084270
|
|
x_stddev 0.290293
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ cat variance.mlr
|
|
@n += 1;
|
|
@sumx += $x;
|
|
@sumx2 += $x**2;
|
|
end {
|
|
@mean = @sumx / @n;
|
|
@var = (@sumx2 - @mean * (2 * @sumx - @n * @mean)) / (@n - 1);
|
|
@stddev = sqrt(@var);
|
|
emitf @n, @sumx, @sumx2, @mean, @var, @stddev
|
|
}
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --oxtab put -q -f variance.mlr data/medium
|
|
n 10000
|
|
sumx 4986.019682
|
|
sumx2 3328.652400
|
|
mean 0.498602
|
|
var 0.084270
|
|
stddev 0.290293
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
You can also do this keyed, of course, imitating the keyed-mean example above.
|
|
|
|
<a id="Min/max_without/with_oosvars"/><h2>Min/max without/with oosvars</h2>
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --oxtab stats1 -a min,max -f x data/medium
|
|
x_min 0.000045
|
|
x_max 0.999953
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --oxtab put -q '@x_min = min(@x_min, $x); @x_max = max(@x_max, $x); end{emitf @x_min, @x_max}' data/medium
|
|
x_min 0.000045
|
|
x_max 0.999953
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
<a id="Keyed_min/max_without/with_oosvars"/><h2>Keyed min/max without/with oosvars</h2>
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --opprint stats1 -a min,max -f x -g a data/medium
|
|
a x_min x_max
|
|
pan 0.000204 0.999403
|
|
eks 0.000692 0.998811
|
|
wye 0.000187 0.999823
|
|
zee 0.000549 0.999490
|
|
hat 0.000045 0.999953
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --opprint --from data/medium put -q '
|
|
@min[$a] = min(@min[$a], $x);
|
|
@max[$a] = max(@max[$a], $x);
|
|
end{
|
|
emit (@min, @max), "a";
|
|
}
|
|
'
|
|
a min max
|
|
pan 0.000204 0.999403
|
|
eks 0.000692 0.998811
|
|
wye 0.000187 0.999823
|
|
zee 0.000549 0.999490
|
|
hat 0.000045 0.999953
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
<a id="Delta_without/with_oosvars"/><h2>Delta without/with oosvars</h2>
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --opprint step -a delta -f x data/small
|
|
a b i x y x_delta
|
|
pan pan 1 0.3467901443380824 0.7268028627434533 0
|
|
eks pan 2 0.7586799647899636 0.5221511083334797 0.411890
|
|
wye wye 3 0.20460330576630303 0.33831852551664776 -0.554077
|
|
eks wye 4 0.38139939387114097 0.13418874328430463 0.176796
|
|
wye pan 5 0.5732889198020006 0.8636244699032729 0.191890
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --opprint put '$x_delta = ispresent(@last) ? $x - @last : 0; @last = $x' data/small
|
|
a b i x y x_delta
|
|
pan pan 1 0.3467901443380824 0.7268028627434533 0
|
|
eks pan 2 0.7586799647899636 0.5221511083334797 0.411890
|
|
wye wye 3 0.20460330576630303 0.33831852551664776 -0.554077
|
|
eks wye 4 0.38139939387114097 0.13418874328430463 0.176796
|
|
wye pan 5 0.5732889198020006 0.8636244699032729 0.191890
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
<a id="Keyed_delta_without/with_oosvars"/><h2>Keyed delta without/with oosvars</h2>
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --opprint step -a delta -f x -g a data/small
|
|
a b i x y x_delta
|
|
pan pan 1 0.3467901443380824 0.7268028627434533 0
|
|
eks pan 2 0.7586799647899636 0.5221511083334797 0
|
|
wye wye 3 0.20460330576630303 0.33831852551664776 0
|
|
eks wye 4 0.38139939387114097 0.13418874328430463 -0.377281
|
|
wye pan 5 0.5732889198020006 0.8636244699032729 0.368686
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --opprint put '$x_delta = ispresent(@last[$a]) ? $x - @last[$a] : 0; @last[$a]=$x' data/small
|
|
a b i x y x_delta
|
|
pan pan 1 0.3467901443380824 0.7268028627434533 0
|
|
eks pan 2 0.7586799647899636 0.5221511083334797 0
|
|
wye wye 3 0.20460330576630303 0.33831852551664776 0
|
|
eks wye 4 0.38139939387114097 0.13418874328430463 -0.377281
|
|
wye pan 5 0.5732889198020006 0.8636244699032729 0.368686
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
<a id="Exponentially_weighted_moving_averages_without/with_oosvars"/><h2>Exponentially weighted moving averages without/with oosvars</h2>
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --opprint step -a ewma -d 0.1 -f x data/small
|
|
a b i x y x_ewma_0.1
|
|
pan pan 1 0.3467901443380824 0.7268028627434533 0.346790
|
|
eks pan 2 0.7586799647899636 0.5221511083334797 0.387979
|
|
wye wye 3 0.20460330576630303 0.33831852551664776 0.369642
|
|
eks wye 4 0.38139939387114097 0.13418874328430463 0.370817
|
|
wye pan 5 0.5732889198020006 0.8636244699032729 0.391064
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
|
|
<p/>
|
|
<div class="pokipanel">
|
|
<pre>
|
|
$ mlr --opprint put '
|
|
begin{ @a=0.1 };
|
|
$e = NR==1 ? $x : @a * $x + (1 - @a) * @e;
|
|
@e=$e
|
|
' data/small
|
|
a b i x y e
|
|
pan pan 1 0.3467901443380824 0.7268028627434533 0.346790
|
|
eks pan 2 0.7586799647899636 0.5221511083334797 0.387979
|
|
wye wye 3 0.20460330576630303 0.33831852551664776 0.369642
|
|
eks wye 4 0.38139939387114097 0.13418874328430463 0.370817
|
|
wye pan 5 0.5732889198020006 0.8636244699032729 0.391064
|
|
</pre>
|
|
</div>
|
|
<p/>
|
|
</div>
|
|
</td>
|
|
|
|
</table>
|
|
</body>
|
|
</html>
|