Hash bars – simple ASCII-art charts in your console, database or Excel

When you have some data like this:

MONTH	COUNT
2011-10	417
2011-09	903
2011-08	1051
2011-07	759
2011-06	835
2011-05	647
2011-04	393

it may be difficult to spot a trend in it. That’s why people use charts and other visualization tools and there’s a lot of them (you could use Excel, Google Charts, gnuplot, sparklines etc.).

However, sometimes it’s not possible or convenient to use any of this tools. In such cases, you can easily create a simple ASCII-art style chart. Doesn’t this look better?

MONTH	COUNT
2011-10	417	########
2011-09	903	##################
2011-08	1051	#####################
2011-07	759	###############
2011-06	835	################
2011-05	647	############
2011-04	393	#######

It’s actually so embarrassingly obvious I would not bother posting about but in the last couple of days several people told me that they think it’s a great idea that they wish they knew earlier. So here it is.

SQL

Simple visualization of SQL queries results is one of the domains where this technique is quite handy. For example, to get the nice table presented above, I used the following query:

  SELECT TO_CHAR (some_date, 'YYYY-MM') AS MONTH,
         COUNT (1) AS COUNT,
         RPAD ('#', COUNT (1) / 50, '#')
    FROM some_table
GROUP BY TO_CHAR (some_date, 'YYYY-MM')
ORDER BY 1 DESC;

The secret sauce is the RPAD() function (see line #3) that pads its argument to given length using its third parameter as the padding character. To be honest I don’t know if it’s standard in SQL, but quick search revealed it is supported by Oracle, MySQL and PostgreSQL.

Console

The UNIX command line is probably where this is most useful (you could probably create something similar on Windows cmd prompt with right tools installed).

For example, I’ll create an ASCII-art chart of load average on my machine, as reported by uptime command:

$ uptime
21:20  up 2 days, 9 mins, 6 users, load averages: 0.32 0.47 0.48

As you can see, my machine is not under much stress right now but I will find something for it to do :)

Perl

So, let’s fire up some console and type:

$ perl -e 'while(1) {`uptime` =~ /averages: ([\d.]+)/; printf("% 5s %s\n", $1, "#" x ($1 * 10)); sleep 10 }' 
 0.54 #####
 0.85 ########
 1.25 ############
 1.98 ###################
 2.77 ###########################
 3.34 #################################
 3.81 ######################################
 3.45 ##################################
 3.14 ###############################
 3.04 ##############################
 3.03 ##############################
 2.79 ###########################
 2.52 #########################
 2.13 #####################

Isn’t that pretty? If you don’t grok perl, this one-liner calls uptime command every 10 seconds, parses its output to find load average for last minute, then displays this value and corresponding number of #‘es (using perl’s string multiplication operator x). Since the load is quite small, we multiply this number by 10 to get a nice line of #‘es.

Ruby

So you want Ruby? OK, let’s use Ruby to chart the number of words of given length in English dictionary (I know it’s silly, but it makes a pretty graph):

lens = Hash.new(0)
File.open('/usr/share/dict/words').lines.each do |line|
  lens[line.size - 1] += 1
end

puts "LEN COUNT"
lens.keys.sort.each do |k|
  puts "%3d %5d %s\n" % [k, lens[k], "#" * (lens[k] / 600)]
end

You’re lucky, no one-liner this time :) To create the hash bar in ruby, we can just use the String’s * method. The number 600 is somewhat arbitrary, it’s just a constant that makes the chart fit my blog’s layout. Let’s run it!

$ ruby words.rb 
LEN COUNT
  1    52 
  2   155 
  3  1351 ##
  4  5110 ########
  5  9987 ################
  6 17477 #############################
  7 23734 #######################################
  8 29926 #################################################
  9 32380 #####################################################
 10 30867 ###################################################
 11 26010 ###########################################
 12 20460 ##################################
 13 14937 ########################
 14  9763 ################
 15  5924 #########
 16  3377 #####
 17  1813 ###
 18   842 #
 19   428 
 20   198 
 21    82 
 22    41 
 23    17 
 24     5 

Now, isn’t that a pretty bell curve?

You may be wondering (as I was) how is it possible to have 52 one-letter words when the alphabet has only 26 letters? Surely the only such words are “a”, “I”, and possibly “O”? As it happens, /usr/share/dict/words contains all the letters of the alphabet, both in upper- and down-case forms as separate words (at least on my system). That’s why there’s 52 of them.

Bash

With some hacking, we can create similar chart using bash alone. Since bash does not provide any string multiplication function (that I know of), let’s first define a function that will display given number of hashes:

$ hashes () {
> tmp='#';
> while [ ${#tmp} -lt $1 ]; do
>   tmp=$tmp$tmp$tmp;
> done;
> echo ${tmp:0:$1}
> }

This function grows the variable tmp until its length does not exceed the argument $1, then cuts it to correct length and displays.

What can we chart with such a function? Let’s create a chart of number of lines per source file in one of my projects (beware — another one-liner):

$ wc -l *.m | grep -v total | while read n f ; do echo $f; hashes $(( $n / 3 )); done
ChangeHoursController.m
#######################################
Config.m
###########################################
OptionsController.m
############################
SelectDaysController.m
################
WeekProgress.m
#################################################
WeekProgressAppDelegate.m
#############################
WeekProgressViewController.m
######################################

First, we run the wc -l command, which returns the number of lines per file, then get rid of the unwanted total sum, and process it line by line with while read ... construct. Again, I chose the number 3 mainly because it makes the chart fit my blog’s layout.

Excel / Open Office

I know you can create graphic charts in Excel, but it takes quite a lot of effort, especially if you want to have something that is not embarrassingly ugly. Fortunately, there is a REPT() function in Excel (supported also by OpenOffice), which allows creating of nice hash bars.

Hash bars in Excel

As can be seen on the picture, the expression to produce the hash bar is: =REPT("#";B2/100).

This technique can also be used to visualize more than one value on the same chart. For example, the following picture shows a comparison of some fictional costs and sales figures:

Multi-value chart

The lines (composed from _ characters) represent sales while the # stands for the costs. The expression to create this is: =REPT("_";C2/100) & "#" & REPT("_";(B2-C2)/100). The first REPT() call generates first part of line with a # at the end to represent the costs, then another line is appended which represent the difference between sales and costs.

For more ideas of Excel charts, see http://www.juiceanalytics.com/writing/more-on-excel-in-cell-graphing/.