Successful data management is all about finding the proper tools and formats for dealing with your data.  There is no one-size-fits-all solution.  And the very first question you should be asking yourself is:  ”How much data are we talking about?”

It’s all relative

Most people are familiar with data and data management techniques from within their own field of study.  Whether one has a large dataset or not is therefore a relative question.  A dataset is considered large or small relative to some other collection of data.  But the tools for dealing with data — hardware and software — are constantly improving and what may have been considered a ‘large’ amount of data a few years ago may no longer be so big.  Consequently, the appropriate tools for dealing with your dataset may be changing as well.  With RAM selling for under $50/GB, many datasets are starting to look a lot smaller.

We’ll begin our examination of data volumes by putting various datasets and databases on a logarithmic scale just to get a sense of their relative size.  Remember, each tick mark represents a factor of 10 increase in size.  (References are included in the list of links at the end of this article.)

Data volumes range over more than twelve orders of magnitude!  Where does your dataset fit in?

Clearly, datasets in the single megabyte range (gray) are at the insignificant end of the scale.  At about the 100 megabyte scale (green) it becomes important to have a plan for how to manage the data with an eye on potential software limitations.  Still, it should be smooth sailing with with respect to hardware until you start handling hundreds of Gigabytes of data.  At that point, hardware and software limitations will both impact your decision making.

From 100 gigabytes to perhaps 10 terabytes (yellow) you can still buy off-the-shelf components that will store that much data but data managers must proceed with caution.  Above 10 terabytes (orange) requires carefully designed, networked storage devices.  This is the realm where computer scientists need to be part of your team.  Those projects that involve storing above a petabyte of data (red) are at the cutting edge of what we are planning for the next decade.

For a more personal comparison, our recently purchased, plain vanilla iMac came with 4 gigabytes of RAM and one terabyte of disk.  That would allow us to download and play with some of the largest datasets of climate measurements (as opposed to model output).

Megabyte sized (small) datasets

Several of the projects we have worked on involve data collections at the small end of the scale.  Any dataset that involves humans in the collection, data entry, processing or validation of individual data points will always be under 100 megabytes.  This is the area that contains what we like to refer to as “high value datasets” — those datasets that have actual measurements made by humans as opposed to model output generated by computers or streams of data generated by automated sensing devices.

Because these small datasets are not even as big as available RAM on most machines, there is no requirement to store them in any particular compact format or access them with any particular software.  You should always keep in mind that reading from and writing to disk are by far the slowest operations on your computer.  Once data are read into RAM, any filtering and subsetting and processing of data should be lightning fast.

When planning data management, we always subscribe to Einstein’s philosophy of:  “As simple as possible, but not simpler.”  Whatever is simple and easy and flexible and fast is the right choice for working with small datasets.  Too many data management applications get bogged down in the complexity of using yesterday’s sexy computer science tools even though they are completely unjustified by the data volumes.  Often it would be much more cost effective to simply buy some more memory and then use a brute force approach to filtering and subsetting.  Computer memory is much cheaper than the human memory required to keep a complex system working.

For datasets of this size we often recommend storing the data in one or more simple CSV files or an SQLite database if no other solution is preordained.

Gigabyte sized (big) datasets

Once you start working with datasets that are a gigabyte in size and larger you will need to consider very carefully two components:  1) what software tools are used to access and analyze the data; and 2) what data format(s) the data should be stored in.  These are not independent choices as a particular set of tools usually relies upon the data being available in a particular format.

Sometimes you will be required to support formats required by a particular piece of software already used by the community of practice working with this data.  Other times you may be at liberty to make recommendations.  Our philosophy of “as simple as possible” is meant to apply both to the team in charge of managing the data as well as end users of the data.  It is therefore very important to interview those who expect interactive access to data to find out how they intend to use the data:  what tools they use, what kind of subsetting or querying is required, what kind of interactive access is expected, etc.  Hopefully, certain themes will arise from these questions that will help guide your choices.

At this scale, the structure of your data will also have a lot to say about what options are available to you.  If you have regularly gridded data the NetCDF format, widely used in the climate data community, may be appropriate while relational data will need to be stored in a RDBMS like MySQL or PostgreSQL.

Terabyte sized and bigger (huge)

We have never worked with datasets larger than about a terabyte.  In this size class data management of necessity becomes it’s own separate activity with the associated specialists and funding.  Usually, datasets this large consist of lots of raw data which is important to keep but may not be what is needed for higher level analysis.  It is often possible to generate and store partially processed versions of the raw data which reduce the data volume by several orders of magnitude and allow much simpler data management solutions.  An example of this approach applied to genomic data is found in an excellent article from 2008:  How much data is a human genome?  It depends how you store it.

The executive summary describes the approach:

For those who don’t want to read through the tedious details that follow, here’s the take-home message: if you want to store the data in a raw format for later re-analysis, you’re looking at between 2 and 30 terabytes (one terabyte = 1,000 gigabytes). A much more user-friendly format, though, would be as a file containing each and every DNA letter in your genome, which would take up around 1.5 gigabytes (small enough for three genomes to fit on a standard data DVD). Finally, if you have very accurate sequence data and access to a high-quality reference genome you can squeeze your sequence down to around 20 megabytes.

Clearly, intelligent data management for very large datasets involves a lot of decisions about which users the data is being managed for.  One set of users will require access to the raw data but are willing to wait while at the other end of the spectrum you have people who want instant access to the summary information.  As we said at the beginning, there is no one-size-fits-all solution and good data management is about identifying different classes of users and finding the right solutions for each class.

Additional information

The following list of links provides a little more detail on the data mentioned in graphic above and a few interesting posts on working with very large data volumes:

• British Petroleum Statistical Review (BP Stat Review)
• Wadeable Streams Assessment (EPA WSA)
• Historical Statistics for Mineral and Material Commodities in the United States (USGS DS 140)
• How much data is a human genome?  It depends how you store it. (2008)
• International Comprehensive Ocean-Atmosphere Data Set
• Earth System Grid
• Man behind MasterCard’s 100-terabyte data warehouse (2008)
• CERN – LHC Computing
• Sloan Digital Sky Survey
• The Petabyte Problem:  Scrubbing, Curating and Publishing Big Data (2008)
• Petabytes on a budget:  How to build cheap cloud storage (2009)

For those who always watch the credits at the end of movies we have a little more to say along the lines of philosophical underpinnings after we present the questionnaire.  For the impatient, here you go:

Questions about the community involved in creating, using and managing the data

1) Data Providers

• Why were the data collected?
• Who are the data providers?
• How do you get feedback from them?
• What questions do they have about the data?
• What software tools do they currently use to work with data?
• What data formats do they prefer?
• What leverage exists to get their cooperation?
• What resources do they have?
• What internal procedures do they have that impact when and how data are delivered?
• What political needs must be met?
• What would make them happy customers?

2) Data Consumers

• Who are the data consumers?
• How do you get feedback from them?
• What questions do they have about the data?
• What software tools do they currently use to work with data?
• What data formats do they prefer?
• How do they want to interact with the data?
• Are there requirements to interact with other software systems?  (e.g. “web services”)
• What would make them happy customers?

3) Data Management

• Is data management a dedicated activity?
• Who is responsible for data management?
• Do they have a background in the field of study?
• Is this data management project driven by the needs of software or of science?
• What financial resources does the individual/team have?
• What personnel resources do they have?
• What hardware resources do they have?
• How will they communicate with data providers and data consumers?
• Who will provide long term support for data management?

Questions about the nature of the data.

4) Volume

• How many actual numeric measurements (not including textual meta-data) are made in a year? — thousand/million/billion/trillion
• How many are made in a day?

5) ‘Speed’

• What temporal precision is needed for incoming data? — second/minute/hour/day/month/year
• How up-to-date should the ‘released data’ be? — Everything up to the last minute/hour/day/month/year?

6) ‘Shape’

• How are data currently stored? — text file/CSV/XML/GIS/spreadsheet/RDBMS/binary file/other
• Can the data be expressed as a single row-by-column table?
• Are any data geo-spatially located?
• Do any data have regular spacing along an axis with physical units? — latitude/longitude/depth/height/time
• What additional metadata must be stored?

Questions about functionality to be achieved.

7) Validation

• How are data currently being validated?
• How are successfully validated data points being identified?

