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?

Improve this question
$\endgroup$

4 Answers 4

Reset to default
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}} *)
Improve this answer
$\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$ Commented 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$ Commented 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$ Commented 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$ Commented 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"}]
Improve this answer
$\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$ Commented Aug 10, 2012 at 13:38
  • $\begingroup$ Definately the way +1 $\endgroup$ Commented 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)

Improve this 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"}]}]
Improve this answer
$\endgroup$

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.