# What is a good way of perfoming operations on specified columns of a table with many columns

If I have a table of data and I want to do a specific operation on say the third column, I usually do something like this:

OperatedOnTable =
MyTable /. {Col1_, Col2_, Col3_, Col4_} -> {Col1, Col2, 2*Pi*Col3 + 42, Col4}


where MyTable has four columns in this case.

This is fine when I have a table with several columns, but when I have a table that has, say, twenty columns, this quickly becomes unwieldy and silly, as I need to map every single column such that I may pick out the columns I want to operate on.

For tables with large numbers of columns, how can I perform an operation on a specific column or column?

I should add, it would be preferable to be able to perform operations on multiple columns as is possible with my example, e.g.

OperatedOnTable =
MyTable /.
{Col1_, Col2_, Col3_, Col4_} -> {Col1 + 1/137, Col2, 2*Pi*Col3 + 42, Col4}


Note that I have performed two distinct operations on two distinct columns with a single command. I'd like to be able to replicate this functionality.

I want to achieve the same functionality as:

OperatedOnTable =
MyTable /.
{Col1_, Col2_, Col3_, Col4_, Col5_, Col6_, Col7_, Col7_, Col8_,
Col9_, Col10_, Col11_, Col12_, Col13_, Col14_, Col15_, Col16_} ->
{Col1 + 1/137, Col2, 2*Pi*Col3 + 42, Col4, Col5, Col6, Col7, Col7, Col8,
Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col16}


This achieves what I want, manipulation of multiple columns in a single operation, but as we can see it is somewhat silly for tables with tens of columns.

I'm using Mathematica 12.1.1.0.