8) Versioning

• How is raw data being versioned?  (e.g. How are changes to the data store being tracked?)
• Can earlier versions be retrieved?
• How is released data being versioned?

9) Provenance

• How is the history and origin of each data point being tracked as data goes from individual submissions to larger aggregations?

10) Authorization

• Who is allowed to enter data?
• Who is allowed to extract data?
• What should be open to the general public?
• What kind of secure technology is mandated/desired?

11) Analysis

• Are any specific kinds of analysis associated with the data?
• Is it desirable to build a system that helps users perform appropriate analysis?
• What software requirements does this impose upon this data management project?

12) Interactive Access

• What sort of interactive access should be provided to data consumers? — subsetting/querying/reformatting/analysis/visualization

Our philosophy of the practice of data management is that it should always be in the service of both the data providers and data consumers (1 & 2).  One must have a thorough understanding of the original questions that motivated the data collection effort and the additional questions that the data can be used to answer before attempting to come up with data management solutions.  The tools and formats that are currently in use as well as the political landscape in the provider and consumer communities set the fundamental frame for organizing data.

The questions about data management resources and responsibilities (3) are absolutely key.  We believe that data management should always be a dedicated component of any data gathering or data analysis activity.  Data management occupies a fundamental position between the worldview of the data providers and that of the data consumers (see the post on Data Producers vs. Data Consumers).  Any ideas that these two groups are one-and-the-same must be left behind.  In this modern age of open data dissemination the expectation is that any data that are collected will be combined with other data and used by a wide variety of individuals, each with a different set of needs.  It is important, therefore, for data managers to identify target audiences and provide added benefit to these ‘customers’.  The simplest way to gauge the success of a data management project is to ask:  ”Did we make the job of person X easier?”  We also feel very strongly that those involved in any data management project should have some familiarity with the discipline involved.  At a minimum they should be in regular contact with the providers and consumers of data.

Questions 4 – 6 are designed to get people to really think about what kind of structure might be best for their data.  We have plenty of war stories of SQL databases being set up with dozens of interrelated tables and complex schemas for data sets that were no longer being updated.  As it turned out, the data could be written out as a single CSV file of a thousand rows by a hundred columns and this simpler format was actually more useful to the downstream consumers of data.  Others build overly complex systems based on outdated ideas of how much data can be stored on disk or in memory.  The ‘speed’ issue (5) has to do with transactional vs. archival databases and will be the topic of a future post but boils down to how up-to-date data access needs to be.  Are you designing an airline booking system that needs to be aware of transactions completed a few seconds ago or are your data only updated and released once a month?

The last section on functionality has several leading questions to get people thinking about what a data management system could have.  We believe that data should be validated, updated, trackable, etc. and these questions need to be answered before you design your data handling system.  We also believe that it is in the interest of everyone to make vetted data, analyses and visualizations available to as broad an audience as possible.  Publicly available data and analysis allow us to harness the interest and skills of people we may not even know about.  It is important to plan on internet access even if the budget only allows for a web page pointing to publicly available CSV files.  Even that would be a far sight better than no data access at all or keeping the data essentially locked up in a complicated software system.

Best of luck answering your own data management questions!

For those who don’t know, Vivek Kundra is Chief Information Officer of the Obama Administration and is the driving force behind the Data.gov initiative described in a previous post:  Reinventing the Wheel at Data.gov.  In that post we complained that Data.gov was simply another new website providing links to agency data without any discipline specific evaluation of the data that was being served up.  This is a complaint we still level against that initiative but Kundra’s recent talk at the UW gave us a sense of what he is up against with a very limited set of resources.

Other’s have captured ideas from his talk in some detail:

• Notes from Federal CIO Vivek Kundra talk at UW
• Vivek Kundra Outlines Ambitious Government Plans for I.T.

From our perspective, the take home messages were the following:

• Identifying and getting feedback from real ‘customers’, not just project managers, is key to developing useful tools.
• The government needs to pull the plug on IT projects that are going nowhere.
• Making more data open and available to the public with channels for feedback is the only realistic way to improve data quality.

If you want to get a sense of the man and his ideas you can watch a video of his talk.  Our question and his response on the topic of data quality in historical datasets begins at 1:00:03.

