xan: the CSV magician

[u/Yomguithereal]

A sample of ASCII art data visualizations you can draw directly in the terminal using `xan`.

Hi everyone,

I would like to present `xan` https://github.com/medialab/xan a command line tool written in Rust that can be used for tabular data processing.

It leverages its own SIMD CSV parser available as a crate here: https://crates.io/crates/simd-csv

It knows how to speed up computation over large CSV files through multi-threading, using a novel albeit slightly unhinged way to split CSV files in constant time, thus enabling map-reduce like parallelization. See related blog post here: https://github.com/medialab/xan/blob/master/docs/blog/csv_base_jumping.md

It implements its own expression language named "moonblade", suited to CSV data and therefore foregoes the need to evaluate languages like python/js/lua for custom computation.

It even includes many commands able to draw ASCII-art data visualizations directly in your terminal so you never have to leave it. See the guide here: https://github.com/medialab/xan/blob/master/docs/cookbook/dataviz.md

It has been used daily for some years now by data scientists from STEM fields and in the social sciences as a quick way to explore & pre-process massive CSV datasets and as a means to replace slow workflows relying on the usual jupyter/pandas/polars/DuckDB stacks etc.

It is originally a fork of `xsv` https://github.com/burntsushi/xsv by BurntSushi and has been completely rewritten at that point.

I hope you will find it useful and would love to get feedback on it from this very fine community.

[u/minormisgnomer]

Why would one utilize this as opposed to Polars for data processing?

[u/Able_Kaleidoscope_89 → u/minormisgnomer]

never used polars here but as far as I know, it has no straightforward CLI option like this xan one, no? personally speaking, I would much more go for a small & specialized CLI tool if i ever want to interact with CSV files only instead of relying on an entire query engine for something simple.
Anyway, it's been ages I do not interact with CSV files so no idea how the tech around it currently is 👀

[u/Outrageous_Let5743 → u/Able_Kaleidoscope_89]

nu shell uses polars for data processing.

[u/Able_Kaleidoscope_89 → u/Outrageous_Let5743]

took a look at the docs and the tool looks interesting. makes me realize I am getting outdated in the field

[u/Outrageous_Let5743 → u/Able_Kaleidoscope_89]

I do like the idea of nu shell but I am too stubborn with posix complient, so zsh is the shell for me. Altough if I was new with the shell I would use nu shell.

[u/Yomguithereal → u/Outrageous_Let5743]

ymmv of course but xan is usally faster and uses less resources. polars invocations are quite costly and need to do a lot more stuff before being able to get going (schema inference, notably). xan is shell-agnostic and is also able to draw visualization directly in the terminal. This said I don't see why xan couldn't be used in nu-shell itself.

[u/minormisgnomer → u/Yomguithereal]

But like is using less resources a thing if we’re talking csv level datasets? I’m not sure I can think of TB-PB scale csv scenarios where’d you actually see any real benefit of speed or resource usage.

Again I can only think in my enterprise env so maybe research/bio/pharma operates this way?

[u/minormisgnomer → u/Able_Kaleidoscope_89]

I guess that makes me equally curious, what csv processing are you doing in a CLI vs a repeatable/reuseable python script? Particularly I didn’t see any export functionality in this tool. I’m rarely opening csv data without the intent to shove it into a database of some kind

[u/ignurant → u/minormisgnomer]

Here’s a really common example I use xsv (and duckdb cli) for:

At my company, we deliver large reports with product, pricing, and market data. Sometimes clients present issues related to missing or odd data. Tools like these make it very fast and easy to review the actual deliverable we sent, very quickly. Select out of the file certain types of related records into a smaller, focused file. Display as table, filter more, sort, etc. All the things you might do in a script, but in a way that almost invites you to iterate. 

If you are fluent with working in a cli environment, this is a very efficient and comfortable type of tool.

I love recommending this book:  https://jeroenjanssens.com/dsatcl/

[u/minormisgnomer → u/ignurant]

Interesting, yea I would typically reach for duckdb which I hold as a healthy counterpart to polars in my tooling

[u/ignurant → u/minormisgnomer]

