Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

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.

share|improve this question
1  
No time for a full answer right now, alas. One general comment is that the utulity of data frames in R is largely due to a right combination of mutability, immutability, and practicality. In particular, R data frames have very intuitive and user-friendly ways of extracting sub-data frames, columns and rows, based on various types of indexing. Also, filtering, sorting, and many other common data-manipulation operations. Emulating enough features of R data frames so that we are over the threshold where they become really useful is quite a non-trivial undertaking. –  Leonid Shifrin Aug 21 '12 at 22:36
    
But this is not to say that this wouldn't be a nice project, and also potentially useful. You just need to consider a number of things at once and how they will play together, to make a decent emulation. –  Leonid Shifrin Aug 21 '12 at 22:39
2  
As to your question about the error message: you should have used a more precise TagSetDelayedoperator: DF /: Part[DF[expr_], r_String, rest___] := ... etc. The UpSetDeayed attempts to create UpValues for all level-1 symbols, which in this case includes String. Have a look at the last section of this answer, where I discuss this in more detail. Finally, regarding your current implementation: not that you won't have O(1) access time for elements of your ... –  Leonid Shifrin Aug 22 '12 at 0:48
    
... tables, or rows (columns), and neither will you have O(1) set time for these elements, all due to the use of immutable data structures which will necessarily be copied. This will likely be unacceptable for large tables. So, the right form of mutability is the key. Which brings us back to what has been discussed many times before here, on SO and on MathGroup: Mathematica currently does not offer a native support for mutable data structures, so we are left with emulations of various degree and quality. This is one of the reasons why I consider this problem quite non-trivial. –  Leonid Shifrin Aug 22 '12 at 0:52
    
R's series data structure, which is similar but one dimensional is discussed in mathematica.stackexchange.com/questions/2821/… –  Searke Aug 22 '12 at 13:20
show 3 more comments

3 Answers

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}}
share|improve this answer
    
Re "Stop putting your String field labels in the data table." - they are there to begin with in Excel. Maybe you mean, separate out the headers into metadata structure? I edited the Q to also address scalability. –  alancalvitti Aug 22 '12 at 18:58
1  
+1 but I can't accept this approach since it doesn't overload built in functions on DF using UpValues. Is it possible to avoid Position and the other complexity issues while still using the general approach outlined in the original Q? –  alancalvitti Aug 22 '12 at 19:19
    
@alancalvitti (1) that's why I included the "done programmatically like so" part; do it once on import and use the more efficient form thereafter. (2) What is the specific goal of overloading in this case? I did not mean to ignore your requirements, I simply didn't understand the benefit and DFpart seemed easy enough. Is it a matter of having the [[ ]] bracket notation or something else? The reason I didn't overload Part is that it has attribute HoldFirst therefore only explicit appearances of DF[___] would match the pattern. I'll try to amend my answer when I better understand. –  Mr.Wizard Aug 22 '12 at 20:35
    
Actually, Part doesn't have HoldFirst but it behaves that way. –  Mr.Wizard Aug 22 '12 at 20:42
    
@alancalvitti you never answered my question above; I'd like to upgrade my answer to your needs if you'll explain them. –  Mr.Wizard Aug 25 '12 at 22:46
show 3 more comments

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"]
share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.