Another example: Eight of the Worst Spreadsheet Blunders

As I always say: "Excel should not be on the computer of a scientist/data scientist". It seems to do some kind of job for people working in administration though - I am told.

Here a slightly different way of cleaning the data in Mathematica.

(*Import*)
dataimport = Import["https://www.rio2016.com/en/medal-count-sports", "Data"];
(*Generate first table*)
tableresults = 
  Select[Map[Flatten, 
 Transpose[{Flatten[ConstantArray[#[[1]], #[[2]]] & /@ 
 Transpose[{dataimport[[4, 2]][[All, 1]], Length /@ dataimport[[4, 2]][[All, 2]]}]], Flatten[dataimport[[4, 2]][[All, 2]], 1]}]], Length[#] == 8 &];
(*Generate table with type of medal*)
TableForm[
 Flatten /@ Transpose[{tableresults[[All, 1 ;; 4]], ConstantArray["Gold", Length[tableresults]], tableresults[[All, 5 ;; 6]], 
 ConstantArray["Silver", Length[tableresults]], tableresults[[All, 7 ;; 8]], ConstantArray["Bronze", Length[tableresults]]}]]

To me that seems to be much shorter than the endless list of Excel instructions.

Cheers,

M.

Hi Tim,

would something like this work for you?

As before

jointTable = 
  Flatten /@ 
     Transpose[{tableresults[[All, 1 ;; 4]], 
     ConstantArray["Gold", Length[tableresults]], 
     tableresults[[All, 5 ;; 6]], 
     ConstantArray["Silver", Length[tableresults]], 
     tableresults[[All, 7 ;; 8]], 
     ConstantArray["Bronze", Length[tableresults]]}];

This table contains all the data. You now only need to pick the columns you need and join them together:

Join[jointTable[[All, 1 ;; 5]], jointTable[[All, {1, 2, 6, 7, 8}]], jointTable[[All, {1, 2, 9, 10, 11}]]] // TableForm

You can, of course, sort this now according to your favourite criterion. If you call

singleTable=Join[jointTable[[All, 1 ;; 5]], jointTable[[All, {1, 2, 6, 7, 8}]], jointTable[[All, {1, 2, 9, 10, 11}]]]

Then

SortBy[singleTable, First] // TableForm

or

SortBy[singleTable, #[[3]] &] // TableForm

or

SortBy[singleTable, #[[4]] &] // TableForm

might give you representation that you prefer.

Cheers,

Marco

Make sure you audit your results carefully. It would be unfortunate for people to not know of Singapore's first ever Gold medal this year!

This is a brilliant response, @Marco Thiel, and I second that "Excel should not be on the computer of a scientist/data scientist".

Hi Sander,

what a nice reference. I very much agree that in many areas, like biology, they do tend to use Excel. I do work at the interface with other areas of science and have experienced similar issues a lot.

Thanks a lot for that reference. I'll make sure to use it...

Cheers,

Marco

Very elegant, thank you for the lesson!

Not that I was able to end up with a 100% correct solution either, but unfortunately for the swimmers in the women's 100m freestyle and the men's 100m butterfly the results are lost in your solution. Due to the corner case in these races where ties occurred for 1st place in the women's race and silver in the men's race.

I completely agree with your sentiment on the use of Excel. The original author's goal was to create an Excel spreadsheet. My ugly solution was a poor example of using the targeted tool.

Scraping web data is a real challenge!

And, IMHO, cleaning scrapped data requires a robust visual inspection tool. While Mathematica 11 has made good steps to improve display of Datasets, it is still far short of a tool that is good for visual inspection.

Tim, Good hunting on a solution that works to your goal!

For Big-Data Scientists, ‘Janitor Work’ Is Key Hurdle to Insights http://www.nytimes.com/2014/08/18/technology/for-big-data-scientists-hurdle-to-insights-is-janitor-work.html