Sometimes I get data requests that sound like they should be easy but turn out to have not much in the way of usable data available. What were the hottest days on record in the UK (you may have noticed that it’s a bit warm this week, plus it’s likely to get warmer).
So hottest day ever is easy, that turns up in press releases from the Met Office fairly often, as does on occasions the previous hottest day, and sometimes information about other heatwaves. But getting together a top 10 (or more) takes some work to put together several sources of data.
Some sources:
- Met Office list of various extremes
- Met Office reports on various weather events (quite useful for data for how hot it was in different parts of the UK during heatwaves)
- Curated list of the hottest day for each year
The issue is that there aren’t many spreadsheets (or any), so at least part of finding the hot days means looking through text – which would be much easier if you could make the list sortable by temperature.
How to quickly make a list sortable in a bit more detail:
- First copy and paste the list into a spreadsheet.
- Use the =MID formula to pull out the temperature in each line to a separate cell. This works here because the start of each line is (mostly) standardised, with the year followed by the temperature (to one decimal place). The formula is =MID(A1,6,4), where A1 is the cell reference, 6 is the number of characters in where the information you want starts (after year and a space), and four is the number of characters you want.
- Adding random characters at the end of the above formula (using something like &”££$£”) is helpful for turning it into a number (as the output initially is text).
- Copy your formula down your list. Copy all and paste values only. Find and replace the extra characters with nothing. You should be left with a column of numbers.
- Sort your list. You may need to clean up a few lines where the formula didn’t work. After that you can re-sort to get an idea of some super-hot days you can cross-reference with other sources and add into your overall list
Want more? Buy Getting Started with Data Journalism, a beginners and beyond guide to finding, cleaning analysing and visualising data in any size newsroom.
