1
$\begingroup$

I have data in a CSV file I've Import[]ed. Some of the data needs fixing up - date formats changed, data normalized, etc. After fixing up I'll insert it into an SQL database.

My CSV file has headers, but they may be reordered in future files.

What is a sane way to fix up values in columns? EG: Apply FixDateField[] to all the values in a column and return the resulting table.

I'm missing something here. What is it?

Example data, assume we have a few thousand rows, and a hundred or so actual columns. But only a few of these need fixing up.

 {{"Sample", "Data", "creationdate", "othervariable"},
  {2.3, 4.3, "20141008125809", 8.4},
  {3.2, 1.3, "20141008125809", 9.2},
  {3.2, 1.3, "20141008125809", 11.84}}
$\endgroup$
  • 1
    $\begingroup$ Yes, you are missing the sample data that I highly recommend you include in your question if you are likely to receive useful answers. ;-) $\endgroup$ – Mr.Wizard Oct 14 '14 at 1:27
1
$\begingroup$

Since v9 (if I recall) you can use MapAt with Span for such things:

sample = {{"Sample", "Data", "creationdate", "othervariable"}, {2.3, 4.3, 
    "20141008125809", 8.4}, {3.2, 1.3, "20141008125809", 9.2}, {3.2, 1.3, 
    "20141008125809", 11.84}};

MapAt[fixDate, sample, {2 ;;, 3}]
{{"Sample", "Data", "creationdate", "othervariable"},
 {2.3, 4.3, fixDate["20141008125809"], 8.4},
 {3.2, 1.3, fixDate["20141008125809"], 9.2},
 {3.2, 1.3, fixDate["20141008125809"], 11.84}}

For in-place modification you can use Set:

sample[[2 ;;, 3]] = foo /@ sample[[2 ;;, 3]];

sample
{{"Sample", "Data", "creationdate", "othervariable"},
 {2.3, 4.3, foo["20141008125809"], 8.4},
 {3.2, 1.3, foo["20141008125809"], 9.2},
 {3.2, 1.3, foo["20141008125809"], 11.84}}

If you are going to be frequently performing column-based operations it is better to transpose the table turning columns into rows. Among other things this makes it possible to pack data that is of the same type in a given row (originally column). For example here is one possible format:

sample = {{"Sample", "Data", "creationdate", "othervariable"}, {2.3, 4.3, 
    "20141008125809", 8.4}, {3.2, 1.3, "20141008125809", 9.2}, {3.2, 1.3, 
    "20141008125809", 11.84}};

idx = PositionIndex[sample[[1]]];
dat = sample[[2 ;;]]\[Transpose];

Now:

MapAt[fixDate, dat, idx @ "creationdate"]
{{2.3, 3.2, 3.2},
 {4.3, 1.3, 1.3},
 fixDate[{"20141008125809", "20141008125809", "20141008125809"}],
 {8.4, 9.2, 11.84}}

Note that fixDate will need to be written to handle vector arguments but in Mathematica this is often desirable anyway.

This makes it convenient to reference columns by name rather than number. For another example, especially if you do not have PositionIndex, please see:

$\endgroup$
  • 1
    $\begingroup$ I could have sworn you were going to eat :) $\endgroup$ – RunnyKine Oct 14 '14 at 1:42
  • 1
    $\begingroup$ @RunnyKine The stomach will survive. imgs.xkcd.com/comics/duty_calls.png $\endgroup$ – Mr.Wizard Oct 14 '14 at 1:49
  • $\begingroup$ MapAt was exactly what I was looking for. Until DataSets become mutable, this is the right approach. Thanks very much! $\endgroup$ – user4860 Oct 14 '14 at 20:00
0
$\begingroup$

If you frequently reorder the columns, you could do something like:

tbl = {{"colNbr", "ColStr", "ColDate"}, {1, "1", "1/11/11"}, {2, "2",  "2/12/12"}};
fNbr[n_] := n^2
fStr[s_] := s <> "joined"
fDate[d_] := dodate[d]
hdrs = {"ColDate", "colNbr", "ColStr"};
funcs = {fDate, fNbr, fStr};
funcsReordered = First /@ Extract[funcs, Position[hdrs, #] & /@ tbl[[1]]]

Inner[#1@#2 &, funcsReordered, Transpose@tbl[[2 ;;]], List]

(* {{1, "1joined", dodate["1/11/11"]}, {4, "2joined", dodate["2/12/12"]}}
*)
$\endgroup$

Your Answer

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