<commit_before>h1. One CSV, 30 stories: #1 Bootstrapping

_This is day 1 of "One CSV, 30 stories":http://blog.whatfettle.com/2014/10/13/one-csv-thirty-stories/ a series of articles exploring "price paid data":https://www.gov.uk/government/statistical-data-sets/price-paid-data-downloads from the Land Registry found on GOV.UK. The code for this and the other articles is available as open source from "GitHub":https://github.com/psd/price-paid-data_

To get started we need to download a copy of the complete CSV file:

bc. $ curl -O http://publicdata.landregistry.gov.uk/market-trend-data/price-paid-data/a/pp-complete.csv

A "CSV":http://en.wikipedia.org/wiki/Comma-separated_values format file is all well and good for those of a spreadsheet persuasion, but I'm an inveterate Unix hack, and whilst I could process CSV in a scripting language, or use something like "csvquote":https://github.com/dbro/csvquote, a one-off conversion to "Tab Separated Values (TSV)":http://en.wikipedia.org/wiki/Tab-separated_values will open up the "power of the command line":http://jeroenjanssens.com/2013/09/19/seven-command-line-tools-for-data-science.html.

The Python script "csv_to_tsv.py":https://github.com/clarkgrubb/data-tools/blob/master/src/csv_to_tsv.py is a slow but simple way of converting CSV to TSV. Unfortunately this script and other Unix commands fail when processing the Land Registry CSV due to illegal characters:

bc. $ iconv pp-complete.csv > /dev/null
iconv: pp-complete.csv:11432006:106: cannot convert
$ sed -n '11432006p' pp-complete.csv |hexdump -C
00000000  22 7b 46 31 38 46 41 42  41 43 2d 30 35 46 38 2d  |"{F18FABAC-05F8-|
00000010  34 44 44 41 2d 39 31 44  44 2d 39 37 37 34 35 45  |4DDA-91DD-97745E|
00000020  39 34 36 31 42 39 7d 22  2c 22 33 37 38 30 30 30  |9461B9}","378000|
00000030  22 2c 22 32 30 30 37 2d  30 33 2d 31 36 20 30 30  |","2007-03-16 00|
00000040  3a 30 30 22 2c 22 4e 31  36 20 38 41 59 22 2c 22  |:00","N16 8AY","|
00000050  46 22 2c 22 4e 22 2c 22  4c 22 2c 22 33 37 22 2c  |F","N","L","37",|
00000060  22 54 4f 50 20 46 4c 41  54 20 b6 53 45 43 4f 4e  |"TOP FLAT .SECON|
00000070  44 2f 54 48 49 52 44 20  46 4c 4f 4f 52 d9 22 2c  |D/THIRD FLOOR.",|
00000080  22 50 45 4c 4c 45 52 49  4e 20 52 4f 41 44 22 2c  |"PELLERIN ROAD",|
00000090  22 4c 4f 4e 44 4f 4e 22  2c 22 4c 4f 4e 44 4f 4e  |"LONDON","LONDON|
000000a0  22 2c 22 48 41 43 4b 4e  45 59 22 2c 22 47 52 45  |","HACKNEY","GRE|
000000b0  41 54 45 52 20 4c 4f 4e  44 4f 4e 22 2c 22 41 22  |ATER LONDON","A"|
000000c0  0d 0a                                             |..|
000000c2

The "UK Government Open Standards Board":https://gds.blog.gov.uk/2013/04/15/the-open-standards-board/ (on which I happen to serve) set "UTF-8":http://standards.data.gov.uk/challenge/cross-platform-character-encoding as a standard encoding for documents just for this reason. Fortunately it's simple to fix in this case by assuming the file is valid ISO8859-1 and converting it:

bc. $ cat pp-complete.csv | iconv -f ISO-8859-1 -t UTF-8 | bin/csv-to-tsv.py 

Pre-processing the CSV provides an opportunity to remove a little additional fluff. The Land Registry provides a transaction identifier for each line in the CSV file, which is useful for de-duping, but is also 40+ characters per-line we can cut out:

bc. | cut -d'  ' -f2-

along with the time time portion of the date, which is always midnight:

bc. $ cut -d',' -f3 data/pp-complete.csv | sed -e 's/^.* //' | sort | uniq
cut: data/pp-complete.csv: Illegal byte sequence
00:00"

this boilerplate text is easily removed:

bc. | sed -e 's/ 00:00//' > pp.tsv

leaving a much smaller file containing the same number of lines:

bc.  $ ls - pp*
-rw-r----- 1 psd  staff  3331762329 12 Oct 17:55 pp-complete.csv
-rw-r----- 1 psd  staff  1863018927 13 Oct 20:29 pp.tsv
$ wc -l pp*
 19325571 pp-complete.csv
 19325571 pp.tsv
 38651142 total

Well that was a little more gnarly than I would have liked for the first day, but at least we now have information about almost 20 million transactions on property in a single, processable file. Tomorrow we can indulge in a little light data spelunking!
<commit_msg>Tomorrow is another day<commit_after>h1. One CSV, 30 stories: #1 Bootstrapping

_This is day 1 of "One CSV, 30 stories":http://blog.whatfettle.com/2014/10/13/one-csv-thirty-stories/ a series of articles exploring "price paid data":https://www.gov.uk/government/statistical-data-sets/price-paid-data-downloads from the Land Registry found on GOV.UK. The code for this and the other articles is available as open source from "GitHub":https://github.com/psd/price-paid-data_

To get started we need to download a copy of the complete CSV file:

bc. $ curl -O http://publicdata.landregistry.gov.uk/market-trend-data/price-paid-data/a/pp-complete.csv

A "CSV":http://en.wikipedia.org/wiki/Comma-separated_values format file is all well and good for those of a spreadsheet persuasion, but I'm an inveterate Unix hack, and whilst I could process CSV in a scripting language, or use something like "csvquote":https://github.com/dbro/csvquote, a one-off conversion to "Tab Separated Values (TSV)":http://en.wikipedia.org/wiki/Tab-separated_values will open up the "power of the command line":http://jeroenjanssens.com/2013/09/19/seven-command-line-tools-for-data-science.html.

The Python script "csv_to_tsv.py":https://github.com/clarkgrubb/data-tools/blob/master/src/csv_to_tsv.py is a slow but simple way of converting CSV to TSV. Unfortunately this script and other Unix commands fail when processing the Land Registry file due to illegal characters:

bc. $ iconv pp-complete.csv > /dev/null
iconv: pp-complete.csv:11432006:106: cannot convert
$ sed -n '11432006p' pp-complete.csv |hexdump -C
00000000  22 7b 46 31 38 46 41 42  41 43 2d 30 35 46 38 2d  |"{F18FABAC-05F8-|
00000010  34 44 44 41 2d 39 31 44  44 2d 39 37 37 34 35 45  |4DDA-91DD-97745E|
00000020  39 34 36 31 42 39 7d 22  2c 22 33 37 38 30 30 30  |9461B9}","378000|
00000030  22 2c 22 32 30 30 37 2d  30 33 2d 31 36 20 30 30  |","2007-03-16 00|
00000040  3a 30 30 22 2c 22 4e 31  36 20 38 41 59 22 2c 22  |:00","N16 8AY","|
00000050  46 22 2c 22 4e 22 2c 22  4c 22 2c 22 33 37 22 2c  |F","N","L","37",|
00000060  22 54 4f 50 20 46 4c 41  54 20 b6 53 45 43 4f 4e  |"TOP FLAT .SECON|
00000070  44 2f 54 48 49 52 44 20  46 4c 4f 4f 52 d9 22 2c  |D/THIRD FLOOR.",|
00000080  22 50 45 4c 4c 45 52 49  4e 20 52 4f 41 44 22 2c  |"PELLERIN ROAD",|
00000090  22 4c 4f 4e 44 4f 4e 22  2c 22 4c 4f 4e 44 4f 4e  |"LONDON","LONDON|
000000a0  22 2c 22 48 41 43 4b 4e  45 59 22 2c 22 47 52 45  |","HACKNEY","GRE|
000000b0  41 54 45 52 20 4c 4f 4e  44 4f 4e 22 2c 22 41 22  |ATER LONDON","A"|
000000c0  0d 0a                                             |..|
000000c2

The "UK Government Open Standards Board":https://gds.blog.gov.uk/2013/04/15/the-open-standards-board/ (on which I happen to serve) set "UTF-8":http://standards.data.gov.uk/challenge/cross-platform-character-encoding as a standard encoding for documents just for this reason. Fortunately it's simple to fix in this case by assuming the file is valid ISO8859-1 and converting it:

bc. $ cat pp-complete.csv | iconv -f ISO-8859-1 -t UTF-8 | bin/csv-to-tsv.py 

Pre-processing the CSV provides an opportunity to remove a little additional fluff. The Land Registry provides a transaction identifier for each line in the CSV file, which is useful for de-duping, but is also 40+ characters per-line we can cut out:

bc. | cut -d'  ' -f2-

along with the time time portion of the date, which is always midnight:

bc. $ cut -d',' -f3 data/pp-complete.csv | sed -e 's/^.* //' | sort | uniq
cut: data/pp-complete.csv: Illegal byte sequence
00:00"

this boilerplate text is easily removed:

bc. | sed -e 's/ 00:00//' > pp.tsv

leaving a much smaller file containing the same number of lines:

bc.  $ ls - pp*
-rw-r----- 1 psd  staff  3331762329 12 Oct 17:55 pp-complete.csv
-rw-r----- 1 psd  staff  1863018927 13 Oct 20:29 pp.tsv
$ wc -l pp*
 19325571 pp-complete.csv
 19325571 pp.tsv
 38651142 total

Well that was a little more gnarly than I would have liked for the first day, but at least we now have information about almost 20 million transactions on property in a single, processable file.

Tomorrow we can indulge in a little light data spelunking!

{'fraction_non_alphanumeric': 0.09614457831325302, 'fraction_numerical': 0.14746987951807228, 'mean_word_length': 3.3437990580847723, 'pattern_counts': {'":': 20, '<': 3, '<?xml version=': 0, '>': 7, 'https://': 10, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 2, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5300216', 'n_tokens_mistral': 3934, 'n_tokens_neox': 2859, 'n_words': 1233}