It just came out 5-6 days ago. Most shocking was that for gene-papers in Nature 30% has some issue with Excel. I was horrified!

Marco,

Thanks, that is very helpful!

Tim

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]], joinble[[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

Marco,

Thanks. Your solution is the kind of thing that I was looking for, though the final table doesn't match the format that I need exactly (I need each medal on its own row). Your repeated use of Map is exactly the kind of thing that I need to practice on, so thanks for showing that solution.

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.