This assumes TSV input, but there are plenty of reasons to prefer that to CSV. If I'm working from CSV sources I usually convert to TSV first thing in my shell pipeline.
Re:Show HN: Command Line Tool to Sort CSV and TSV Files by M...
by sigil on Saturday January 06, 2018 @01:50PM
(#16086791)
For a dataset larger than physical memory, using a memory filesystem like tmpfs for the merge stage will either swap (|tmpfs| < |ram|) or deadlock (|tmpfs| >= |ram|).
Instead, your best bet in that case is to give sort as much physical memory as you can spare:
sort -S 95% -k1 huge.tsv
Extra disk I/O is inevitable since your dataset doesn't fit in memory. At least during a merge sort your disk reads will be O(N) and sequentially ordered.
Note: in the special case that your dataset is slightly larger than physical memory, splitting it up in advance such that one of the `sort -m` input files lives on a tmpfs should indeed be faster.
Other things to check out if you need Very Fast Large Sorts:
- Use `sort --parallel=N` to use multiple cores. By default it only uses 1.
- Use `sort --batch-size=NMERGE` to increase the number of files merged at once. Otherwise you may be doing more mergesort stages than are necessary.
Re:Show HN: Command Line Tool to Sort CSV and TSV Files by M...
by sigil on Friday January 05, 2018 @03:13PM
(#16081269)
I know the annoyance you're talking about, but I think you're better off wrapping sort(1) with something translates from column names to indexes. Among the reasons: sortcsv buffers all input into memory [1], while sort(1) uses a divide-and-conquer merge sort to avoid this.
Re:Show HN: Command Line Tool to Sort CSV and TSV Files by M...
by feelin_googley on Friday January 05, 2018 @01:49PM
(#16080629)
Can anyone provide sample input and output for the example?
I find it difficult to evaluate text processing software quickly against existing solutions when there is no example given, such as: here is some sample input and here is the desired output, as is done at, e.g., unix.com.
Re:Show HN: Command Line Tool to Sort CSV and TSV Files by M...
by danso on Friday January 05, 2018 @05:36PM
(#16082381)
xsv is fantastic. I'm a longtime user and fan of csvkit but I've slowly switched some of my habitual usage to xsv. Note that csvkit -- not being a single program like xsv but rather a collection of utilities -- contains a few branches of functionality that xsv doesn't aim to replicate, namely csvsql (convert CSV into SQL create and insert statements) and in2csv (convert XLS and JSON to CSV).
Re:Show HN: Command Line Tool to Sort CSV and TSV Files by M...
by johnweldon on Friday January 05, 2018 @02:56PM
(#16081145)
I wrote this tool because I don't want to explicitly know the original encoding, I just want _any_ encoding to be converted to UTF8. AFAIK, iconv requires the source encoding to be specified on the command line.
http://csvkit.readthedocs.io/en/1.0.2/scripts/csvsort.html
Some of my favorites tools it includes are csvsql and csvlook.
To overcome the slowdown of disk I/O, perhaps a workaround could be to use mfs or tmpfs, maybe something like:
Personally, I gave up on sort for large files and use k/kdb+. I suspect it is faster for sorting than sort or the Go libraries, but I could be wrong.Instead, your best bet in that case is to give sort as much physical memory as you can spare:
Extra disk I/O is inevitable since your dataset doesn't fit in memory. At least during a merge sort your disk reads will be O(N) and sequentially ordered.Note: in the special case that your dataset is slightly larger than physical memory, splitting it up in advance such that one of the `sort -m` input files lives on a tmpfs should indeed be faster.
Other things to check out if you need Very Fast Large Sorts:
- Use `sort --parallel=N` to use multiple cores. By default it only uses 1.
- Use `sort --batch-size=NMERGE` to increase the number of files merged at once. Otherwise you may be doing more mergesort stages than are necessary.
[1] https://github.com/johnweldon/sortcsv/blob/55818bd8e5f9feecc...
[1] https://github.com/johnweldon/sortcsv/blob/master/main.go#L1...
> Miller is like awk, sed, cut, join, and sort for name-indexed data such as CSV, TSV, and tabular JSON.
https://github.com/johnkerl/miller#readme
Use to8 to convert from UTF(32|16)(LE)? etc. to UTF8 first, then sort with this tool.
I've used iconv for years and it's never let me down.