Improve/fix HTML rendering of tables

[TomSteinberg]

e.g http://www.whatdotheyknow.com/request/it_support_services_1295#incoming-258044 http://www.whatdotheyknow.com/request/it_support_services_347#incoming-258014 http://www.whatdotheyknow.com/request/it_support_services_1236#incoming-257000

taken from #18 by @hsenag

[garethrees]

At the moment the conversion doesn't actually render the content in to a html table, so its a conversion issue rather than simply adding some styling.

[hsenag]

Another example: https://www.whatdotheyknow.com/request/mental_health_services_4#incoming-601920

The original email has a nice looking HTML table.

[tomchance]

I would really welcome this fix/improvement. I submit a fair few FOI requests where I end up with incomprehensible tables. For example:

https://www.whatdotheyknow.com/request/homeless_due_to_end_of_private_t_19#incoming-713565

As a result I usually write a reply asking that they email me the contents directly, which means others can't access the data if they happen across the request on the web site.

[RichardTaylor]

Another example:

https://www.whatdotheyknow.com/request/number_children_home_educated_wi

What WhatDoTheyKnow shows:

Summary of Elective Home Education Cases

Academic Year

2014/15 2015/16 2016/17

SEN All From From All From From All From From status Cases mainstream Special Cases mainstream Special Cases mainstream Special Statement 10 <10 < 5 11 6 <5 9 5 <5 (1) EHCP (2) 6 <5 < 5 6 6 0 8 7 0 SEN Support 67 63 0 81 77 0 102 99 0 (3) All SEN 83 72 < 5 98 89 <5 119 111 <5 (4, 5)

Table in HTML:

In this case we offered the user an image of the table and may upload the HTML version of the response and link it from an annotation:

https://twitter.com/WhatDoTheyKnow/status/889469208383418368

[RichardTaylor]

Adding another example where a table provided in HTML format wasn't legible https://www.whatdotheyknow.com/request/housing_register_8?unfold=1#incoming-1135657 in that case we put the tables in a Google spreadsheet and linked to it.

Also noting #4003 is a related, broader, ticket for preserving all HTML formatting in response emails.

[RichardTaylor]

Another example: https://www.whatdotheyknow.com/request/requests_for_reception_start_at

[garethrees]

https://blog.socialcops.com/technology/engineering/camelot-python-library-pdf-data/

[lizconlan]

https://blog.socialcops.com/technology/engineering/camelot-python-library-pdf-data/

Had a quick play with that last night (used an older laptop so made the path to install harder for myself than it needed to be) and it looks interesting. I picked a random PDF attachment with a table in it (the first one I stumbled across) and it made a reasonable job of it, reducing the entire message to just the tabular content detail here.

