Data Processing Resources: Command-line Interface (CLI) for CSV, TSV, JSON, and XML

Sometimes you don’t want pandas, tidyverse, Excel, or PostgreSQL. You know they are very powerful and flexible, you know if you’re already using them daily you can utilize them. But sometimes you just want to be left alone with your CVS, TSV, JSON and XML files, process them quickly on the command line, and get done with it. And you want something a little more specialized than awk , cut, and sed.

This list is by no means complete and authoritative. I compiled this as a reference that I can come back later. If you have other suggestions that are according to the spirit of this article, feel free to share them by writing a comment at the end. Without further ado, here’s my list:

• xsv: A fast CSV command line toolkit written by the author of ripgrep. It’s useful for indexing, slicing, analyzing, splitting and joining CSV files.
• q: run SQL directly on CSV or TSV files.
• csvkit: a suite of command-line tools for converting to and working with CSV, the king of tabular file formats.
• textql: execute SQL against structured text like CSV or TSV.
• miller: like awk, sed, cut, join, and sort for name-indexed data such as CSV, TSV, and tabular JSON. You get to work with your data using named fields, without needing to count positional column indices.
• agate: a Python data analysis library that is optimized for humans instead of machines. It is an alternative to numpy and pandas that solves real-world problems with readable code.

Honorable mentions:

• SQLite: import a CSV File Into an SQLite Table, and use plain SQL to query it.
• csv-mode for Emacs: sort, align, transpose, and manage rows and fields of CSV files.
• lnav: the Log Navigator. See the tutorial in Linux Magazine.
• jq: this one is THE tool for processing JSON on the command-line. It’s like sed for JSON data – you can use it to slice and filter and map and transform structured data with the same ease that sed, awk, grep and friends let you play with text.
• JMESPath tutorial: a query language for JSON. You can extract and transform elements from a JSON document. There are a lot of implementations at http://jmespath.org/libraries.html and the CLI implementation is jp.

Finally the CLI for XML:

• XMLStarlet Command Line XML Toolkit: a set of command line utilities that can be used to transform, query, validate, and edit XML documents and files using simple set of shell commands in similar way it is done for plain text files using UNIX grep, sed, awk, diff, patch, join, etc commands. (I used this most recently in https://github.com/emres/turkeyEarthquakeAlerter.)

Advertisements

 

How to start your calendar analytics using Microsoft Graph API

As a Data Officer who started to work in a big manufacturing company a few months ago, I attended a lot of meetings in many locations. After a while, wearing my analytical hat, I asked myself: how can I easily see

• how many meetings I’ve attended
• how many people I’ve met
• how many locations I’ve been to

Based on the fact my company had already switched to Microsoft Office 365, I came across a very nice unified system: Microsoft Graph API. According to the description in its official overview, :

You can use the Microsoft Graph API to interact with the data of millions of users in the Microsoft cloud. Use Microsoft Graph to build apps for organizations and consumers that connect to a wealth of resources, relationships, and intelligence, all through a single endpoint https://graph.microsoft.com. Microsoft Graph is made up of resources connected by relationships. For example, a user can be connected to a group through a memberOf relationship, and to another user through a manager relationship. Your app can traverse these relationships to access these connected resources and perform actions on them through the API. You can also get valuable insights and intelligence about the data from Microsoft Graph. For example, you can get the popular files trending around a particular user, or get the most relevant people around a user.

 

A visit to the largest computer museum in the world: The Heinz Nixdorf MuseumsForum

It all started more than seven years ago, when I read a short article in January, 2010 issue of Communications of the ACM, titled “Great Computing Museums of the World (Part One)“.

“The Heinz Nixdorf MuseumsForum (HNF; www.hnf.de) in Paderborn, Germany, is the world’s largest computer museum. The museum, which is also an established conference center, showcases the history of information technology—beginning with cuneiform writing and going right through to the latest developments in robotics, artificial intelligence, and ubiquitous computing.

The multimedia journey through time takes visitors through 5,000 years of history, starting with the origins of numbers and writing in Mesopotamia in 3000 B.C. and covering the entire cultural history of writing, calculating, and communications. Alongside typewriters and calculating machines, the exhibition shows punched card systems, a fully functioning automatic telephone exchange system from the 1950s, components from the earliest computer (which filled a whole room), over 700 pocket calculators, and the first PCs. Work environments from different centuries are also staged in the exhibition.

The exhibition highlights include fully functioning replicas of the Leibniz calculating machine and the Hollerith tabulating machine, a Thomas Arithmometer dating from 1850, a Jacquard loom operated with punched tape, components of the ENIAC from 1945, the on-board computer from the Gemini space capsule, the Apple 1, a LEGO Turing machine, and Europe’s largest collection of cipher machines. One of the current attractions at HNF is the world’s most famous automaton: Wolfgang von Kempelen’s chess playing machine, the Chess Turk, which dates from the 18th century.”

I was more than impressed, and wanted to visit Paderborn to see the world’s largest computer museum. I knew it was just a few hours away by car from Antwerp, but I’ve always postponed going there for various reasons. I didn’t want there to go alone, and I knew I needed someone like-minded enough to accompany me on this “nerdy” journey. Finally, last week, I and a physicist / data scientist friend of mine decided to go there, notwithstanding the weather conditions, and very snowy German highways.

I think this is the only museum where digital relics from my childhood and youth (1980s and 1990s) are considered as museum-worthy as replicas of 5000 year old Sumerian tablets! 🙂 It was pure joy and fascination to visit the halls of the museum, and be guided by very thematic and knowledgeable, gentle robots. One of them, Victoria, was a sight to be seen! The other one was also great, and you can watch “him” in action:

 

Qualia: a unique musical experience from Gökhan Goralı

Imagine that you grew up in a very constrained and controlled laboratory environment. Imagine that your expert science professors taught you everything that can be known about mathematics and physics of sound waves that travel by air pressure, or through other material; you know all about frequencies, their relationships, properties of various materials, and can easily derive new theorems from what you already know. Now, I know it will be very difficult, but try to imagine that you also never heard any music, any musical instrument at all. Imagine all you heard was human language conversation (no singing or humming at all), and natural sounds such as a knock on the door, or some truck engine making a noise, but nothing else. No music.

And now, imagine that for the first time in your life, you hear sounds coming from “Qualia” by Gökhan Goralı. Surely there will be some radical changes in your brain, in your neural circuitry. But will you have gained “new knowledge”? Didn’t you already know all about the math and physics of sound, the relationship between frequencies, how some of them are called ‘musical notes’? The musical album at hand is a digital one, so it would be straightforward to describe all mathematical and physical aspects of bits in those MP3 files, frequencies and their durations, etc. When you hear music for the first time, when you year “Qualia” for the first time, what is that you learn for the first time? What does it mean to say that the extra experience you have is ‘qualia‘?

What is that elusive state of mind? Do two people who listen to the same “Qualia” have the same state of neural configuration? Is it comparable, or is ‘qualia’ simply what is beyond comparison? There are no easy answers, there are only difficult questions.

In other words, there is only one long journey, the biggest experiment of all, going for millions of years. And this brand new album is an experiment within the grand experiment. With its recursive loops, Pure Data patches, soundscapes, guitars, bass lines and drums… with the dedication of countless hours by spirits in pursuit of the unknown, Qualia leads to many states of mind. Originating from one of the most ancient cities in the world, it might remind you of a historical future in which you might start to remember things you never knew. It deserves listening and re-listening, going deeper in every iteration, and maybe coming up with a different experience each time.

It is difficult to do justice to “Qualia” in a few primitive sentences, after all, the title itself hints at the phenomenon of going beyond a reductionist approach. If you believe there’s something special to being a human, then you owe yourself to listen to this great album.

Another nice thing, external to this album, is the fact that whatever you pay for it will go to the World Bicycle Relief & Eşpedal, so that this great artistic experience will change not only your state of mind, but also the life of a person, and in a very positive way.

If you are curious about the background to this album, you can read an interview here in Turkish (or an automatic English translation), and maybe watch Ex Machina. Do you think a robot, an Artificial General Intelligence, would experience this album the same way a human would do? Think about this as you listen to the ‘hard’ and ‘soft’ tunes of “Qualia”.

 

 

 

HOW-TO: Poor Man’s Quiz Scorer in Emacs Lisp

Sometimes it’s about small data.

Recently I’ve been studying a topic using a book, and at the end of each chapter there are quizzes of 20-25 questions. My method was to open a text buffer in Emacs, and vertically note down the question number, my answers, and after finishing this, go to the correct answer list, and mark correct answers with Y, and wrong ones with N:

Y 1- A,B,C
Y 2- C
N 3- D
N 4- C
Y 5- A
Y 6- B,C,D
Y 7- B

That was all fine, but I found myself counting the number of correct answers, and calculate my score in terms of percentage manually. I could of course quickly run the `M-x count-matches` (or `how-many`) to see how many correct / wrong answers I had, but doing this more than a few tests seemed to become tedious. Therefore, Emacs Lisp to the rescue!

With this simple Emacs Lisp function, assigned to F12 function key, I can now simply hit F12 and see my score percentage in Emacs:

“Your test score percentage: 71.42857142857143”

There are of course alternative methods to solve this straightforward problem, e.g. you can run some shell scripts on your Emacs (or VIM) buffer, or on a simple text file. But I like this solution being self-contained in Emacs, as well as the fact that it’ll continue to run as intended 30 years from now, in a newer Emacs version probably. This, and the fact that, my preferred tool for dealing with textual data gives me the flexibility to program it any way I want. It might have its drawbacks, it might be showing its age in its architecture, there are newer, shiny tools, there are specialized IDEs for various programming languages I use, but all of these notwithstanding, I still like to think it’s a beautiful thing that an editor released when I was born, that helped me with many tasks for decades, will still be with me for decades to come.

 

One year with “Haskell Programming from First Principles”

My relationship with the Haskell programming language, my efforts to learn it had its ups and downs throughout the years. According to my memory and the archives of my blog, my first attempts had been around 2005 – 2006, more than 12 years ago. Back then, apart from a few books written by university professors, and some Wiki-based books, I couldn’t find much high quality material for beginners. Therefore, my efforts didn’t last very long. A few years later, I heard the news about a new book, “Real World Haskell” being written. I was excited once again, I even made a few comments here and there as the book was being written. Unfortunately, life happened, and I couldn’t spend much time on that nice book, too. Fast forward to the end of 2015, and I was working at a company in Ghent, Belgium where there were some Haskell experts, trying out things in an industrial storage system development environment. The teams that I was part of had nothing to do with Haskell though, my daily job was almost always about Python, Bash, ActionScript, Java, and some Scala. Nevertheless, being in such an environment rekindled my curiosity, and I decided to look around to see if there was some new Haskell books targeted at people who didn’t use this language before. Luckily, I’ve heard about the book “Haskell Programming from First Principles“, and I decided to give it a try. Therefore I bought the book, and started to read and study it in the beginning of 2016. Since Haskell was not at all used in my daily job, I could study the book only in my spare time, therefore it took me about 1 year to finish the book, doing most of the exercises.

 

For the Love of Books: Turkey versus Belgium

The 80. edition of Antwerp Book Fair (Boekenbeurs) has finished recently. Shortly after that, another book fair, 35. Istanbul Book Fair took place. I was curious to compare the number of visitors, and especially the percentage of book fair visitors by taking the population of the city into account.

Istanbul is home to about 15 million people. Of these 15 million people, 558 thousand visited the fair in 9 days. On the other hand, the population of Antwerp is about 500 thousand people, and out of that, 170 thousand people visited the fair in 10 days on average in 2010 to 2012. In terms of percentage, the picture looks like the following:

In other words, 3.7% of Istanbul visited their book fair, whereas 34% of Antwerp population visited their book fair. If 34% of Istanbul’s population visited the book fair, that would make ~ 5.1 million people in 9 days.

This picture tells us something about literacy and interest in books, but of course we should always ask the question: how representative such a crude statistic is? Another interesting fact is that Istanbul makes up about 20% of Turkey’s population, whereas Antwerp makes up about 4.5% of Belgium’s population. (If we want to compare Turkey to a country with similar population and its most populated city: Germany is close to Turkey with its 80 million people, and its most populated city, Berlin, is home to 3.4 million people. In other words, Germany’s most populated city makes up only 4.25% of the Germany’s population.)