We were pleased that his response acknowledged the sorry state of some historical data.  Refreshingly, his solution is not to set up an expensive government program to ‘fix’ the bad data — we’ve been down that road before.  Instead, he believes the only hope is to daylight government data sets and crowdsource the solution.

Now this is a solution that we can get behind!

1. make public versions of data available as soon as possible
2. open a channel for the public, the ultimate ‘customers’, to provide feedback on data quality and usability issues
3. have an individual who’s main job is to improve the quality, completeness and usability of datasets generated by their department

We know that many agencies are used to going their own way with respect to managing their data.  And we believe that the needs of research require special tools and systems.  However, we also believe that there is no excuse for delaying access to public versions of agency data which should be made as early as possible.  And these public versions need not require extensive software engineering — don’t let perfect be the enemy of good.  For many, many government datasets, a simple CSV version of the data would go a long way toward making the data more useful.

(You might be surprised to find out how many complicated SQL schema’s ultimately boil down to a single row vs. column table.  We’ll save that as the topic for a future post.)

Best Hopes for common sense data management.

What’s in a name?  That which we call a rose
By any other name would smell as sweet.

Ahhh love.  Juliet speaks lovely poetry but we learn, as the story unfolds, that names and the identification they impart are in fact extremely important.  This is no less true in data management where country names are anything but standardized.

Anyone care to explain the difference between “England”, “Great Britain” and the “United Kingdom”?  Sadly, these politically distinct entities are often confounded.  If we start with England and then add Scotland and Wales we get “Great Britain”.  If we then add Northern Ireland we end up with the “United Kingdom” or UK.  (But remember, even the UK does not officially include the Isle of Mann or the Channel Islands which are dependencies of the crown.) Country names are inextricably bound to the vagaries of history and politics.

Our first example only looked at the name of one country in the language of that country.  Other countries of course have their own word for the UK: Royaume-Uni (French), Reino Unido (Spanish), Vereinigtes Königreich (German), Verenigd Koninkrijk (Dutch).  Linguists will recognize that these are all local versions of the English phrase “United Kingdom”.

Translating country names is not always this simple.  Each of Germany’s neighbors has a name for Germany that stems from the Germanic tribe that they had to deal with back in the Roman era:  Allemagne (French), Germania (Italy) (but tedesco for a German person), Duitsland (Dutch), Niemcy (Polish).  Whatever agreement these nations might reach in their assessment of Germany’s past, there is no agreement on what to call the Bundesrepublik Deutschland.

Country name inconsistencies

As this is an English language site, we will investigate country name inconsistencies a little further by looking at some problematic names found in a few English language datasets.  These datasets are all high value, well curated datasets that make every attempt to be standardized:

It’s quite an interesting mix.  The only “British” dataset is careful to use the full “Republic of Ireland” and apparently wants to appease China with its use of “China Hong Kong SAR”.  Meanwhile, the Riyadh based Joint Oil Data Initiative is careful about “Libyan Arab Jamahiriya” and the “Islamic Republic of” Iran.  By contrast, the American datasets seem almost colloquial, leaving off all the formal titles and even using “Burma” instead of “Myanmar”.  These names may be in common use in the US but they don’t always match other groups’ standards.

As data managers we always look for standard identifiers to represent well defined concepts.  Political units known as nations are an excellent example of such a well defined concept.  No one disagrees about what is meant whether one types “US”, “USA”, United States” or “United States of America”.  But for anyone working with data, especially those attempting to merge data from different sources it makes a huge difference whether the national identifier is standardized or not.  Writing code to catch all possible variations of English language country names is just the sort of tedious exercise that can be avoided by the proper use of standard identifiers.

ISO 3166-1 alpha-2

Luckily, ISO-3166-1 alpha-2, is a widely adopted standard that will meet our needs.  This standard specifies a unique, 2-character code for each nation.  Once we have the codes, we can create tables of country names in different languages.  Here is another table with the ISO-3166-1 alpha-2 codes and the recommend English and French names available from the ISO site.

One of the truisms of scientific data management is that it is always more robust in the long run to store identifiers in a standardized, computer readable fashion.  When human readable output is needed, code can be written to generate whatever local version of the standard identifier is needed.  This is much preferable to storing human friendly identifiers that prevent data from different sources from being combined.  Or worse, that work in some cases but not others.

