Reducing boilerplate with associations

Question

I'm looking for advice either on alternative ways to approach my problem in general, or specifically on meta-programming (which seems to me to be a way around this). In my work I have databases of geometrical objects (points and vectors) that are stored with a field per component ("foo_x, foo_y, foo_z"). I've adopted a style of working with these where I use DatabaseLink to query the DB and get all of my fields, then I create a new association with real Mathematica lists so that I can work with them more naturally for the rest of the notebook. Here's an example with obviously a dummy data source.

rawData = <|"FooX" -> #[[1]], "FooY" -> #[[2]], "FooZ" -> #[[3]], "BarX" -> #[[4]], "BarY" -> #[[5]], "BarZ" -> #[[6]]|> & /@ 
RandomReal[{1, 10}, {10, 6}]
vectorData = <|"Foo" -> {#FooX, #FooY, #FooZ}, "Bar" -> {#BarX, #BarY, #BarZ}|> & /@ rawData
ListPointPlot3D[vectorData[[All, "Foo"]]]

(The ListPlot is just to illustrate why I bother with vectorData). The code in vectorData is the boilerplate I want to reduce though. I've already listed the fields in the query to populate rawData and the fields have a consistent naming scheme. The rule really is as simple as <|"___"-> {#___X, #___Y, #___Z}|> so I'm hoping there is a way I can generate vectorData generically without having to always duplicate field names between the DB query and the "nice" representation. Does anyone know a way to accomplish that (or, stepping back, have a better approach to this kind of workflow?)

Answer 1

This should do it:

vectorize[label_, assocs_] := Map[
  label -> Lookup[#, {label <> "X", label <> "Y", label <> "Z"}] &,
  assocs
  ]

Association /@ Transpose[vectorize[#, rawData] & /@ {"Foo", "Bar"}]

Perhaps you don't need the intermediate format rawData or the association vectorData. You could just query the database directly like:

vectors[label_, conn_] := SQLSelect[conn, "table", {label <> "_x", label <> "_y", label <> "_z"}]

Although I haven't tried it, I think this should give you the matrix you are looking for.

At the top of your file, you might put a list of variables like this:

foo = vectors["Foo", conn];
bar = vectors["Bar", conn];

Or you could also put them in an Association, as you wish. If you really want associations in your format (which is not necessarily the way to go) then you could use MapThread:

MapThread[<| "Foo" -> #, "Bar" -> #2 |>, {foo, bar}]

The reason it might not be the way to go is that it might be preferable to use MapThread directly when doing computations, rather than working with Associations.