(But, as far as I can tell, it's only useful for tables stuck inside PDFs)

[garethrees]

https://github.com/adworse/iguvium – Ruby gem for extracting tables from PDF as a structured info

[mikejamesthompson]

I've come across this recently with tables of data pasted into response emails and then rendered unintelligible by the conversion.

It looks as if most of the examples above are HTML tables that get mangled, so that seems like a good place to focus. Parsing tables out of PDFs is a whole separate challenge in itself (I've been doing this a lot recently ...) and there are plenty of products/libraries out there that (attempt to) do this - Camelot, PDFTables.com, Tabula etc.

Is there any reason you couldn't give people access to the raw response? That would give people the ability to copy-paste the table direct into a spreadsheet or just to eyeball it.

[RichardTaylor]

+1 see issue at:

https://www.whatdotheyknow.com/request/july_2019_discretionary_deferral_17#comment-89964

[RichardTaylor]

+1 another example at

https://www.whatdotheyknow.com/request/tenancies_ended_by_death_of_the#incoming-1455601

[RichardTaylor]

A WhatDoTheyKnow user writes:

A problem which I frequently encounter is that figures are supplied by the respondent as tables.

These are scrambled when they appear on WDTK (titles are shown detached from the figures), which is usually decipherable, but which occasionally necessitates a further clarification request.

Could you find a way to show the tables on WDTK as received (presumably received in the body of an email)?

[garethrees]

https://nanonets.com/blog/table-extraction-deep-learning/

[MattK1234]

+1 from me following the poor formatting of the table at https://www.whatdotheyknow.com/request/potholes_rights_of_way_maintenan

The WDTK admin team have provided the user with a copy of the raw email.

[RichardTaylor]

+1 https://www.whatdotheyknow.com/request/minimum_maximum_and_median_fpas#incoming-1640619

The data has been copied to a Google sheet linked from an annotation.

[RichardTaylor]

+1

https://www.whatdotheyknow.com/request/details_of_current_housing_stock#incoming-1743561

[garethrees]

+1 from a pro user support query.

[mdeuk]

+1 from a WDTK user and a tired administrator for this one - the message itself has a rather poor plain-text copy which doesn't help; but HTML is 'fine' if you view it in a capable client.

I'll also add a +1 for #3547 here - it would have helped when extracting the data, since we could have formatted it in an intelligible way.

https://www.whatdotheyknow.com/request/list_of_litter_bins_in_the_borou#incoming-1806040

[RichardTaylor]

+1 https://www.whatdotheyknow.com/request/barriers_on_hobmoor#incoming-1673541

[RichardTaylor]

+1 https://www.whatdotheyknow.com/request/healthcare_worker_accommodation_6#incoming-1848176

[garethrees]

Sometimes we manually convert these for pro users on request. Process is:

Download raw email > open in apple mail > copy and paste into text edit (to preserve rich text formatting) > make any redactions > print as pdf > upload file > link in annotation.

[RichardTaylor]

+1

https://www.whatdotheyknow.com/request/vser_payments_teaching_staff#incoming-1938493

Data supplied to user by email, and made available via Google Sheets

[FOIMonkey]

+1 The response here is almost illegible https://www.whatdotheyknow.com/request/property_and_assets_and_building_291#incoming-1221782

[garethrees]

Wondering whether we can convert the main body HTML part to PDF and add it as an "attachment" or similar, so that:

• We still render plain text by default
• We don't have to sanitise and render random HTML
• Users have better access to the underlying email presentation

Would have to consider possible extra work when it comes to hiding and censor rules though.

[RichardTaylor]

Further example

https://www.whatdotheyknow.com/request/deaths_and_hospital_admissions_f_117#incoming-1943749

[WilliamWDTK]

This issue (alongside, perhaps, #4578) has actually been cited in an FOI response:

I have attached a PDF document with my full response. This includes some tables and hyperlinks that may not otherwise be supported by the WhatDoTheyKnow website.

https://www.whatdotheyknow.com/request/docs_21#incoming-1517708

[WilliamWDTK]

Another example on this request.

The table in the raw email actually looks like this:

	Officers	Staff
	Total	Economic Crime	Total	Economic Crime
2013	2083.38	13.8	1690.95	7.59
2014	1955.04	13.8	1640.36	8.59
2015	1927.24	15.8	1639.78	11
2016	2068.73	10.8	1715.48	13
2017	2056.54	10.8	1805	11.41
2018	1974.72	11.8	1705.84	10.99
2019	1944.71	10.8	1707.2	12.4
2020	2145.17	10.68	1822.86	12.4
2021	2240.64	9.88	1828.03	12.67
2022	2328.23	11.73	1888.18	13.16

[RichardTaylor]

Further example at https://www.whatdotheyknow.com/request/cumulative_amounts_owed_to_the_c#incoming-1776516

[ajparsons]

Just to add to the above, this seems like one of the few areas WhatDoTheyKnow is less useful than the user just sending an email.

Is making the original email available as a download just to the requester an option?

[RichardTaylor]

Classifying requests on WhatDoTheyKnow reveals lots more examples that we're not logging or acting on; often the lack of formatting doesn't prevent accessing the information with care and effort, but it does make it less easy to read than intended.

[RichardTaylor]

Is making the original email available as a download just to the requester an option?

If we did this for all incoming messages it would become another thing to check to see if redactions had worked on when putting censor rules in place.

We probably shouldn't do it retrospectively, not without careful consideration, as we might end up publishing lots of new material.

We could do it on a per-request basis, but that wouldn't help as significantly.

Could we detect a table, and present it in HTML form at the end of a message in a similar way as we extract some links/references and present them at the end?

[ajparsons]

Just for ref, the problem I was looking at was a request where some cells were blank, making it hard to easily read or convert the table when it got flattened. Automated approaches might move values around in this case so would need to be extracted from the html version rather than the text (but that's probably possible?)

Could extract all table elements and make new attachments from them as a reduced version of Gareth's pdf idea above.

A recurring problem seems to be that pulling content out of the html is creating extra problems for redaction. Could just have an approach of deleting any derived attachments if a change is applied as a way of balancing the big benefit in many cases with tables? Those requests would just end up like all requests are at the moment.

Email downloads might not be the right answer in this case, but do censor rules matter as much if just available to the original requester? Feel like giving people the option of all the content they'd have got by email is good. This is especially in terms of the case for pro vs email. But can take that to another issue.

[RichardTaylor]

I think we want to solve this issue for all readers, not specifically requesters.

If we manually act in these cases we don't just provide the readable table to the requester we also publish it.

Making the material available to the requester easily/automatically might stop them contacting us, prompting us to make the material available to all. It might make the issue from the point of view of the non-requester reader, worse.

Could extract all table elements and make new attachments from them as a reduced version of Gareth's pdf idea above.

I think that's very similar to the proposal of extracting the table and presenting it on the request page. A separate page / "generated attachment" might be preferable though due to the risk of formatting being disrupted by a large table. As with "view as HTML" we'd want a header linking back to the request page.

[FOIMonkey]

but do censor rules matter as much if just available to the original requester?

Yes. Though not the main use, sometimes they are in place to keep information from the requester. We would have to be extremely careful.

[FOIMonkey]

Another example here: https://www.whatdotheyknow.com/request/scaffolding_contractors_used_by#incoming-2126200

The FOI officer noticed and provided the data as a spreadsheet.

[FOIMonkey]

+1 This is almost unreadable:

https://www.whatdotheyknow.com/request/sexual_orientation_workforce_dat_113#incoming-1850225

[RichardTaylor]

+1 Another example https://www.whatdotheyknow.com/request/insider_trading_action_taken_by#incoming-2161478

[HelenWDTK]

+1 An authority have contacted us because they are unhappy about the way that a response that they have provided is displaying. The table in the email got mangled by the system.

[WilliamWDTK]

Here is another example where it is difficult to read. Here, the pipe separators that you sometimes see aren't present: https://www.whatdotheyknow.com/request/availability_of_sober_accommodat_34#incoming-1938694

The formatted email appears as below:

[garethrees]

In the admin view for the main body part (https://github.com/mysociety/alaveteli/pull/7999) I used simple_format, which is doing a good enough job at rendering these in most cases I've looked at (haven't looked at loads, so might want to take a slightly larger sample before adding).

e.g. https://www.whatdotheyknow.com/request/foi_request_modern_slavery_train_13#incoming-2502777 → https://www.whatdotheyknow.com/admin/attachments/4466504/edit

[HelenWDTK]

+1 An authority has contacted us to complain that our mangling of their response has damaged the reputation of the information governance team.

[WilliamWDTK]

This also affects responses from Welsh bodies, which often have bilingual signatures using tables/similar, ending up like this: Original: Admin view: