miller

Miller is like awk, sed, cut, join, and sort for name-indexed data such as CSV, TSV, and tabular JSON

9,213

222

9,213

119

View on GitHub

Top Related Projects

xsv

10,589

A fast CSV command line toolkit written in Rust.

tsv-utils

1,442

eBay's TSV Utilities: Command line tools for large, tabular data files. Filtering, statistics, sampling, joins and more.

csvtk

1,048

A cross-platform, efficient and practical CSV/TSV toolkit in Golang

structured-text-tools

7,018

A list of command-line tools for manipulating structured text data

Quick Overview

Miller (mlr) is a command-line tool for querying, transforming, and analyzing data in various formats such as CSV, TSV, JSON, and more. It provides a powerful set of operations for data manipulation, similar to awk, sed, cut, join, and sort, but with a more user-friendly syntax and support for multiple file formats.

Pros

Supports multiple input and output formats (CSV, TSV, JSON, NDJSON, etc.)
Powerful and flexible data manipulation capabilities
Easy-to-learn syntax, especially for users familiar with SQL or awk
Fast performance, even with large datasets

Cons

Learning curve for users not familiar with command-line tools
Limited GUI options for those who prefer graphical interfaces
May require additional setup for some advanced features
Not as widely known or used as some other data manipulation tools

Code Examples

Basic filtering and column selection:

mlr --csv filter '$age > 30' then cut -f name,age input.csv

This command filters rows where the age is greater than 30 and selects only the name and age columns from a CSV file.

Aggregation and sorting:

mlr --csv stats1 -a sum -f salary -g department then sort -nr salary_sum input.csv

This command calculates the sum of salaries grouped by department and sorts the results in descending order.

JSON to CSV conversion with field renaming:

mlr --ijson --ocsv put '$full_name = $first_name . " " . $last_name' then cut -x -f first_name,last_name input.json

This command reads a JSON file, creates a new field called "full_name" by concatenating first and last names, removes the original name fields, and outputs the result as CSV.

Getting Started

To get started with Miller, follow these steps:

Install Miller:
- On macOS: brew install miller
- On Ubuntu: sudo apt-get install miller
- For other platforms, visit the GitHub repository for installation instructions.

Basic usage:

mlr --csv cat input.csv                 # View CSV file contents
mlr --csv head -n 5 input.csv           # View first 5 rows of CSV file
mlr --csv filter '$column > 10' input.csv  # Filter rows based on condition

For more advanced usage and examples, refer to the Miller documentation and cheat sheet available on the GitHub repository.

Competitor Comparisons

xsv

10,589

A fast CSV command line toolkit written in Rust.

Pros of xsv

Faster performance for large datasets
More memory-efficient, suitable for processing huge CSV files
Simpler command-line interface for basic CSV operations

Cons of xsv

Limited to CSV format only, while Miller supports multiple formats
Fewer data manipulation features compared to Miller's extensive toolset
Less flexibility in handling complex data transformations

Code Comparison

Miller example:

mlr --csv cut -f name,age then sort -n age input.csv

xsv example:

xsv select name,age input.csv | xsv sort -N age

Both tools can perform similar operations, but Miller offers more versatility in data manipulation and supports multiple input/output formats. xsv, on the other hand, excels in performance and memory efficiency when dealing with large CSV files.

Miller provides a rich set of operations for data transformation, while xsv focuses on core CSV processing tasks. The choice between the two depends on the specific requirements of your data processing tasks, the size of your datasets, and the complexity of the operations you need to perform.

tsv-utils

1,442

eBay's TSV Utilities: Command line tools for large, tabular data files. Filtering, statistics, sampling, joins and more.

Pros of tsv-utils

Written in D, offering high performance for large datasets
Focused specifically on TSV processing, with optimized tools for common operations
Includes unique utilities like tsv-append and tsv-sample

Cons of tsv-utils

Limited to TSV format, less flexible for other data formats
Fewer data manipulation capabilities compared to Miller's extensive features
Requires D compiler for installation from source

Code comparison

Miller:

mlr --csv cut -f name,age then sort -n age input.csv

tsv-utils:

tsv-select -f name,age input.tsv | tsv-sort -k2,2n

Key differences

Miller supports multiple input/output formats (CSV, TSV, JSON, etc.), while tsv-utils focuses solely on TSV
Miller offers a more extensive set of data manipulation operations
tsv-utils provides better performance for large TSV datasets due to its D implementation
Miller has a more intuitive command syntax for complex operations
tsv-utils includes some unique utilities not found in Miller, tailored for TSV processing

Both tools are valuable for data processing, with Miller offering more flexibility and tsv-utils providing optimized performance for TSV-specific tasks. The choice between them depends on the specific requirements of the data processing task at hand.

csvtk

1,048

A cross-platform, efficient and practical CSV/TSV toolkit in Golang

Pros of csvtk

Written in Go, offering potential performance benefits for certain operations
Provides specialized CSV-specific functionality like splitting and merging
Supports multi-threaded processing for improved speed on large datasets

Cons of csvtk

More limited in scope, focusing primarily on CSV operations
Less extensive documentation and community support compared to Miller
Fewer data manipulation and transformation capabilities

Code Comparison

Miller example:

mlr --csv cut -f name,age then sort -f age input.csv

csvtk example:

csvtk cut -f name,age input.csv | csvtk sort -k age

Both tools offer similar basic functionality for CSV processing, but Miller provides a more comprehensive set of operations and supports multiple input/output formats beyond CSV. csvtk is more specialized for CSV-specific tasks and may offer performance advantages in certain scenarios due to its Go implementation.

Miller excels in data transformation and analysis tasks, supporting a wider range of operations and a more expressive command syntax. It also has better documentation and a larger user community.

csvtk, while more limited in scope, offers some unique CSV-specific features and may be faster for certain operations, especially when dealing with large CSV files. Its multi-threaded processing capability can be advantageous for performance-critical tasks.

structured-text-tools

7,018

A list of command-line tools for manipulating structured text data

Pros of structured-text-tools

Comprehensive list of various text processing tools, offering a wider range of options
Includes tools for multiple data formats (CSV, JSON, XML, etc.), not limited to a single format
Serves as a curated resource for discovering and comparing different tools

Cons of structured-text-tools

Not a standalone tool, requires users to install and learn multiple tools
Less consistent user experience across different tools
May require more setup time and effort to use multiple tools effectively

Code comparison

Miller:

mlr --csv cut -f name,age input.csv

structured-text-tools (using jq for JSON processing):

jq '.[] | {name, age}' input.json

Note: The code comparison is not directly equivalent, as structured-text-tools is a collection of tools rather than a single tool. The example shows how one might use a tool from the list (jq) for a similar task.

Summary

Miller is a single, powerful tool for processing structured text data, while structured-text-tools is a curated list of various tools for different text processing tasks. Miller offers a more consistent experience and easier setup, while structured-text-tools provides a broader range of options and supports more data formats. The choice between them depends on the specific needs of the user and the variety of tasks they need to perform.

jq

31,623

Command-line JSON processor

Pros of jq

Specialized for JSON processing with a rich query language
Lightweight and fast for JSON-specific operations
Widely adopted and well-documented

Cons of jq

Limited to JSON format, less versatile for other data types
Steeper learning curve for complex operations
Less suitable for large-scale data processing tasks

Code Comparison

jq example:

echo '{"name": "John", "age": 30}' | jq '.name'

Miller example:

echo 'name,age\nJohn,30' | mlr --csv cut -f name

Summary

jq excels in JSON processing with a powerful query language, while Miller offers broader data format support and simpler syntax for general data manipulation. jq is ideal for JSON-specific tasks, whereas Miller is more versatile for various data formats and larger datasets. The choice between them depends on the specific data processing needs and the primary data formats being handled.

Convert designs to code with AI

Introducing Visual Copilot: A new AI model to turn Figma designs to high quality code using your components.

Try Visual Copilot

README

What is Miller?

Miller is like awk, sed, cut, join, and sort for data formats such as CSV, TSV, JSON, JSON Lines, and positionally-indexed.

What can Miller do for me?

With Miller, you get to use named fields without needing to count positional indices, using familiar formats such as CSV, TSV, JSON, JSON Lines, and positionally-indexed. Then, on the fly, you can add new fields which are functions of existing fields, drop fields, sort, aggregate statistically, pretty-print, and more.

cover-art

Miller operates on key-value-pair data while the familiar Unix tools operate on integer-indexed fields: if the natural data structure for the latter is the array, then Miller's natural data structure is the insertion-ordered hash map.
Miller handles a variety of data formats, including but not limited to the familiar CSV, TSV, and JSON/JSON Lines. (Miller can handle positionally-indexed data too!)

In the above image you can see how Miller embraces the common themes of key-value-pair data in a variety of data formats.

Getting started

Installing

There's a good chance you can get Miller pre-built for your system:

OS	Installation command
Linux	`yum install miller` `apt-get install miller`
Mac	`brew install miller` `port install miller`
Windows	`choco install miller` `winget install Miller.Miller`

See also README-versions.md for a full list of package versions. Note that long-term-support (LtS) releases will likely be on older versions.

Community

Discussion forum: https://github.com/johnkerl/miller/discussions
Feature requests / bug reports: https://github.com/johnkerl/miller/issues
How to contribute: https://miller.readthedocs.io/en/latest/contributing/

Build status

Building from source

First:
- cd /where/you/want/to/put/the/source
- git clone https://github.com/johnkerl/miller
- cd miller
With make:
- To build: make. This takes just a few seconds and produces the Miller executable, which is ./mlr (or .\mlr.exe on Windows).
- To run tests: make check.
- To install: make install. This installs the executable /usr/local/bin/mlr and manual page /usr/local/share/man/man1/mlr.1 (so you can do man mlr).
- You can do ./configure --prefix=/some/install/path before make install if you want to install somewhere other than /usr/local.
Without make:
- To build: go build github.com/johnkerl/miller/v6/cmd/mlr.
- To run tests: go test github.com/johnkerl/miller/v6/pkg/... and mlr regtest.
- To install: go install github.com/johnkerl/miller/v6/cmd/mlr will install to GOPATH/bin/mlr.
See also the doc page on building from source.
For more developer information please see README-dev.md.

For developers

License

License: BSD2

Features

Miller is multi-purpose: it's useful for data cleaning, data reduction, statistical reporting, devops, system administration, log-file processing, format conversion, and database-query post-processing.
You can use Miller to snarf and munge log-file data, including selecting out relevant substreams, then produce CSV format and load that into all-in-memory/data-frame utilities for further statistical and/or graphical processing.
Miller complements data-analysis tools such as R, pandas, etc.: you can use Miller to clean and prepare your data. While you can do basic statistics entirely in Miller, its streaming-data feature and single-pass algorithms enable you to reduce very large data sets.
Miller complements SQL databases: you can slice, dice, and reformat data on the client side on its way into or out of a database. You can also reap some of the benefits of databases for quick, setup-free one-off tasks when you just need to query some data in disk files in a hurry.
Miller also goes beyond the classic Unix tools by stepping fully into our modern, no-SQL world: its essential record-heterogeneity property allows Miller to operate on data where records with different schema (field names) are interleaved.
Miller is streaming: most operations need only a single record in memory at a time, rather than ingesting all input before producing any output. For those operations which require deeper retention (sort, tac, stats1), Miller retains only as much data as needed. This means that whenever functionally possible, you can operate on files which are larger than your system’s available RAM, and you can use Miller in tail -f contexts.
Miller is pipe-friendly and interoperates with the Unix toolkit.
Miller's I/O formats include tabular pretty-printing, positionally indexed (Unix-toolkit style), CSV, TSV, JSON, JSON Lines, and others.
Miller does conversion between formats.
Miller's processing is format-aware: e.g. CSV sort and tac keep header lines first.
Miller has high-throughput performance on par with the Unix toolkit.
Miller is written in portable, modern Go, with zero runtime dependencies. You can download or compile a single binary, scp it to a faraway machine, and expect it to work.

