Mathematica Stack Exchange is a question and answer site for users of Mathematica. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 11 down vote accepted

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}} *)
share|improve this answer
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. – Simon Woods Aug 10 '12 at 10:24
@Simon or to Golf it: transform[data_, {q__}, fun_] := (data\[Transpose] /. {id : q | _?0, x__} :> {id} ~Join~ fun@{x})\[Transpose] – Mr.Wizard Aug 10 '12 at 11:29
@Mr.Wizard, I love it. Especially the use of a pattern which will never match anything :-) Could also use q|q there. – Simon Woods Aug 10 '12 at 12:17
@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.) – Mr.Wizard Aug 10 '12 at 21:01

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.


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];

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"}]

{{"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);

keyMap3[6 - # &, data2, {"MDQ1", "MDQ3"}]
share|improve this answer
+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. – Leonid Shifrin Aug 10 '12 at 13:38
Definately the way +1 – Rojo Aug 10 '12 at 15:09

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)

share|improve this answer

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"}]}]
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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