14
$\begingroup$

Consider a relational table derived from survey data, where each column ("001-01" ...) represents a responder and each row ("MDQ1"...) a survey question.

To help intuition, response data is represented graphically by color-scaled disks, but the underlying data matrix is just a table of integers and bordered by the aforementioned string metadata in the first row and column.

enter image description here

A small submatrix is given here:

  data = {{"ID", "MDQ1", "MDQ2", "MDQ3"}, {"001-01", 3, 2, 5}, {"002-01", 4, 1,
       5}, {"003-01", 2, 2, 5}}

Further, define a variable for the headers

ids = {"ID", "MDQ1", "MDQ2", "MDQ3"}

To clean this data, the response values for a subset of questions, for example {"MDQ1", "MDQ3"} must be transformed, for example, by the function (6-#)&

(Note: in the figure I highlighted "MDQ1" and "MDQ5" - that's just for illustration)

I would prefer to use ReplacePart but afaik, pattern matching can only be applied to position index and not to the data values - but in this case the metadata (headers) is part of the data.

The alternative for this task is MapAt, but it requires the intermediate generation of the positional index and barely readable code:

MapAt[If[NumericQ[#], 6 - #, #] &, data, 
 Flatten[Table[{i, First@First@Position[ids, #]}, {i, 2, 
      Length@data}] & /@ {"MDQ1", "MDQ3"}, 1]]

Is there a way to close the semantic gap between MapAt and ReplacePart and achieve more terse, more readable code?

$\endgroup$

4 Answers 4

11
$\begingroup$

Sometimes, the simplest approach might not be all that unreadable after all. Double Transpose works fine here:

transform[data_, q_, fun_] := 
     Transpose[data] /. ({#, x__} :> {#, Sequence @@ fun[{x}]} & /@ q) // Transpose

transform[data, {"MDQ1", "MDQ3"}, 6 - # &]
(* {{"ID", "MDQ1", "MDQ2", "MDQ3"}, {"001-01", 3, 2, 1}, 
    {"002-01", 2, 1,1}, {"003-01", 4, 2, 1}} *)
$\endgroup$
4
  • 2
    $\begingroup$ I like the simplicity. IMO it would be even more readable to use {id : Alternatives @@ q, x__} :> {id}~Join~fun@{x} as the replacement rule. $\endgroup$ Aug 10, 2012 at 10:24
  • 2
    $\begingroup$ @Simon or to Golf it: transform[data_, {q__}, fun_] := (data\[Transpose] /. {id : q | _?0, x__} :> {id} ~Join~ fun@{x})\[Transpose] $\endgroup$
    – Mr.Wizard
    Aug 10, 2012 at 11:29
  • $\begingroup$ @Mr.Wizard, I love it. Especially the use of a pattern which will never match anything :-) Could also use q|q there. $\endgroup$ Aug 10, 2012 at 12:17
  • $\begingroup$ @Simon Thanks. Indeed you could, but you slow down the matching as every alternative is tried twice on expressions that don't match. (EDIT: I realize I wrote "Golf" but I was actually seeking to make it practical, if a bit obfuscated.) $\endgroup$
    – Mr.Wizard
    Aug 10, 2012 at 21:01
8
$\begingroup$

I default to using Part in cases like this. I like the fact that it can be used for in-place modification, or on a copy of the data.

I would also use either a list of replacement rules (optimized with Dispatch if it is long) or a DownValues hash table in place of searching with Position in each application.

With:

data = {{"ID", "MDQ1", "MDQ2", "MDQ3"},
        {"001-01", 3, 2, 5}, {"002-01", 4, 1, 5}, {"003-01", 2, 2, 5}};

idx = # -> #2[[1]] & ~MapIndexed~ data[[1]];

On a copy:

keyMap1[f_, dat_, key_] := Module[{x = dat},
   Scan[(x[[2 ;;, #]] = f /@ x[[2 ;;, #]]) &, key /. idx];
   x
 ]

keyMap1[6 - # &, data, {"MDQ1", "MDQ3"}]
{{"ID", "MDQ1", "MDQ2", "MDQ3"},{"001-01", 3, 2, 1},{"002-01", 2, 1, 1},{"003-01", 4, 2, 1}}

In-place modification:

SetAttributes[keyMap2, HoldAll]

keyMap2[f_, dat_, key_] :=
  Scan[(dat[[2 ;;, #]] = f /@ dat[[2 ;;, #]]) &, key /. idx]

keyMap2[6 - # &, data, {"MDQ1", "MDQ3"}]

data
{{"ID", "MDQ1", "MDQ2", "MDQ3"},{"001-01", 3, 2, 1},{"002-01", 2, 1, 1},{"003-01", 4, 2, 1}}

In either case if you have multiple tables you could add idx as a parameter or use {"MDQ1", "MDQ3"} /. idx (with the appropriate index) as an argument.

I chose to explicitly map f onto the column elements; this is more general, but it is not as fast as using the Listable property. If you are willing to make sure that every function you use is listable then the method will perform better after replacing f /@ with f @.

Alternatively you might consider bundling this information with the table itself in a container:

data2 = idxtab[data, idx];

keyMap3[f_, idxtab[dat_, index_], key_] :=
  Module[{x = dat},
    (x[[2 ;;, #]] = f /@ x[[2 ;;, #]]) & /@ (key /. index);
    x
  ]

keyMap3[6 - # &, data2, {"MDQ1", "MDQ3"}]
$\endgroup$
2
  • 1
    $\begingroup$ +1. I agree with the approach. Behind this particular question, I see a broader one, requesting a different and more mutable (relational) data model, similar to data frames in R, which Mathematica currently lacks. I would approach the problem in similar ways, since mutability is IMO inherent in the general question / problem I see here, if not this particular one. $\endgroup$ Aug 10, 2012 at 13:38
  • $\begingroup$ Definately the way +1 $\endgroup$
    – Rojo
    Aug 10, 2012 at 15:09
3
$\begingroup$

You could use Position initially to know where in each row to MapAt, then Map MapAt over the data:

transform[data_, q_, fun_] := 
  With[{i = Position[ids, s_ /; MemberQ[q, s]]}, MapAt[fun, #, i] & /@ data]

(usage stolen from R.M's answer)

$\endgroup$
2
$\begingroup$

We can make OP's original approach just a little bit shorter:

MapAt[If[NumericQ[#], 6 - #, #] &, data, 
      Join @@ Tuples[{Range[2, Length[dat]], 
                      Flatten[Position[ids, #] & /@ {"MDQ1", "MDQ3"}]}]
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.