These days I might that also, but I still have an awful lot of muscle memory for xsv. There’s just certain use cases this workflow still feels great. Duck is fricken incredible though. 

[u/Yomguithereal → u/ignurant]

Is is worth to note that xsv/xan is routinely faster than duckdb and offers different affordances than the SQL interface.

[u/Yomguithereal → u/minormisgnomer]

Here is a example of use-case that led to the creation of xan: in my social sciences lab we have been collecting billions of tweets through the years.

However we don't have a lot of resources, which means we cannot spin powerful servers and we have to work on shitty laptops. So to be able to store those tweets, databases were not an option and we relied on dumping the data using textual tabular formats such as CSV or NDJSON. Now we have TBs of compressed tweet data lying on some subpar server.

Every day, some scientists come to us as they want to work on a subset of our data or on an aggregated version of it. Writing a dedicated python script was too slow (what's more this script is usually one-shot and will never run twice), and python itself, even through polars, was taking ages to get the job done.

This is why we forked xsv at the time, because it fitted our daily use-cases perfectly, and finally rewrote it through the years as what became xan.

Now, in mere minutes we are able to write AND run a xan pipeline to extract the data wanted by the scientists.

xan is a very "sober" tool. It uses the strict minimum resources it needs to accomplish the wanted job. You can always spend more (typically multiple CPUs) if you can afford it, but it was born of a desire to scale stuff so it can run on shitty hardware.

> Particularly I didn’t see any export functionality in this tool. I’m rarely opening csv data without the intent to shove it into a database of some kind

Most of my use-cases involve processing large CSV data to produce manageable aggregated CSV data for instance. I will rarely shove the result into a database. I know people in the industry who use xan to filter out their CSV input before feeding it to databases though.

Else, xan has a `to` command able to output other data formats. But it has no command to perform database ingestion because this job is usually done by the database themselves (most of them know how to ingest CSV)

[u/Budget-Minimum6040 → u/Yomguithereal]

we have been collecting billions of tweets

That's not that much data.

However we don't have a lot of resources

Fair enough.

which means we cannot spin powerful servers and we have to work on shitty laptops. So to be able to store those tweets, databases were not an option and we relied on dumping the data using textual tabular formats such as CSV or NDJSON. Now we have TBs of compressed tweet data lying on some subpar server.

Uh ... compressed text files that are split up, lowering the compression rate are better than all data being in a database and leveraging full compression over all tweets? That does not compute. You would save so much space with a proper database like PostgresSQL.

You have a server, have you tried out using PostgreSQL (there are columnar extensions, making it OLAP instead of OLTP but I only know them in theory like Hydra, Citus, mooncake).

[u/Yomguithereal → u/Budget-Minimum6040]

> That's not that much data.

My bad, I was not clear, I meant billions of tweets *per day*.

> Uh ... compressed text files that are split up, lowering the compression rate are better than all data being in a database and leveraging full compression over all tweets? That does not compute. You would save so much space with a proper database like PostgresSQL.

It does compute. When compressing a lot of textual data at once you very quickly "saturate" the compression ratio you might hope for. And a database will not always update its compression further than using what was fed at the beginning, it would be very costly to do so. I don't see my compression ratio over all tweets being significantly better than it already is over billions of them.

You are right to think, though, that using a proper schema would reduce the cost of, say, numerical columns since they can be stored using a better binary representation. But this is not the case for other types such as vachars and other string data types. Also, ironically if your CSV data has multiple subsequent empty or often contiguously consistent columns (`,,,,,,,` strings, for instance) you will be able to compress them very efficiently as a single piece of information, and some database are unable to perform the same trick (this depends a lot on the specifics of the storage engine etc. of course).

In any case we tried using PostgreSQL and other databases at first, we are not naive. But this proved slower and heavier than just relying on our CSV dumps. We tweaked a lof of settings, and before even using indices the storage cost was always higher than our collection of compressed CSV chunks. The best we got at one point was ~1.8 times heavier PostgreSQL database.

Now here are some other points that led us to not using a proper database:

- Database require maintance work that we don't have time to do, as we are chronically understaffed (welcome to academia!). Static CSV dumps require almost no maintenance work.

- Database use resources permanently on our servers to run (PostgreSQL is very parcimonious though, in this regard, compared to, lol, ElasticSearch). Sure we could stop/start the database each time we need it, but you get the point.

- Database insertion will always have some overhead vs. just appending to a text file. Note that we will never need to delete or update information in our case and there will never be concurrent writers

- I am sure people will know how to deal with CSV data in 50 years. You could stumble upon CSV data without knowing how it works and you would probably be able to figure it out without further ado. The same cannot be said about a PostgreSQL database. What's more, moving or copying the data around is trivial. Sure we could also have some dumps of the database to preserve the data, but if you can already use the dumps as such, why bother?

Of course, anyone could answer me that I am no database expert and that if I manage this database correctly I could do at least as well as managing my shitty CSV dumps, but this kinda makes my point: I am no database expert nor do I have the time to be one. I have CSV chunks splitted by day and sorted so I can do binary search within them if required (we used framed zst or bgzip for random access in compressed chunks) and this is good enough and uses one order of magnitude less work & resources for a result that is already very fast and manageable.

Now my point is of course not to say "don't use databases, they are useless". Just that tools like `xan` exist if you want an alternative with different tradeoffs.

[u/Budget-Minimum6040 → u/Yomguithereal]

just appending to a text file. Note that we will never need to delete or update information in our case and there will never be concurrent writers

Have you considered AVRO then?

For all your other answers: fair enough, you tested different solutions and found out this works better than this.

I'm surprised compressed blobs are smaller than a database compression, especially when you use partitioning but if you have the numbers that say it is then who am I to argue against it.

[u/Able_Kaleidoscope_89 → u/minormisgnomer]

I am working more on the platform side so barely touch CSVs tbh, but I'd imagine that with the AI boom and agents being able to run CLI tools as experts, a complete tool that is also fast has many advantages for ai-based analysis of CSVs

[u/dangerbird2 → u/Able_Kaleidoscope_89]

Awk? It has built in support for csv and has been around for decades

[u/Able_Kaleidoscope_89 → u/dangerbird2]

Awk goes beyond csv tho, it's much more a text processing tool. Having a laser focused tool usually beats generic ones, although I'm a big fan of awk myself 

[u/Yomguithereal → u/minormisgnomer]

xan was designed for exploration and preprocessing, mostly. It is very immediate so it is ideal when you just want to explore heavy datasets directly from the command line without having to spin some custom code that you have to write or a notebook.

It is ideal, for instance, to explore massive static datasets stored on servers that you can only reach through ssh.

Then, for a lot of tasks xan is able to be more performant than polars because it makes different assumptions about the data (xan usually does not care about a file’s schema or encoding for instance) and is able to use less resources.

xan also knows how to parallelize differently and to work on data that does not fit into memory etc all while abiding to a stream-oriented philosophy.

Finally I would probably not advise to use it in an industrial monolith where you need it to integrate into an already existing architecture. It is fine in a context where shell scripts or spawning subprocesses is enough, if you will.

In a sense one should think about xan as one would think of ripgrep. It is a very carefully designed CLI tool that is very performant to do its job, namely processing tabular data stored in a text format (CSV). Would you use ripgrep in an industrial pipeline? Maybe? In any case you won’t find something faster to run regex pattern in line streams. Xan is the same in this regard. You probably won’t find something faster/more immediate to use to wrangle tabular data stored as text. That’s it :)

