19

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.

  • In my experience, it's really, really messy as many data providers don't assign identifiers to each record, then you run into the chance that the fields used to establish identity are updated ... but is it a delete + a new entry, or a correction to the record? Another odd problem I've run into is data that's packaged into weekly files -- but a time change might move a record into a different week, but the way we apply updates into our database might end up with a lost or duplicated record. – Joe May 26 '13 at 21:42
6

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.

4

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!

4

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.

2

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

  • github may be not a fitting solution for datasets - From their ToS: "If your bandwidth usage significantly exceeds the average bandwidth usage (as determined solely by GitHub) of other GitHub customers, we reserve the right to immediately disable your account or throttle your file hosting until you can reduce your bandwidth consumption." – Deer Hunter May 27 '13 at 9:47
  • 1
    True, but I'm pretty sure that for many, many small organiztions (at county or municipal level) that have low use of bandwidth this is still useful. Even more, the same process and use of git doen't need github necessarilly - that's only to make the tutorial simpler. You can always configure a git repository in your iwn servers and keep track of changes in files – Alvaro Graves May 27 '13 at 21:15
  • 1
    Here's a tip about using Github for datasets: the size limits for files and bandwidth limits are different for Github Pages, so if you keep the data on the gh-pages branch, you can store more there with fewer concerns about violating ToS. – dwillis May 30 '13 at 15:12
2

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.

  • 3
    Please, consider putting main points into your answer to prevent link rot. – Deer Hunter May 28 '13 at 12:40
2

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.)

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.