It is quite possible to have your cake and eat it, too.  We are not recommending that you create Excel spreadsheets that are devoid of human readable names, requiring your users to know that DZ stands for Algeria.  Instead, just add another column labeled country_code or ISO_3166_1_alpha_2 or similar that contains the appropriate code.  That way you and the users of your data will be able to write software to automatically process the data without worrying about whether a cell has “USA” or “United States”.

If, on the other hand, your identifiers live in a database and will be processed before a human ever sees them then you should only store the identifiers and have the presentation layer of software convert the coded identifiers into human readable strings, preferably in the readers language of choice.

FIPS 10-4

Over the years, of course, other standards have come and gone.  Perhaps the most important competing 2-character code was the FIPS 10-4 standard developed by the US National Institute of Standards and Technology.  This standard was withdrawn by NIST in 2008 in favor of the ISO 3166 standard.

Unfortunately, not every US agency has abandoned their old fashioned ways.  Databases for the Energy Information Administration Country Energy Profiles and the CIA World Factbook still use the old FIPS 10-4 codes so you need to be careful if you are accessing those databases programatically.  (If you aren’t you may be surprised at Switzerland’s huge population and coal production — in ISO 3166 ‘CH’ means Switzerland but in FIPS 10-4 ‘CH’ means China.)

python Babel module

As an added incentive to encourage the use of these country codes we would like to introduce you to the python Babel module.  The Babel module helps with internationalization (aka i18n) and we will use it to create a table of country names in different languages from our standardized codes.  Do not be put off by the geeky nature of the home page — you don’t need to learn much to make use of this excellent module.

Installation is a snap on Ubuntu Linux:

sudo easy_install babel

Translations for our country codes are found in Locale objects in the module, one for each language.  As you might expect, the supported languages are themselves encoded — this time according to ISO 639-2 language codes.  We’ll reserve detailed description of the Babel module for another post.  For now, lets cut to the chase and show the code that will generate HTML rows containing the list of country names in the languages of those countries:

#!/usr/bin/python
# -*- coding: utf-8 -*-

import sys
from babel import Locale

# NOTE:  'my' dropped as the Burmese characters won't display on my Mac
for language in ('ga','ru','ar','fa','zh','en'):
    locale = Locale(language)
    print('<tr>')
    print('<td>' + language + '</td>')

    for country in ('IE','RU','LY','IR','MM','HK','US'):
        print('<td>' + locale.territories[country] + '</td>')

    print('</tr>')

Eleven actual lines of code and here is the result:

Pretty amazing, eh?

So now you know why we always use standardized codes for country names and languages. And you should too!

The importance of storing ‘missing’

One of the most important components of a data management strategy is the proper handling of missing values.

Every data collection effort is essentially an attempt to answer a set of questions of this form:

What was the value of _______ at such-and-such time and place?

A climatologist will store a numeric value in a particular set of units like temperature in degrees C:

What was the high temperature in degrees C in Saint. Louis on Jul 20, 2000?

A census worker will store a string of characters that represent an identifier like a name:

What was the name of the head of household at this address on April 3, 2010?

As in all human endeavors, success is not 100% guaranteed.  So we must be ready to deal with the possibility that our data collection effort comes up short.  Perhaps the thermometer in St. Louis malfunctioned on our date of interest.  In this case the correct answer to the question “What was the temperature?” would be “We don’t know.”  In the past , and disappointingly continuing into the present, some people would enter 0 (the number zero) when a datum was missing.  Anyone examining the data at some later date would be quite surprised at the sudden cold snap that hit St. Louis on July 20, 2000!

And what about our census worker?  If no one is at home should they enter into the NAME field the string:  ”not at home”.  That would be an unusual name.  Perhaps there is a spelling error and they meant “Notatome” which is a valid surname.  Or perhaps they combined typos with a first-last switch and are really identifying “Tome Nota” — a Portugese language Christian youth ministry.  We can’t really know for sure unless we have a rock solid standard for storing the meaning “we don’t know” or “not measured” or “missing”.

Storing missing values in spreadsheets