• MyTable = MapAt[2*Pi*#+ 42&, MyTable, {All, 3}]?
– kglr
Sep 27 '20 at 20:18
• ... or MyTable[[All, 3]] = 2*Pi*MyTable[[All, 3]] + 42?
– kglr
Sep 27 '20 at 20:19
• The first one is a good one but that means only one operation on one column at a time right? Won't the second just leave a result with one column?
– Q.P.
Sep 27 '20 at 20:23
• Q.P., if you inspect myTable = Array[a, {4, 4}]; myTable = MapAt[foo, myTable, {All, 3}];myTable and myTable = Array[a, {4, 4}]; myTable[[All, 3]] = foo /@ myTable[[All, 3]];myTable you can see that both methods modify the third column of myTable.
– kglr
Sep 27 '20 at 20:40
• @kglr, quite right! What about different operations on different columns in the same operation? This is what I really like about OperatedOnTable = MyTable /. {Col1_, Col2_, Col3_, Col4_}->{Col1 + 1/137, Col2, 2*Pi*Col3 + 42, Col4}. It's also very clear to see what one has done, but for more than say eight or ten columns it gets a bit silly.
– Q.P.
Sep 27 '20 at 20:49

myTable = Array[Subscript[a, Row[{##}]] &, {5, 10}];

MatrixForm @ myTable


functions = {foo, bar};
columns = {2, 5};


1. Part assignment:

myTable[[All, columns]] = {foo@#, bar@#2} & @@@ myTable[[All, columns]];
myTable // MatrixForm


myTable = Array[Subscript[a, Row[{##}]] &, {5, 10}];
myTable = Fold[MapAt[First@#2, #, {All, Last@#2}] &, myTable,
Transpose[{functions, columns}]];

myTable // MatrixForm


myTable = Array[Subscript[a, Row[{##}]] &, {5, 10}];
myTable = ReplacePart[myTable,
MapThread[{i_, #} :> #2[myTable[[i, #]]] &, {columns, functions}]];

myTable // MatrixForm


myTable = Array[Subscript[a, Row[{##}]] &, {5, 10}];

myTable = MapIndexed[asso[#2[[2]]]@# /. Missing[__] -> Identity &, myTable, {2}];
myTable // MatrixForm


myTable = Array[Subscript[a, Row[{##}]] &, {5, 10}];

myTable = Normal @ Dataset[myTable][All, Thread[columns -> functions]];
myTable // MatrixForm


• Looks good. Let me have a play around and I'll accept.
– Q.P.
Sep 27 '20 at 21:12

A little too long for a comment, and I have marked this as 'Community Wiki' (not an original contribution). In addtion, I think the Query method should not be 'buried' in a comment.

Query

Sjoerd Smit has posted a neat method for applying a function to a matrix column using Query.

   Query[All, {1 -> (#+1/137&),3->(2 Pi# + 42&)}]@myTable2//TeXForm


$$\left( \begin{array}{cccc} a_{11}+\frac{1}{137} & a_{12} & 2 \pi a_{13}+42 & a_{14} \\ a_{21}+\frac{1}{137} & a_{22} & 2 \pi a_{23}+42 & a_{24} \\ a_{31}+\frac{1}{137} & a_{32} & 2 \pi a_{33}+42 & a_{34} \\ a_{41}+\frac{1}{137} & a_{42} & 2 \pi a_{43}+42 & a_{44} \\ a_{51}+\frac{1}{137} & a_{52} & 2 \pi a_{53}+42 & a_{54} \\ a_{61}+\frac{1}{137} & a_{62} & 2 \pi a_{63}+42 & a_{64} \\ a_{71}+\frac{1}{137} & a_{72} & 2 \pi a_{73}+42 & a_{74} \\ a_{81}+\frac{1}{137} & a_{82} & 2 \pi a_{83}+42 & a_{84} \\ a_{91}+\frac{1}{137} & a_{92} & 2 \pi a_{93}+42 & a_{94} \\ a_{101}+\frac{1}{137} & a_{102} & 2 \pi a_{103}+42 & a_{104} \\ \end{array} \right)$$

 Query[All, {1 -> foo, 3 -> bar, 6-> Sqrt}]@myTable//TeXForm


$$\left( \begin{array}{cccccc} \text{foo}\left[a_{11}\right] & a_{12} & \text{bar}\left[a_{13}\right] & a_{14} & a_{15} & \sqrt{a_{16}} \\ \text{foo}\left[a_{21}\right] & a_{22} & \text{bar}\left[a_{23}\right] & a_{24} & a_{25} & \sqrt{a_{26}} \\ \text{foo}\left[a_{31}\right] & a_{32} & \text{bar}\left[a_{33}\right] & a_{34} & a_{35} & \sqrt{a_{36}} \\ \text{foo}\left[a_{41}\right] & a_{42} & \text{bar}\left[a_{43}\right] & a_{44} & a_{45} & \sqrt{a_{46}} \\ \text{foo}\left[a_{51}\right] & a_{52} & \text{bar}\left[a_{53}\right] & a_{54} & a_{55} & \sqrt{a_{56}} \\ \end{array} \right)$$

Query (in this case) seems to be based on MapAt 'under the hood'

In[7]:=Query[All, {1 -> (#+ 137&),3->(2 Pi# + 42&)}]@myTable2x

Out[7]= MapAt[#1 + 137 & , {All, 1}][MapAt[2 Pi #1 + 42 & , {All, 3}][myTable2x]]


And for the lazy, the following doesn't seem too bad either:

myTable//MapAt[2 Pi # + 42 & , {All, 3}]// MapAt[# + 137 & , {All, 1}]


$$\left( \begin{array}{cccccc} a_{11}+137 & a_{12} & 2 \pi a_{13}+42 & a_{14} & a_{15} & a_{16} \\ a_{21}+137 & a_{22} & 2 \pi a_{23}+42 & a_{24} & a_{25} & a_{26} \\ a_{31}+137 & a_{32} & 2 \pi a_{33}+42 & a_{34} & a_{35} & a_{36} \\ a_{41}+137 & a_{42} & 2 \pi a_{43}+42 & a_{44} & a_{45} & a_{46} \\ a_{51}+137 & a_{52} & 2 \pi a_{53}+42 & a_{54} & a_{55} & a_{56} \\ \end{array} \right)$$

Inner

Inner is another possibility (see here)

myTable//Inner[Times,#,ConstantArray[1,Length@#[[1]]],{#1+1/137,#2,#3, 2 Pi #4+42, ##5}&]&//TeXForm


$$\left( \begin{array}{cccccc} a_{11}+\frac{1}{137} & a_{12} & a_{13} & 2 \pi a_{14}+42 & a_{15} & a_{16} \\ a_{21}+\frac{1}{137} & a_{22} & a_{23} & 2 \pi a_{24}+42 & a_{25} & a_{26} \\ a_{31}+\frac{1}{137} & a_{32} & a_{33} & 2 \pi a_{34}+42 & a_{35} & a_{36} \\ a_{41}+\frac{1}{137} & a_{42} & a_{43} & 2 \pi a_{44}+42 & a_{45} & a_{46} \\ a_{51}+\frac{1}{137} & a_{52} & a_{53} & 2 \pi a_{54}+42 & a_{55} & a_{56} \\ \end{array} \right)$$

Related

Applying a function to every item in a column of a matrix

Matrices

myTable = Array[Subscript[a, Row[{##}]] &, {5, 6}];
myTable2 = Array[Subscript[a, Row[{##}]] &, {10, 4}];

• I see you are someone of culture when it comes to choices or arbitrary numbers for examples. Sep 29 '20 at 12:23

kglr's ReplacePart method is completely general, but I think in many situations writing the rules explicitly is easier and gives more readable code. So I would write:

data = Array[f, {4, 4}];
ReplacePart[data, {{r_, 1} :> data[[r, 1]] + 137, {r_, 3} :> 2 π data[[r, 3]]}]

{{137 + f[1, 1], f[1, 2], 2 π f[1, 3], f[1, 4]},
{137 + f[2, 1], f[2, 2], 2 π f[2, 3], f[2, 4]},
{137 + f[3, 1], f[3, 2], 2 π f[3, 3], f[3, 4]},
{137 + f[4, 1], f[4, 2], 2 π f[4, 3], f[4, 4]}}


I can see what my code does at a glance, but I can't do the with kglr's code. Of course, you may consider this more a reflection on my smarts than a valid critique of kglr's coding style.

### Update

The following is added to address concerns raised by the Craig Carter in a comment to this answer.

It's pretty simple. Just change the column specs to the appropriate row specs. Like so:

ReplacePart[data, {{1, c_} :> data[[1, c]] + 137, {3, c_} :> 2 π data[[3, c]]}

{{137 + f[1, 1], 137 + f[1, 2], 137 + f[1, 3], 137 + f[1, 4]},
{f[2, 1], f[2, 2], f[2, 3], f[2, 4]},
{2 π f[3, 1], 2 π f[3, 2], 2 π f[3, 3], 2 π f[3, 4]},
{f[4, 1], f[4, 2], f[4, 3], f[4, 4]}}

• I have to say this is more what I had in mind. One can immediately ready what this does, superficially it's similar to the example I provided in my question. Would you say there are any limitations with this approach? I ask as you said kglr's solution is more general.
– Q.P.
Sep 27 '20 at 22:12
• @Q.P. This method is also general, but if your table had many, many columns and you were going to modify a lot of them, kglr's code would save some typing, Sep 27 '20 at 22:22
• The Query method is very nice. Is there a simple way to achieve the same thing for rows? ( e.g. Transpose[ Query[All, {1 -> foo, 2 -> bar, 4 -> Sqrt}]@Transpose[myTable]] ) Sep 29 '20 at 18:55
• @CraigCarter. I don't understand how your comment relates to my answer. It seems to be new question rather than a comment. Sep 29 '20 at 20:35
• @m_goldberg it is probably a new question because it pertains to rows. Nevertheless, if your answer could be generalized here, it would be instructive for others, I think. Sep 29 '20 at 21:19