Modifying list of lists to standardise lengths and field positions

Question

Sorry, beginner here. I feel this should be simple but I'm struggling...

I'm reading a set of race timing data from a web site and want to manipulate it into a list of lists that I can then export to a CSV file for later reading into a database.

Here are three example result rows:

 d = {{50, "M370", "John Doe", "Honda", 2.24, 56, 17.26, 24.38, 33.52, 
  38.69, 45.52, 53.69, "Practice"}, {51, "M398", "Jane Doe", 
  "KTM", 2.49, 52, 57.03, 47.51, "FAIL", "Practice"}, {56, "M553", 
  "John Wayne", "Husqvarna", 2.54, 53, "FLAG", "Practice"}}

The first row (John Doe) is a normal, 13-column wide result row and I want to add a 14th column with the value "False".

The second row (Jane Doe) is an example of a 'fail' which is where the competitor crashed or came off the track. In this case I just want to change

 {51, "M398", "Jane Doe", "KTM", 2.49, 52, 57.03, 47.51, "FAIL", "Practice"}

into

 {51, "M398", "Jane Doe", "KTM", 2.49, 52, 57.03, 47.51, 0, 0, 0, 0, "Practice", "False"}

i.e. remove the word "Fail", insert zeros as time placeholders and set column 14 value to 'false'.

Finally, if the competitor ahead (competitors start at 30 sec intervals) has a crash then the current competitor (John Wayne) is "flagged" and they get to go again. In this case I want to do change:

{56, "M553", "John Wayne", "Husqvarna", 2.54, 53, "FLAG", "Practice"}

into

{56, "M553", "John Wayne", "Husqvarna", 2.54, 53, 0, 0, 0, 0, 0, 0, "Practice", "True"}

I tried using Position[d, "FLAG", 2] to find "Flag entries but I'm struggling to convert its results into something that allows me to insert the required number of dummy columns and set the final column in a reasonably elegant way.

Any suggestions, please?

Answer 1

It is an application of rules using If statements. You may want to use Which instead. As always while programming such conditions, the order in which these are applied is important.

raceMods[k_List] := Module[{},
  If[FreeQ[k, "FAIL"] && FreeQ[k, "FLAG"] && Length@k == 13, 
   Append[k, "False"],
   If[MemberQ[k, "FAIL"], 
    k[[1 ;; 8]]~Join~{0, 0, 0, 0}~Join~{"Practice", "False"},
    If[MemberQ[k, "FLAG"], 
     k[[1 ;; 6]]~Join~{0, 0, 0, 0, 0, 0}~Join~{"Practice", "True"}]
    ]
   ]
  ]

---

Usage:

raceMods /@ d // TableForm

---

Result:

$$\left( \begin{array}{cccccccccccccc} 50 & \text{M370} & \text{John Doe} & \text{Honda} & 2.24 & 56 & 17.26 & 24.38 & 33.52 & 38.69 & 45.52 & 53.69 & \text{Practice} & \text{False} \\ 51 & \text{M398} & \text{Jane Doe} & \text{KTM} & 2.49 & 52 & 57.03 & 47.51 & 0 & 0 & 0 & 0 & \text{Practice} & \text{False} \\ 56 & \text{M553} & \text{John Wayne} & \text{Husqvarna} & 2.54 & 53 & 0 & 0 & 0 & 0 & 0 & 0 & \text{Practice} & \text{True} \\ \end{array} \right)$$

Answer 2

There are many ways to do this, and what is best would depend on more information about your context. I'm not sure if your example is directly from your source, or if you've already done some manipulation to get your example data. Here is an approach that allows for a great deal of control at the expense of verbosity:

NormalizeTiming[data : {_Integer, _String, _String, _String, _Real, _Integer, "FLAG", _String}] :=
 Append[Insert[data, Splice[{0, 0, 0, 0, 0, 0}], {6}], "True"];
NormalizeTiming[data : {_Integer, _String, _String, _String, _Real, _Integer, _Real, _Real, "FAIL", _String}] :=
  Append[Insert[data, Splice[{0, 0, 0, 0}], {6}], "False"];
NormalizeTiming[data : {_Integer, _String, _String, _String, _Real, _Integer, _Real, _Real, _Real, _Real, _Real, _Real, "Practice"}] :=
  Append[data, "False"];

You would then do

NormalizeTiming /@ d

If there are many more rules than this, then this approach would become unwieldy.

You could instead use predicates:

IsFlag[data_] := And[8 == Length@data, "FLAG" == data[[-2]]];
IsFail[data_] := And[10 == Length@data, "FAIL" == data[[-2]]];

NormalizeTiming[data_?IsFlag] := 
  Append[Insert[data, Splice[{0, 0, 0, 0, 0, 0}], {6}], "True"];
NormalizeTiming[data_?IsFail] := 
  Append[Insert[data, Splice[{0, 0, 0, 0}], {6}], "False"];
NormalizeTiming[data_] := Append[data, "False"];

These patterns and predicates may be overspecified or they may lack robustness (e.g. does capitalization matter?), but hopefully you get the idea.

You can try to do an all-in-one replacement that uses all of the necessary rules, but in my experience, this is difficult to maintain (i.e. when you want to add/update rules or just try to understand what you did 6 months later).

You might also want to look into Association and Dataset. If you can get your data into those kinds of structures, there are more possibilities for manipulating the data. But, as I said, more contex would be helpful before demonstrating those strategies.

Answer 3

Oh, so the data in the middle is all of the same "type". And also the boolean you want to append is some sort of "continue racing" flag, correct? It would have been helpful to see some column headers or some other semantic context. Anyway, this info makes it much easier (although still subject to robustness questions):

NormalizeTimingData[data_] :=
  With[
    {continue = MemberQ[data, "FLAG"]},
    Join[
      PadRight[TakeWhile[data, Not@*MatchQ["FLAG" | "FAIL"]], 12], 
      {data[[-1]], continue}]]