Spreadsheet software like MicroSoft Excel and OpenOffice Calc is ubiquitous.  Given the prevalence of PC’s in the government and business world, most people have Excel on their desktop and many small datasets are generated in Excel and disseminated as Excel files.  It is important, therefore, to learn how to use the features of Excel to properly encode the meaning “missing value” whenever necessary.

One of the reasons spreadsheet software is so popular is that it allows users to see the data they are working with, giving them a sense of control over the data.  “Seeing is believing” and most spreadsheet users seem to trust their own eyes more than they trust their software tools.  Although we agree that human visual perception is an incredible tool for hunting ripe blackberries, we would argue that it is not evolutionarily suited to validating numbers and text.  One common example of the problem with human validation of numbers and text is our inability to tell the difference between an empty cell and one containing a whitespace character such as Unicode character ‘SPACE’ (U+0020).  According to your eyes, there is no difference between the two.  But in reality, there is.

This may not seem like a problem if you are generating charts inside of Excel and can clean up the data yourself when you run into problems.  Things get trickier, however, when these data are merged with other data and further processed by people who have no specific knowledge of the dataset.  If a column of unvalidated data containing the SPACE character is assumed by downstream software to be numeric, a data type error will typically result.  If, instead, the data column is assumed to contain character strings, no error will be detected and the SPACE character will be used as a valid string.  The burden of checking for cells containing only whitespace characters and then converting these to missing values falls to the programmers working with the merged dataset.  It’s no wonder, then, that merged database projects so often go over budget when the incoming data is poorly validated.

One of the problems in using spreadsheet software to work with data is that the default data validation settings on these tools are too forgiving.  By default, data entered into a cell can be of any type — string, float, decimal, date, etc.  One of the most important things an Excel user can do is to harness the built in validation tools every time new data are added.  MicroSoft provides extensive documention on using and customizing data validation within Excel and users should become familiar with these features.  Users can validate not only the data type but can set up valid ranges that alert users to problematic values when they are entered.

Getting back to our original theme, missing values in Excel are identified by cells that are truly empty.  To understand what this means you have to understand the different cell types used by Excel.  Internally, Excel recognizes the following types of cells:

• EMPTY
• TEXT
• NUMBER
• DATE
• BOOLEAN
• ERROR
• BLANK

Everything makes sense right up through BOOLEAN.  The ERROR type is used when there is an internal Excel error message associated with the value of a cell — presumably due to problems with a formula.  But what about BLANK?  Well, BLANK cells are those that have formatting information but no stored value.  From the data manager’s point of view they should be the same as EMPTY.

Unfortunately, older versions of Excel had a different idea.  A MicroSoft support article for Excel versions prior to 2002 has the following:

In Microsoft Excel, when you use a formula that tests for a zero value, you may see unexpected results if the cell is blank. Microsoft Excel interprets a blank cell as zero, and not as empty or blank. Therefore, any cells that are blank are evaluated as zero in the function.

Ahhh, spreadsheets.  If you have to use them make sure that you have a recent version and that you learn to use the data validation tools available.

Storing missing values in CSV files

We are quite fond of CSV files as a lowest common demoninator format for data exchange.  CSV files can be read by just about any software out there and greatly reduce the the effort involved in reformatting data.  Many datasets are inherently tabular in nature and it is straightforward to export Excel worksheets as CSV files.  An earlier post described how to automatically convert an entire Acess database into a set of CSV files.  The only problem with CSV as a standard is that it isn’t really standardized.  So let us evaluate the options for storing the meaning “missing” in a CSV file.

In the bad old days, which are still with us in some areas, lots of different identifiers were used to store the idea of “missing value”.  Here are a few we have encountered:

• “-”
  The hyphen is a convenient note to make on a paper spreadsheet to identify something like:  “Measurement attempted but no value obtained”.  However, it is not widely understood by analysis software.
• “0″
  Do this under penalty of death!!!
• “999″
  A perennial favorite, this is a valid numeric value and only conveys the meaning “missing” if one is intimately familiar with the valid domain of the data.
• “-9999″, “-999″, “9999″, “999999″, “9999999″
  Yes, we’ve seen them all.  Some sites use six 9′s and seven 9′s as separate missing value flags to identify which technician was responsible for working up the sample associated with each cell.  If you ever expect your data to be used by anyone other than yourself please, please don’t do this.