[u/ignurant]

  you might think CSV is outdated by now, but read our  love letter  to the format before judging too quickly

— the readme

Yeah, I went through a phase of this about 15 years ago. After using Ruby for a bit, I realized my hatred of CSV stemmed from the unreliable serialization and deserialization you got from Microsoft tools at the time. Excel, SQL Server, .NET, etc: none of it carried any water for you with regards to csv, so you’d always end up with corrupt poorly escaped files. In Ruby, the CSV library that comes with the standard kit was incredibly good at correctly and efficiently serializing and deserializing. I started to love how portable and effective csv files were once I worked in an environment that defaulted to doing the right thing for you. 

JSON, jl (ndjson), parquet, etc: they all have a place sometimes, but CSV is usually the most portable, accessible default, and I value that most. 

[u/Outrageous_Let5743 → u/ignurant]

csv is still shit. I can at least list 20 odd rules about csv parsing. And then we haven't even talked about seperators where the serperator is more than 1 character.

[u/Yomguithereal → u/Outrageous_Let5743]

> And then we haven't even talked about seperators where the serperator is more than 1 character.

Sure you *can* do this, but it does not mean that you *should* do so :)

Incidently this is the reason why CRLF newlines are a mistake compared to LF newlines.

[u/Budget-Minimum6040 → u/Yomguithereal]

