Emulating R data frame getters with UpValues

Question

What's the best way to emulate R's data frames functionality? This includes the ability to select rows and columns in a 2-dimensional table by the string identifiers positioned typically in the first row or column, see this related question, (and thanks to Leonid Shifrin for cluing me to the term 'data frame.')

Wrapping a 2D array in the symbol DF enables leveraging upvalues to essentially overload Part for the getters, here defined separately for singleton and list string arguments; matching uses Position.

To select rows at level 1 of the array:

Part[DF[expr_], r_String, rest___] ^:= 
 expr[[Position[expr, r][[1, 1]], rest]]

Part[DF[expr_], r_List /; And @@ StringQ /@ r, rest___] ^:= 
 expr[[Position[expr, #][[1, 1]] & /@ r, rest]]

Similarly to get columns at level 2 of the array:

Part[DF[expr_], All, c_String] ^:= 
 Transpose[expr][[Position[Transpose@expr, c][[1, 1]]]]

Part[DF[expr_], All, c_List /; And @@ StringQ /@ c] ^:= 
 Transpose[expr][[Position[Transpose@expr, #][[1, 1]] & /@ c]]

These seem to work correctly (though I've not implemented preconditions checks that the header strings are at the correct level or that the array Dimension length is 2), eg given data:

data = {{"ID", "COL1", "COL2", "COL3"}, {"ROW1", 1, 2, 3}, {"ROW2", 4,
     5, 6}, {"ROW3", 7, 8, 9}};

yields:

DF[data][[{"ID", "ROW1", "ROW3"}]]

(* {{"ID", "COL1", "COL2", "COL3"}, {"ROW1", 1, 2, 3}, {"ROW3", 7, 8, 9}}*)

and

DF[data][[All, {"ID", "COL2"}]]

(* {{"ID", "ROW1", "ROW2", "ROW3"}, {"COL2", 2, 5, 8}} *)

Note that this mechanism is somewhat more elegant than R, where the string arguments are wrapped in the 'c' function.

Several related questions:

First, these definitions raise similar errors - why?

UpSetDelayed::write: "Tag String in DF[expr_][[r_String,rest___]] is Protected."

Second - key question - how to define general getter passing row & column arguments to return a subarray, eg DF[data][[{"ID","ROW1"},{"ID","COL2"}]] but avoiding replicating the list and singleton combinations above? Is there an implementation of the general case that avoids the special cases?

• Arguments need not include the "ID" row or column, composing the row getter followed by column getter, or vice versa, won't work directly.
• Note the asymmetry and limitation between the methods to access rows and columns: the row accessor is more general since All can be passed as a special case, but also numeric parameters and Spans.

Finally, can the UpValues mechanism be used to emulate other data frames functionality? Would it be possible to use as setters? Also, Apparently in R data frames are used to query the data with conditionals.

EDIT

Note, given the comments on computational complexity and scalability, I would like to stress that the primary purpose of this approach is to deal with small to medium datasets imported from Excel, where string headers, unlike RDBMS, are not separated as metadata.

In many data analysis applications, for example biomedicine and healthcare, Excel-based workflows are the norm (for input data, intermediates and output). Moreover, query (and statistical) development time dominates execution time. So syntax and modular approaches to data frames outweigh computational complexity considerations.

Answer 1

In answer to your first question, and as Leonid stated in a comment, you need to use TagSetDelayed to remove the ambiguity from your UpSet definition, e.g.

DF /: Part[DF[expr_], r_String, rest___] := expr[[Position[expr, r][[1, 1]], rest]]

---

I previously laid out a framework for this kind of problem, but you seem not to have used it.
I shall attempt a second time to demonstrate my recommendation.

Guiding principles:

1. Stop putting your String field labels in the data table.

2. Stop using Position to find the field labels.

Point #1
You will not be able to pack the array if it contains string labels, therefore whatever framework you use should not put labels in that data table if is to be at all optimized and general.

Point #2
Repeatedly calling Position to find an object in a list is slow, grievously so with large data sets. This is what hash table type storage such as DownValues and Dispatch tables are for. Use them!

Impementation

With these principles in mind we shall reformulate your data like this:

DF[rules, array]

Within this structure rules will be a series of rule lists indexing each level of the array. For actual deployment each list would be optimized with Dispatch, but I shall leave that out of the code below for simplicity.

For your data this gives us:

dat =
 DF[
   {{"ROW1" -> 1, "ROW2" -> 2, "ROW3" -> 3},
    {"COL1" -> 1, "COL2" -> 2, "COL3" -> 3}},
   Range@9 ~Partition~ 3
 ];

This could be done programmatically like so:

DF[
  Dispatch[# -> #2[[1]] & ~MapIndexed~ #] & /@ {data[[2 ;;, 1]], data[[1, 2 ;;]]},
  Developer`ToPackedArray @ data[[2 ;;, 2 ;;]]
]

We can then define an efficient part function for that data like this:

DFpart[DF[rls_, array_], spec___] := 
  array[[##]] & @@ 
    Replace[{{spec}, rls} ~Flatten~ {2}, {p_, r_} :> (p /. r), 1]

DFpart[dat, {"ROW1", "ROW3"}, {"COL2", "COL3"}]

{{2, 3}, {8, 9}}

You can mix numeric and string part specifications, or use numeric specifications beyond the depth that is indexed in the rule tables, e.g. DFpart[dat, 2, "COL3", {4,2}] (if the data included it). Should you use numeric field labels in the rule lists (not recommended as that would be highly confusing) the rules would take precedence.

This method still does not address Leonid's mutability concern, but it should be considerably more efficient that your own.

---

You said the method above is not acceptable because it does not overload DF within Part. Here is an adjustment to match the syntax shown in your question. The data now has a head of List and must be wrapped in DF explicitly, as you did.

dat2 =
 {
   {{"ROW1" -> 1, "ROW2" -> 2, "ROW3" -> 3},
    {"COL1" -> 1, "COL2" -> 2, "COL3" -> 3}},
   Range@9 ~Partition~ 3
 };

DF /: DF[{rls_, array_}][[spec___]] := 
 array[[##]] & @@ Replace[{{spec}, rls} ~Flatten~ {2}, {p_, r_} :> (p /. r), 1]

Usage:

DF[dat2][[{"ROW1", "ROW3"}, {"COL2", "COL3"}]]

{{2, 3}, {8, 9}}

If using this form another approach is to pass dat2 symbolically. This may be logical if extending functionality to the dat[[part]] = new form, but I don't know a way to do that without Unprotecting Set or using a new function e.g. DFset. For your examination:

Clear[DF]
SetAttributes[DF, HoldFirst]

DF /: DF[sym_][[spec___]] := 
 sym[[2, ##]] & @@ Replace[{{spec}, sym[[1]]} ~Flatten~ {2}, {p_, r_} :> (p /. r), 1]

Unprotect[Set];

Set[DF[sym_][[spec___]], rhs_] :=
  (sym[[2, ##]] = rhs) & @@ 
    Replace[{{spec}, sym[[1]]} ~Flatten~ {2}, {p_, r_} :> (p /. r), 1]

Protect[Set];

Now:

DF[dat2][[{"ROW1", "ROW3"}, 1 ;; 2]] = {{a, b}, {c, d}};

DF[dat2][[{"ROW1", "ROW3"}, 1 ;; 2]]

{{a, b}, {c, d}}

Answer 2

For read-only access to Excel data, I use a custom Set function that basically assigns an excel sheet to a symbol:

    setFromExcel::usage = "setFromExcel[symbol, file, sheet, opts] is a custom Set method which enriches 'symbol' for use as a data function.";
    skipRows::usage = "skipRows is an Option to the setFromExcel function. Use 'skipRows -> <some integer>' to ignore the first rows of the sheet.";
    keyValue::usage = "keyValue is an Option to the setFromExcel function. If True, it uses the excel sheet as a list key-value pairs. Default is False.";
    setFromExcel::primaryKeyError = "Cannot find primary key `1`";
    setFromExcel::columnError = "Cannot find column with name `1`";


    SetAttributes[setFromExcel, HoldFirst];
    setFromExcel[f_Symbol, 
      file_String /; FileExistsQ[file], 
      sheet_String, 
      OptionsPattern[{skipRows -> 0, keyValue -> False}]] := Module[{lbl, dt, x},
        dt = Drop[Import[file, {"Sheets", sheet}], OptionValue[skipRows]];
        dt = Select[dt, Not[First[#]==""]&];
        If[Not[OptionValue[keyValue]],
            lbl = ToString /@ First[dt];
            lbl = lbl /. (x_String /; StringMatchQ[x, RegularExpression[".*\\.$"]] :> StringDrop[x, -1]);
            lbl = MapIndexed[Rule[#1, First[#2]] &, lbl];
            f[code_String, prop_String] := Module[{subset, idx},
              subset = Select[dt, First[#] == code &];
              If[Length[subset]==0, Message[setFromExcel::primaryKeyError, code]; Return[$Failed]];
              idx = prop /. lbl;
              If[Not[NumberQ[idx]], Message[setFromExcel::columnError, prop]; Return[$Failed]];
                  Part[First[subset], idx]
                ];
                f[] := Drop[First /@ dt, 1];
                f["Properties"] := First /@ lbl;
                f,
              (* else *)
                f[code_String] := Module[{subset},
                    subset = Select[dt, First[#] == code &];
                    If[Length[subset]==0, Message[setFromExcel::primaryKeyError, code]; Return[$Failed]];
                    Drop[First[subset], 1] /. "" -> Sequence[] /.  x_List /; Length[x] == 1 :> First[x]
                ];
                f[] := Drop[First /@ dt, 1];
                f
        ]
    ];

Now if the data resides in an excel sheet "TestData", it can be imported with

Clear[fn, testData];
fn = "path/to/workbook.xls";
setFromExcel[testData, fn, "Testdata"]

This sets the DownValues of the testData symbol. This function can now be used similar to CountryData and similar to R's data.frame:

testData[]
testData["Properties"]
testData["ROW2", "COL3"]

Answer 3

Here is my latest attempt at access functions ("getters") to emulate 2D data frames by overloading Part with UpValues on symbol DF. I would appreciate any improvements in efficiency (see test below) and generalization to handle more general headers like dates, which currently are not handled.

This version uses Select as matching mechanism. Currently the matching is only on the first element of a row or column but this can be easily extended via options and defaults.

SelectInFrame[h_, data_] := Select[data, #[[1]] == h &]

Nothing forces Excel frames to be relational primary keys, ie headers need not be unique. However, the methods here assume unique frame headers as precondition (though it is not explicitly handled).

Compared to the functions in the original question, here it is the headers that are wrapped in DF rather than the data matrix. This enables matching over data types other than String, since often Excel column headers are strings but the row headers may be more general numeric values or dates (however composite expressions like DateList are not supported yet).

The following function matches the basic patterns: data[[DF@"ROW1"]] or data[[DF@{"ROW1", "ROW3"}]] using the Thread handling and singleton delisting mechanisms discussed in the links.

DF /: Part[data_, DF[rows_]] := 
 Thread[Unevaluated@SelectInFrame[rows, data], List, 1] /.  _[x_] :> x

The next handles patterns like data[[DF@{"ROW1", "ROW3"}, 2 ;; 3]]

DF /: Part[data_, DF[rows_], cols_] :=  
 With[{out = data[[DF[rows]]]}, 
  If[Length@Dimensions@out == 1, out[[cols]], out[[All, cols]]]
  ]

(Is there a more succinct method to express the conditional?)

Next handles searching on columns: data[[All, DF@{"COL2", "COL3"}]]

DF /: Part[data_, rows_, DF[cols_]] := 
 With[{out = Transpose[data][[DF[cols], rows]]},
  If[Length@Dimensions@out == 1,(*List/@*) out, Transpose[out]]
  ]

Note the commented out (List/@): there is ambiguity in how to format the output when matching a single column (coupled with Grid for instance)

Finally this handles both row & column frame matching (see test below). The algorithm uses row-selection first then prepend the frame row (1st row) because it might not have been selected, performs column-selection then discards the frame row with Rest:

DF /: Part[data_, DF[rows_], DF[cols_]] := 
 With[{outRows = data[[DF[rows]]]},
  If[Length@Dimensions@outRows == 1,
   Rest@Prepend[List@outRows, First@data][[All, DF[cols]]] /.  _[
      x_] :> x,
   Rest@Prepend[outRows, First@data][[All, DF[cols]]] /.  _[x_] :> x
   ]
  ]

Again, if there's a more succinct or efficient method - hopefully with as little name pollution as possible - please post it an answer.

TEST:

Performance on accessing a 500x500 submatrix of 1000x1000 numerical matrix with String frame:

bigdata = 
  Module[{n = 1000}, 
   Prepend[Transpose@
     Prepend[Table[Random[], {n}, {n}], 
      Table["ROW" <> ToString[i], {i, n}]],
    Prepend[Table["COL" <> ToString[i], {i, n}], "ID"]
    ]
   ];

Access by position This is on a 2.66 Ghz Mac Pro, MMA 8.0.4:

First@Timing@bigdata[[300 ;; 800, 400 ;; 900]]

(* 0.004031 *) 

Now by matching DF header:

rows = Table["ROW" <> ToString@i, {i, 300, 800}];
cols = Table["COL" <> ToString@i, {i, 400, 900}];

First@Timing@bigdata[[DF@rows, DF@cols]]

(* 0.951801 *) 

So it's about 200x slower. While a 1000x1000 matrix not a realistic Excel test case, suggestions for improvement in readability and efficiency are appreciated.

Obviously this barely scratches the surface of R's data frames as Leonid pointed out, but it's good to know MMA has the syntax to emulate some functionality.