• “N/A”
  This designator for “Not Available” is quite human readable but is not commonly understood by analysis software.
• “NaN”
  This designator is used in floating point computations and is not really appropriate for data that might be stored in spreadsheets or CSV files.  For full details see the Wikipedia article.
• “”
  The blank string of zero length is an excellent designator of “nothing here”.  The only downside is that it is not explicit and some tools will not automatically populate cells with their own internal designator for “missing value” without some coaching.
• “NA”
  This designator is widely understood as the ASCII representation of “missing value” and is the default in many packages including our personal favorite — the R statistical analysis package.  Even here there is a problem, though, as “NA” is also the ISO 3066-1 identifier for Namibia.  We have a rule of avoiding Namibian conflict whenever possible which brings us to our own preferred designator for “missing value”:
• “na”
  The R package, and presumably others, can be coached to accept this as the default missing value string when reading in CSV files and this designator avoids the problems mentioned above.

The bottom line is that we recommend either “na” or “NA” as the missing value flag for CSV files.  Anything else is inviting trouble and expense for everyone downstream.

Overcorrecting — Storing ‘missing’ when you really mean zero

Have you ever heard anyone say “… for you and I …”?  A lot of people have been taught that they overuse “me” and then overcorrect by using “I” even when the pronoun is the object of a verb or preposition.  Well, the same thing happens with zero vs. missing.

There are times when the correct answer is zero and not “we don’t know.”  Overcorrecting by storing the missing value deisgnator instead of zero can also cause problems for anyone working with the data.

Two examples should get the idea across:

The USGS DataSeries 140 is a fantastic, century-long history of production and use data for a long list of minerals.  The lead-use Excel file from this dataset allows one to track the year-to-year evolution of lead use in different categories, one per column.  The column labeled “Gasoline additives” decreases gradually from a high of 218,000 metric tons in 1976 (ouch!) to 4,000 metric tons in 1994.  The cells for years after 1994 are of type EMPTY.

As we read above, cells of type EMPTY are to be interpreted as “we don’t know”.  So, do the folks at USGS really not know how much lead was used for gasoline additives in say … 2002?  Hardly.  The Notes worksheet in the file states quite clearly:

From 1987 to 1995, data reported on gasoline additives depicts a progressive phaseout. The U.S. Environmental Protection Agency issued a direct final rule that prohibits the use of chemical additives in gasoline for highway vehicles, effective January 1, 1996.

So the amount of lead used for gasoline additives, at least post 1995, is in fact zero.  And that is what should be stored in the spreadsheet, not the Excel designator for “we don’t know”.  Anyone trying to create a pie plot showing percentage usage in different categories is stymied when the amount in one of the categories is “unknown”.  No category value => no total amount => no percentages.  It would be much better for downstream users of the data if zero were stored in these cells.

Even folks who work at the highest levels of government, data-driven science with some of the best software tools available for data handling and analysis are subject to this error.  The International Combined Ocean Atmosphere Data Set (ICOADS) is an extremely high value dataset that contains ocean observations of climate variables like air and sea surface temperatures, wind speed and pressure.  With this dataset one can create maps depicting the ocean surface temperature for any month going all the way back to January 1800.  (Don’t expect to see much data before 1900, though.)

Along with the measured variables this dataset also includes variables containing the number of observations that were used to create the monthly average in a particular grid cell.  The idea here is that you might have more faith in a temperature value if the lat-lon box for a particular month contained many observations as opposed to just one or two.  So each NUMOBS_SST value is really an answer to the question:

How many observations of sea surface temperature were used to generate the SST value stored in the associated space-time grid cell?

The answer should never be “we don’t know”.  But that is in fact what is stored.  The NUMOBS_SST field has lots of missing values and a few “1″s in January of 1854.  To our way of thinking it should contain a lot of “0″s and a few “1″s.

Perhaps we are nitpicking here.  Users of this data do not seem unhappy with the data files as they are.  We are merely using this as an example to highlight the difference between zero and missing in the hopes of fostering better data management in all fields.

Here’s to honest reporting of “we don’t know” and to not letting your zeroes go missing.