We have enough data today that we it may not be realistic to understand all of them. In hopes of vaguely understanding these data, I have been developing methods for exploring the contents of large collections of weakly structured spreadsheets. We can get some feel for the contents of these collections by assembling metadata about many spreadsheets and run otherwise typical analyses on the data-about-data; this gives us some understanding patterns in data publishing and a crude understanding of the contents. I have also developed spreadsheet-speci c search tools that try to nd related spreadsheets based on similarities in implicit schema. By running crude statistics across many disparate datasets, we can learn a lot about unweildy collections of poorly structured data.
My initial curiosity stemmed from the release of thousands of spreadsheets in government open data initiatives. I wanted to know what they had released so that I may nd interesting things in it.
More practically, I often am looking for data from multiple sources that I can connect in relation to a particular topic. For example, in a project I had data about cash ows through the United States treasury and wanted to join them to data about the daily interest rates for United States bonds. In situations like this, I usually need to know the name of the dataset or to ask around until I nd the name. I wanted a faster and more systematic approach to this.
Before we discuss my spreadsheet exploration methods, let's discuss some more ordinary methods that I see in common use today.
As a baseline, one approach is to look manually at every cell in many spreadsheets. This takes a long time, but it is feasible in some situations.
Many groups develop domain-speci c metaformats for
expressing a very speci c sort of data. For example, JSON API
is a metaformat for expressing the response of a database
query on the web [
Agreement on format and metaformat makes it faster and easier to inspect individual les. On the other hand, it does not alleviate the need to acquire lots of di erent les and to at least glance at them. We spend less time manually inspecting each dataset, but we must still manually inspect lots of dataset.
The same sort of thing happens when data publishers provide graphs of each individual dataset. When we provide some graphs of a dataset rather than simply the standard data le, we are trying to make it easier for people to understand that particular dataset, rather than trying to focus them on a particular subset of datasets.
Data may be easier to nd if we catalog our data well and
adhere to certain data quality standards. With this
reasoning, many "open data" guidelines provide direction as to how
a person or organization with lots of datasets might allow
other people to use them [
At a basic level, these guidelines suggest that data should be available on the internet and under a free license; at the other end of the spectrum, guidelines suggest that data be in standard formats accompanied with particular metadata. Datasets can be a joy to work with when these data quality guidelines are followed, but this requires much upfront work by the publishers of the data.
In practice, I nd that people learn what's in a spreadsheet through word of mouth, even if the data are already published on the internet in standard formats with good metadata.
Amanda Hickman teaches journalism and keeps a list of data
sources for her students [
There entire conferences about the contents of newly
released datasets, such as the annual meeting of the
Association of Public Data Users [
The Open Knowledge Foundation [
In order to explore methods for examining thousands of spreadsheets, I needed to nd spreadsheets that I could explore.
Many governments and other large organizations publish spreadsheets on data catalog websites. Data catalogs make it kind of easy to get a bunch of spreadsheets all together. The basic approach is this.
1. Download a list of all of the dataset identi ers that are present in the data catalog. 2. Download the metadata document about each dataset.
This allows me to get all of the data from most of the open data catalogs I know about.
After I've downloaded spreadsheets and their metadata, I
often assemble them into a spreadsheet about spreadsheets
[
My rst approach was involved running rather crude analyses on this interesting dataset-about-datasets that I had assembled.
I started out by simply counting how many datasets each catalog website had.
The smaller sites had just a few spreadsheets, and the larger sites had thousands.
Many organizations report this count of datasets that they publish, and this number turns out to be nearly useless. As illustration of this, let's consider a speci c group of spreadsheets. Here are the titles of a few spreadsheets in New York City's open data catalog.
Math Test Results 2006-2012 - Citywide - Gender Math Test Results 2006-2012 - Citywide - Ethnicity English Language Arts (ELA) Test Results 2006-2012 - Citywide - SWD English Language Arts (ELA) Test Results 2006-2012 - Citywide - ELL
English Language Arts (ELA) Test Results 2006-2012 - Citywide - Gender Math Test Results 2006-2012 - Citywide - All Students English Language Arts (ELA) Test Results 2006-2012 - Citywide - Ethnicity These spreadsheets all had the same column names; they were "grade", "year", "demographic", "number tested", "mean scale score", "num level 1", "pct level 1", "num level 2", "pct level 2", "num level 3", "pct level 3", "num level 4", "pct level 4", "num level 3 and 4", and "pct level 3 and 4". These "datasets" can all be thought of as subsets of the same single dataset of test scores.
If I just take di erent subsets of a single spreadsheet (and optionally pivot/reshape the subsets), I can easily expand one spreadsheet into over 9000. This is why the dataset count gure is near useless.
I can also look at how big they are. It turns out that most of them are pretty small.
Having gotten some feel for the contents of these various
data catalogs, I started running some less arbitrary
statistics. As discussed in section 2.3, many groups have written
guidelines as to how data should be published [
File format of datasets can tell us quite a lot about the data.
I looked at the MIME types of the full data les for each
dataset on catalogs running Socrata software and compared
them between data catalogs [
If datasets are represented as tables inside the Socrata software, they are available in many formats. If they are uploaded in formats not recognized by Socrata, they are only available in their original format.
I looked at a few data catalogs for which many datasets
were presented in their original format. In some cases, the
le formats can point out when a group of related les is
added at once. For example, the results indicate that the
city of San Francisco in 2012 added a bunch of shape le
format datasets to its open data catalog from another San
Francicso government website. As another example, most of
the datasets in the catalog of the state of Missouri are tra c
surveys, saved as PDF les [
Many of the data publishing guidelines indicate that datasets
should be freely licensed. All of the data catalog websites
that I looked at include a metadata eld for the license of the
dataset, and I looked at the contents of that eld. I found
that most datasets had no license [
One common guideline is that data be available on the internet. If a dataset shows up in one of these catalogs, you might think that it is on the internet. It turns out that the links to these datasets often do not work.
I tried downloading the full data le for each dataset refenced
in any of these catalogs and recorded any errors I received
[
Many link URLs were in fact local le paths or links within an intranet.
Many link "URLs" were badly formed or were not URLs at all.
Some servers did not have SSL con gured properly.
While assessing the adherence to various data publishing guidelines, I kept noticing that it's very hard to nd spreadsheets that are relevant to a particular analysis unless you already know that the spreadsheet exists.
Major search engines focus on HTML format web pages, and spreadsheet les are often not indexed at all. The various data catalog software programs discussed in section 3 include a search feature, but this feature only works within the particular website. For example, I have to go to the Dutch government's data catalog website in order to search for Dutch data.
To summarize my thoughts about the common means of searching through spreadsheets, I see two main issues. The rst issue is that the search is localized to datasets that are published or otherwise managed by a particular entity; it's hard to search for spreadsheets without rst identifying a speci c publisher or repository. The second issue is that the search method is quite naive; these websites are usually running crude keyword searches.
Having articulated these di culties in searching for spreadsheets, I started trying to address them.
When I'm looking for spreadsheets, the publishing
organization is unlikely to be my main concern. For example, if I'm
interested in data about the composition of di erent
pesticides, but I don't really care whether the data were collected
by this city government or by that country government.
To address this issue, I made a a disgustingly simple site
that forwards your search query to 100 other websites and
returns the results to you in a single page [
The other issue is that our search algorithms don't take advantage of all of the structure that is encoded in a spreadsheet. I started to address this issue by pulling schemarelated features out of the spreadsheets (section 4.2).
Taking this further, I've been thinking about what it would mean to have a search engine for spreadsheets.
When we search for ordinary written documents, we send
words into a search engine and get pages of words back.
What if we could search for spreadsheets by sending
spreadsheets into a search engine and getting spreadsheets back?
The order of the results would be determined by various
specialized statistics; just as we use PageRank to nd relevant
hypertext documents, we can develop other statistics that
help us nd relevant spreadsheets.
6.3.1 Schema-based searches
I think a lot about rows and columns. When we de ne tables
in relational databases, we can say reasonably well what each
column means, based on names and types, and what a row
means, based on unique indices. In spreadsheets, we still
have column names, but we don't get everything else.
The unique indices tell us quite a lot; they give us an idea
about the observational unit of the table and what other
tables we can nicely join or union with that table.
Commasearch [
In the indexing phase, spreadsheets are examined do nd all combinations of columns that act as unique indices, that is, all combinations of elds whose values are not duplicated within the spreadsheet. In the search phase, comma search nds all combinations of columns in the input spreadsheet and then looks for spreadsheets that are uniquely indexed by these columns. The results are ordered by how much overlap there is between the values of the two spreadsheets. To say this more colloquially, comma search looks for manyto-one join relationships between disparate datasets.
I've been downloading lots of spreadsheets and doing crude, silly things with them. I started out by looking at very simple things like how big they are. I also tried to quantify other people's ideas of how good datasets are, like whether they are freely licensed. In doing this, I have noticed that it's pretty hard to search for spreadsheets; I've been developing approaches for rough detection of implicit schemas and for relating spreadsheets based on these schemas.
A couple of people can share a few spreadsheets without any special means, but it gets hard when there are more than a couple people sharing more than a few spreadsheets. Statistics about adherence to data publishing guidelines can be helpful to those who are tasked with cataloging and maintaining a diverse array of datasets. Data quality statistics can provide a quick and timely summary of the issues with di erent datasets and allow for a more targeted approach in the maintenance of a data catalog.
New strategies for searching spreadsheets can help us nd data that are relevant to a topic within the context of analysis.