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 '19 at 7:38
  • $\begingroup$ Nice, in principle it should work for me. $\endgroup$ – Kass Jul 19 '19 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 '19 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 '19 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 '19 at 9:56

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 '19 at 7:55
  • $\begingroup$ @Kass Have a look at the edit. $\endgroup$ – Alexei Boulbitch Jul 19 '19 at 8:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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