Need a theme or package with very specific functionality
March 5, 2010 10:28 AM Subscribe
I need recommendations for very specific blog functionality.
I'm active in UK politics and live in a Borough that is running a largish budget deficit. As part of my political to and fro, for various reasons I became very interested in Councillor compensation, especially as it pertains to duties.
While our borough doesn't publish this information on line, a Freedom of Information Act (FOIA) request got me the compensation information I was seeking - P60 (or P45 as appropriate) numbers for each Councillor in our ward. There were 31 FOIAs in that first wave, covering 2008/2009 and I'm launching a second wave Monday which will cover another 40 or so Councillors for 2009/2010.
Full disclosure on Councillor compensation schemes is or should be publicly accessible, but presently is scattered across six different web sites. And some (the end of year compensation numbers) isn't even on the web! Clearly they'd rather not have this data published. So I'm planning to change that.
But I need some very specific blog functionality, and I would prefer Wordpress and free, if at all possible.
For each Councillor I will have demographic data (e.g., name, party, ward) and assignment data; effectively what they do for the ward - basic Councillor duties or do they have what in the UK are called Special Responsibility Allowances, SRAs.
Then I'd like to have the compensation data, both in aggregate and broken down by duties by year. The idea is to provide full transparency into what your Councillor is being paid.
Clearly the less labour intensive this process is the happier I'll be. I'd also like some aggregation capabilities, if at all possible - what does Party X cost in total? Comparison would be nice as well - cost of average Party X Councillor compared to Party Y or compare cost of Councillor in Ward X to Ward Y. I've got a number of queries in mind that would be very revealing.
I can't say folks at The Council are thrilled I'm doing this; I've had to not only file multiple FOIA requests to get lots of this information, but also involve the media on at least one occasion and even go so far as to launch what is called a Members Enquiry, all to get information that legally purports to be public domain.
So I need some software that will help me present data and not make any errors along the way which might detract from my purpose, or unfairly tarnish a Councillor (e.g., misstate pay in this an election year).
I'm a Quant in real life so if Excel pivot table functionality means anything to you that would be ideal. But I'm fine with static, predefined views as well.
I've perused the Wordpress themes directories and can't find something appropriate.
Does anyone have any suggestions?
I'm active in UK politics and live in a Borough that is running a largish budget deficit. As part of my political to and fro, for various reasons I became very interested in Councillor compensation, especially as it pertains to duties.
While our borough doesn't publish this information on line, a Freedom of Information Act (FOIA) request got me the compensation information I was seeking - P60 (or P45 as appropriate) numbers for each Councillor in our ward. There were 31 FOIAs in that first wave, covering 2008/2009 and I'm launching a second wave Monday which will cover another 40 or so Councillors for 2009/2010.
Full disclosure on Councillor compensation schemes is or should be publicly accessible, but presently is scattered across six different web sites. And some (the end of year compensation numbers) isn't even on the web! Clearly they'd rather not have this data published. So I'm planning to change that.
But I need some very specific blog functionality, and I would prefer Wordpress and free, if at all possible.
For each Councillor I will have demographic data (e.g., name, party, ward) and assignment data; effectively what they do for the ward - basic Councillor duties or do they have what in the UK are called Special Responsibility Allowances, SRAs.
Then I'd like to have the compensation data, both in aggregate and broken down by duties by year. The idea is to provide full transparency into what your Councillor is being paid.
Clearly the less labour intensive this process is the happier I'll be. I'd also like some aggregation capabilities, if at all possible - what does Party X cost in total? Comparison would be nice as well - cost of average Party X Councillor compared to Party Y or compare cost of Councillor in Ward X to Ward Y. I've got a number of queries in mind that would be very revealing.
I can't say folks at The Council are thrilled I'm doing this; I've had to not only file multiple FOIA requests to get lots of this information, but also involve the media on at least one occasion and even go so far as to launch what is called a Members Enquiry, all to get information that legally purports to be public domain.
So I need some software that will help me present data and not make any errors along the way which might detract from my purpose, or unfairly tarnish a Councillor (e.g., misstate pay in this an election year).
I'm a Quant in real life so if Excel pivot table functionality means anything to you that would be ideal. But I'm fine with static, predefined views as well.
I've perused the Wordpress themes directories and can't find something appropriate.
Does anyone have any suggestions?
Best answer: This functionality isn't really something I'd expect to see implemented at the theme level, unless someone has had to solve almost exactly the same problem as you before.
This might be a good job for Dabble DB. On their "commons," they host public data for free, and what you've got clearly fits. They give you a lot of interesting ways to slice and dice your data--they also have some inexplicable limitations, but on balance, it's a really good service.
posted by adamrice at 10:52 AM on March 5, 2010
This might be a good job for Dabble DB. On their "commons," they host public data for free, and what you've got clearly fits. They give you a lot of interesting ways to slice and dice your data--they also have some inexplicable limitations, but on balance, it's a really good service.
posted by adamrice at 10:52 AM on March 5, 2010
Best answer: Can you be more specific :-)
Do you have storyboards for how you want it to look and work?
It might help a developer.
For example, do you want people to be able to make their own searches and
inquiries in the database?
I would expect this to be more of a plugin than a theme. These looked promising,
but I don't have any experience with them:
http://wordpress.org/extend/plugins/mytreasures/
http://wordpress.org/extend/plugins/projectmanager/
As to the applicability of these plugins, express or implied, for any purpose to which
you put them, no definite statement can be made. It would be a good idea to spot check
some of your results, and to provide an avenue for notification of errors or inaccuracies.
posted by the Real Dan at 1:11 PM on March 5, 2010
Do you have storyboards for how you want it to look and work?
It might help a developer.
For example, do you want people to be able to make their own searches and
inquiries in the database?
I would expect this to be more of a plugin than a theme. These looked promising,
but I don't have any experience with them:
http://wordpress.org/extend/plugins/mytreasures/
http://wordpress.org/extend/plugins/projectmanager/
As to the applicability of these plugins, express or implied, for any purpose to which
you put them, no definite statement can be made. It would be a good idea to spot check
some of your results, and to provide an avenue for notification of errors or inaccuracies.
posted by the Real Dan at 1:11 PM on March 5, 2010
I know it's way off from what you're after, but you might look at the technology the Guardian used for the MP Expenses Scandal. Since you're after free, probably look to see what Simon Wilison has published on the subject as well.
I'm in a similar position, but for less honorable reasons; my employer, a state funded school, published salaries in binder in the library. I (and others, I'm sure) would like it digitized and make available to read and process, so I can go into salary negations well prepared.
For the digitization part, I used a trial version of ABBYY. Fifty pages for free, with some limitations in the UI. Open source tools don't do a sufficient job on the layout analysis, I've found. I don't know how big your FOIA requests are, so you may need to turn to crowd sourcing in any event.
For many people, when they indicate they want 'Excel pivot tables', they're really after a crutch, and their data is better described as a database than spreadsheet. Unfortunately, for you, the excel xls format is notoriously hard to parse. What I did was take the XLS files ABBYY output and convert them to CSV, so I can import them into a database.
From here, I can only offer speculation and my intentions. I've put this project on hiatus to work on some other stuff before returning to the task. My plan is to define a database schema via Django, let it construct an appropriate table set in Postgres (similar to MySQL) and start cleaning up the OCR from the admin interface. The advantage of doing cleanup after putting it into the website is that serious errors are probably also serious outliers. Once the cleanup and basic table views are done, I can start writing reports and output templates and call it done.
Which leaves the matter of getting CSV data into the database. Most databases have ways of importing CSV. I've found a few scripts to do the same thing for Django. But either way, I need to define a schema to import the CSV into first. And that's where I've left it, for further thought.
This is of course, way more work than I think you anticipated. It might not even be the smartest way to do it; I ought to take a look at pChart and see what else is out there.
posted by pwnguin at 6:23 PM on March 5, 2010
I'm in a similar position, but for less honorable reasons; my employer, a state funded school, published salaries in binder in the library. I (and others, I'm sure) would like it digitized and make available to read and process, so I can go into salary negations well prepared.
For the digitization part, I used a trial version of ABBYY. Fifty pages for free, with some limitations in the UI. Open source tools don't do a sufficient job on the layout analysis, I've found. I don't know how big your FOIA requests are, so you may need to turn to crowd sourcing in any event.
For many people, when they indicate they want 'Excel pivot tables', they're really after a crutch, and their data is better described as a database than spreadsheet. Unfortunately, for you, the excel xls format is notoriously hard to parse. What I did was take the XLS files ABBYY output and convert them to CSV, so I can import them into a database.
From here, I can only offer speculation and my intentions. I've put this project on hiatus to work on some other stuff before returning to the task. My plan is to define a database schema via Django, let it construct an appropriate table set in Postgres (similar to MySQL) and start cleaning up the OCR from the admin interface. The advantage of doing cleanup after putting it into the website is that serious errors are probably also serious outliers. Once the cleanup and basic table views are done, I can start writing reports and output templates and call it done.
Which leaves the matter of getting CSV data into the database. Most databases have ways of importing CSV. I've found a few scripts to do the same thing for Django. But either way, I need to define a schema to import the CSV into first. And that's where I've left it, for further thought.
This is of course, way more work than I think you anticipated. It might not even be the smartest way to do it; I ought to take a look at pChart and see what else is out there.
posted by pwnguin at 6:23 PM on March 5, 2010
Google Documents.
You can export them as non-editable versions which people can copy and muck around with.
You have a good subset of Excel functionality in there, as well as a number of visualisation widgets and functions built in.
You can invite a number of collaborators to work on the data in tandem.
The spreadsheet is excellent. You'd be able to create a dashboard front-page, and stack the raw data into multiple sheets behind it.
posted by davemee at 7:26 AM on March 6, 2010 [1 favorite]
You can export them as non-editable versions which people can copy and muck around with.
You have a good subset of Excel functionality in there, as well as a number of visualisation widgets and functions built in.
You can invite a number of collaborators to work on the data in tandem.
The spreadsheet is excellent. You'd be able to create a dashboard front-page, and stack the raw data into multiple sheets behind it.
posted by davemee at 7:26 AM on March 6, 2010 [1 favorite]
... oh, then you could use a plugin to embed those documents directly into standard WordPress posts and/or pages.
posted by davemee at 2:33 AM on March 7, 2010
posted by davemee at 2:33 AM on March 7, 2010
« Older Artists living as if it were the Victorian era? | Plz don't take my bloods, I need them. Newer »
This thread is closed to new comments.
I've collected / am collecting it from six different sources, one of which are the individual FOIA requests detailing specifically what each Councillor earned for the years in question.
I'll be loading the data into a MySQL database and PHP is available.
Thanks for the lead on pChart - looks pretty interesting but its not clear to me if it will support different aggregations (i.e. "drill down" / "drill up") that I'd like. Would be good to display high level information e.g., different parties as a percentage of total spend, etc.
Thanks again!
posted by Mutant at 10:48 AM on March 5, 2010