I look for an efficient way to work with a column as a part of large dataset. Unfortunatly because of my limited experience, so far the only sure way for me was to extract the column first, do my replacements and then insert the column back (e.g. like here Elegant operations on matrix rows and columns)

I wonder what would be a more direct way for working with a column as a part of the whole dataset (my dataset is a list of lists, not "Dataset", as as otherwise I could have used this technique Replace Elements in a Dataset by rules)

Here is an example of my data:

data={{"age", "type", "size"},{10,"cat",1},{22,"dog",5},{2,"cat",11}};

I would like to replace all "cat" in column "type" by 1 and "dog" by zero. However my question is more general -- just how to work and apply whichever command applicable to a list to a column without extracting it and so obtaining the modification directly within the whole dataset.

  • $\begingroup$ Probably MapAt[f,data,{All,2}], where f can be defined to make desired transformations, say f["cat"]:=1; f["dog"]:=0; f[x_]:=x. $\endgroup$
    – Alx
    Jul 19, 2019 at 7:38
  • $\begingroup$ Nice, in principle it should work for me. $\endgroup$
    – Kass
    Jul 19, 2019 at 7:55
  • $\begingroup$ I take it you don't want to use 'modify in place', for example data[[All,2]]=data[[All,2]]/.{"cat":> 1, "dog":> 0};data ? $\endgroup$
    – user1066
    Jul 19, 2019 at 8:13
  • $\begingroup$ @user1066 it is nice too, thanks! Though for a bigger problem e.g. replacing within several columns simultaneously and according to several rules I guess the answer Alx is a bit more scalable. $\endgroup$
    – Kass
    Jul 19, 2019 at 9:13
  • $\begingroup$ Note that MapAt creates a new array with col-2 modified: MapAt[f,data,{All,2}];data. If the dataset is large you may (or may not) want that. All is explained in the 'Elegant operations...' link you quote. $\endgroup$
    – user1066
    Jul 19, 2019 at 9:56

3 Answers 3


First define the function:


Now apply this to the second column with MapAt:


gives desired substitution:

{{"age", "type", "size"},{10,1,1},{22,0,5},{2,1,11}}

Try the following.

Here is your example:

data = {{"age", "type", "size"}, {10, "cat", 1}, {22, "dog", 5}, {2,"cat", 11}};


data /. {"cat" -> 1, "dog" -> 0}

(* {{"age", "type", "size"}, {10, 1, 1}, {22, 0, 5}, {2, 1, 11}}  *)

Edit: to address your question. If you may have "cat" or "dog" in other columns make a bit more detailed rule. Let your data has the form:

data2 = {{"age", "type", "size"}, {"cat", 10, 10}, {10, 10,"dog"}, {10, "cat", 1}, {22, "dog", 5}, {2, "cat", 11}};

Then try this:

data2 /. {{a_, "cat", b_} -> {a, 1, b}, {c_, "dog", d_} -> {c, 0, d}}

(*  {{"age", "type", "size"}, {"cat", 10, 10}, {10, 10, "dog"}, {10, 1, 
  1}, {22, 0, 5}, {2, 1, 11}}  *)

Have fun!

  • 1
    $\begingroup$ That far I got too. What should I do when I have "cat" and "dog" in other columns but I do not want to replace there? $\endgroup$
    – Kass
    Jul 19, 2019 at 7:55
  • $\begingroup$ @Kass Have a look at the edit. $\endgroup$ Jul 19, 2019 at 8:01
data = {{"age", "type", "size"}, {10, "cat", 1}, {22, "dog", 5}, {2, "cat", 11}};

Using ReplaceAt (new in 13.1) with Switch

ReplaceAt[x_ :> Switch[x, "cat", 1, "dog", 2, _, x], {All, 2}] @ data

{{"age", "type", "size"}, {10, 1, 1}, {22, 2, 5}, {2, 1, 11}}


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.