Using the Indian Government’s Open datasets for analysis in Tableau: Analysing government school data between 2012–15

As a student of data science and machine learning in my masters course, I was always fascinated by how people could create amazingly colourful visualizations using Tableau.

It was only over the past few months, as I started teaching my students storytelling with data, that I started to explore the tool myself in some depth.

And while using the features of the tool is one thing, I realized that the quality of free publicly available datasets does not allow us to get the kind of results we see in the e-learnings or youtube videos that find.

In addition, the value of information is when it is new, and this deteriorates very quickly so our analysis today may not be relevant in a few days unless we preserve the context and use it to teach the method.

And that is precisely what I aim to do today with data from the Indian government’s data portal using historical data. I understand that the business value of this analysis will be zilch because this data is nearly 8 years old, having said that the data is complete so it will be a good way to explain how we can use publicly available government data to find some insights.

With more updated stats on the same fields, I believe people can replicate the process and use the results to make more valuable and actionable recommendations.

For today’s post I have taken the data from the link below.

Open Government Data (OGD) Platform India

This site shows the infrastructure of Indian schools between 2012–2015, and I will attempt to show how we can present a story based on this.

This dataset shows the gross enrollment ratio and dropout ratio by gender and student level across different states as well as stats on several other basic amenities in the schools aggregated at a state level.

Since this gives several CSV files with just 2 common fields, the state and the year, I created several joins in tableau to combine the files using the 2 common fields.

Getting the data into tableau using joins

As a next step, I did some basic cleaning on the state field as well as converting the remaining values to measure, so I ignore those values where things are not reported or null.

Data after cleaning

And now for the visualizations.

One of the lessons I learnt the hard way early on is that we need to consciously look for a difference or a mismatch in any data so we can then ask questions and explain it. I remember a session I attended recently, and the faculty taught us that data gets very boring without any variation or difference to investigate, so it becomes very important to find some big variation or difference in pattern, and then start our story by attempting to explain it.

So on searching for this, I found this variation in the states having computerization in their schools. By looking at the percentage of computerization across states, we see a clear difference where some states are did well and some did not. In addition, by using the year in the “Pages” card, I am able to visualize how this improved year on year by seeing the colour change.

Now this is where the surprise is. The data shows that the states of Gujarat and Maharashtra are doing a lot better than Karnataka in terms of computerization in schools in 2015, despite our belief to the contrary with Bangalore being the IT hub and all.

Similarly, another visualization that prompts us to ask questions and take action is given below, when I compared the gross enrollment rates vs dropouts in primary schools across genders by state.

(For this I used calculated fields for to compare dropouts and GER of primary school boys and girls).

Dropout vs GER in primary school students aggregated at state level and coloured to show gender disparity

This set of graphs highlights the gender disparity and clearly shows that, by and large, the states in the north and eastern part of the country have a low female GER in primary school, while the western and central states have a high female dropout rate.

Now this is very starkly in contrast with the computerization graph we have above, so it shows we can have both extremes coexisting at the same time, and this is also something that I recently read in a textbook on the multiple competing truths in any story.

Now we could weave the whole story into a storyboard as shown below.

This is basically how we can annotate and tell our story through a storyboard

So I hope you enjoyed reading this, and I hope to post more about visualization if I can find some interesting data to look at and use.

Till I post again, keep reading!!