-
Notifications
You must be signed in to change notification settings - Fork 3
/
format.html
81 lines (74 loc) · 9.72 KB
/
format.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
<!DOCTYPE html>
<html lang="en">
<head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta charset="utf-8" />
<link rel="icon" href="https://open-innovations.org/resources/images/logos/oi-square-8.svg" />
<title>Business rates: data format</title>
<script type="text/javascript" src="https://open-innovations.org/resources/stuquery.js"></script>
<link rel="StyleSheet" href="https://open-innovations.org/resources/style-oi.css" type="text/css" />
<link rel="StyleSheet" href="style.css" type="text/css" />
</head>
<body>
<div id="site-message" class="c14-bg"><div>Draft: This is a work-in-progress by the Open Data Collaboration group at ODI Leeds.</div></div>
<div id="main">
<header>
<div class="b1-bg padded">
<div class="holder" style="text-align:center;">
<a href="https://open-innovations.org/" aria-label="Open Innovations"><img src="https://open-innovations.org/resources/images/logos/oi-square-8.svg" alt="Open Innovations" width="100px" height="100px" /></a>
<h1>Business rates: data format</h1>
</div>
</div>
</header>
<div class="holder">
<p>The Open Data Collaboration group at Open Innovations (Open Innovations, Bradford Council, Calderdale Council, and Leeds Council) started a project to visualise business rates data. Although each of the three councils publish open data about their business rates, they each have different field titles and don't always include the same fields. This project aimed to standardise the way the data was shared to make it more usable and to get value (within a council and externally) from the datasets.</p>
<h2>File format</h2>
<p>The file should be saved as a CSV file. If you are starting from an Excel file you can choose to export the file as CSV. Make sure to only export the table of data rather than any extra lines of explanatory notes or totals. Excel's export option tends to do a bad job with dates. So we created the <a href="https://odileeds.github.io/CSVCleaner/">CSV Cleaner</a> tool to help tidy them up. You might also want to try <a href="http://csvlint.io/">ODI HQ's CSV Lint</a> tool to check your CSV file is valid. See our <a href="https://github.com/odileeds/open-data-tips">Open Data tips</a> for more. We've also made a <a href="validator.html">validator</a> to check your CSV file against.</p>
<p><a href="validator.html" class="button c8-bg">Validate your business rates file</a></p>
<h2>Headings and data formats</h2>
<p>To make a tool that works across multiple councils, we have to first agree a common standard for sharing the data. This means standard column headings and requiring some columns to exist. However, we are practical and know that some fields will be hard for some councils to include and other councils will want to include more fields where they have them. The result was the following suggestion for common heading titles:</p>
<ol>
<li><code>Property reference number</code> e.g. <code>A89123</code><br />A string representing the unique ID used inside the council. The format of this will vary from council to council. It is possibly useful internally within a council to match to other records.</li>
<li><code>BA reference number</code> e.g. <code>2053386634713</code><br />This is the unique property code from the VOA; at the moment it is only included in the Leeds data and may be hard for some councils so isn't mandatory. If this matches the "Building reference number" included in the Energy Performance Certificate data it would allow us to join the two datasets together to get extra insight.</li>
<li><code>Ratepayer</code><span class="req">Required</span> e.g. <code>Local Business Ltd</code><br />If the rate payer is a named individual, you may wish to replace the name with <code>REDACTED</code> or <code>INDIVIDUAL</code> to protect their privacy.</li>
<li><code>Company number</code> e.g. <code>09417733</code><br />A string representing the unique ID used to identify the company and verify the fact that it is an entity registered with Companies House.</li>
<li><code>Charity number</code> e.g. <code>202918</code><br />A string representing the unique ID used to identify the charity and verify the fact that it is an entity registered with the Charity Comission.</li>
<li><code>Address</code><span class="req">Required</span> e.g. <code>3rd floor Munro House, Duke Street, Leeds</code><br /></li>
<li><code>Postcode</code><span class="req">Required</span> e.g. <code>LS9 8AG</code><br /></li>
<li><code>Latitude</code><span class="req">Required</span> e.g. <code>53.79682</code><br /> Units: degrees. It only needs to be given to 5 or 6 decimal places. This may be calculated from postcode centroids but if more accurate data exists that would be better. If you only have postcodes, you could use the <a href="http://geoportal.statistics.gov.uk/datasets/national-statistics-postcode-lookup-latest-centroids">National Statistics Postcode Lookup (Latest) Centroids</a> dataset to convert from postcode to latitude and longitude. It is <a href="https://www.ons.gov.uk/methodology/geography/licences">released under the Open Government Licence</a>.</li>
<li><code>Longitude</code><span class="req">Required</span> e.g. <code>-1.53445</code><br /> Units: degrees. It only needs to be given to 5 or 6 decimal places. This may be calculated from postcode centroids but if more accurate data exists that would be better. If you only have postcodes, you could use the <a href="http://geoportal.statistics.gov.uk/datasets/national-statistics-postcode-lookup-latest-centroids">National Statistics Postcode Lookup (Latest) Centroids</a> dataset to convert from postcode to latitude and longitude. It is <a href="https://www.ons.gov.uk/methodology/geography/licences">released under the Open Government Licence</a>.</li>
<li><code>Occupied</code><span class="req">Required</span> e.g. <code>Y</code><br /> A simple flag with <code>Y</code> for occupied and either blank or <code>N</code> to indicate empty (to help reduce file size)</li>
<li><code>Liability start date</code><span class="req">Required</span> e.g. <code>2019-04-01</code><br /> Should be in YYYY-MM-DD format as <a href="https://www.gov.uk/government/publications/open-standards-for-government/date-times-and-time-stamps-standard">recommended by GDS</a>. This format reduces ambiguity in parsing dates and gives flexibility with time precision.</li>
<li><code>Empty from</code><span class="req">Required</span> e.g. <code>2019-04-01</code><br />Should be in YYYY-MM-DD format as <a href="https://www.gov.uk/government/publications/open-standards-for-government/date-times-and-time-stamps-standard">recommended by GDS</a>. This format reduces ambiguity in parsing dates and gives flexibility with time precision.</li>
<li><code>Rateable value</code><span class="req">Required</span> e.g. <code>13250</code><br /> Units: GBP. Should be in GBP and just as a decimal number without commas or currency symbols.</li>
<li><code>VOA code</code><span class="req">Required</span> e.g. <code>CO</code><br /> These are the <a href="data/pdcde.csv"><em>Primary Description Code</em> values</a>.</li>
<li><code>VOA description</code> e.g. <code>Offices and Premises</code><br />These are the <a href="data/pdcde.csv"><em>Default Description</em> values</a>.</li>
<li><code>Exemptions</code><br /></li>
<li><code>Exemptions start date</code> e.g. <code>2019-04-05</code><br />Should be in YYYY-MM-DD format as <a href="https://www.gov.uk/government/publications/open-standards-for-government/date-times-and-time-stamps-standard">recommended by GDS</a>. This format reduces ambiguity in parsing dates and gives flexibility with time precision.</li>
<li><code>Relief types</code><br /></li>
<li><code>Relief total</code> e.g. <code>13250</code><br />Units: GBP. Should be in GBP and just as a decimal number without commas or currency symbols.</li>
<li><code>Relief mandatory</code> e.g. <code>6541.65</code><br />Units: GBP. Should be in GBP and just as a decimal number without commas or currency symbols.</li>
<li><code>Relief discretionary</code> e.g. <code>341.89</code><br />Units: GBP. Should be in GBP and just as a decimal number without commas or currency symbols.</li>
</ol>
<p>Obviously, it can be hard to export data in the right formats. To help we've created a <a href="https://odileeds.github.io/CSVCleaner/">CSVCleaner</a> tool that should help reformat dates, clean-up currency, remove trailing spaces, and fix some column headings. You just drop a CSV file into it.</p>
<h2>URLs</h2>
<p>Business Rates data is updated fairly regularly. You don't want developers using out-of-date versions of your data. Help them stay up-to-date by using reliable, persistent, URLs to access your data; as the founder of the web says "<a href="https://www.w3.org/Provider/Style/URI">Cool URIs don't change</a>". We suggest the following:</p>
<ul>
<li><code>council-name.gov.uk/data/business-rates/latest</code> - the latest CSV file you have published</li>
<li><code>council-name.gov.uk/data/business-rates/archive</code> - a webpage that lists your entire archive of Business Rates data</li>
<li><code>council-name.gov.uk/data/business-rates/archive/201904</code> - e.g. an archived CSV file for April 2019</li>
</ul>
<p>These can also be set up as redirects to send people to the CSV file if it is already hosted elsewhere e.g. AWS/a council data store/other. You could use a similar URL structure for Brownfield Sites and many other common datasets.</p>
</div>
</div>
<footer class="b1-bg">
<div class="holder">
<p>© 2019-21 Open Innovations (formerly ODI Leeds)
</div>
</footer>
<script src="https://open-innovations.org/resources/oi.log.js"></script>
<script>
OI.log.setup({'id':'open-innovations','target':['odileeds.github.io']}).add('action=view');
</script>
</body>
</html>