What people are saying about Miller

Today I discovered Millerâit's like jq but for CSV: https://t.co/pn5Ni241KM

Also, "Miller complements data-analysis tools such as R, pandas, etc.: you can use Miller to clean and prepare your data." @GreatBlueC @nfmcclure
— Adrien Trouillaud (@adrienjt) September 24, 2020

Underappreciated swiss-army command-line chainsaw.

"Miller is like awk, sed, cut, join, and sort for [...] CSV, TSV, and [...] JSON." https://t.co/TrQqSUK3KK
— Dirk Eddelbuettel (@eddelbuettel) February 28, 2017

Miller looks like a great command line tool for working with CSV data. Sed, awk, cut, join all rolled into one: http://t.co/9BBb6VCZ6Y
— Mike Loukides (@mikeloukides) August 16, 2015

Miller is like sed, awk, cut, join, and sort for name-indexed data such as CSV: http://t.co/1zPbfg6B2W - handy tool!
— Ilya Grigorik (@igrigorik) August 22, 2015

Btw, I think Miller is the best CLI tool to deal with CSV. I used to use this when I need to preprocess too big CSVs to load into R (now we have vroom, so such cases might be rare, though...)https://t.co/kUjrSSGJoT
— Hiroaki Yutani (@yutannihilat_en) April 21, 2020

Miller: a *format-aware* data munging tool By @__jo_ker__ to overcome limitations with *line-aware* workshorses like awk, sed et al https://t.co/LCyPkhYvt9

The project website is a fantastic example of good software documentation!!
— Donny Daniel (@dnnydnl) September 9, 2018

Holy holly data swiss army knife batman! How did no one suggest Miller https://t.co/JGQpmRAZLv for solving database cleaning / ETL issues to me before

Congrats to @__jo_ker__ for amazingly intuitive tool for critical data management tasks!#DataScienceandLaw #ComputationalLaw
— James Miller (@japanlawprof) June 12, 2018

ð¤¯@__jo_ker__'s Miller easily reads, transforms, + writes all sorts of tabular data. It's standalone, fast, and built for streaming data (operating on one line at a time, so you can work on files larger than memory).

And the docs are dream. I've been reading them all morning! https://t.co/Be2pGPZK6t
— Benjamin Wolfe (he/him) (@BenjaminWolfe) September 9, 2021

Contributors â¨

Thanks to all the fine people who help make Miller better (emoji key):

_{Andrea Borruso} ð¤ ð¨	_{Shaun Jackman} ð¤	_{Fred Trotter} ð¤ ð¨	_komosa ð¤	_{jungle-boogie} ð¤	_{Thomas Klausner} ð	_{Stephen Kitt} ð¦
_{Leah Neukirchen} ð¤	_{Luigi Baldoni} ð¦	_{Hiroaki Yutani} ð¤	_{Daniel M. Drucker} ð¤	_{Nikos Alexandris} ð¤	_kundeng ð¦	_{Victor Sergienko} ð¦
_{Adrian Ho} ð¨	_zachp ð¦	_{David Selassie} ð¤	_{Joel Parker Henderson} ð¤	_{Michel Ace} ð¤	_{Matus Goljer} ð¤	_{Richard Patel} ð¦
_{Jakub Podlaha} ð¨	_{Miodrag MiliÄ} ð¦	_{Derek Mahar} ð¤	_spmundi ð¤	_{Peter KÃ¶rner} ð¡ï¸	_rubyFeedback ð¤	_rbolsius ð¦
_awildturtok ð¤	_agguser ð¤	_jganong ð¤	_{Fulvio Scapin} ð¤	_{Jordan Torbiak} ð¤	_{Andreas Weber} ð¤	_vapniks ð¦
_Zombo ð¦	_{Brian Fulton-Howard} ð¦	_ChCyrill ð¤	_{Jauder Ho} ð»	_{PaweÅ Sacawa} ð	_schragge ð	_Jordi ð ð¤