Towards Data Analytics: Open Data and the CKAN Google Sheets add-on
Original story by Ryan Garnett. Republished with permission.
1.0 Purpose
Data. For many people, this simple four letter word is scary. Data is becoming increasingly more important in daily life but the vast majority of people are not comfortable working with it. The fear of mathematics, difficulty understanding statistics, uncertainty on what data can do, or the lack of access are all contributors to a data illiterate society. With data being coined as the “new oil” and data science the “sexiest job of the 21st century”, it’s essential that we address the discomforts many experience when working with data. There are many reasons for this, as stated above, but the combination of access and education are two significant contributing factors.
Learning data isn’t always fun and finding relevant datasets that have meaning for individuals is difficult. Educators, in public schools and post secondary institutions, for example, experience this regularly. To address these insufficiencies, government organizations throughout the world are increasingly making data available via open data initiatives.
In a nutshell open data is data that is made available to the public for use free of charge; allowing residents, students, educators, start-ups, and businesses to access and leverage the data for their needs. So what’s the problem? Typically this data is only available via the organization’s open data portal, meaning that individuals must go to a portal and search for the dataset, download it, and then import it into a software application. This may seem like a straightforward task, but for those with lower data literacy this is daunting and the first barrier to becoming data literate.
Within the education sector, K-12 and post secondary, a large number of institutions are moving towards using Google. The suite of tools provide a means for educators and students to work more seamlessly together, with one lacking area: data access. There are many ideas on data access, with “go where people play” being a growing trend. The premise of go where people play is that people should be able to access data from within the applications that they currently use; rather than needing to go to an external source for data access. With this I would like to introduce the CKAN Google Sheets add-on, a go where people play method for accessing open data from within the most widely used education spreadsheet — Google Sheets.
2.0 The CKAN Google Sheets Add-on
The CKAN Google Sheets add-on was developed by Evert Pot. The add-on provides functionality to load and display open data from CKAN enabled data portals. For more information visit the Google web store or Evert Pot’s GitHub page.
2.1 Installing the Google Sheets Open Data Add-on
Installing Google add-ons is pretty straightforward. Add-ons are installed from within Google Sheets from the Add-ons menu. The process walks you step-by-step finishing with an installed add-on. Currently the CKAN add-on has not been verified, which will require an additional step. The add-on has been tested and no safety concerns have been identified. The installation steps are as follows:
- Access G Suite Marketplace (within Google Sheets from Add-ons, Get add-ons)
- Search for add-on
- Select add-on (CKAN sheets add-on)
- Install add-on
- Provide add-on permission
- Choose Google account
- Accept unverified add-on (click Advanced and then Go to CKAN sheets add-on)
- One more thing…allow
2.2 Accessing Open Data
Traditionally accessing open data was performed within an organizations web portal, but the Google Sheets add-on allows you to access open data managed within a CKAN environment from within Google Sheets; a win for educators and data literacy. The add-on allows users to search for open data from within Google Sheets, removing the requirement of searching in different portals and importing different files into Google Sheets. Now users have a seamless simplified customer experience.
Once the add-on is installed it is a few clicks away from being access, simply go to Add-ons, CKAN sheets add-on, Open; a new window will open and dock to the right side of the sheet.
By default three providers are listed. In this story we will access data from the City of Toronto. Don’t see a provider you are interested in? If they have a public CKAN API (application programming interface, a fancy for saying web link), you can add it through the Add new provider button.
With your data provider selected you can scroll through the data sets list and click on the one of interest. Once clicked the description of the data set will be populated. To add the data to the sheet scroll down and click the Insert sheet button, and like magic the data is added to the sheet.
With the data added to the sheet countless forms of analysis, data exploration and data visualization can be performed. The CKAN add-on provides access to a wide and varying amount of data, providing a great entry into data analytics for people of all ages and data literacy. Happy data analysis’ing.
3.0 Next Steps
The City of Toronto’s Open Data team is focusing on improving access to City data, as well as helping with data literacy. An area of interest is working with K-12 educators to develop content that can be used by students of all ages. We plan to create data analytic content that aligns with different K-12 curriculum, specifically using Open Data within Google Sheets.
4.0 Resources
For more information on Open Data, accessing open datasets, and data literacy visit the City of Toronto’s Open Data site and Open Data Portal:
City of Toronto’s Open Data site
