Bayanathi Technology: Playing with published data

The easiest way to try to make some sense out of data published in reports may be to make graphs and/or sort the data to see if there is some recognizable pattern. It is normal to see almost all statistical reports included graphs to try to communicate and highlight their notable results. Arranging the data in a particular theme in ascending or descending order is less common but some extensive statistical reports like a census report produce tables of data with some variables of interest sorted by provinces or some other administrative regions with the region with highest score at the top and that with lowest score at the bottom.

Graphing from data and sorting data from data in some electronic format could both be done quite well with Excel or some other spreadsheet. But only if you know how to get them into your spreadsheet first. The easiest way to do it would be when you could download the data in what is called a data file with “csv” extension. A Comma-Separted-Value (csv) data file can be opened with a spreadsheet directly and then saved as an Excel file or other spreadsheet file if you wish. Now smarter people will use what is known as an “api” or application programming interface provided by the data provider to get data from the Internet.

For me playing with published data should be as natural as children playing with sand on the beach. Here in Myanmar it used to be that not all data of public interest collected from people were published, or if published they were not always publicly available. In one instance, I was even told by a government official that I couldn't cite data from their published report. It was fifteen years ago, though, and I was officially collecting some indicators for the ASEAN Secretariat. If knowledge is power, it certainly wasn't people's power, I thought. Hopefully everything has changed now as exemplified by the offer of DHS microdata to researchers when the results came out recently.

For generating data of public interest, Demographic and Health Survey (DHS) usually supported by USAID and Multiple Indicators Cluster Survey (MICS) supported by UNICEF are the two most popular surveys in the developing countries. We Myanmars have seen a series of MICS being carried out in our country but the DHS Myanmar 2015-16 is the first DHS for us and its report is available here.

The good news is that you could register and request the microdata for this DHS apart from macro data and information available freely through the reports and the hosted database.

Quickly going over the DHS report, I noticed what seemed to be a somewhat strange shape of the population pyramid. I visually compared it with the population pyramid of the Myanmar Population Census 2014 report.

It seems like the age range 15-29 in DHS is pinched appreciably. So I tried putting the two pyramids on the same graph as shown below.

Why this difference? We know that a large number from the working age population has gone out to work in neighboring countries and elsewhere, and therefore it seems like the reduced proportion of 15-34 age range in males and 15-29 in females may be explained by this fact. However, we note that the populations graphed by both the DHS and Census were enumerated on the same defacto basis. So it seems like the population 15-34 has been genuinely under represented in the DHS. However, I am not smart enough to understand if that would have significant impact on the the survey results or not. Surely real statisticians could make it understandable to the ordinary folks.

Anyway, my real intention is to share a few dumb techniques I picked up to extract data from postscript or pdf files to csv files and make them into graphs after reading them into a spreadsheet. The particular type of graph we are going to make is a population pyramid which many of the users of Excel or other spreadsheets may not be familiar and find it hard to do.

My target audience is the fellow dummies in Myanmar. More widely the general public. Here in Myanmar most still use pirated software, I guess. But, pardon me, I have been using freeware or opensource software for quite a long time. And I strongly encourage others to do so. Why should we be enslaved? Still?

First we'll extract data from a document in pdf format. Normally we open such document with Adobe Reader which comes free. If you want to extract a picture of a data table in that document to put it in your report, you could easily use the “Take a snapshot” tool. But there is no way you could cut a table and then paste it somewhere using the Adobe Reader. To do so I used the Tabula software available for free here. To manipulate and graph the extracted csv data you need a spreadsheet and I recommend Open Office Calc spreadsheet which is a component of the Open Office software available here. Open Office is a dependable alternative to the Microsoft Office software and it is completely free.

The materials used will come from the DHS report available here and the Myanmar Population Census report available here.

I created the graph of population pyramid from DHS report superimposed on the population pyramid from the Population Census report that is shown above with the following steps:

Step-1: Run the Tabula software by double clicking its shortcut.

I assume that you have installed the Tabula software and know how to create the shortcut on the desktop of your PC. Now Tabula will be opened and waits for you to specify the pdf files to import. Note that I've specified the names of the two above files and clicked the Import button in the following screenshot.

Now you click the green button to extract data. Here you scroll down to page-52 in the DHS report where the Table 2.7 for population pyramid data is and select the data to be extracted (area shown in pink).

Then click the green button and you get the data preview.

Is the data OK? No! The first column puts “Age” and “Male” together. Well, read the hints given in the text on the left-most side of the Tabula screen and try to adjust the selection so that you get the preview right.

Then saved the data as csv by clicking the Export button.

Step-2: Manipulate data in spreadsheet.

Now I opened the resulting csv file with Open Office Calc:

Similarly I exported the Table A-6a giving the population by five-year age groups and sex from the Census report to csv format and opened with Open Office Calc. Collecting the two data sets in a spreadsheet and after the extraction of columns and some arithmetic, I get the following table that has all the information needed to create my population pyramid:

Step-3: Graph it.

The above table shows percentages of males and females of different age groups in their own groups. Notice that males are shown as negative percentages. This is because we need to use a bar chart to draw a population pyramid with males on the left of the X-axis.

Another trick is to align the bars for males(left) and females(right) in the same age group perfectly level. You do this by overlapping bars by 100%.