Replacing within a column without extracting it/modifying data

Question

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.

Answer 1

First define the function:

f["cat"]:=1;
f["dog"]:=0;
f[x_]:=x;

Now apply this to the second column with MapAt:

MapAt[f,data,{All,2}]

gives desired substitution:

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

Answer 2

Try the following.

Here is your example:

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

Now:

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!