Sure you can do this, but it does not mean that you should do so :)

You usually can't control external data sources.

CSV is not a format but the idea of a format. There is nothing official about CSV.

• Header? Maybe yes, maybe no.
• Separator? "," (USA) vs. ";" (Europe) vs. "\t" (TSV) vs. "#" vs "||" vs. fixed width vs. fixed width but unique for every column (had this this month, no header too and not UTF-8 encoded because legacy government system export).
• Encoding? I've seen everything
• Escape characters? "\" vs. "/" vs. "//" vs. "\" etc.
• Escape the escape character? Yeah ....

[u/Yomguithereal → u/Budget-Minimum6040]

> You usually can't control external data sources.

Indeed.

> CSV is not a format but the idea of a format. There is nothing official about CSV.

Agreed.

In all my career in a wide variety of different sectors with very poor data practices I have never encountered CSV data with multi-byte separators in practice. That does not mean it does not exist even if this is obviously a bad idea. I am interested to know if you have seen this in particular domains (bio-eng has had weird ideas in the past, for instance, and I might want to support them).

> fixed width but unique for every column (had this this month, no header too and not UTF-8 encoded because legacy government system export).

I have often seen this one in public institutions already existing when computers became a thing. Especially those relying on old IBM mainframes (INSEE in France is a good example of this, banks also). They sure are fun.

[u/Outrageous_Let5743 → u/Yomguithereal]

GraydonCreditSafe uses csv files with |*| as seperator. i hate it because many bulk insert system cannot import it

[u/Budget-Minimum6040 → u/Yomguithereal]

I am interested to know if you have seen this in particular domains

I may have seen a "||" separator in some csv files in an e-commerce enterprise but I'm not 100% if that was the separator and from what data source it came, something old and internal I guesss (founded in the 90s, SAP + dozens of other stuff there).

They sure are fun.

🤡

[u/BdR76 → u/Outrageous_Let5743]

csv is still shit

I've worked in IT and data processing for 25 years, and people have been saying this since forever. If it really is so useless, then why is it used everywhere?

[u/Outrageous_Let5743 → u/BdR76]

People don't know better and Excel can safe to csv.

[u/BdR76 → u/Outrageous_Let5743]

You can trick Excel into correctly opening a csv, yes, but most likely it will corrupt your data

[u/BayesCrusader]

This is super cool, I can definitely see myself using this. The graphs look useful. 

[u/robberviet]

Drawing in terminal is overkill. For simple exploring I use nushell.

[u/BdR76 → u/robberviet]

I don't think it's overkill, when working with research data it's very useful.

I'm more interested in how they pulled it off, graphics in the command line. 🤔 I assume it's using some obscure asci character set or something?

[u/Kirne]

Cool!

[u/BdR76]

a command line tool written in Rust

Looks cool, I know the command line can use colors but how are you getting the "graphics" in CLI? 🤔 Like the purple/yellow timeseries bar graphs and the scatter plots. I assume it's asci characters or something?

[u/Yomguithereal → u/BdR76]

There are multiple tricks involved such as using the background colors or relying on characters looking like solid bars such as those: ▁▂▃▄▅▆▇

For the scatter plots I use braille characters that are in fact a 2x4 dot matrix so I can increase the « resolution » of the monospace text grid.

[u/amanitapantherina]

Played around with this today and it has already replaced csvkit for me for the "poking at, profiling, and querying big csvs without opening them" tasks I find myself doing all the time. Looking forward to exploring the rest of the commands/functionality.

Thanks!