Keeping track of updates to open data published as CSV

Question

I looked through several related questions here (e.g. Tools for merging similar datasets continuously) but nothing seemed to quite answer my question. So, apologies in advance if I missed something.

I'm working on cleaning, normalizing, and linking some open data that consists of data dumps released as CSV files. New versions are released twice a month.

I want to include the new versions (which have additional data as well as possibly corrections of old data) without "losing my place" as it were with the normalizing I've already done. What's the best way to manage regular, periodic updates of CSV data?

Out of the box "diff" tools don't offer much for CSV. I don't mind doing some scripting (python) to solve the problem but I'm not sure of the best approach for tracking, reviewing, and incorporating updates to data received in this form.

Answer 1

You might want to consider maintaining your cleanups as a set of operations or diffs which get applied to the source data. This would help isolate you from changes to the source and allow you to reapply them to a new dataset.

OpenRefine maintains a history of operations, but you could do something similar with a set of version controlled scripts in your favorite scripting language. You also might be able to use a set of patch files produced by something like DiffKit.

Answer 2

Git will work reasonably well for CSVs, given that they are just flat files with text.

A slightly more robust workflow might look something like this:

1. For each row in in the CSV (call it SOURCE_DATA), hash the entire line and store the hash value in a separate table (call it HASHES)
2. Do your data processing on the raw input data, and store the output in a separate table (call it PROCESSED_DATA) and add a column for the hash value
3. Every time your source data is updated, hash each line in the new SOURCE_DATA, check if it exists in HASHES, and if it does then just move to the next line (this prevents you from reprocessing data you've already done); otherwise do your processing and add it to PROCESSED_DATA

This workflow will work well for data sets where:

• Exact-duplicate rows don't matter
• Updates may occur on previously-released rows

Hope that is helpful!

Answer 3

If your CSV has newlines inside 'cells' then this will screw-up command-line diff. You'd be better off parsing it in python to either remove them, or to feed into the python diff.

Hashing each line will tell you track a row that stays the same. It is no good for keeping track of a row that changes a bit. Maybe you don't need to do this. Row IDs have been commented on a couple of times, so it would be useful if you could tell us if this is a problem or not.

Git is an excellent system for keeping track of your 'place', and what you decided when previously 'reviewing' changes. I would recommend not trying to implement your own branching/merging system!

However, the king of data merging is to make everything Linked Data. You are doing the cleaning and converting to a common format anyway, so why not use well-known identifiers for things and their properties? Merging becomes a cinch.

And like any good open data wrangler you'll want to republish your resulting data. Doing it with these well-known standards saves you having to describe any custom format you thought up, and others will be able to use your data without all the parsing and interpretation that you had to go through.

Answer 4

CSV is a very simple format, I would suggest to keep track of the changes via a control version system.

I wrote a short tutorial on how to publish open data using github that tou may find interesting

https://github.com/alangrafu/github-open-data-portal/wiki/How-to-publish-Open-Data-using-github

Answer 5

By far, the best thing I've seen on this complicated topic is Adrian Holovaty's series Sane Updates are Harder than you Think. Unfortunately, its really too much to summarize here.

Answer 6

Git or version control works well for managing the file updates themselves.

In order to reduce false-positive changes detected by git, you can sort the lines. In order to reduce processing of duplicates, you can assign hashes as mentioned by Dave Guarino.

What we do in praxis on datasets that do not have an unique and immutable identifier is to assign columns into three categories: identifier, tracked and untracked.

identifier are columns that, if any of these is identical to another dataset in the other version will treat these datasets as the same. Depending on your use case, you may decide that all of the identifiers have to be the same, but if for example a road can either change name or geometry, either should be enough to identify.

tracked are columns that will trigger a change if modified.

untracked are columns that are not relevant, or expected to change in every dataset (such as the last modified column, for example.)

Answer 7

Consider using the PostgreSQL open source database system. CSV files can be copied and stored as tables. See http://www.postgresql.org/docs/9.3/static/sql-copy.html for details.

Or opened directly for read access using FDW's (Foreign Data Wrappers). FDW's make the CSV file look like a database table. See http://www.postgresql.org/docs/9.3/static/file-fdw.html for details.

If the CSV data is initially copied into the database as a table, it can be compared to an updated CSV file by creating table view of that CSV file.

The two tables can be compared for similarity, or what been added, deleted or updated with simple SQL queries. The results can be seen in Excel using an ODBC driver connection.

Databases are multi-user and can scale to store 1000's of files and 100 G bytes in size. The system is completely programmable, many tasks can be automated using scripts.