Code: Official information kept secret too long
This document describes how data was analysed and how figures were produced for the article Official information kept secret too long.
This article relied on 10 sources of data for the graphs it produced.
I used my Charter data analysis library to load and process the data and to produce all the graphs used in the article.
Code: Working days calculations
The OIA defines a working day as:
working day means any day of the week other than—
(a) Saturday, Sunday, Good Friday, Easter Monday, Anzac Day, Labour Day, the Sovereign's birthday, and Waitangi Day; and
Official Information Act 1982 s(2)
(ab) if Waitangi Day or Anzac Day falls on a Saturday or a Sunday, the following Monday; and
(b) a day in the period commencing with 25 December in any year and ending with 15 January in the following year.
I have used the following code to calculate the number of working days that have passed between two days:
window.workingDays = (function() {
/* The OIA defines working days as excluding these holidays, which aren't always the same date:
Good Friday
Easter Monday
Anzac Day (Mondayised)
Labour Day
"the Sovereign's birthday"
Waitangi Day (Mondayised)
*/
const holidays = {
2014: {
1: [6],
3: [18, 21, 25],
5: [2],
9: [27]
},
2015: {
1: [6],
3: [3, 6, 27],
5: [1],
9: [26]
},
2016: {
1: [8],
2: [25, 28],
3: [25],
5: [6],
9: [24]
},
2017: {
1: [6],
3: [14, 17, 25],
5: [5],
9: [23]
},
2018: {
1: [6],
2: [30],
3: [2, 25],
5: [4],
9: [22]
},
2019: {
1: [6],
3: [19, 22, 25],
5: [3],
9: [28]
},
2020: {
1: [6],
3: [10, 13, 27],
5: [1],
9: [26]
}
};
const module = {
getWorkingDaysBetween: function(startDate, endDate) {
startDate = new Date(startDate);
endDate = new Date(endDate);
if (startDate > endDate) {
// If the dates are the wrong way around, swap them and report the error in the console
console.error('Dates passed to getWorkingDaysBetween in reverse order:', Array.from(arguments));
let temp = startDate;
startDate = endDate;
endDate = temp;
}
startDate.setHours(0);
startDate.setMinutes(0);
startDate.setSeconds(0);
startDate.setMilliseconds(0);
endDate.setHours(0);
endDate.setMinutes(0);
endDate.setSeconds(0);
endDate.setMilliseconds(0);
let stepDate = startDate;
let days = 0;
while (!module._matchDate(stepDate, endDate)) {
stepDate.setDate(stepDate.getDate() + 1);
if (module._isWorkingDay(stepDate)) {
days += 1;
}
}
return days;
},
addWorkingDays: function(fromDate, numWorkingDays) {
if (!(fromDate instanceof Date) || isNaN(fromDate.getTime())) {
throw `Error: ${fromDate} is not a date.`;
}
let toDate = new Date(fromDate);
// First, just increase by the specified number of days
toDate.setDate(toDate.getDate() + numWorkingDays);
// Then, see if any more working days need to be added, and add that many days
// Repeat until 0 working days remaining
let workingDaysBetween = module.getWorkingDaysBetween(fromDate, toDate);
let workingDaysRemaining = numWorkingDays - workingDaysBetween;
while (workingDaysRemaining > 0) {
toDate.setDate(toDate.getDate() + workingDaysRemaining);
workingDaysBetween = module.getWorkingDaysBetween(fromDate, toDate);
workingDaysRemaining = numWorkingDays - workingDaysBetween;
}
return toDate;
},
_matchDate: function(date1, date2) {
let yearsMatch = date1.getFullYear() === date2.getFullYear();
let monthsMatch = date1.getMonth() === date2.getMonth();
let daysMatch = date1.getDate() === date2.getDate();
let fullMatch = yearsMatch && monthsMatch && daysMatch;
return fullMatch;
},
_isWorkingDay: function(date) {
let weekDay = date.getDay();
if (weekDay === 6 || weekDay === 0) {
// Saturday and Sunday are not working days
return false;
} else {
let month = date.getMonth();
let dateNum = date.getDate();
if ((month === 0 && dateNum <= 15) || (month === 11 && dateNum >= 25)) {
// Days from 25 December to 15 January are not working days
return false;
}
let year = date.getFullYear();
if (holidays[year][month] && holidays[year][month].indexOf(dateNum) > -1) {
// Specified holidays are not working days
return false;
}
// All other days are working days
return true;
}
}
};
return {
between: module.getWorkingDaysBetween,
add: module.addWorkingDays
};
})();
State Services Commission
Prior to contacting the other 11 agencies, in February 2018 I asked the State Services Commission for some data on their OIA response times:
Please release the following information, from 1 July 2016 to 30 June 2017, broken down by request:
1. The date and time at which the request was received.
OIA Response Times | Mark Hanna
2. The date and time at which the request was answered.
3. Whether or not the request was extended under section 15A of the OIA.
3a. If the request was extended, the date and time at which notice of the extension was communicated to the requester.
3b. If the request was extended, the duration of the extension.
4. The outcome of the request (e.g. all information released, transferred to another agency).
The State Services Commission sent me the data in this spreadsheet:
I converted this spreadsheet to CSV format using Google Sheets, which also involved splitting some merged cells.
2319321 Excel.XLSX.xlsx - Sheet1.csv
This data was loaded into Charter using this code:
const cols = Analyser.getColNumbers({
RECEIVED_DATE: 'B',
RECEIVED_TIME: 'C',
RESPONDED_DATE: 'D',
RESPONDED_TIME: 'E',
EXTENDED: 'F',
EXTENDED_DATE_TIME: 'G',
EXTENDED_DURATION: 'H',
OUTCOME: 'I'
});
const fileConfig = {
headerRows: 2,
cols
};
Analyser.loadFile('../data/ssc/2319321 Excel.XLSX.xlsx - Sheet1.csv', fileConfig, analyseData);
Because the spreadsheet uses "Yes" and "" (an empty string) to represent whether or not a request was extended, some code is necessary to recode that into the more usable true and false:
log('"Extended" column before recoding:', rows.getColSummary(cols.EXTENDED));
cols.EXTENDED = rows.addDerivedCol(row => row[cols.EXTENDED] === 'Yes');
log('"Extended" column after recoding:', rows.getColSummary(cols.EXTENDED));
Some requests did not have both a start date and an end date. These were all ignored:
log('Initial rows:', rows.length);
rows = rows.filterAnd(
cols.RECEIVED_DATE, val => !!val,
cols.RESPONDED_DATE, val => !!val
);
log('Rows after exclusion:', rows.length);
First, I calculated the number of days allowed for each request by adding the length of any extension on to the base 20 working days allowed under the OIA.
let getWorkingDaysAllowed = function (row) {
let daysAllowed = 20; // By default, requests are allowed 20 working days
if (row[cols.EXTENDED]) {
daysAllowed += row[cols.EXTENDED_DURATION];
}
return daysAllowed;
};
cols.DAYS_ALLOWED = rows.addDerivedCol(getWorkingDaysAllowed);
let maxDaysAllowed = Stats.max(rows.getCol(cols.DAYS_ALLOWED));
log('Max working days allowed:', maxDaysAllowed);
let labelsDaysAllowed = Stats.intRange(20, maxDaysAllowed);
let chartDaysAllowed = {
title: 'Working days allowed',
labels: labelsDaysAllowed,
dataSeries: [
{
name: 'Working days allowed',
color: '#43ADB9',
dataPoints: rows.getColAsDataSeries(cols.DAYS_ALLOWED, labelsDaysAllowed)
}
]
};
let yAxisDaysAllowed = {
label: 'Requests',
max: 80,
values: 4
};
let xAxisDaysAllowed = {
label: 'Working days',
valuesEvery: 5
};
let $chartDaysAllowed = Charter.createBarChart(chartDaysAllowed, yAxisDaysAllowed, xAxisDaysAllowed);
chart($chartDaysAllowed);
Then, I calculated the number of working days taken to complete each request:
let getDaysTaken = function (row) {
let startDate = row[cols.RECEIVED_DATE];
let endDate = row[cols.RESPONDED_DATE];
let daysBetween = workingDays.between(startDate, endDate);
return daysBetween;
};
cols.DAYS_TAKEN = rows.addDerivedCol(getDaysTaken);
let maxDaysTaken = Stats.max(rows.getCol(cols.DAYS_TAKEN));
log('Maximum days taken:', maxDaysTaken);
const labelsDaysTaken = Stats.intRange(0, maxDaysTaken);
let chartDaysTaken = {
title: 'Working days taken',
labels: labelsDaysTaken,
dataSeries: [
{
name: 'Working days taken',
color: '#43ADB9',
dataPoints: rows.getColAsDataSeries(cols.DAYS_TAKEN, labelsDaysTaken)
}
]
};
let yAxisDaysTaken = {
label: 'Requests',
values: 4
};
let xAxisDaysTaken = {
label: 'Working days',
valuesEvery: 5
};
let $chartDaysTaken = Charter.createBarChart(chartDaysTaken, yAxisDaysTaken, xAxisDaysTaken);
chart($chartDaysTaken);
While it can be useful to look at the data this way, it does obscure which requests were extended, and by how much. Seeing how many allowed days were remaining requires combining the previous two measures.
I also reversed the order of the labels so the chart would be more intuitive, showing delayed requests further to the right, and split the data between extended and un-extended requests.
let getDaysRemaining = function (row) {
let daysAllowed = row[cols.DAYS_ALLOWED];
let daysTaken = row[cols.DAYS_TAKEN];
let daysRemaining = daysAllowed - daysTaken;
return daysRemaining;
};
cols.DAYS_REMAINING = rows.addDerivedCol(getDaysRemaining);
let maxDaysRemaining = Stats.max(rows.getCol(cols.DAYS_REMAINING));
log('Maximum days remaining:', maxDaysRemaining);
let minDaysRemaining = Stats.min(rows.getCol(cols.DAYS_REMAINING));
log('Minimum days remaining:', minDaysRemaining);
const labelsDaysRemaining = Stats.intRange(25, -5);
let chartDaysRemaining = {
title: 'SSC OIA days remaining 2016-07-01 – 2017-06-30',
labels: labelsDaysRemaining,
showLegend: true,
dataSeries: [
{
name: 'Extended requests',
color: '#43ADB9',
dataPoints: rows.filter(cols.EXTENDED, true).getColAsDataSeries(cols.DAYS_REMAINING, labelsDaysRemaining)
},
{
name: 'Unextended requests',
color: '#2B6346',
dataPoints: rows.filter(cols.EXTENDED, false).getColAsDataSeries(cols.DAYS_REMAINING, labelsDaysRemaining)
}
],
stacked: true
};
let yAxisDaysRemaining = {
label: 'Requests',
max: 40,
values: 4
};
let xAxisDaysRemaining = {
label: 'Working days',
valuesEvery: 5
};
let $chartDaysRemaining = Charter.createBarChart(chartDaysRemaining, yAxisDaysRemaining, xAxisDaysRemaining);
chart($chartDaysRemaining);
The length of extensions is already directly in the data, so it's easy to plot:
let maxExtensionLength = Stats.max(rows.getCol(cols.EXTENDED_DURATION));
log('Longest extension duration:', maxExtensionLength);
const labelsExtensionLength = Stats.intRange(0, 50);
let chartExtensionLength = {
title: 'SSC OIA extension length 2016-07-01 – 2017-06-30',
labels: labelsExtensionLength,
dataSeries: [
{
name: 'Extension duration',
color: '#42778D',
dataPoints: rows.getColAsDataSeries(cols.EXTENDED_DURATION, labelsExtensionLength)
}
],
stacked: true
};
let yAxisExtensionLength = {
label: 'Requests',
max: 6,
values: 6
};
let xAxisExtensionLength = {
label: 'Working days',
valuesEvery: 5
};
let $chartExtensionLength = Charter.createBarChart(chartExtensionLength, yAxisExtensionLength, xAxisExtensionLength);
chart($chartExtensionLength);
Calculating the days remaining when a request was extended is similar to calculating the days remaining when it was completed. Because all requests had 20 working days available before being extended, I didn't need to first calculate the days taken to extend the request before I could calculate the days remaining when the request was extended:
let getExtensionDaysRemaining = function (row) {
let startDate = row[cols.RECEIVED_DATE];
let extensionDate = row[cols.EXTENDED_DATE_TIME];
let daysAllowed = 20;
if (extensionDate) {
let daysTaken = workingDays.between(startDate, extensionDate);
let daysRemaining = daysAllowed - daysTaken;
return daysRemaining;
} else {
// The request was not extended
return undefined;
}
};
cols.EXTENDED_DAYS_REMAINING = rows.addDerivedCol(getExtensionDaysRemaining);
let extendedRequests = rows.filter(cols.EXTENDED, true);
let maxExtensionDaysRemaining = Stats.max(extendedRequests.getCol(cols.EXTENDED_DAYS_REMAINING));
log('Maximum days remaining when request extended:', maxExtensionDaysRemaining);
const labelsExtensionDaysRemaining = Stats.intRange(10, -5);
let chartExtensionDaysRemaining = {
title: 'SSC OIA days remaining when extended 2016-07-01 – 2017-06-30',
labels: labelsExtensionDaysRemaining,
dataSeries: [
{
name: 'Working days remaining when extended',
color: '#415E75',
dataPoints: extendedRequests.getColAsDataSeries(cols.EXTENDED_DAYS_REMAINING, labelsExtensionDaysRemaining)
}
]
};
let yAxisExtensionDaysRemaining = {
label: 'Requests',
max: 6,
values: 6
};
let xAxisExtensionDaysRemaining = {
label: 'Working days',
valuesEvery: 5
};
let $chartExtensionDaysRemaining = Charter.createBarChart(chartExtensionDaysRemaining, yAxisExtensionDaysRemaining, xAxisExtensionDaysRemaining);
chart($chartExtensionDaysRemaining);
Ministry for Primary Industries
After having earlier analysed data released from the State Services Commission, I sent identical requests to 11 agencies in April 2018, including the Ministry for Primary Industries. Here's what I asked them:
Please release the following information, from 1 July 2017 to 30 December 2017, broken down by request:
1. The date on which the request was received.
OIA Response Times | Mark Hanna
2. The date on which the request was answered.
2a. If the request was answered on the last allowable day, the time at which the request was answered
3. Whether or not the request was extended under section 15A of the OIA.
3a. If the request was extended, the date on which notice of the extension was sent to the requester.
3b. If the notice of extension was sent to the requester on the last allowable day, the time at which the notice of extension was sent to the requester.
3c. If the request was extended, the duration of the extension.
4. The outcome of the request (e.g. all information released, transferred to another agency).
It's worth noting that the range of dates I used here is shorter. I had asked the SSC for 12 months of data, but I decided in my follow-ups to ask other agencies for 6 months of data instead, to make it easier to extract.
Yes, I have noticed that I forgot the 31st of December exists. Thankfully the OIA takes a break at that time of year, so I don't expect my mistake in forgetting it will have made much of a difference.
A senior advisor from MPI got back to me to help me refine my request to make it easier for them to answer, advising that some of the information I'd asked for would be difficult to extract:
Kia Ora Mark,
I am the advisor who is working on your request. I am contacting you about certain parts of your request that the Ministry is likely to refuse given the information is not held centrally, specifically 2a, 3a, 3c, 4 of your request. This would require manually going into every request and finding the applicable paperwork. This is to advise that we can consider the other parts of your request for release, specifically:
- Date of receipt
- Date Due
- Date decision sent
- Whether a request was extended
Is there any other information on this topic you may be interested in? Let me know and I can see if this is something that we can consider for release.
OIA Response Times | MPI
We agreed that MPI would refuse those parts of my request, and their advisor also explained that their system updated the recorded due date of a request when it was extended. Because I would be able to use this to calculate the length of an extension, I was happy for them to provide "Date Due" information instead of the length of extensions.
The Ministry for Primary Industries sent me the data in this spreadsheet:
OIA18 0265 data for release.xlsx
I converted this spreadsheet to CSV format using Google Sheets.
OIA18 0265 data for release.xlsx - Sheet1.csv
This data was loaded into Charter using this code:
const cols = Analyser.getColNumbers({
ID: 'A',
RECEIVED_DATE: 'B',
DUE_DATE: 'C',
RESPONDED_DATE: 'D',
EXTENDED: 'E'
});
const fileConfig = {
headerRows: 1,
footerRows: 4,
cols
};
Analyser.loadFile('../data/mpi/OIA18 0265 data for release.xlsx - Sheet1.csv', fileConfig, analyseData);
Because the spreadsheet uses "Yes" and "No" to represent whether or not a request was extended, some code is necessary to recode that into the more usable true and false:
log('"Extended" column before recoding:', rows.getColSummary(cols.EXTENDED));
cols.EXTENDED = rows.addDerivedCol(row => row[cols.EXTENDED] === 'Yes');
log('"Extended" column after recoding:', rows.getColSummary(cols.EXTENDED));
One request recorded a completed date before its received date, which appeared to be due to the year for the completed date having been recorded incorrectly.
let updatedRequests = [];
rows.forEach(row => {
let receivedDate = new Date(row[cols.RECEIVED_DATE]);
let respondedDate = new Date(row[cols.RESPONDED_DATE]);
if (respondedDate < receivedDate) {
let year = respondedDate.getFullYear();
let nextYear = year+1;
let shortYear = year.toString().replace(/^20/, '');
let shortNextYear = nextYear.toString().replace(/^20/, '');
row[cols.RESPONDED_DATE] = row[cols.RESPONDED_DATE].replace(shortYear, shortNextYear);
updatedRequests.push(row[cols.ID]);
}
});
log('Updated responded dates:', rows.filter(cols.ID, updatedRequests).getCol(cols.RESPONDED_DATE));
let getWorkingDaysAllowed = function (row) {
// MPI provided the date a request was received, and the date it was due
let received = row[cols.RECEIVED_DATE];
let due = row[cols.DUE_DATE];
let daysAllowed = workingDays.between(received, due);
return daysAllowed;
};
cols.DAYS_ALLOWED = rows.addDerivedCol(getWorkingDaysAllowed);
let maxDaysAllowed = Stats.max(rows.getCol(cols.DAYS_ALLOWED));
log('Max working days allowed:', maxDaysAllowed);
let labelsDaysAllowed = Stats.intRange(20, maxDaysAllowed);
let chartDaysAllowed = {
title: 'Working days allowed',
labels: labelsDaysAllowed,
dataSeries: [
{
name: 'Working days allowed',
color: '#43ADB9',
dataPoints: rows.getColAsDataSeries(cols.DAYS_ALLOWED, labelsDaysAllowed)
}
]
};
let yAxisDaysAllowed = {
label: 'Requests',
max: 180,
values: 4
};
let xAxisDaysAllowed = {
label: 'Working days',
valuesEvery: 5
};
let $chartDaysAllowed = Charter.createBarChart(chartDaysAllowed, yAxisDaysAllowed, xAxisDaysAllowed);
chart($chartDaysAllowed);
Then, I calculated the number of working days taken to complete each request:
let getDaysTaken = function (row) {
let startDate = row[cols.RECEIVED_DATE];
let endDate = row[cols.RESPONDED_DATE];
let daysBetween = workingDays.between(startDate, endDate);
return daysBetween;
};
cols.DAYS_TAKEN = rows.addDerivedCol(getDaysTaken);
let maxDaysTaken = Stats.max(rows.getCol(cols.DAYS_TAKEN));
log('Maximum days taken:', maxDaysTaken);
const labelsDaysTaken = Stats.intRange(0, maxDaysTaken);
let chartDaysTaken = {
title: 'Working days taken',
labels: labelsDaysTaken,
dataSeries: [
{
name: 'Working days taken',
color: '#43ADB9',
dataPoints: rows.getColAsDataSeries(cols.DAYS_TAKEN, labelsDaysTaken)
}
]
};
let yAxisDaysTaken = {
label: 'Requests',
values: 4
};
let xAxisDaysTaken = {
label: 'Working days',
valuesEvery: 20
};
let $chartDaysTaken = Charter.createBarChart(chartDaysTaken, yAxisDaysTaken, xAxisDaysTaken);
chart($chartDaysTaken);
While it can be useful to look at the data this way, it does obscure which requests were extended, and by how much. Seeing how many allowed days were remaining requires combining the previous two measures.
I also reversed the order of the labels so the chart would be more intuitive, showing delayed requests further to the right, and split the data between extended and un-extended requests.
let getDaysRemaining = function (row) {
let daysAllowed = row[cols.DAYS_ALLOWED];
let daysTaken = row[cols.DAYS_TAKEN];
let daysRemaining = daysAllowed - daysTaken;
return daysRemaining;
};
cols.DAYS_REMAINING = rows.addDerivedCol(getDaysRemaining);
let maxDaysRemaining = Stats.max(rows.getCol(cols.DAYS_REMAINING));
log('Maximum days remaining:', maxDaysRemaining);
let minDaysRemaining = Stats.min(rows.getCol(cols.DAYS_REMAINING));
log('Minimum days remaining:', minDaysRemaining);
const labelsDaysRemaining = Stats.intRange(20, -20);
let chartDaysRemaining = {
title: 'MPI OIA days remaining 2017-07-01 – 2017-12-30',
labels: labelsDaysRemaining,
showLegend: true,
dataSeries: [
{
name: 'Extended requests',
color: '#43ADB9',
dataPoints: rows.filter(cols.EXTENDED, true).getColAsDataSeries(cols.DAYS_REMAINING, labelsDaysRemaining)
},
{
name: 'Unextended requests',
color: '#2B6346',
dataPoints: rows.filter(cols.EXTENDED, false).getColAsDataSeries(cols.DAYS_REMAINING, labelsDaysRemaining)
}
],
stacked: true
};
let yAxisDaysRemaining = {
label: 'Requests',
max: 150,
values: 3
};
let xAxisDaysRemaining = {
label: 'Working days',
valuesEvery: 10
};
let $chartDaysRemaining = Charter.createBarChart(chartDaysRemaining, yAxisDaysRemaining, xAxisDaysRemaining);
chart($chartDaysRemaining);
The length of extensions has to be calculated by subtracting the original 20 working days from the calculated number of working days allowed, based on the due date:
let getExtensionLength = function (row) {
if (row[cols.EXTENDED]) {
let allowed = row[cols.DAYS_ALLOWED];
let extensionDuration = allowed - 20;
return extensionDuration;
}
};
cols.EXTENDED_DURATION = rows.addDerivedCol(getExtensionLength);
let maxExtensionLength = Stats.max(rows.getCol(cols.EXTENDED_DURATION));
log('Longest extension duration:', maxExtensionLength);
const labelsExtensionLength = Stats.intRange(0, 40);
let chartExtensionLength = {
title: 'MPI OIA extension length 2017-07-01 – 2017-12-30',
labels: labelsExtensionLength,
dataSeries: [
{
name: 'Extension duration',
color: '#42778D',
dataPoints: rows.getColAsDataSeries(cols.EXTENDED_DURATION, labelsExtensionLength)
}
],
stacked: true
};
let yAxisExtensionLength = {
label: 'Requests',
max: 20,
values: 4
};
let xAxisExtensionLength = {
label: 'Working days',
valuesEvery: 5
};
let $chartExtensionLength = Charter.createBarChart(chartExtensionLength, yAxisExtensionLength, xAxisExtensionLength);
chart($chartExtensionLength);
Ministry of Justice
After having earlier analysed data released from the State Services Commission, I sent identical requests to 11 agencies in April 2018, including the Ministry of Justice. Here's what I asked them:
Please release the following information, from 1 July 2017 to 30 December 2017, broken down by request:
1. The date on which the request was received.
OIA Response Times | Mark Hanna
2. The date on which the request was answered.
2a. If the request was answered on the last allowable day, the time at which the request was answered
3. Whether or not the request was extended under section 15A of the OIA.
3a. If the request was extended, the date on which notice of the extension was sent to the requester.
3b. If the notice of extension was sent to the requester on the last allowable day, the time at which the notice of extension was sent to the requester.
3c. If the request was extended, the duration of the extension.
4. The outcome of the request (e.g. all information released, transferred to another agency).
It's worth noting that the range of dates I used here is shorter. I had asked the SSC for 12 months of data, but I decided in my follow-ups to ask other agencies for 6 months of data instead, to make it easier to extract.
Yes, I have noticed that I forgot the 31st of December exists. Thankfully the OIA takes a break at that time of year, so I don't expect my mistake in forgetting it will have made much of a difference.
The Ministry of Justice sent me the data in this spreadsheet:
034 OIA 67091 Mark Hanna Data Final.xlsx
I converted this spreadsheet to CSV format using Google Sheets.
034 OIA 67091 Mark Hanna Data Final.xlsx - Sheet1.csv
This data was loaded into Charter using this code:
const cols = Analyser.getColNumbers({
RECEIVED_DATE: 'C',
DUE_DATE: 'E',
RESPONDED_DATE: 'D',
EXTENDED: 'F'
});
const fileConfig = {
headerRows: 1,
footerRows: 2,
cols
};
Analyser.loadFile('../data/moj//034 OIA 67091 Mark Hanna Data Final.xlsx - Sheet1.csv', fileConfig, analyseData);
The Ministry of Justice was also kind enough to include some additional fields, such as the number of working days taken to respond and the number of days overdue a request was. I opted to calculate these values based off the dates provided, for consistency with how I analysed data from other agencies.
However, they unfortunately provided the dates in the format 'dd/mm/yyyy', which is ambiguous in some contexts thanks to Americans using the format 'mm/dd/yyyy'. So in order to ensure consistency, I converted them into the unambiguous format 'yyyy-mm-dd':
log('Example cell from "Responded" column before recoding:', rows[0][cols.RESPONDED_DATE]);
let convertDate = function (dateString) {
let formatPattern = /(\d+)\/(\d+)\/(\d+)/;
let match = dateString.match(formatPattern);
if (match) {
let day = match[1];
let month = match[2];
let year = match[3];
let newDateString = `${year}-${month.padStart(2, '0')}-${day.padStart(2, '0')}`;
return newDateString;
} else {
return null;
}
};
rows.forEach(row => {
row[cols.RECEIVED_DATE] = convertDate(row[cols.RECEIVED_DATE]);
row[cols.DUE_DATE] = convertDate(row[cols.DUE_DATE]);
row[cols.RESPONDED_DATE] = convertDate(row[cols.RESPONDED_DATE]);
});
log('Example cell from "Responded" column after recoding:', rows[0][cols.RESPONDED_DATE]);
They also included some requests which had not yet been completed. These were marked with a responded date of "-", which I recoded to null. Because I meant to analyse how long it had taken to respond to requests, I removed these requests from my analysis:
let allRows = rows;
let numRows = rows.length;
rows = rows.filter(cols.RESPONDED_DATE, date => date !== null);
let numRemovedRows = numRows - rows.length;
log('Requests removed due to lack of a responded date:', numRemovedRows);
Because the spreadsheet uses "Yes" and "No" to represent whether or not a request was extended, some code is necessary to recode that into the more usable true and false:
log('"Extended" column before recoding:', rows.getColSummary(cols.EXTENDED));
cols.EXTENDED = rows.addDerivedCol(row => row[cols.EXTENDED] === 'Yes');
log('"Extended" column after recoding:', rows.getColSummary(cols.EXTENDED));
let getWorkingDaysAllowed = function (row) {
// The Ministry of Justice provided the date a request was received, and the date it was due
let received = row[cols.RECEIVED_DATE];
let due = row[cols.DUE_DATE];
let daysAllowed = workingDays.between(received, due);
return daysAllowed;
};
cols.DAYS_ALLOWED = rows.addDerivedCol(getWorkingDaysAllowed);
let maxDaysAllowed = Stats.max(rows.getCol(cols.DAYS_ALLOWED));
log('Max working days allowed:', maxDaysAllowed);
let labelsDaysAllowed = Stats.intRange(20, maxDaysAllowed);
let chartDaysAllowed = {
title: 'Working days allowed',
labels: labelsDaysAllowed,
dataSeries: [
{
name: 'Working days allowed',
color: '#43ADB9',
dataPoints: rows.getColAsDataSeries(cols.DAYS_ALLOWED, labelsDaysAllowed)
}
]
};
let yAxisDaysAllowed = {
label: 'Requests',
max: 400,
values: 4
};
let xAxisDaysAllowed = {
label: 'Working days',
valuesEvery: 5
};
let $chartDaysAllowed = Charter.createBarChart(chartDaysAllowed, yAxisDaysAllowed, xAxisDaysAllowed);
chart($chartDaysAllowed);
Then, I calculated the number of working days taken to complete each request:
let getDaysTaken = function (row) {
let startDate = row[cols.RECEIVED_DATE];
let endDate = row[cols.RESPONDED_DATE];
let daysBetween = workingDays.between(startDate, endDate);
return daysBetween;
};
cols.DAYS_TAKEN = rows.addDerivedCol(getDaysTaken);
let maxDaysTaken = Stats.max(rows.getCol(cols.DAYS_TAKEN));
log('Maximum days taken:', maxDaysTaken);
const labelsDaysTaken = Stats.intRange(0, maxDaysTaken);
let chartDaysTaken = {
title: 'Working days taken',
labels: labelsDaysTaken,
dataSeries: [
{
name: 'Working days taken',
color: '#43ADB9',
dataPoints: rows.getColAsDataSeries(cols.DAYS_TAKEN, labelsDaysTaken)
}
]
};
let yAxisDaysTaken = {
label: 'Requests',
values: 4
};
let xAxisDaysTaken = {
label: 'Working days',
valuesEvery: 20
};
let $chartDaysTaken = Charter.createBarChart(chartDaysTaken, yAxisDaysTaken, xAxisDaysTaken);
chart($chartDaysTaken);
While it can be useful to look at the data this way, it does obscure which requests were extended, and by how much. Seeing how many allowed days were remaining requires combining the previous two measures.
I also reversed the order of the labels so the chart would be more intuitive, showing delayed requests further to the right, and split the data between extended and un-extended requests.
let getDaysRemaining = function (row) {
let daysAllowed = row[cols.DAYS_ALLOWED];
let daysTaken = row[cols.DAYS_TAKEN];
let daysRemaining = daysAllowed - daysTaken;
return daysRemaining;
};
cols.DAYS_REMAINING = rows.addDerivedCol(getDaysRemaining);
let maxDaysRemaining = Stats.max(rows.getCol(cols.DAYS_REMAINING));
log('Maximum days remaining:', maxDaysRemaining);
let minDaysRemaining = Stats.min(rows.getCol(cols.DAYS_REMAINING));
log('Minimum days remaining:', minDaysRemaining);
const labelsDaysRemaining = Stats.intRange(20, -20);
let chartDaysRemaining = {
title: 'Ministry of Justice OIA days remaining 2017-07-01 – 2017-12-30',
labels: labelsDaysRemaining,
showLegend: true,
dataSeries: [
{
name: 'Extended requests',
color: '#43ADB9',
dataPoints: rows.filter(cols.EXTENDED, true).getColAsDataSeries(cols.DAYS_REMAINING, labelsDaysRemaining)
},
{
name: 'Unextended requests',
color: '#2B6346',
dataPoints: rows.filter(cols.EXTENDED, false).getColAsDataSeries(cols.DAYS_REMAINING, labelsDaysRemaining)
}
],
stacked: true
};
let yAxisDaysRemaining = {
label: 'Requests',
max: 90,
values: 3
};
let xAxisDaysRemaining = {
label: 'Working days',
valuesEvery: 10
};
let $chartDaysRemaining = Charter.createBarChart(chartDaysRemaining, yAxisDaysRemaining, xAxisDaysRemaining);
chart($chartDaysRemaining);
The length of extensions has to be calculated by subtracting the original 20 working days from the calculated number of working days allowed, based on the due date:
let getExtensionLength = function (row) {
if (row[cols.EXTENDED]) {
let allowed = row[cols.DAYS_ALLOWED];
let extensionDuration = allowed - 20;
return extensionDuration;
}
};
cols.EXTENDED_DURATION = rows.addDerivedCol(getExtensionLength);
let maxExtensionLength = Stats.max(rows.getCol(cols.EXTENDED_DURATION));
log('Longest extension duration:', maxExtensionLength);
const labelsExtensionLength = Stats.intRange(0, 45);
let chartExtensionLength = {
title: 'Ministry of Justice OIA extension length 2017-07-01 – 2017-12-30',
labels: labelsExtensionLength,
dataSeries: [
{
name: 'Extension duration',
color: '#42778D',
dataPoints: rows.getColAsDataSeries(cols.EXTENDED_DURATION, labelsExtensionLength)
}
],
stacked: true
};
let yAxisExtensionLength = {
label: 'Requests',
max: 8,
values: 4
};
let xAxisExtensionLength = {
label: 'Working days',
valuesEvery: 5
};
let $chartExtensionLength = Charter.createBarChart(chartExtensionLength, yAxisExtensionLength, xAxisExtensionLength);
chart($chartExtensionLength);
Ministry of Foreign Affairs and Trade
After having earlier analysed data released from the State Services Commission, I sent identical requests to 11 agencies in April 2018, including the Ministry of Foreign Affairs and Trade. Here's what I asked them:
Please release the following information, from 1 July 2017 to 30 December 2017, broken down by request:
1. The date on which the request was received.
OIA Response Times | Mark Hanna
2. The date on which the request was answered.
2a. If the request was answered on the last allowable day, the time at which the request was answered
3. Whether or not the request was extended under section 15A of the OIA.
3a. If the request was extended, the date on which notice of the extension was sent to the requester.
3b. If the notice of extension was sent to the requester on the last allowable day, the time at which the notice of extension was sent to the requester.
3c. If the request was extended, the duration of the extension.
4. The outcome of the request (e.g. all information released, transferred to another agency).
It's worth noting that the range of dates I used here is shorter. I had asked the SSC for 12 months of data, but I decided in my follow-ups to ask other agencies for 6 months of data instead, to make it easier to extract.
Yes, I have noticed that I forgot the 31st of December exists. Thankfully the OIA takes a break at that time of year, so I don't expect my mistake in forgetting it will have made much of a difference.
The Ministry of Foreign Affairs and Trade notified me on the original due date of my request that they were extending it by 10 working days, though they sent me the data two days later in a spreadsheet:
Mark Hanna OIA spreadsheet.xlsx
I converted this spreadsheet to CSV format using Google Sheets.
Mark Hanna OIA spreadsheet.xlsx - Sheet1.csv
This data was loaded into Charter using this code:
const cols = Analyser.getColNumbers({
ID: 'A',
RECEIVED_DATE: 'B',
RESPONDED_DATE: 'G',
EXTENDED_DATE: 'E',
EXTENDED: 'D',
EXTENDED_DURATION: 'F'
});
const fileConfig = {
headerRows: 4,
cols
};
Analyser.loadFile('../data/mfat//Mark Hanna OIA spreadsheet.xlsx - Sheet1.csv', fileConfig, analyseData);
Because the spreadsheet uses "Y" and "N" to represent whether or not a request was extended, some code is necessary to recode that into the more usable true and false:
log('"Extended" column before recoding:', rows.getColSummary(cols.EXTENDED));
cols.EXTENDED = rows.addDerivedCol(row => row[cols.EXTENDED] === 'Y');
log('"Extended" column after recoding:', rows.getColSummary(cols.EXTENDED));
MFAT provided a breakdown of both date and time for each of the dates it provided. However, it split these into separate cells for the received date and the responded date, but not for the date on which an extension was notified.
Instead, that column contained some cells with both date and time, some with date alone, and some with "N/A". So I converted that column into one containing only the date information:
log('Example cell from "Extended Date" column before recoding:', rows[0][cols.EXTENDED_DATE]);
let convertDateTime = function (dateString) {
let formatPattern = /(\d+\/\d+\/\d+)/;
let match = dateString.match(formatPattern);
if (match) {
let date = match[1];
let newDateString = date;
return newDateString;
} else {
return null;
}
};
rows.forEach(row => {
row[cols.EXTENDED_DATE] = convertDateTime(row[cols.EXTENDED_DATE]);
});
log('Example cell from "Extended Date" column after recoding:', rows[0][cols.EXTENDED_DATE]);
Unfortunately, MFAT provided the dates in the format 'dd/mm/yyyy', which is ambiguous in some contexts thanks to Americans using the format 'mm/dd/yyyy'. So in order to ensure consistency, I converted them into the unambiguous format 'yyyy-mm-dd':
log('Example cell from "Responded" column before recoding:', rows[0][cols.RESPONDED_DATE]);
let convertDate = function (dateString) {
let formatPattern = /(\d+)\/(\d+)\/(\d+)/;
let match = dateString.match(formatPattern);
if (match) {
let day = match[1];
let month = match[2];
let year = match[3];
let newDateString = `${year}-${month.padStart(2, '0')}-${day.padStart(2, '0')}`;
return newDateString;
} else {
return null;
}
};
rows.forEach(row => {
row[cols.RECEIVED_DATE] = convertDate(row[cols.RECEIVED_DATE]);
row[cols.RESPONDED_DATE] = convertDate(row[cols.RESPONDED_DATE]);
if (row[cols.EXTENDED]) {
row[cols.EXTENDED_DATE] = convertDate(row[cols.EXTENDED_DATE]);
}
});
log('Example cell from "Responded" column after recoding:', rows[0][cols.RESPONDED_DATE]);
There was also one complex request, without a clear date and time responded because it was partially transferred, then partially responded to and partially extended, then the remaining partial response was sent. Because there was no clear value to use here in my analysis, I removed this row:
let numRows = rows.length;
rows = rows.filter(cols.ID, id => id !== 88);
let numRowsRemoved = numRows - rows.length;
log('Number of rows removed:', numRowsRemoved);
let getWorkingDaysAllowed = function (row) {
let daysAllowed = 20; // By default, requests are allowed 20 working days
if (row[cols.EXTENDED]) {
daysAllowed += row[cols.EXTENDED_DURATION];
}
return daysAllowed;
};
cols.DAYS_ALLOWED = rows.addDerivedCol(getWorkingDaysAllowed);
let maxDaysAllowed = Stats.max(rows.getCol(cols.DAYS_ALLOWED));
log('Max working days allowed:', maxDaysAllowed);
let labelsDaysAllowed = Stats.intRange(20, maxDaysAllowed);
let chartDaysAllowed = {
title: 'Working days allowed',
labels: labelsDaysAllowed,
dataSeries: [
{
name: 'Working days allowed',
color: '#43ADB9',
dataPoints: rows.getColAsDataSeries(cols.DAYS_ALLOWED, labelsDaysAllowed)
}
]
};
let yAxisDaysAllowed = {
label: 'Requests',
max: 50,
values: 4
};
let xAxisDaysAllowed = {
label: 'Working days',
valuesEvery: 5
};
let $chartDaysAllowed = Charter.createBarChart(chartDaysAllowed, yAxisDaysAllowed, xAxisDaysAllowed);
chart($chartDaysAllowed);
Then, I calculated the number of working days taken to complete each request:
let getDaysTaken = function (row) {
let startDate = row[cols.RECEIVED_DATE];
let endDate = row[cols.RESPONDED_DATE];
let daysBetween = workingDays.between(startDate, endDate);
return daysBetween;
};
cols.DAYS_TAKEN = rows.addDerivedCol(getDaysTaken);
let maxDaysTaken = Stats.max(rows.getCol(cols.DAYS_TAKEN));
log('Maximum days taken:', maxDaysTaken);
const labelsDaysTaken = Stats.intRange(0, maxDaysTaken);
let chartDaysTaken = {
title: 'Working days taken',
labels: labelsDaysTaken,
dataSeries: [
{
name: 'Working days taken',
color: '#43ADB9',
dataPoints: rows.getColAsDataSeries(cols.DAYS_TAKEN, labelsDaysTaken)
}
]
};
let yAxisDaysTaken = {
label: 'Requests',
values: 4
};
let xAxisDaysTaken = {
label: 'Working days',
valuesEvery: 20
};
let $chartDaysTaken = Charter.createBarChart(chartDaysTaken, yAxisDaysTaken, xAxisDaysTaken);
chart($chartDaysTaken);
While it can be useful to look at the data this way, it does obscure which requests were extended, and by how much. Seeing how many allowed days were remaining requires combining the previous two measures.
I also reversed the order of the labels so the chart would be more intuitive, showing delayed requests further to the right, and split the data between extended and un-extended requests.
let getDaysRemaining = function (row) {
let daysAllowed = row[cols.DAYS_ALLOWED];
let daysTaken = row[cols.DAYS_TAKEN];
let daysRemaining = daysAllowed - daysTaken;
return daysRemaining;
};
cols.DAYS_REMAINING = rows.addDerivedCol(getDaysRemaining);
let maxDaysRemaining = Stats.max(rows.getCol(cols.DAYS_REMAINING));
log('Maximum days remaining:', maxDaysRemaining);
let minDaysRemaining = Stats.min(rows.getCol(cols.DAYS_REMAINING));
log('Minimum days remaining:', minDaysRemaining);
const labelsDaysRemaining = Stats.intRange(25, -30);
let chartDaysRemaining = {
title: 'Ministry of Foreign Affairs and Trade OIA days remaining 2017-07-01 – 2017-12-30',
labels: labelsDaysRemaining,
showLegend: true,
dataSeries: [
{
name: 'Extended requests',
color: '#43ADB9',
dataPoints: rows.filter(cols.EXTENDED, true).getColAsDataSeries(cols.DAYS_REMAINING, labelsDaysRemaining)
},
{
name: 'Unextended requests',
color: '#2B6346',
dataPoints: rows.filter(cols.EXTENDED, false).getColAsDataSeries(cols.DAYS_REMAINING, labelsDaysRemaining)
}
],
stacked: true
};
let yAxisDaysRemaining = {
label: 'Requests',
max: 30,
values: 3
};
let xAxisDaysRemaining = {
label: 'Working days',
valuesEvery: 10
};
let $chartDaysRemaining = Charter.createBarChart(chartDaysRemaining, yAxisDaysRemaining, xAxisDaysRemaining);
chart($chartDaysRemaining);
The length of extensions has to be calculated by subtracting the original 20 working days from the calculated number of working days allowed, based on the due date:
let getExtensionLength = function (row) {
if (row[cols.EXTENDED]) {
let allowed = row[cols.DAYS_ALLOWED];
let extensionDuration = allowed - 20;
return extensionDuration;
}
};
cols.EXTENDED_DURATION = rows.addDerivedCol(getExtensionLength);
let maxExtensionLength = Stats.max(rows.getCol(cols.EXTENDED_DURATION));
log('Longest extension duration:', maxExtensionLength);
const labelsExtensionLength = Stats.intRange(0, 50);
let chartExtensionLength = {
title: 'Ministry of Foreign Affairs and Trade OIA extension length 2017-07-01 – 2017-12-30',
labels: labelsExtensionLength,
dataSeries: [
{
name: 'Extension duration',
color: '#42778D',
dataPoints: rows.getColAsDataSeries(cols.EXTENDED_DURATION, labelsExtensionLength)
}
],
stacked: true
};
let yAxisExtensionLength = {
label: 'Requests',
max: 30,
values: 3
};
let xAxisExtensionLength = {
label: 'Working days',
valuesEvery: 5
};
let $chartExtensionLength = Charter.createBarChart(chartExtensionLength, yAxisExtensionLength, xAxisExtensionLength);
chart($chartExtensionLength);
Accident Compensation Corporation
After having earlier analysed data released from the State Services Commission, I sent identical requests to 11 agencies in April 2018, including the Accident Compensation Corporation. Here's what I asked them:
Please release the following information, from 1 July 2017 to 30 December 2017, broken down by request:
1. The date on which the request was received.
OIA Response Times | Mark Hanna
2. The date on which the request was answered.
2a. If the request was answered on the last allowable day, the time at which the request was answered
3. Whether or not the request was extended under section 15A of the OIA.
3a. If the request was extended, the date on which notice of the extension was sent to the requester.
3b. If the notice of extension was sent to the requester on the last allowable day, the time at which the notice of extension was sent to the requester.
3c. If the request was extended, the duration of the extension.
4. The outcome of the request (e.g. all information released, transferred to another agency).
It's worth noting that the range of dates I used here is shorter. I had asked the SSC for 12 months of data, but I decided in my follow-ups to ask other agencies for 6 months of data instead, to make it easier to extract.
Yes, I have noticed that I forgot the 31st of December exists. Thankfully the OIA takes a break at that time of year, so I don't expect my mistake in forgetting it will have made much of a difference.
The Accident Compensation Corporation sent me the data in a spreadsheet:
July to Dec 2017 OIA responses.xlsx
The dates in this spreadsheet were displayed in the format "dd-MMMM", but I converted them within Google Drive to the format "yyyy-mm-dd". I then converted this spreadsheet to CSV format using Google Sheets.
July to Dec 2017 OIA responses.xlsx - Sheet1.csv
This data was loaded into Charter using this code:
const cols = Analyser.getColNumbers({
ID: 'A',
RECEIVED_DATE: 'B',
RESPONDED_DATE: 'C',
DUE_DATE: 'D',
EXTENDED_DATE: 'F',
});
const fileConfig = {
headerRows: 1,
cols
};
Analyser.loadFile('../data/acc/July to Dec 2017 OIA responses.xlsx - Sheet1.csv', fileConfig, analyseData);
ACC had included some requests which had not yet been completed. These requests did not have a value for the responded date. Because I meant to analyse how long it had taken to respond to requests, I removed these requests from my analysis:
let numRows = rows.length;
rows = rows.filter(cols.RESPONDED_DATE, date => date !== '');
let numRemovedRows = numRows - rows.length;
log('Requests removed due to lack of a responded date:', numRemovedRows);
Some of the requests in the spreadsheet ACC provided had due dates and extended dates before their start dates.
It seemed very likely that the cause of the issue was that the requests were actually due in 2019, especially given the year was not displayed in the spreadsheet provided by ACC (though that information was included in the raw value of the cell).
To solve this, I found all the requests with due dates before their received dates, and shifted the due date from 2018 to 2019. I then checked this change by looking at the number of working days between the received date and the updated due date.
let dueDatesUpdated = 0;
let extendedDatesUpdated = 0;
rows.forEach(row => {
let receivedDate = new Date(row[cols.RECEIVED_DATE]);
let dueDate = new Date(row[cols.DUE_DATE]);
if (dueDate < receivedDate) {
let year = dueDate.getFullYear();
row[cols.DUE_DATE] = row[cols.DUE_DATE].replace(year, year + 1);
dueDatesUpdated += 1;
}
if (row[cols.EXTENDED_DATE]) {
let extendedDate = new Date(row[cols.EXTENDED_DATE]);
if (extendedDate < receivedDate) {
let year = extendedDate.getFullYear();
row[cols.EXTENDED_DATE] = row[cols.EXTENDED_DATE].replace(year, year + 1);
extendedDatesUpdated += 1;
}
}
});
log('Number of rows with incorrect due dates:', dueDatesUpdated);
log('Number of rows with incorrect extended dates:', extendedDatesUpdated);
The spreadsheet included an "Extension date" column but not one that just marked whether or not a request was extended. The presence or absence of a value in this column is consistent with the values in the "Comments" column denoting extensions, so I checked for the presence of an extension date to mark whether or not a request was extended:
cols.EXTENDED = rows.addDerivedCol(row => row[cols.EXTENDED_DATE] !== '');
log('"Extended" column after recoding:', rows.getColSummary(cols.EXTENDED));
let getWorkingDaysAllowed = function (row) {
// ACC provided the date a request was received, and the date it was due
let received = row[cols.RECEIVED_DATE];
let due = row[cols.DUE_DATE];
let daysAllowed = workingDays.between(received, due);
return daysAllowed;
};
cols.DAYS_ALLOWED = rows.addDerivedCol(getWorkingDaysAllowed);
let maxDaysAllowed = Stats.max(rows.getCol(cols.DAYS_ALLOWED));
log('Max working days allowed:', maxDaysAllowed);
let labelsDaysAllowed = Stats.intRange(20, maxDaysAllowed);
let chartDaysAllowed = {
title: 'Working days allowed',
labels: labelsDaysAllowed,
dataSeries: [
{
name: 'Working days allowed',
color: '#43ADB9',
dataPoints: rows.getColAsDataSeries(cols.DAYS_ALLOWED, labelsDaysAllowed)
}
]
};
let yAxisDaysAllowed = {
label: 'Requests',
max: 350,
values: 1,
valuesAt: [25, 100, 200, 300]
};
let xAxisDaysAllowed = {
label: 'Working days',
valuesEvery: 5
};
let $chartDaysAllowed = Charter.createBarChart(chartDaysAllowed, yAxisDaysAllowed, xAxisDaysAllowed);
chart($chartDaysAllowed);
Then, I calculated the number of working days taken to complete each request:
let getDaysTaken = function (row) {
let startDate = row[cols.RECEIVED_DATE];
let endDate = row[cols.RESPONDED_DATE];
let daysBetween = workingDays.between(startDate, endDate);
return daysBetween;
};
cols.DAYS_TAKEN = rows.addDerivedCol(getDaysTaken);
let maxDaysTaken = Stats.max(rows.getCol(cols.DAYS_TAKEN));
log('Maximum days taken:', maxDaysTaken);
const labelsDaysTaken = Stats.intRange(0, maxDaysTaken);
let chartDaysTaken = {
title: 'Working days taken',
labels: labelsDaysTaken,
dataSeries: [
{
name: 'Working days taken',
color: '#43ADB9',
dataPoints: rows.getColAsDataSeries(cols.DAYS_TAKEN, labelsDaysTaken)
}
]
};
let yAxisDaysTaken = {
label: 'Requests',
values: 4
};
let xAxisDaysTaken = {
label: 'Working days',
valuesEvery: 20
};
let $chartDaysTaken = Charter.createBarChart(chartDaysTaken, yAxisDaysTaken, xAxisDaysTaken);
chart($chartDaysTaken);
While it can be useful to look at the data this way, it does obscure which requests were extended, and by how much. Seeing how many allowed days were remaining requires combining the previous two measures.
I also reversed the order of the labels so the chart would be more intuitive, showing delayed requests further to the right, and split the data between extended and un-extended requests.
let getDaysRemaining = function (row) {
let daysAllowed = row[cols.DAYS_ALLOWED];
let daysTaken = row[cols.DAYS_TAKEN];
let daysRemaining = daysAllowed - daysTaken;
return daysRemaining;
};
cols.DAYS_REMAINING = rows.addDerivedCol(getDaysRemaining);
let maxDaysRemaining = Stats.max(rows.getCol(cols.DAYS_REMAINING));
log('Maximum days remaining:', maxDaysRemaining);
let minDaysRemaining = Stats.min(rows.getCol(cols.DAYS_REMAINING));
log('Minimum days remaining:', minDaysRemaining);
const labelsDaysRemaining = Stats.intRange(20, -35);
let chartDaysRemaining = {
title: 'ACC OIA days remaining 2017-07-01 – 2017-12-30',
labels: labelsDaysRemaining,
showLegend: true,
dataSeries: [
{
name: 'Extended requests',
color: '#43ADB9',
dataPoints: rows.filter(cols.EXTENDED, true).getColAsDataSeries(cols.DAYS_REMAINING, labelsDaysRemaining)
},
{
name: 'Unextended requests',
color: '#2B6346',
dataPoints: rows.filter(cols.EXTENDED, false).getColAsDataSeries(cols.DAYS_REMAINING, labelsDaysRemaining)
}
],
stacked: true
};
let yAxisDaysRemaining = {
label: 'Requests',
max: 120,
values: 3
};
let xAxisDaysRemaining = {
label: 'Working days',
valuesEvery: 5
};
let $chartDaysRemaining = Charter.createBarChart(chartDaysRemaining, yAxisDaysRemaining, xAxisDaysRemaining);
chart($chartDaysRemaining);
The length of extensions has to be calculated by subtracting the original 20 working days from the calculated number of working days allowed, based on the due date:
let getExtensionLength = function (row) {
if (row[cols.EXTENDED]) {
let allowed = row[cols.DAYS_ALLOWED];
let extensionDuration = allowed - 20;
return extensionDuration;
}
};
cols.EXTENDED_DURATION = rows.addDerivedCol(getExtensionLength);
let maxExtensionLength = Stats.max(rows.getCol(cols.EXTENDED_DURATION));
log('Longest extension duration:', maxExtensionLength);
const labelsExtensionLength = Stats.intRange(0, 30);
let chartExtensionLength = {
title: 'ACC OIA extension length 2017-07-01 – 2017-12-30',
labels: labelsExtensionLength,
dataSeries: [
{
name: 'Extension duration',
color: '#42778D',
dataPoints: rows.getColAsDataSeries(cols.EXTENDED_DURATION, labelsExtensionLength)
}
],
stacked: true
};
let yAxisExtensionLength = {
label: 'Requests',
max: 20,
values: 4
};
let xAxisExtensionLength = {
label: 'Working days',
valuesEvery: 5
};
let $chartExtensionLength = Charter.createBarChart(chartExtensionLength, yAxisExtensionLength, xAxisExtensionLength);
chart($chartExtensionLength);
Calculating the days remaining when a request was extended is similar to calculating the days remaining when it was completed. Because all requests had 20 working days available before being extended, I didn't need to first calculate the days taken to extend the request before I could calculate the days remaining when the request was extended:
let getExtensionDaysRemaining = function (row) {
let startDate = row[cols.RECEIVED_DATE];
let extensionDate = row[cols.EXTENDED_DATE];
let daysAllowed = 20;
if (row[cols.EXTENDED]) {
let daysTaken = workingDays.between(startDate, extensionDate);
let daysRemaining = daysAllowed - daysTaken;
return daysRemaining;
} else {
// The request was not extended
return undefined;
}
};
cols.EXTENDED_DAYS_REMAINING = rows.addDerivedCol(getExtensionDaysRemaining);
let extendedRequests = rows.filter(cols.EXTENDED, true);
let maxExtensionDaysRemaining = Stats.max(extendedRequests.getCol(cols.EXTENDED_DAYS_REMAINING));
log('Maximum days remaining when request extended:', maxExtensionDaysRemaining);
let minExtensionDaysRemaining = Stats.min(extendedRequests.getCol(cols.EXTENDED_DAYS_REMAINING));
log('Minimum days remaining when request extended:', minExtensionDaysRemaining);
const labelsExtensionDaysRemaining = Stats.intRange(15, 0);
let chartExtensionDaysRemaining = {
title: 'ACC OIA days remaining when extended 2017-07-01 – 2017-12-30',
labels: labelsExtensionDaysRemaining,
dataSeries: [
{
name: 'Working days remaining when extended',
color: '#415E75',
dataPoints: extendedRequests.getColAsDataSeries(cols.EXTENDED_DAYS_REMAINING, labelsExtensionDaysRemaining)
}
]
};
let yAxisExtensionDaysRemaining = {
label: 'Requests',
max: 20,
values: 4
};
let xAxisExtensionDaysRemaining = {
label: 'Working days',
valuesEvery: 5
};
let $chartExtensionDaysRemaining = Charter.createBarChart(chartExtensionDaysRemaining, yAxisExtensionDaysRemaining, xAxisExtensionDaysRemaining);
chart($chartExtensionDaysRemaining);
Ministry of Business, Innovation and Employment
After having earlier analysed data released from the State Services Commission, I sent identical requests to 11 agencies in April 2018, including the Ministry of Business, Innovation and Employment. Here's what I asked them:
Please release the following information, from 1 July 2017 to 30 December 2017, broken down by request:
1. The date on which the request was received.
OIA Response Times | Mark Hanna
2. The date on which the request was answered.
2a. If the request was answered on the last allowable day, the time at which the request was answered
3. Whether or not the request was extended under section 15A of the OIA.
3a. If the request was extended, the date on which notice of the extension was sent to the requester.
3b. If the notice of extension was sent to the requester on the last allowable day, the time at which the notice of extension was sent to the requester.
3c. If the request was extended, the duration of the extension.
4. The outcome of the request (e.g. all information released, transferred to another agency).
It's worth noting that the range of dates I used here is shorter. I had asked the SSC for 12 months of data, but I decided in my follow-ups to ask other agencies for 6 months of data instead, to make it easier to extract.
Yes, I have noticed that I forgot the 31st of December exists. Thankfully the OIA takes a break at that time of year, so I don't expect my mistake in forgetting it will have made much of a difference.
The Ministry of Business, Innovation and Employment sent me the data in a spreadsheet:
DOIA 1718 1483 Final spreadsheet.xlsx
I converted this spreadsheet to CSV format using Google Sheets.
DOIA 1718 1483 Final spreadsheet.xlsx - Sheet1.csv
This data was loaded into Charter using this code:
const cols = Analyser.getColNumbers({
ID: 'A',
RECEIVED_DATE: 'B',
DUE_DATE: 'C',
RESPONDED_DATE: 'D',
EXTENDED_DATE: 'F',
EXTENDED: 'E'
});
const fileConfig = {
headerRows: 1,
cols
};
Analyser.loadFile('../data/mbie/DOIA 1718 1483 Final spreadsheet.xlsx - Sheet1.csv', fileConfig, analyseData);
Some of the requests in the spreadsheet MBIE provided had extended dates before their start dates.
In each of these cases, it seemed very likely that the cause of the issue was that the wrong year had been entered. In some cases, the previous year had been entered. In the others, "07" had been entered instead of "17".
To solve this, I found all the requests with due dates before their received dates, and shifted the due date from 2018 to 2019. I then checked this change by looking at the number of working days between the received date and the updated due date.
Likewise, there were some requests where the extended date was recorded as being after the responded date, because they were recorded as being in 2018 instead of 2017.
let badRows = [];
rows.forEach(row => {
let receivedDate = new Date(row[cols.RECEIVED_DATE]);
if (row[cols.EXTENDED_DATE]) {
let extendedDate = new Date(row[cols.EXTENDED_DATE]);
if (extendedDate < receivedDate) {
badRows.push(row[cols.ID]);
let year = extendedDate.getFullYear();
if (year < 2010) {
row[cols.EXTENDED_DATE] = row[cols.EXTENDED_DATE].replace(/-07$/, '17');
} else {
year -= 2000;
row[cols.EXTENDED_DATE] = row[cols.EXTENDED_DATE].replace(year, year + 1);
}
}
let respondedDate = new Date(row[cols.RESPONDED_DATE]);
if (respondedDate < extendedDate) {
badRows.push(row[cols.ID]);
let year = extendedDate.getFullYear() - 2000;
row[cols.EXTENDED_DATE] = row[cols.EXTENDED_DATE].replace(year, year - 1);
}
}
});
log('Rows with incorrect extended dates:', badRows.join(', '), `(${badRows.length} total)`);
The "Date sent" column included some non-date values such as "transferred" and "withdrawn":
cols.INCOMPLETE_STATUS = rows.addDerivedCol(row => {
let status = row[cols.RESPONDED_DATE];
let date = new Date(status);
let isValidDate = !isNaN(date.getDate());
if (!isValidDate) {
return status;
}
});
log(rows.getColSummary(cols.INCOMPLETE_STATUS));
Because I only wanted to examine completed requests, I removed these requests from my analysis.
let numRows = rows.length;
rows = rows.filter(cols.RESPONDED_DATE, dateString => {
let date = new Date(dateString);
let isValidDate = !isNaN(date.getDate());
return isValidDate;
});
let numRowsRemoved = numRows - rows.length;
log('Number of rows removed:', numRowsRemoved);
Because the spreadsheet uses "Yes" and "No" to represent whether or not a request was extended, some code is necessary to recode that into the more usable true and false:
log('"Extended" column before recoding:', rows.getColSummary(cols.EXTENDED));
cols.EXTENDED = rows.addDerivedCol(row => row[cols.EXTENDED] === 'Yes');
log('"Extended" column after recoding:', rows.getColSummary(cols.EXTENDED));
let getWorkingDaysAllowed = function (row) {
// MBIE provided the date a request was received, and the date it was due
let received = row[cols.RECEIVED_DATE];
let due = row[cols.DUE_DATE];
let daysAllowed = workingDays.between(received, due);
return daysAllowed;
};
cols.DAYS_ALLOWED = rows.addDerivedCol(getWorkingDaysAllowed);
let maxDaysAllowed = Stats.max(rows.getCol(cols.DAYS_ALLOWED));
log('Max working days allowed:', maxDaysAllowed);
let labelsDaysAllowed = Stats.intRange(20, maxDaysAllowed);
let chartDaysAllowed = {
title: 'Working days allowed',
labels: labelsDaysAllowed,
dataSeries: [
{
name: 'Working days allowed',
color: '#43ADB9',
dataPoints: rows.getColAsDataSeries(cols.DAYS_ALLOWED, labelsDaysAllowed)
}
]
};
let yAxisDaysAllowed = {
label: 'Requests',
max: 600,
values: 1,
valuesAt: [25, 50, 200, 400]
};
let xAxisDaysAllowed = {
label: 'Working days',
valuesEvery: 5
};
let $chartDaysAllowed = Charter.createBarChart(chartDaysAllowed, yAxisDaysAllowed, xAxisDaysAllowed);
chart($chartDaysAllowed);
Then, I calculated the number of working days taken to complete each request:
let getDaysTaken = function (row) {
let startDate = row[cols.RECEIVED_DATE];
let endDate = row[cols.RESPONDED_DATE];
let daysBetween = workingDays.between(startDate, endDate);
return daysBetween;
};
cols.DAYS_TAKEN = rows.addDerivedCol(getDaysTaken);
let maxDaysTaken = Stats.max(rows.getCol(cols.DAYS_TAKEN));
log('Maximum days taken:', maxDaysTaken);
const labelsDaysTaken = Stats.intRange(0, maxDaysTaken);
let chartDaysTaken = {
title: 'Working days taken',
labels: labelsDaysTaken,
dataSeries: [
{
name: 'Working days taken',
color: '#43ADB9',
dataPoints: rows.getColAsDataSeries(cols.DAYS_TAKEN, labelsDaysTaken)
}
]
};
let yAxisDaysTaken = {
label: 'Requests',
values: 4
};
let xAxisDaysTaken = {
label: 'Working days',
valuesEvery: 20
};
let $chartDaysTaken = Charter.createBarChart(chartDaysTaken, yAxisDaysTaken, xAxisDaysTaken);
chart($chartDaysTaken);
While it can be useful to look at the data this way, it does obscure which requests were extended, and by how much. Seeing how many allowed days were remaining requires combining the previous two measures.
I also reversed the order of the labels so the chart would be more intuitive, showing delayed requests further to the right, and split the data between extended and un-extended requests.
let getDaysRemaining = function (row) {
let daysAllowed = row[cols.DAYS_ALLOWED];
let daysTaken = row[cols.DAYS_TAKEN];
let daysRemaining = daysAllowed - daysTaken;
return daysRemaining;
};
cols.DAYS_REMAINING = rows.addDerivedCol(getDaysRemaining);
let maxDaysRemaining = Stats.max(rows.getCol(cols.DAYS_REMAINING));
log('Maximum days remaining:', maxDaysRemaining);
let minDaysRemaining = Stats.min(rows.getCol(cols.DAYS_REMAINING));
log('Minimum days remaining:', minDaysRemaining);
const labelsDaysRemaining = Stats.intRange(20, -20);
let chartDaysRemaining = {
title: 'Ministry of Business, Innovation and Employment OIA days remaining 2017-07-01 – 2017-12-30',
labels: labelsDaysRemaining,
showLegend: true,
dataSeries: [
{
name: 'Extended requests',
color: '#43ADB9',
dataPoints: rows.filter(cols.EXTENDED, true).getColAsDataSeries(cols.DAYS_REMAINING, labelsDaysRemaining)
},
{
name: 'Unextended requests',
color: '#2B6346',
dataPoints: rows.filter(cols.EXTENDED, false).getColAsDataSeries(cols.DAYS_REMAINING, labelsDaysRemaining)
}
],
stacked: true
};
let yAxisDaysRemaining = {
label: 'Requests',
max: 300,
values: 3
};
let xAxisDaysRemaining = {
label: 'Working days',
valuesEvery: 10
};
let $chartDaysRemaining = Charter.createBarChart(chartDaysRemaining, yAxisDaysRemaining, xAxisDaysRemaining);
chart($chartDaysRemaining);
The length of extensions has to be calculated by subtracting the original 20 working days from the calculated number of working days allowed, based on the due date:
let getExtensionLength = function (row) {
if (row[cols.EXTENDED]) {
let allowed = row[cols.DAYS_ALLOWED];
let extensionDuration = allowed - 20;
return extensionDuration;
}
};
cols.EXTENDED_DURATION = rows.addDerivedCol(getExtensionLength);
let maxExtensionLength = Stats.max(rows.getCol(cols.EXTENDED_DURATION));
log('Longest extension duration:', maxExtensionLength);
const labelsExtensionLength = Stats.intRange(0, 40);
let chartExtensionLength = {
title: 'Ministry of Business, Innovation and Employment OIA extension length 2017-07-01 – 2017-12-30',
labels: labelsExtensionLength,
dataSeries: [
{
name: 'Extension duration',
color: '#42778D',
dataPoints: rows.getColAsDataSeries(cols.EXTENDED_DURATION, labelsExtensionLength)
}
],
stacked: true
};
let yAxisExtensionLength = {
label: 'Requests',
max: 60,
values: 3
};
let xAxisExtensionLength = {
label: 'Working days',
valuesEvery: 10
};
let $chartExtensionLength = Charter.createBarChart(chartExtensionLength, yAxisExtensionLength, xAxisExtensionLength);
chart($chartExtensionLength);
Calculating the days remaining when a request was extended is similar to calculating the days remaining when it was completed. Because all requests had 20 working days available before being extended, I didn't need to first calculate the days taken to extend the request before I could calculate the days remaining when the request was extended.
MBIE didn't provide an extension date for every extended request, so I had to ignore some of them for this analysis. One request was also marked with its extension date as "n/a", so I also removed that one from this analysis.
let getExtensionDaysRemaining = function (row) {
let startDate = row[cols.RECEIVED_DATE];
let extensionDate = row[cols.EXTENDED_DATE];
let daysAllowed = 20;
if (row[cols.EXTENDED]) {
let date = new Date(extensionDate);
if (isNaN(date.getDate())) {
// We don't know when this request was extended, so ignore it
return undefined;
}
let daysTaken = workingDays.between(startDate, extensionDate);
let daysRemaining = daysAllowed - daysTaken;
return daysRemaining;
} else {
// The request was not extended
return undefined;
}
};
cols.EXTENDED_DAYS_REMAINING = rows.addDerivedCol(getExtensionDaysRemaining);
let extendedRequests = rows.filter(cols.EXTENDED, true);
let maxExtensionDaysRemaining = Stats.max(extendedRequests.getCol(cols.EXTENDED_DAYS_REMAINING));
log('Maximum days remaining when request extended:', maxExtensionDaysRemaining);
let minExtensionDaysRemaining = Stats.min(extendedRequests.getCol(cols.EXTENDED_DAYS_REMAINING));
log('Minimum days remaining when request extended:', minExtensionDaysRemaining);
const labelsExtensionDaysRemaining = Stats.intRange(20, -20);
let chartExtensionDaysRemaining = {
title: 'MBIE OIA days remaining when extended 2017-07-01 – 2017-12-30',
labels: labelsExtensionDaysRemaining,
dataSeries: [
{
name: 'Working days remaining when extended',
color: '#415E75',
dataPoints: extendedRequests.getColAsDataSeries(cols.EXTENDED_DAYS_REMAINING, labelsExtensionDaysRemaining)
}
]
};
let yAxisExtensionDaysRemaining = {
label: 'Requests',
max: 80,
values: 4
};
let xAxisExtensionDaysRemaining = {
label: 'Working days',
valuesEvery: 5
};
let $chartExtensionDaysRemaining = Charter.createBarChart(chartExtensionDaysRemaining, yAxisExtensionDaysRemaining, xAxisExtensionDaysRemaining);
chart($chartExtensionDaysRemaining);
Ministry of Social Development
After having earlier analysed data released from the State Services Commission, I sent identical requests to 11 agencies in April 2018, including the Ministry of Social Development. Here's what I asked them:
Please release the following information, from 1 July 2017 to 30 December 2017, broken down by request:
1. The date on which the request was received.
OIA Response Times | Mark Hanna
2. The date on which the request was answered.
2a. If the request was answered on the last allowable day, the time at which the request was answered
3. Whether or not the request was extended under section 15A of the OIA.
3a. If the request was extended, the date on which notice of the extension was sent to the requester.
3b. If the notice of extension was sent to the requester on the last allowable day, the time at which the notice of extension was sent to the requester.
3c. If the request was extended, the duration of the extension.
4. The outcome of the request (e.g. all information released, transferred to another agency).
It's worth noting that the range of dates I used here is shorter. I had asked the SSC for 12 months of data, but I decided in my follow-ups to ask other agencies for 6 months of data instead, to make it easier to extract.
Yes, I have noticed that I forgot the 31st of December exists. Thankfully the OIA takes a break at that time of year, so I don't expect my mistake in forgetting it will have made much of a difference.
The Ministry of Social Development sent me the data in a spreadsheet:
Hanna data for OIA response.xlsx
I converted this spreadsheet to CSV format using Google Sheets.
Hanna data for OIA response.xlsx - Sheet1.csv
This data was loaded into Charter using this code:
const cols = Analyser.getColNumbers({
ID: 'A',
RECEIVED_DATE: 'B',
DUE_DATE: 'C',
RESPONDED_DATE_RAW: 'E',
EXTENDED_DATE: 'D',
INTERNAL_DUE_DATE: 'F',
INFORMATION_SENT_DATE: 'G'
});
const fileConfig = {
headerRows: 3,
cols
};
Analyser.loadFile('../data/msd/Hanna data for OIA response.xlsx - Sheet1.csv', fileConfig, analyseData);
Unfortunately, MSD provided the dates in the format 'dd/mm/yyyy', which is ambiguous in some contexts thanks to Americans using the format 'mm/dd/yyyy'. So in order to ensure consistency, I converted them into the unambiguous format 'yyyy-mm-dd':
log('Example cell from "Responded" column before recoding:', rows[7][cols.RESPONDED_DATE_RAW]);
let convertDate = function (dateString) {
let formatPattern = /(\d+)\/(\d+)\/(\d+)/;
let match = dateString.match(formatPattern);
if (match) {
let day = match[1];
let month = match[2];
let year = match[3];
let newDateString = `${year}-${month.padStart(2, '0')}-${day.padStart(2, '0')}`;
return newDateString;
} else {
return dateString;
}
};
rows.forEach(row => {
row[cols.RECEIVED_DATE] = convertDate(row[cols.RECEIVED_DATE]);
row[cols.DUE_DATE] = convertDate(row[cols.DUE_DATE]);
row[cols.RESPONDED_DATE_RAW] = convertDate(row[cols.RESPONDED_DATE_RAW]);
row[cols.EXTENDED_DATE] = convertDate(row[cols.EXTENDED_DATE]);
row[cols.INTERNAL_DUE_DATE] = convertDate(row[cols.INTERNAL_DUE_DATE]);
row[cols.INFORMATION_SENT_DATE] = convertDate(row[cols.INFORMATION_SENT_DATE]);
});
log('Example cell from "Responded" column after recoding:', rows[7][cols.RESPONDED_DATE_RAW]);
In cases where a request was transferred or withdrawn, or in a few other cases where the information was not available, MSD recorded most of the dates as "nil". I excluded requests in these categories from my analysis:
let isValidDateString = function (dateString) {
let date = new Date(dateString);
let isValidDate = !isNaN(date.getDate());
return isValidDate;
};
let numRows = rows.length;
rows = rows.filter(
cols.INFORMATION_SENT_DATE, isValidDateString
);
let numRowsRemoved = numRows - rows.length;
log('Requests removed:', numRowsRemoved);
MSD also did not record a value for the date on which a decision was communicated if the information was sent to the requester before the request's due date. So in cases where no decision date was recorded, but there was a date recorded in the "Date response sent to Requestor" column, I added this date to the "Date Decision Communicated" column:
let numUpdatedRows = 0;
let getRespondedDate = function (row) {
let recordedRespondedDate = row[cols.RESPONDED_DATE_RAW];
let informationSentDate = row[cols.INFORMATION_SENT_DATE];
let respondedDate;
if (isValidDateString(recordedRespondedDate)) {
respondedDate = recordedRespondedDate;
} else {
respondedDate = informationSentDate;
}
if (respondedDate !== recordedRespondedDate) {
numUpdatedRows += 1;
}
return respondedDate;
};
cols.RESPONDED_DATE = rows.addDerivedCol(getRespondedDate);
log(`${numUpdatedRows} rows updated`);
There were some cases where MSD recorded a request as having been responded to before it was received. It was not immediately clear what the correct data should be, so I removed these requests from my analysis:
let badRowIds = [];
for (let i = 0; i < rows.length; i++) {
let row = rows[i];
let startDate = new Date(row[cols.RECEIVED_DATE]);
let endDate = new Date(row[cols.RESPONDED_DATE]);
if (startDate > endDate) {
badRowIds.push(row[cols.ID]);
}
}
rows = rows.filter(cols.ID, id => !badRowIds.includes(id));
log(`${badRowIds.length} removed`);
The spreadsheet included an "Extension due date to Requestor" column but not one that just marked whether or not a request was extended. I checked for the presence of a valid extension date to mark whether or not a request was extended:
cols.EXTENDED = rows.addDerivedCol(row => isValidDateString(row[cols.EXTENDED_DATE]));
log('"Extended" column after recoding:', rows.getColSummary(cols.EXTENDED));
let getWorkingDaysAllowed = function (row) {
// MSD provided the date a request was received, and the date it was due
let received = row[cols.RECEIVED_DATE];
let due = row[cols.EXTENDED] ? row[cols.EXTENDED_DATE] : row[cols.DUE_DATE];
if (isValidDateString(received) && isValidDateString(due)) {
let daysAllowed = workingDays.between(received, due);
return daysAllowed;
}
};
cols.DAYS_ALLOWED = rows.addDerivedCol(getWorkingDaysAllowed);
let maxDaysAllowed = Stats.max(rows.getCol(cols.DAYS_ALLOWED));
log('Max working days allowed:', maxDaysAllowed);
let labelsDaysAllowed = Stats.intRange(20, maxDaysAllowed);
let chartDaysAllowed = {
title: 'Working days allowed',
labels: labelsDaysAllowed,
dataSeries: [
{
name: 'Working days allowed',
color: '#43ADB9',
dataPoints: rows.getColAsDataSeries(cols.DAYS_ALLOWED, labelsDaysAllowed)
}
]
};
let yAxisDaysAllowed = {
label: 'Requests',
max: 180,
values: 3
};
let xAxisDaysAllowed = {
label: 'Working days',
valuesEvery: 5
};
let $chartDaysAllowed = Charter.createBarChart(chartDaysAllowed, yAxisDaysAllowed, xAxisDaysAllowed);
chart($chartDaysAllowed);
Then, I calculated the number of working days taken to complete each request:
let getDaysTaken = function (row) {
let startDate = row[cols.RECEIVED_DATE];
let endDate = row[cols.RESPONDED_DATE];
let daysBetween = workingDays.between(startDate, endDate);
return daysBetween;
};
cols.DAYS_TAKEN = rows.addDerivedCol(getDaysTaken);
let maxDaysTaken = Stats.max(rows.getCol(cols.DAYS_TAKEN));
log('Maximum days taken:', maxDaysTaken);
const labelsDaysTaken = Stats.intRange(0, maxDaysTaken);
let chartDaysTaken = {
title: 'Working days taken',
labels: labelsDaysTaken,
dataSeries: [
{
name: 'Working days taken',
color: '#43ADB9',
dataPoints: rows.getColAsDataSeries(cols.DAYS_TAKEN, labelsDaysTaken)
}
]
};
let yAxisDaysTaken = {
label: 'Requests',
values: 4
};
let xAxisDaysTaken = {
label: 'Working days',
valuesEvery: 20
};
let $chartDaysTaken = Charter.createBarChart(chartDaysTaken, yAxisDaysTaken, xAxisDaysTaken);
chart($chartDaysTaken);
While it can be useful to look at the data this way, it does obscure which requests were extended, and by how much. Seeing how many allowed days were remaining requires combining the previous two measures.
I also reversed the order of the labels so the chart would be more intuitive, showing delayed requests further to the right, and split the data between extended and un-extended requests.
let getDaysRemaining = function (row) {
let daysAllowed = row[cols.DAYS_ALLOWED];
let daysTaken = row[cols.DAYS_TAKEN];
let daysRemaining = daysAllowed - daysTaken;
return daysRemaining;
};
cols.DAYS_REMAINING = rows.addDerivedCol(getDaysRemaining);
let maxDaysRemaining = Stats.max(rows.getCol(cols.DAYS_REMAINING));
log('Maximum days remaining:', maxDaysRemaining);
let minDaysRemaining = Stats.min(rows.getCol(cols.DAYS_REMAINING));
log('Minimum days remaining:', minDaysRemaining);
const labelsDaysRemaining = Stats.intRange(20, -20);
let chartDaysRemaining = {
title: 'MSD OIA days remaining 2017-07-01 – 2017-12-30',
labels: labelsDaysRemaining,
showLegend: true,
dataSeries: [
{
name: 'Extended requests',
color: '#43ADB9',
dataPoints: rows.filter(cols.EXTENDED, true).getColAsDataSeries(cols.DAYS_REMAINING, labelsDaysRemaining)
},
{
name: 'Unextended requests',
color: '#2B6346',
dataPoints: rows.filter(cols.EXTENDED, false).getColAsDataSeries(cols.DAYS_REMAINING, labelsDaysRemaining)
}
],
stacked: true
};
let yAxisDaysRemaining = {
label: 'Requests',
max: 90,
values: 3
};
let xAxisDaysRemaining = {
label: 'Working days',
valuesEvery: 10
};
let $chartDaysRemaining = Charter.createBarChart(chartDaysRemaining, yAxisDaysRemaining, xAxisDaysRemaining);
chart($chartDaysRemaining);
MSD often send notice of a decision before releasing the requested information. In these cases, they set an internal deadline. Calculating the days remaining for this deadline is the same as calculating the days remaining for the standard legal deadline, just using different dates.
let internalRows = rows.filter(cols.INTERNAL_DUE_DATE, isValidDateString);
let getWorkingDaysAllowedInternal = function (row) {
// MSD provided the date a request was received, and the date it was due
let received = row[cols.RECEIVED_DATE];
let due = row[cols.INTERNAL_DUE_DATE];
if (isValidDateString(received) && isValidDateString(due)) {
let daysAllowed = workingDays.between(received, due);
return daysAllowed;
}
};
cols.DAYS_ALLOWED_INTERNAL = rows.addDerivedCol(getWorkingDaysAllowedInternal);
let maxDaysAllowedInternal = Stats.max(internalRows.getCol(cols.DAYS_ALLOWED_INTERNAL));
log('Max working days allowed:', maxDaysAllowedInternal);
let labelsDaysAllowedInternal = Stats.intRange(20, maxDaysAllowedInternal);
let chartDaysAllowedInternal = {
title: 'Working days allowed',
labels: labelsDaysAllowedInternal,
dataSeries: [
{
name: 'Working days allowed',
color: '#43ADB9',
dataPoints: internalRows.getColAsDataSeries(cols.DAYS_ALLOWED_INTERNAL, labelsDaysAllowedInternal)
}
]
};
let yAxisDaysAllowedInternal = {
label: 'Requests',
max: 10,
values: 2
};
let xAxisDaysAllowedInternal = {
label: 'Working days',
valuesEvery: 5
};
let $chartDaysAllowedInternal = Charter.createBarChart(chartDaysAllowedInternal, yAxisDaysAllowedInternal, xAxisDaysAllowedInternal);
chart($chartDaysAllowedInternal);
Then, I calculated the number of working days taken to send the information for each of these requests:
let getDaysTakenInternal = function (row) {
let startDate = row[cols.RECEIVED_DATE];
let endDate = row[cols.INFORMATION_SENT_DATE];
let daysBetween = workingDays.between(startDate, endDate);
return daysBetween;
};
cols.DAYS_TAKEN_INTERNAL = rows.addDerivedCol(getDaysTakenInternal);
let maxDaysTakenInternal = Stats.max(internalRows.getCol(cols.DAYS_TAKEN_INTERNAL));
log('Maximum days taken:', maxDaysTakenInternal);
const labelsDaysTakenInternal = Stats.intRange(0, maxDaysTakenInternal);
let chartDaysTakenInternal = {
title: 'Working days taken',
labels: labelsDaysTakenInternal,
dataSeries: [
{
name: 'Working days taken',
color: '#43ADB9',
dataPoints: internalRows.getColAsDataSeries(cols.DAYS_TAKEN_INTERNAL, labelsDaysTakenInternal)
}
]
};
let yAxisDaysTakenInternal = {
label: 'Requests',
values: 4
};
let xAxisDaysTakenInternal = {
label: 'Working days',
valuesEvery: 20
};
let $chartDaysTakenInternal = Charter.createBarChart(chartDaysTakenInternal, yAxisDaysTakenInternal, xAxisDaysTakenInternal);
chart($chartDaysTakenInternal);
The number of days remaining on the internal deadline is just the difference between the days allowed and the days taken.
let getDaysRemainingInternal = function (row) {
let daysAllowed = row[cols.DAYS_ALLOWED_INTERNAL];
let daysTaken = row[cols.DAYS_TAKEN_INTERNAL];
let daysRemaining = daysAllowed - daysTaken;
return daysRemaining;
};
cols.DAYS_REMAINING_INTERNAL = rows.addDerivedCol(getDaysRemainingInternal);
let maxDaysRemainingInternal = Stats.max(internalRows.getCol(cols.DAYS_REMAINING_INTERNAL));
log('Maximum days remaining:', maxDaysRemainingInternal);
let minDaysRemainingInternal = Stats.min(internalRows.getCol(cols.DAYS_REMAINING_INTERNAL));
log('Minimum days remaining:', minDaysRemainingInternal);
const labelsDaysRemainingInternal = Stats.intRange(15, -25);
let chartDaysRemainingInternal = {
title: 'MSD OIA days remaining (internal deadline) 2017-07-01 – 2017-12-30',
labels: labelsDaysRemainingInternal,
showLegend: true,
dataSeries: [
{
name: 'Extended requests',
color: '#43ADB9',
dataPoints: internalRows.filter(cols.EXTENDED, true).getColAsDataSeries(cols.DAYS_REMAINING_INTERNAL, labelsDaysRemainingInternal)
},
{
name: 'Unextended requests',
color: '#2B6346',
dataPoints: internalRows.filter(cols.EXTENDED, false).getColAsDataSeries(cols.DAYS_REMAINING_INTERNAL, labelsDaysRemainingInternal)
}
],
stacked: true
};
let yAxisDaysRemainingInternal = {
label: 'Requests',
max: 20,
values: 5
};
let xAxisDaysRemainingInternal = {
label: 'Working days',
valuesEvery: 5
};
let $chartDaysRemainingInternal = Charter.createBarChart(chartDaysRemainingInternal, yAxisDaysRemainingInternal, xAxisDaysRemainingInternal);
chart($chartDaysRemainingInternal);
Ministry of Health
After having earlier analysed data released from the State Services Commission, I sent identical requests to 11 agencies in April 2018, including the Ministry of Health. Here's what I asked them:
Please release the following information, from 1 July 2017 to 30 December 2017, broken down by request:
1. The date on which the request was received.
OIA Response Times | Mark Hanna
2. The date on which the request was answered.
2a. If the request was answered on the last allowable day, the time at which the request was answered
3. Whether or not the request was extended under section 15A of the OIA.
3a. If the request was extended, the date on which notice of the extension was sent to the requester.
3b. If the notice of extension was sent to the requester on the last allowable day, the time at which the notice of extension was sent to the requester.
3c. If the request was extended, the duration of the extension.
4. The outcome of the request (e.g. all information released, transferred to another agency).
It's worth noting that the range of dates I used here is shorter. I had asked the SSC for 12 months of data, but I decided in my follow-ups to ask other agencies for 6 months of data instead, to make it easier to extract.
Yes, I have noticed that I forgot the 31st of December exists. Thankfully the OIA takes a break at that time of year, so I don't expect my mistake in forgetting it will have made much of a difference.
The Ministry of Health sent me the data in a spreadsheet:
OIA Data for Req H201802830.xlsx
I converted this spreadsheet to CSV format using Google Sheets.
OIA Data for Req H201802830.xlsx - Sheet1.csv
This data was loaded into Charter using this code:
const cols = Analyser.getColNumbers({
RECEIVED_DATE: 'A',
RESPONDED_DATE: 'B',
EXTENDED_DATE: 'D',
EXTENDED_DURATION: 'F'
});
const fileConfig = {
headerRows: 2,
cols
};
Analyser.loadFile('../data/moh/OIA Data for Req H201802830.xlsx - Sheet1.csv', fileConfig, analyseData);
The Ministry of Health included a request which had not yet been completed, which had no responded date. Because I meant to analyse how long it had taken to respond to requests, I removed this request from my analysis:
let numRows = rows.length;
rows = rows.filter(cols.RESPONDED_DATE, date => !!date);
let numRemovedRows = numRows - rows.length;
log('Requests removed due to lack of a responded date:', numRemovedRows);
Unfortunately, the Minsitry of Health provided dates in the format 'dd/mm/yy', which is ambiguous in some contexts thanks to Americans using the format 'mm/dd/yyyy'. So in order to ensure consistency, I converted them into the unambiguous format 'yyyy-mm-dd':
log('Example cell from "Responded" column before recoding:', rows[0][cols.RESPONDED_DATE]);
let convertDate = function (dateString) {
let formatPattern = /(\d+)\/(\d+)\/(\d+)/;
let match = dateString.match(formatPattern);
if (match) {
let day = match[1];
let month = match[2];
let year = match[3];
if (parseInt(year, 10) < 2000) {
year = '20' + year;
}
let newDateString = `${year}-${month.padStart(2, '0')}-${day.padStart(2, '0')}`;
return newDateString;
} else {
return null;
}
};
rows.forEach(row => {
row[cols.RECEIVED_DATE] = convertDate(row[cols.RECEIVED_DATE]);
row[cols.RESPONDED_DATE] = convertDate(row[cols.RESPONDED_DATE]);
row[cols.EXTENDED_DATE] = convertDate(row[cols.EXTENDED_DATE]);
});
log('Example cell from "Responded" column after recoding:', rows[0][cols.RESPONDED_DATE]);
The Ministry of Health didn't include a column to specify whether or not a request was extended, but they did specify the duration of any extensions so I was able to recode the EXTENDED_DURATION column into an EXTENDED column:
let getExtended = function (row) {
let isExtended = !!row[cols.EXTENDED_DURATION];
return isExtended;
};
cols.EXTENDED = rows.addDerivedCol(getExtended);
log('EXTENDED column after recoding:', rows.getColSummary(cols.EXTENDED));
I also added an "ID" column, to make it easier to identify rows that I wanted to check in the original data.
let nextId = 1;
let getId = function (row) {
let id = nextId;
nextId += 1;
return nextId;
};
cols.ID = rows.addDerivedCol(getId);
I noticed a typo in one of the rows, where the date on which the request was extended was incorrectly recorded as being in 2018 but it clearly should have been recorded as the same date in 2017. I updated this cell directly:
let badRow = rows.filter(cols.ID, 29)[0];
badRow[cols.EXTENDED_DATE] = badRow[cols.EXTENDED_DATE].replace('2018', '2017');
log(`Bad row's received date: ${badRow[cols.RECEIVED_DATE]}`);
log(`Bad row's extended date: ${badRow[cols.EXTENDED_DATE]}`);
log(`Bad row's responded date: ${badRow[cols.RESPONDED_DATE]}`);
let getWorkingDaysAllowed = function (row) {
// The Ministry of Health provided the date a request was received, and the duration of any extension
let received = row[cols.RECEIVED_DATE];
let extensionDuration = row[cols.EXTENDED_DURATION] || 0;
let daysAllowed = 20 + extensionDuration;
return daysAllowed;
};
cols.DAYS_ALLOWED = rows.addDerivedCol(getWorkingDaysAllowed);
let maxDaysAllowed = Stats.max(rows.getCol(cols.DAYS_ALLOWED));
log('Max working days allowed:', maxDaysAllowed);
let labelsDaysAllowed = Stats.intRange(20, maxDaysAllowed);
let chartDaysAllowed = {
title: 'Working days allowed',
labels: labelsDaysAllowed,
dataSeries: [
{
name: 'Working days allowed',
color: '#43ADB9',
dataPoints: rows.getColAsDataSeries(cols.DAYS_ALLOWED, labelsDaysAllowed)
}
]
};
let yAxisDaysAllowed = {
label: 'Requests',
max: 350,
values: 1,
valuesAt: [100, 200, 300]
};
let xAxisDaysAllowed = {
label: 'Working days',
valuesEvery: 5
};
let $chartDaysAllowed = Charter.createBarChart(chartDaysAllowed, yAxisDaysAllowed, xAxisDaysAllowed);
chart($chartDaysAllowed);
Then, I calculated the number of working days taken to complete each request:
let getDaysTaken = function (row) {
let startDate = row[cols.RECEIVED_DATE];
let endDate = row[cols.RESPONDED_DATE];
let daysBetween = workingDays.between(startDate, endDate);
return daysBetween;
};
cols.DAYS_TAKEN = rows.addDerivedCol(getDaysTaken);
let maxDaysTaken = Stats.max(rows.getCol(cols.DAYS_TAKEN));
log('Maximum days taken:', maxDaysTaken);
const labelsDaysTaken = Stats.intRange(0, maxDaysTaken);
let chartDaysTaken = {
title: 'Working days taken',
labels: labelsDaysTaken,
dataSeries: [
{
name: 'Working days taken',
color: '#43ADB9',
dataPoints: rows.getColAsDataSeries(cols.DAYS_TAKEN, labelsDaysTaken)
}
]
};
let yAxisDaysTaken = {
label: 'Requests',
values: 4
};
let xAxisDaysTaken = {
label: 'Working days',
valuesEvery: 20
};
let $chartDaysTaken = Charter.createBarChart(chartDaysTaken, yAxisDaysTaken, xAxisDaysTaken);
chart($chartDaysTaken);
While it can be useful to look at the data this way, it does obscure which requests were extended, and by how much. Seeing how many allowed days were remaining requires combining the previous two measures.
I also reversed the order of the labels so the chart would be more intuitive, showing delayed requests further to the right, and split the data between extended and un-extended requests.
let getDaysRemaining = function (row) {
let daysAllowed = row[cols.DAYS_ALLOWED];
let daysTaken = row[cols.DAYS_TAKEN];
let daysRemaining = daysAllowed - daysTaken;
return daysRemaining;
};
cols.DAYS_REMAINING = rows.addDerivedCol(getDaysRemaining);
let maxDaysRemaining = Stats.max(rows.getCol(cols.DAYS_REMAINING));
log('Maximum days remaining:', maxDaysRemaining);
let minDaysRemaining = Stats.min(rows.getCol(cols.DAYS_REMAINING));
log('Minimum days remaining:', minDaysRemaining);
const labelsDaysRemaining = Stats.intRange(20, -10);
let chartDaysRemaining = {
title: 'Ministry of Health OIA days remaining 2017-07-01 – 2017-12-30',
labels: labelsDaysRemaining,
showLegend: true,
dataSeries: [
{
name: 'Extended requests',
color: '#43ADB9',
dataPoints: rows.filter(cols.EXTENDED, true).getColAsDataSeries(cols.DAYS_REMAINING, labelsDaysRemaining)
},
{
name: 'Unextended requests',
color: '#2B6346',
dataPoints: rows.filter(cols.EXTENDED, false).getColAsDataSeries(cols.DAYS_REMAINING, labelsDaysRemaining)
}
],
stacked: true
};
let yAxisDaysRemaining = {
label: 'Requests',
max: 80,
values: 4
};
let xAxisDaysRemaining = {
label: 'Working days',
valuesEvery: 10
};
let $chartDaysRemaining = Charter.createBarChart(chartDaysRemaining, yAxisDaysRemaining, xAxisDaysRemaining);
chart($chartDaysRemaining);
The length of extensions has to be calculated by subtracting the original 20 working days from the calculated number of working days allowed, based on the due date:
let getExtensionLength = function (row) {
if (row[cols.EXTENDED]) {
let allowed = row[cols.DAYS_ALLOWED];
let extensionDuration = allowed - 20;
return extensionDuration;
}
};
cols.EXTENDED_DURATION = rows.addDerivedCol(getExtensionLength);
let maxExtensionLength = Stats.max(rows.getCol(cols.EXTENDED_DURATION));
log('Longest extension duration:', maxExtensionLength);
const labelsExtensionLength = Stats.intRange(0, 30);
let chartExtensionLength = {
title: 'Ministry of Health OIA extension length 2017-07-01 – 2017-12-30',
labels: labelsExtensionLength,
dataSeries: [
{
name: 'Extension duration',
color: '#42778D',
dataPoints: rows.getColAsDataSeries(cols.EXTENDED_DURATION, labelsExtensionLength)
}
],
stacked: true
};
let yAxisExtensionLength = {
label: 'Requests',
max: 15,
values: 3
};
let xAxisExtensionLength = {
label: 'Working days',
valuesEvery: 5
};
let $chartExtensionLength = Charter.createBarChart(chartExtensionLength, yAxisExtensionLength, xAxisExtensionLength);
chart($chartExtensionLength);
Calculating the days remaining when a request was extended is similar to calculating the days remaining when it was completed. Because all requests had 20 working days available before being extended, I didn't need to first calculate the days taken to extend the request before I could calculate the days remaining when the request was extended.
let getExtensionDaysRemaining = function (row) {
let startDate = row[cols.RECEIVED_DATE];
let extensionDate = row[cols.EXTENDED_DATE];
let daysAllowed = 20;
if (row[cols.EXTENDED]) {
let date = new Date(extensionDate);
let daysTaken = workingDays.between(startDate, extensionDate);
let daysRemaining = daysAllowed - daysTaken;
return daysRemaining;
} else {
// The request was not extended
return undefined;
}
};
cols.EXTENDED_DAYS_REMAINING = rows.addDerivedCol(getExtensionDaysRemaining);
let extendedRequests = rows.filter(cols.EXTENDED, true);
let maxExtensionDaysRemaining = Stats.max(extendedRequests.getCol(cols.EXTENDED_DAYS_REMAINING));
log('Maximum days remaining when request extended:', maxExtensionDaysRemaining);
let minExtensionDaysRemaining = Stats.min(extendedRequests.getCol(cols.EXTENDED_DAYS_REMAINING));
log('Minimum days remaining when request extended:', minExtensionDaysRemaining);
const labelsExtensionDaysRemaining = Stats.intRange(15, 0);
let chartExtensionDaysRemaining = {
title: 'Ministry of Health OIA days remaining when extended 2017-07-01 – 2017-12-30',
labels: labelsExtensionDaysRemaining,
dataSeries: [
{
name: 'Working days remaining when extended',
color: '#415E75',
dataPoints: extendedRequests.getColAsDataSeries(cols.EXTENDED_DAYS_REMAINING, labelsExtensionDaysRemaining)
}
]
};
let yAxisExtensionDaysRemaining = {
label: 'Requests',
max: 15,
values: 3
};
let xAxisExtensionDaysRemaining = {
label: 'Working days',
valuesEvery: 5
};
let $chartExtensionDaysRemaining = Charter.createBarChart(chartExtensionDaysRemaining, yAxisExtensionDaysRemaining, xAxisExtensionDaysRemaining);
chart($chartExtensionDaysRemaining);
Ministry of Education
After having earlier analysed data released from the State Services Commission, I sent identical requests to 11 agencies in April 2018, including the Ministry of Education. Here's what I asked them:
Please release the following information, from 1 July 2017 to 30 December 2017, broken down by request:
1. The date on which the request was received.
OIA Response Times | Mark Hanna
2. The date on which the request was answered.
2a. If the request was answered on the last allowable day, the time at which the request was answered
3. Whether or not the request was extended under section 15A of the OIA.
3a. If the request was extended, the date on which notice of the extension was sent to the requester.
3b. If the notice of extension was sent to the requester on the last allowable day, the time at which the notice of extension was sent to the requester.
3c. If the request was extended, the duration of the extension.
4. The outcome of the request (e.g. all information released, transferred to another agency).
It's worth noting that the range of dates I used here is shorter. I had asked the SSC for 12 months of data, but I decided in my follow-ups to ask other agencies for 6 months of data instead, to make it easier to extract.
Yes, I have noticed that I forgot the 31st of December exists. Thankfully the OIA takes a break at that time of year, so I don't expect my mistake in forgetting it will have made much of a difference.
The Ministry of Education initially sent me some data that wasn't entirely clear when it came to the length of extensions. I sent a follow up to ask for some clarification on that and on some dates that appeared to be incorrect, and they sent me the corrected data in the following spreadsheet:
I converted this spreadsheet to CSV format using Google Sheets.
Follow up detail.xlsx - Sheet1.csv
This data was loaded into Charter using this code:
const cols = Analyser.getColNumbers({
RECEIVED_DATE: 'A',
RESPONDED_DATE: 'B',
EXTENDED: 'C',
EXTENDED_DURATION_RAW: 'D',
EXTENDED_DATE: 'E'
});
const fileConfig = {
headerRows: 1,
cols
};
Analyser.loadFile('../data/moe/Follow up detail.xlsx - Sheet1.csv', fileConfig, analyseData);
I also added an "ID" column, to make it easier to identify rows that I wanted to check in the original data.
let nextId = 1;
let getId = function (row) {
let id = nextId;
nextId += 1;
return nextId;
};
cols.ID = rows.addDerivedCol(getId);
The Ministry of Education specified whether or not a response was extended with "Yes" or "No", which I recoded to true or false:
log('Summary of EXTENDED column before recoding:', rows.getColSummary(cols.EXTENDED));
let getExtended = function (row) {
let extended = row[cols.EXTENDED].trim().toLowerCase();
return extended === 'yes';
};
cols.EXTENDED = rows.addDerivedCol(getExtended);
log('Summary of EXTENDED column:', rows.getColSummary(cols.EXTENDED));
Unfortunately, the Ministry of Education recorded the durations of its extensions inconsistently. In some cases it recorded a number of days, which they clarified meant working days. In other cases, they recorded a number of weeks or months. In recoding this column, I treated "days" as a number of working days, "weeks" as 7 regular days which I then converted back to working days, and "months" as until the same date on a subsequent calendar month which I again converted back to working days:
let getExtensionDuration = function (row) {
let extensionDescription = row[cols.EXTENDED_DURATION_RAW].trim();
let extensionNumber = parseInt(extensionDescription, 10);
let receivedDate = new Date(row[cols.RECEIVED_DATE]);
if (extensionDescription) {
if (/day/i.test(extensionDescription)) {
return extensionNumber;
} else if (/week/i.test(extensionDescription)) {
let daysToAdd = extensionNumber * 7;
let dueDate = workingDays.add(receivedDate, 20);
dueDate.setDate(dueDate.getDate() + daysToAdd);
let extensionLength = workingDays.between(receivedDate, dueDate) - 20;
return extensionLength;
} else if (/month/i.test(extensionDescription)) {
let dueDate = workingDays.add(receivedDate, 20);
dueDate.setMonth(dueDate.getMonth() + extensionNumber);
let extensionLength = workingDays.between(receivedDate, dueDate) - 20;
return extensionLength;
}
}
};
cols.EXTENDED_DURATION = rows.addDerivedCol(getExtensionDuration);
log(rows.getColSummary(cols.EXTENDED_DURATION));
One request had an incorrectly recorded year, putting the date of the response before the date the request was received:
let earlyResponseDateRows = [];
rows.forEach(row => {
let receivedDate = new Date(row[cols.RECEIVED_DATE]);
let respondedDate = new Date(row[cols.RESPONDED_DATE]);
if (respondedDate < receivedDate) {
earlyResponseDateRows.push(row[cols.ID]);
log(`Request received on ${row[cols.RECEIVED_DATE]} but recorded as being responded to on ${row[cols.RESPONDED_DATE]}`);
row[cols.RESPONDED_DATE] = row[cols.RESPONDED_DATE].replace(/17$/, 18);
log(`Response date corrected to ${row[cols.RESPONDED_DATE]}`);
}
});
log(`Rows fixed: ${earlyResponseDateRows.length}`);
There were also two requests that appear to have had their response date recorded as being in 2018 when it should have been 2017. I updated these cells directly as well:
let lateResponseDateRows = [];
rows.forEach(row => {
let receivedDate = new Date(row[cols.RECEIVED_DATE]);
let respondedDate = new Date(row[cols.RESPONDED_DATE]);
// If there's more than a year between them
if (respondedDate - receivedDate > (1000*60*60*24*365)) {
lateResponseDateRows.push(row[cols.ID]);
log(`Request received on ${row[cols.RECEIVED_DATE]} but recorded as being responded to on ${row[cols.RESPONDED_DATE]}`);
row[cols.RESPONDED_DATE] = row[cols.RESPONDED_DATE].replace(/18$/, 17);
log(`Response date corrected to ${row[cols.RESPONDED_DATE]}`);
}
});
log(`Rows fixed: ${lateResponseDateRows.length}`);
Some other requests also had incorrect dates when the extension was sent. I found and fixed these more manually:
let row144 = rows.filter(cols.ID, 144)[0];
log(`Received: ${row144[cols.RECEIVED_DATE]}, Extended: ${row144[cols.EXTENDED_DATE]}, Responded: ${row144[cols.RESPONDED_DATE]}`);
row144[cols.EXTENDED_DATE] = row144[cols.EXTENDED_DATE].replace(/01$/, 17);
log(`Corrected extended date: ${row144[cols.EXTENDED_DATE]}`);
let row296 = rows.filter(cols.ID, 296)[0];
log(`Received: ${row296[cols.RECEIVED_DATE]}, Extended: ${row296[cols.EXTENDED_DATE]}, Responded: ${row296[cols.RESPONDED_DATE]}`);
row296[cols.EXTENDED_DATE] = row296[cols.EXTENDED_DATE].replace(/17$/, 18);
log(`Corrected extended date: ${row296[cols.EXTENDED_DATE]}`);
let row297 = rows.filter(cols.ID, 297)[0];
log(`Received: ${row297[cols.RECEIVED_DATE]}, Extended: ${row297[cols.EXTENDED_DATE]}, Responded: ${row297[cols.RESPONDED_DATE]}`);
row297[cols.EXTENDED_DATE] = row297[cols.EXTENDED_DATE].replace(/17$/, 18);
log(`Corrected extended date: ${row297[cols.EXTENDED_DATE]}`);
There was one request with incorrect dates that I couldn't reconcile, which I removed from my analysis.
let row200 = rows.filter(cols.ID, 200)[0];
log(`Request 200. Received ${row200[cols.RECEIVED_DATE]}, Extended: ${row200[cols.EXTENDED_DATE]}, Responded: ${row200[cols.RESPONDED_DATE]}`);
rows = rows.filter(cols.ID, id => id !== 200);
let getWorkingDaysAllowed = function (row) {
// The Ministry of Education provided the date a request was received, and the duration of any extension
let received = row[cols.RECEIVED_DATE];
let extensionDuration = row[cols.EXTENDED_DURATION] || 0;
let daysAllowed = 20 + extensionDuration;
return daysAllowed;
};
cols.DAYS_ALLOWED = rows.addDerivedCol(getWorkingDaysAllowed);
let maxDaysAllowed = Stats.max(rows.getCol(cols.DAYS_ALLOWED));
log('Max working days allowed:', maxDaysAllowed);
let labelsDaysAllowed = Stats.intRange(20, maxDaysAllowed);
let chartDaysAllowed = {
title: 'Working days allowed',
labels: labelsDaysAllowed,
dataSeries: [
{
name: 'Working days allowed',
color: '#43ADB9',
dataPoints: rows.getColAsDataSeries(cols.DAYS_ALLOWED, labelsDaysAllowed)
}
]
};
let yAxisDaysAllowed = {
label: 'Requests',
max: 250,
values: 1,
valuesAt: [100, 200]
};
let xAxisDaysAllowed = {
label: 'Working days',
valuesEvery: 5
};
let $chartDaysAllowed = Charter.createBarChart(chartDaysAllowed, yAxisDaysAllowed, xAxisDaysAllowed);
chart($chartDaysAllowed);
Then, I calculated the number of working days taken to complete each request:
let getDaysTaken = function (row) {
let startDate = row[cols.RECEIVED_DATE];
let endDate = row[cols.RESPONDED_DATE];
let daysBetween = workingDays.between(startDate, endDate);
return daysBetween;
};
cols.DAYS_TAKEN = rows.addDerivedCol(getDaysTaken);
let maxDaysTaken = Stats.max(rows.getCol(cols.DAYS_TAKEN));
log('Maximum days taken:', maxDaysTaken);
const labelsDaysTaken = Stats.intRange(0, maxDaysTaken);
let chartDaysTaken = {
title: 'Working days taken',
labels: labelsDaysTaken,
dataSeries: [
{
name: 'Working days taken',
color: '#43ADB9',
dataPoints: rows.getColAsDataSeries(cols.DAYS_TAKEN, labelsDaysTaken)
}
]
};
let yAxisDaysTaken = {
label: 'Requests',
values: 4
};
let xAxisDaysTaken = {
label: 'Working days',
valuesEvery: 20
};
let $chartDaysTaken = Charter.createBarChart(chartDaysTaken, yAxisDaysTaken, xAxisDaysTaken);
chart($chartDaysTaken);
While it can be useful to look at the data this way, it does obscure which requests were extended, and by how much. Seeing how many allowed days were remaining requires combining the previous two measures.
I also reversed the order of the labels so the chart would be more intuitive, showing delayed requests further to the right, and split the data between extended and un-extended requests.
let getDaysRemaining = function (row) {
let daysAllowed = row[cols.DAYS_ALLOWED];
let daysTaken = row[cols.DAYS_TAKEN];
let daysRemaining = daysAllowed - daysTaken;
return daysRemaining;
};
cols.DAYS_REMAINING = rows.addDerivedCol(getDaysRemaining);
let maxDaysRemaining = Stats.max(rows.getCol(cols.DAYS_REMAINING));
log('Maximum days remaining:', maxDaysRemaining);
let minDaysRemaining = Stats.min(rows.getCol(cols.DAYS_REMAINING));
log('Minimum days remaining:', minDaysRemaining);
const labelsDaysRemaining = Stats.intRange(30, -30);
let chartDaysRemaining = {
title: 'Ministry of Education OIA days remaining 2017-07-01 – 2017-12-30',
labels: labelsDaysRemaining,
showLegend: true,
dataSeries: [
{
name: 'Extended requests',
color: '#43ADB9',
dataPoints: rows.filter(cols.EXTENDED, true).getColAsDataSeries(cols.DAYS_REMAINING, labelsDaysRemaining)
},
{
name: 'Unextended requests',
color: '#2B6346',
dataPoints: rows.filter(cols.EXTENDED, false).getColAsDataSeries(cols.DAYS_REMAINING, labelsDaysRemaining)
}
],
stacked: true
};
let yAxisDaysRemaining = {
label: 'Requests',
max: 50,
values: 5
};
let xAxisDaysRemaining = {
label: 'Working days',
valuesEvery: 10
};
let $chartDaysRemaining = Charter.createBarChart(chartDaysRemaining, yAxisDaysRemaining, xAxisDaysRemaining);
chart($chartDaysRemaining);
The length of extensions has to be calculated by subtracting the original 20 working days from the calculated number of working days allowed, based on the due date:
let maxExtensionLength = Stats.max(rows.getCol(cols.EXTENDED_DURATION));
log('Longest extension duration:', maxExtensionLength);
const labelsExtensionLength = Stats.intRange(0, 30);
let chartExtensionLength = {
title: 'Ministry of Education OIA extension length 2017-07-01 – 2017-12-30',
labels: labelsExtensionLength,
dataSeries: [
{
name: 'Extension duration',
color: '#42778D',
dataPoints: rows.getColAsDataSeries(cols.EXTENDED_DURATION, labelsExtensionLength)
}
],
stacked: true
};
let yAxisExtensionLength = {
label: 'Requests',
max: 40,
values: 4
};
let xAxisExtensionLength = {
label: 'Working days',
valuesEvery: 5
};
let $chartExtensionLength = Charter.createBarChart(chartExtensionLength, yAxisExtensionLength, xAxisExtensionLength);
chart($chartExtensionLength);
Calculating the days remaining when a request was extended is similar to calculating the days remaining when it was completed. Because all requests had 20 working days available before being extended, I didn't need to first calculate the days taken to extend the request before I could calculate the days remaining when the request was extended.
However, there were some requests that were extended for which the Ministry of Education didn't provide the date that the notice of extension was sent. I ignored these requests for this analysis.
let isValidDateString = function (dateString) {
let date = new Date(dateString);
let isValidDate = !isNaN(date.getDate());
return isValidDate;
};
let getExtensionDaysRemaining = function (row) {
let startDate = row[cols.RECEIVED_DATE];
let extensionDate = row[cols.EXTENDED_DATE];
let daysAllowed = 20;
if (row[cols.EXTENDED]) {
if (isValidDateString(row[cols.EXTENDED_DATE])) {
let date = new Date(extensionDate);
let daysTaken = workingDays.between(startDate, extensionDate);
let daysRemaining = daysAllowed - daysTaken;
return daysRemaining;
} else {
log(`Ignoring row ${row[cols.ID]}: no valid extension date ('${row[cols.EXTENDED_DATE]}')`);
return undefined;
}
} else {
// The request was not extended
return undefined;
}
};
cols.EXTENDED_DAYS_REMAINING = rows.addDerivedCol(getExtensionDaysRemaining);
let extendedRequests = rows.filter(cols.EXTENDED, true);
let maxExtensionDaysRemaining = Stats.max(extendedRequests.getCol(cols.EXTENDED_DAYS_REMAINING));
log('Maximum days remaining when request extended:', maxExtensionDaysRemaining);
let minExtensionDaysRemaining = Stats.min(extendedRequests.getCol(cols.EXTENDED_DAYS_REMAINING));
log('Minimum days remaining when request extended:', minExtensionDaysRemaining);
const labelsExtensionDaysRemaining = Stats.intRange(15, -20);
let chartExtensionDaysRemaining = {
title: 'Ministry of Education OIA days remaining when extended 2017-07-01 – 2017-12-30',
labels: labelsExtensionDaysRemaining,
dataSeries: [
{
name: 'Working days remaining when extended',
color: '#415E75',
dataPoints: extendedRequests.getColAsDataSeries(cols.EXTENDED_DAYS_REMAINING, labelsExtensionDaysRemaining)
}
]
};
let yAxisExtensionDaysRemaining = {
label: 'Requests',
max: 50,
values: 5
};
let xAxisExtensionDaysRemaining = {
label: 'Working days',
valuesEvery: 5
};
let $chartExtensionDaysRemaining = Charter.createBarChart(chartExtensionDaysRemaining, yAxisExtensionDaysRemaining, xAxisExtensionDaysRemaining);
chart($chartExtensionDaysRemaining);
Department of Corrections
After having earlier analysed data released from the State Services Commission, I sent identical requests to 11 agencies in April 2018, including the Department of Corrections. Here's what I asked them:
Please release the following information, from 1 July 2017 to 30 December 2017, broken down by request:
1. The date on which the request was received.
OIA Response Times | Mark Hanna
2. The date on which the request was answered.
2a. If the request was answered on the last allowable day, the time at which the request was answered
3. Whether or not the request was extended under section 15A of the OIA.
3a. If the request was extended, the date on which notice of the extension was sent to the requester.
3b. If the notice of extension was sent to the requester on the last allowable day, the time at which the notice of extension was sent to the requester.
3c. If the request was extended, the duration of the extension.
4. The outcome of the request (e.g. all information released, transferred to another agency).
It's worth noting that the range of dates I used here is shorter. I had asked the SSC for 12 months of data, but I decided in my follow-ups to ask other agencies for 6 months of data instead, to make it easier to extract.
Yes, I have noticed that I forgot the 31st of December exists. Thankfully the OIA takes a break at that time of year, so I don't expect my mistake in forgetting it will have made much of a difference.
The Department of Corrections' initial response was inconsistent with statistics compiled by the State Services Commission, so I sent a follow-up request to ask for clarification. The initial response had also included general non-OIA correspondence, so the Department of Corrections sent me an updated set of data in a spreadsheet:
OIAs sent 1 July 2017 30 December 2017.xlsx
I converted this spreadsheet to CSV format using Google Sheets.
OIAs sent 1 July 2017 30 December 2017.xlsx - Sheet1.csv
This data was loaded into Charter using this code:
const cols = Analyser.getColNumbers({
RECEIVED_DATE: 'B',
DUE_DATE: 'C',
RESPONDED_DATE: 'D',
EXTENDED_DATE: 'E'
});
const fileConfig = {
headerRows: 1,
cols
};
// Loaded alongside correspondence data in the next section
However, the Department of Corrections unfortunately provided the dates in the format 'dd/mm/yyyy', which is ambiguous in some contexts thanks to Americans using the format 'mm/dd/yyyy'. So in order to ensure consistency, I converted them into the unambiguous format 'yyyy-mm-dd':
log('Example cell from "Responded" column before recoding:', rows[0][cols.RESPONDED_DATE]);
let convertDate = function (dateString) {
let formatPattern = /(\d+)\/(\d+)\/(\d+)/;
let match = dateString.match(formatPattern);
if (match) {
let day = match[1];
let month = match[2];
let year = match[3];
let newDateString = `${year}-${month.padStart(2, '0')}-${day.padStart(2, '0')}`;
return newDateString;
} else {
return null;
}
};
rows.forEach(row => {
row[cols.RECEIVED_DATE] = convertDate(row[cols.RECEIVED_DATE]);
row[cols.DUE_DATE] = convertDate(row[cols.DUE_DATE]);
row[cols.RESPONDED_DATE] = convertDate(row[cols.RESPONDED_DATE]);
row[cols.EXTENDED_DATE] = convertDate(row[cols.EXTENDED_DATE]);
});
log('Example cell from "Responded" column after recoding:', rows[0][cols.RESPONDED_DATE]);
I also added an "ID" column, to make it easier to identify rows that I wanted to check in the original data.
let nextId = 1;
let getId = function (row) {
let id = nextId;
nextId += 1;
return nextId;
};
cols.ID = rows.addDerivedCol(getId);
Two requests had the date on which its response was sent recorded as having been over a month before it was received. It was not immediately clear what the correct dates should have been, so I removed these rows from my analysis.
let badDateRowIds = [];
rows.forEach(row => {
let startDate = new Date(row[cols.RECEIVED_DATE]);
let endDate = new Date(row[cols.RESPONDED_DATE]);
if (startDate > endDate) {
badDateRowIds.push(row[cols.ID]);
}
});
rows = rows.filter(cols.ID, id => !badDateRowIds.includes(id));
log(`${badDateRowIds.length} rows removed: ${badDateRowIds.join(', ')}`);
The Department of Corrections did not include a column for whether or not a request was extended, but they did include the date on which a request was extended. I recoded this into an EXTENDED column, where requests with a "Date Extension Granted" were considered extended and other requests were considered not extended:
let getExtended = function (row) {
let extended = !!row[cols.EXTENDED_DATE];
return extended;
};
cols.EXTENDED = rows.addDerivedCol(getExtended);
log('Summary of EXTENDED column:', rows.getColSummary(cols.EXTENDED));
In their initial response to my OIA request, the Department of Corrections had accidentally included statistics on how they responded to general non-OIA correspondence. This information was provided as a spreadsheet:
OIA requests received and responded to 1 July to 30 December 2017.xlsx
I converted this spreadsheet to CSV format using Google Sheets.
OIA requests received and responded to 1 July to 30 December 2017.xlsx - Sheet1.csv
This data was loaded into Charter using this code:
const colsB = Analyser.getColNumbers({
RECEIVED_DATE: 'A',
DUE_DATE: 'B',
RESPONDED_DATE: 'C',
EXTENDED_DATE: 'D'
});
const fileConfigB = {
headerRows: 1,
cols: colsB
};
Analyser.loadFile(
'../data/corrections/OIAs sent 1 July 2017 30 December 2017.xlsx - Sheet1.csv', fileConfig,
'../data/corrections/OIA requests received and responded to 1 July to 30 December 2017.xlsx - Sheet1.csv', fileConfigB,
analyseData
);
This secondary data was initialised manually, to keep it separate from the primary data:
let dataConfigB = arguments[1];
let rowsB = dataConfigB.rows;
let colsB = dataConfigB.cols;
However, the Department of Corrections unfortunately provided the dates in the format 'dd/mm/yyyy', which is ambiguous in some contexts thanks to Americans using the format 'mm/dd/yyyy'. So in order to ensure consistency, I converted them into the unambiguous format 'yyyy-mm-dd':
log('Example cell from "Responded" column before recoding:', rowsB[0][colsB.RESPONDED_DATE]);
rowsB.forEach(row => {
row[colsB.RECEIVED_DATE] = convertDate(row[colsB.RECEIVED_DATE]);
row[colsB.DUE_DATE] = convertDate(row[colsB.DUE_DATE]);
row[colsB.RESPONDED_DATE] = convertDate(row[colsB.RESPONDED_DATE]);
row[colsB.EXTENDED_DATE] = convertDate(row[colsB.EXTENDED_DATE]);
});
log('Example cell from "Responded" column after recoding:', rowsB[0][colsB.RESPONDED_DATE]);
I also added an "ID" column, to make it easier to identify rows that I wanted to check in the original data.
nextId = 1;
colsB.ID = rowsB.addDerivedCol(getId);
The Department of Corrections did not include a column for whether or not a request was extended, but they did include the date on which a request was extended. I recoded this into an EXTENDED column, where requests with a "Date Extension Granted" were considered extended and other requests were considered not extended:
let getExtendedB = function (row) {
let extended = !!row[colsB.EXTENDED_DATE];
return extended;
};
colsB.EXTENDED = rowsB.addDerivedCol(getExtendedB);
log('Summary of EXTENDED column:', rowsB.getColSummary(colsB.EXTENDED));
Some rows were missing a due date. None of these rows specified an extension date, so I set this to 20 working days after the day of receipt:
let missingDueDateIds = [];
rowsB.forEach(row => {
if (!row[colsB.DUE_DATE]) {
missingDueDateIds.push(row[colsB.ID]);
let receivedDate = new Date(row[colsB.RECEIVED_DATE]);
let dueDate = workingDays.add(receivedDate, 20);
let day = dueDate.getDate() + '';
let month = dueDate.getMonth() + 1 + '';
let year = dueDate.getFullYear + '';
let newDateString = `${year}-${month.padStart(2, '0')}-${day.padStart(2, '0')}`;
return newDateString;
row[colsB.DUE_DATE] = dueDateString;
}
});
log(`${missingDueDateIds.length} rows updated: ${missingDueDateIds.join(', ')}`);
Some rows were missing a received date. I removed these rows from the analysis:
let missingReceivedDateIds = [];
rowsB.forEach(row => {
if (!row[colsB.RECEIVED_DATE]) {
missingReceivedDateIds.push(row[colsB.ID]);
}
});
rowsB = rowsB.filter(colsB.ID, id => !missingReceivedDateIds.includes(id));
log(`${missingReceivedDateIds.length} rows removed: ${missingReceivedDateIds.join(', ')}`);
Some rows had received dates after their responded dates. I removed these from my analysis:
let wrongOrderRespondedDateIds = [];
rowsB.forEach(row => {
let receivedDate = new Date(row[colsB.RECEIVED_DATE]);
let respondedDate = new Date(row[colsB.RESPONDED_DATE]);
if (receivedDate > respondedDate) {
wrongOrderRespondedDateIds.push(row[colsB.ID]);
}
});
rowsB = rowsB.filter(colsB.ID, id => !wrongOrderRespondedDateIds.includes(id));
log(`${wrongOrderRespondedDateIds.length} rows removed: ${wrongOrderRespondedDateIds.join(', ')}`);
Some rows had received dates after their due dates. None of these requests were extended, so I corrected these due dates to 20 working days after the received date:
let wrongOrderDueDateIds = [];
rowsB.forEach(row => {
let receivedDate = new Date(row[colsB.RECEIVED_DATE]);
let dueDate = new Date(row[colsB.DUE_DATE]);
if (receivedDate > dueDate) {
wrongOrderDueDateIds.push(row[colsB.ID]);
dueDate = workingDays.add(receivedDate, 20);
let day = dueDate.getDate() + '';
let month = dueDate.getMonth() + 1 + '';
let year = dueDate.getFullYear() + '';
let newDateString = `${year}-${month.padStart(2, '0')}-${day.padStart(2, '0')}`;
row[colsB.DUE_DATE] = newDateString;
}
});
log(`${wrongOrderDueDateIds.length} rows updates: ${wrongOrderDueDateIds.join(', ')}`);
I also manually corrected some row which appeared to have an incorrectly recorded years:
let rowB2 = rowsB.filter(colsB.ID, 2)[0];
log(`Row 2 due date before correction: ${rowB2[colsB.RESPONDED_DATE]}`);
rowB2[colsB.RESPONDED_DATE] = rowB2[colsB.RESPONDED_DATE].replace('2017', '2016');
log(`Row 2 due date after correction: ${rowB2[colsB.RESPONDED_DATE]}`);
let rowB3417 = rowsB.filter(colsB.ID, 3417)[0];
log(`Row 3417 due date before correction: ${rowB3417[colsB.DUE_DATE]}`);
rowB3417[colsB.DUE_DATE] = rowB3417[colsB.DUE_DATE].replace('2018', '2017');
log(`Row 3417 due date after correction: ${rowB3417[colsB.DUE_DATE]}`);
let rowB3656 = rowsB.filter(colsB.ID, 3656)[0];
log(`Row 3656 due date before correction: ${rowB3656[colsB.DUE_DATE]}`);
rowB3656[colsB.DUE_DATE] = rowB3656[colsB.DUE_DATE].replace('2018', '2017');
log(`Row 3656 due date after correction: ${rowB3656[colsB.DUE_DATE]}`);
let getWorkingDaysAllowed = function (row) {
// The Department of Corrections provided the date a request was received, and its due date
let received = row[cols.RECEIVED_DATE];
let due = row[cols.DUE_DATE];
let daysAllowed = workingDays.between(received, due);
return daysAllowed;
};
cols.DAYS_ALLOWED = rows.addDerivedCol(getWorkingDaysAllowed);
let maxDaysAllowed = Stats.max(rows.getCol(cols.DAYS_ALLOWED));
log('Max working days allowed:', maxDaysAllowed);
let labelsDaysAllowed = Stats.intRange(20, maxDaysAllowed);
let chartDaysAllowed = {
title: 'Working days allowed',
labels: labelsDaysAllowed,
dataSeries: [
{
name: 'Working days allowed',
color: '#43ADB9',
dataPoints: rows.getColAsDataSeries(cols.DAYS_ALLOWED, labelsDaysAllowed)
}
]
};
let yAxisDaysAllowed = {
label: 'Requests'
};
let xAxisDaysAllowed = {
label: 'Working days',
valuesEvery: 5
};
let $chartDaysAllowed = Charter.createBarChart(chartDaysAllowed, yAxisDaysAllowed, xAxisDaysAllowed);
chart($chartDaysAllowed);
Then, I calculated the number of working days taken to complete each request:
let getDaysTaken = function (row) {
let startDate = row[cols.RECEIVED_DATE];
let endDate = row[cols.RESPONDED_DATE];
let daysBetween = workingDays.between(startDate, endDate);
return daysBetween;
};
cols.DAYS_TAKEN = rows.addDerivedCol(getDaysTaken);
let maxDaysTaken = Stats.max(rows.getCol(cols.DAYS_TAKEN));
log('Maximum days taken:', maxDaysTaken);
const labelsDaysTaken = Stats.intRange(0, maxDaysTaken);
let chartDaysTaken = {
title: 'Working days taken',
labels: labelsDaysTaken,
dataSeries: [
{
name: 'Working days taken',
color: '#43ADB9',
dataPoints: rows.getColAsDataSeries(cols.DAYS_TAKEN, labelsDaysTaken)
}
]
};
let yAxisDaysTaken = {
label: 'Requests',
values: 4
};
let xAxisDaysTaken = {
label: 'Working days',
valuesEvery: 20
};
let $chartDaysTaken = Charter.createBarChart(chartDaysTaken, yAxisDaysTaken, xAxisDaysTaken);
chart($chartDaysTaken);
While it can be useful to look at the data this way, it does obscure which requests were extended, and by how much. Seeing how many allowed days were remaining requires combining the previous two measures.
I also reversed the order of the labels so the chart would be more intuitive, showing delayed requests further to the right, and split the data between extended and un-extended requests.
let getDaysRemaining = function (row) {
let daysAllowed = row[cols.DAYS_ALLOWED];
let daysTaken = row[cols.DAYS_TAKEN];
let daysRemaining = daysAllowed - daysTaken;
return daysRemaining;
};
cols.DAYS_REMAINING = rows.addDerivedCol(getDaysRemaining);
let maxDaysRemaining = Stats.max(rows.getCol(cols.DAYS_REMAINING));
log('Maximum days remaining:', maxDaysRemaining);
let minDaysRemaining = Stats.min(rows.getCol(cols.DAYS_REMAINING));
log('Minimum days remaining:', minDaysRemaining);
const labelsDaysRemaining = Stats.intRange(20, -20);
let chartDaysRemaining = {
title: 'Department of Corrections OIA days remaining 2017-07-01 – 2017-12-30',
labels: labelsDaysRemaining,
showLegend: true,
dataSeries: [
{
name: 'Extended requests',
color: '#43ADB9',
dataPoints: rows.filter(cols.EXTENDED, true).getColAsDataSeries(cols.DAYS_REMAINING, labelsDaysRemaining)
},
{
name: 'Unextended requests',
color: '#2B6346',
dataPoints: rows.filter(cols.EXTENDED, false).getColAsDataSeries(cols.DAYS_REMAINING, labelsDaysRemaining)
}
],
stacked: true
};
let yAxisDaysRemaining = {
label: 'Requests',
max: 300,
values: 4
};
let xAxisDaysRemaining = {
label: 'Working days',
valuesEvery: 10
};
let $chartDaysRemaining = Charter.createBarChart(chartDaysRemaining, yAxisDaysRemaining, xAxisDaysRemaining);
chart($chartDaysRemaining);
For this graph, I found the difference between the data including correspondence and the corrected data. As a first step, I had to repeat the same analysis on the first data as I did on the correct data for the previous graph.
let getWorkingDaysAllowedB = function (row) {
// The Department of Corrections provided the date a request was received, and its due date
let received = row[colsB.RECEIVED_DATE];
let due = row[colsB.DUE_DATE];
let daysAllowed = workingDays.between(received, due);
return daysAllowed;
};
colsB.DAYS_ALLOWED = rowsB.addDerivedCol(getWorkingDaysAllowedB);
let maxDaysAllowedB = Stats.max(rowsB.getCol(colsB.DAYS_ALLOWED));
log('Max working days allowed:', maxDaysAllowedB);
let labelsDaysAllowedB = Stats.intRange(20, maxDaysAllowedB);
let chartDaysAllowedB = {
title: 'Working days allowed',
labels: labelsDaysAllowedB,
dataSeries: [
{
name: 'Working days allowed',
color: '#43ADB9',
dataPoints: rowsB.getColAsDataSeries(colsB.DAYS_ALLOWED, labelsDaysAllowedB)
}
]
};
let yAxisDaysAllowedB = {
label: 'Requests'
};
let xAxisDaysAllowedB = {
label: 'Working days',
valuesEvery: 5
};
let $chartDaysAllowedB = Charter.createBarChart(chartDaysAllowedB, yAxisDaysAllowedB, xAxisDaysAllowedB);
chart($chartDaysAllowedB);
Then, I calculated the number of working days taken to complete each request:
let getDaysTakenB = function (row) {
let startDate = row[colsB.RECEIVED_DATE];
let endDate = row[colsB.RESPONDED_DATE];
let daysBetween = workingDays.between(startDate, endDate);
return daysBetween;
};
colsB.DAYS_TAKEN = rowsB.addDerivedCol(getDaysTakenB);
let maxDaysTakenB = Stats.max(rowsB.getCol(colsB.DAYS_TAKEN));
log('Maximum days taken:', maxDaysTakenB);
const labelsDaysTakenB = Stats.intRange(0, maxDaysTakenB);
let chartDaysTakenB = {
title: 'Working days taken',
labels: labelsDaysTakenB,
dataSeries: [
{
name: 'Working days taken',
color: '#43ADB9',
dataPoints: rowsB.getColAsDataSeries(colsB.DAYS_TAKEN, labelsDaysTakenB)
}
]
};
let yAxisDaysTakenB = {
label: 'Requests',
values: 4
};
let xAxisDaysTakenB = {
label: 'Working days',
valuesEvery: 20
};
let $chartDaysTakenB = Charter.createBarChart(chartDaysTakenB, yAxisDaysTakenB, xAxisDaysTakenB);
chart($chartDaysTakenB);
While it can be useful to look at the data this way, it does obscure which requests were extended, and by how much. Seeing how many allowed days were remaining requires combining the previous two measures.
I also reversed the order of the labels so the chart would be more intuitive, showing delayed requests further to the right, and split the data between extended and un-extended requests.
let getDaysRemainingB = function (row) {
let daysAllowed = row[colsB.DAYS_ALLOWED];
let daysTaken = row[colsB.DAYS_TAKEN];
let daysRemaining = daysAllowed - daysTaken;
return daysRemaining;
};
colsB.DAYS_REMAINING = rowsB.addDerivedCol(getDaysRemainingB);
let maxDaysRemainingB = Stats.max(rowsB.getCol(colsB.DAYS_REMAINING));
log('Maximum days remaining:', maxDaysRemainingB);
let minDaysRemainingB = Stats.min(rowsB.getCol(colsB.DAYS_REMAINING));
log('Minimum days remaining:', minDaysRemainingB);
const labelsDaysRemainingB = Stats.intRange(20, -20);
let extendedRequestsA = rows.filter(cols.EXTENDED, true).getColAsDataSeries(cols.DAYS_REMAINING, labelsDaysRemainingB);
let extendedRequestsB = rowsB.filter(colsB.EXTENDED, true).getColAsDataSeries(colsB.DAYS_REMAINING, labelsDaysRemainingB);
let extendedRequestsDataSeries = [];
extendedRequestsB.forEach((rowB, i) => {
let rowA = extendedRequestsA[i];
let diff = rowB - rowA;
extendedRequestsDataSeries.push(diff);
});
let unextendedRequestsA = rows.filter(cols.EXTENDED, false).getColAsDataSeries(cols.DAYS_REMAINING, labelsDaysRemainingB);
let unextendedRequestsB = rowsB.filter(colsB.EXTENDED, false).getColAsDataSeries(colsB.DAYS_REMAINING, labelsDaysRemainingB);
let unextendedRequestsDataSeries = [];
unextendedRequestsB.forEach((rowB, i) => {
let rowA = unextendedRequestsA[i];
let diff = rowB - rowA;
unextendedRequestsDataSeries.push(diff);
});
let chartDaysRemainingB = {
title: 'Department of Corrections non-OIA correspondence days remaining 2017-07-01 – 2017-12-30',
labels: labelsDaysRemainingB,
showLegend: true,
dataSeries: [
{
name: 'Extended requests',
color: '#43ADB9',
dataPoints: extendedRequestsDataSeries
},
{
name: 'Unextended requests',
color: '#2B6346',
dataPoints: unextendedRequestsDataSeries
}
],
stacked: true
};
let yAxisDaysRemainingB = {
label: 'Requests',
max: 400,
values: 4
};
let xAxisDaysRemainingB = {
label: 'Working days',
valuesEvery: 10
};
let $chartDaysRemainingB = Charter.createBarChart(chartDaysRemainingB, yAxisDaysRemainingB, xAxisDaysRemainingB);
chart($chartDaysRemainingB);
The length of extensions has to be calculated by subtracting the original 20 working days from the calculated number of working days allowed, based on the due date:
let getExtensionLength = function (row) {
if (row[cols.EXTENDED]) {
let allowed = row[cols.DAYS_ALLOWED];
let extensionDuration = allowed - 20;
return extensionDuration;
}
};
cols.EXTENDED_DURATION = rows.addDerivedCol(getExtensionLength);
let maxExtensionLength = Stats.max(rows.getCol(cols.EXTENDED_DURATION));
log('Longest extension duration:', maxExtensionLength);
const labelsExtensionLength = Stats.intRange(0, 40);
let chartExtensionLength = {
title: 'Department of Corrections OIA extension length 2017-07-01 – 2017-12-30',
labels: labelsExtensionLength,
dataSeries: [
{
name: 'Extension duration',
color: '#42778D',
dataPoints: rows.getColAsDataSeries(cols.EXTENDED_DURATION, labelsExtensionLength)
}
],
stacked: true
};
let yAxisExtensionLength = {
label: 'Requests',
max: 40,
values: 4
};
let xAxisExtensionLength = {
label: 'Working days',
valuesEvery: 5
};
let $chartExtensionLength = Charter.createBarChart(chartExtensionLength, yAxisExtensionLength, xAxisExtensionLength);
chart($chartExtensionLength);
Calculating the days remaining when a request was extended is similar to calculating the days remaining when it was completed. Because all requests had 20 working days available before being extended, I didn't need to first calculate the days taken to extend the request before I could calculate the days remaining when the request was extended.
let getExtensionDaysRemaining = function (row) {
let startDate = row[cols.RECEIVED_DATE];
let extensionDate = row[cols.EXTENDED_DATE];
let daysAllowed = 20;
if (row[cols.EXTENDED]) {
let date = new Date(extensionDate);
let daysTaken = workingDays.between(startDate, extensionDate);
let daysRemaining = daysAllowed - daysTaken;
return daysRemaining;
} else {
// The request was not extended
return undefined;
}
};
cols.EXTENDED_DAYS_REMAINING = rows.addDerivedCol(getExtensionDaysRemaining);
let extendedRequests = rows.filter(cols.EXTENDED, true);
let maxExtensionDaysRemaining = Stats.max(extendedRequests.getCol(cols.EXTENDED_DAYS_REMAINING));
log('Maximum days remaining when request extended:', maxExtensionDaysRemaining);
let minExtensionDaysRemaining = Stats.min(extendedRequests.getCol(cols.EXTENDED_DAYS_REMAINING));
log('Minimum days remaining when request extended:', minExtensionDaysRemaining);
const labelsExtensionDaysRemaining = Stats.intRange(10, -25);
let chartExtensionDaysRemaining = {
title: 'Department of Corrections OIA days remaining when extended 2017-07-01 – 2017-12-30',
labels: labelsExtensionDaysRemaining,
dataSeries: [
{
name: 'Working days remaining when extended',
color: '#415E75',
dataPoints: extendedRequests.getColAsDataSeries(cols.EXTENDED_DAYS_REMAINING, labelsExtensionDaysRemaining)
}
]
};
let yAxisExtensionDaysRemaining = {
label: 'Requests',
max: 40,
values: 4
};
let xAxisExtensionDaysRemaining = {
label: 'Working days',
valuesEvery: 5
};
let $chartExtensionDaysRemaining = Charter.createBarChart(chartExtensionDaysRemaining, yAxisExtensionDaysRemaining, xAxisExtensionDaysRemaining);
chart($chartExtensionDaysRemaining);