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
  • 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, 2014 at 1:27

2 Answers 2

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$
3
  • 1
    $\begingroup$ I could have sworn you were going to eat :) $\endgroup$
    – RunnyKine
    Oct 14, 2014 at 1:42
  • 1
    $\begingroup$ @RunnyKine The stomach will survive. imgs.xkcd.com/comics/duty_calls.png $\endgroup$
    – Mr.Wizard
    Oct 14, 2014 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, 2014 at 20:00
1
$\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 and acknowledge you have read our privacy policy.