1
$\begingroup$

I want to read in an xlsx (Excel) or TSV (tab-delimited) file, assuming this results in a rectangular table of data. Then I after applying GroupBy (using multiple keys), I should have several different groups of data though they are 'stacked' on top of each other. How would I select each group, one at a time so that I can apply for example a Line or Curve fitting function to each of the groups and output results for each group?

raw= Import["C:\\STUFF.txt", "TSV"]
header = raw[[1]]
data = raw[[2 ;;]]
myDataset =  Thread[header -> #] & /@ data // Map[Association] // Dataset

This results in a standard database-like table of data. From here, how do I group this table by multiple columns (Ex: "gen","style","state","mode") and then apply A Line or Curve fitting function to each group to find best fit parameters for each group?

This is data BEFORE Sorting :

{{"", "attrib1", "attrib2", "attrib3", "var1", "var2", "var3", 
  "targetValue"}, {1., "donner", "scrabble", "santa", 3.73664, 
  3.13719, 5.45106, 8.5}, {2., "rudolf", "marbles", "grinch", 
  0.774004, 1.33989, 1.68732, 6.9}, {3., "blitzen", "marbles", 
  "grinch", 6.92852, 7.57531, 5.03245, 8.6}, {4., "rudolf", "marbles",
   "santa", 3.88404, 1.95399, 4.92164, 7.6}, {5., "donner", "marbles",
   "grinch", 0.65931, 6.87755, 1.82841, 10.3}, {6., "blitzen", 
  "marbles", "grinch", 3.95726, 1.24112, 1.40049, 9.4}, {7., "donner",
   "scrabble", "santa", 2.29372, 6.97873, 4.71533, 7.8}, {8., 
  "blitzen", "scrabble", "santa", 1.84786, 0.286934, 3.4215, 
  9.3}, {9., "rudolf", "scrabble", "grinch", 5.54639, 4.69658, 3.6046,
   7.5}, {10., "donner", "marbles", "grinch", 3.22634, 9.54838, 
  4.8924, 10.6}, {11., "blitzen", "scrabble", "santa", 3.38442, 
  0.910412, 2.23809, 8.7}, {12., "rudolf", "scrabble", "santa", 
  7.71931, 2.41088, 3.59705, 9.9}}

How do I get to The following Table (grouped by All 3 vars in Mathematica)

{{"", "attrib1", "attrib2", "attrib3", "var1", "var2", "var3", 
  "targetValue"}, {1., "blitzen", "marbles", "grinch", 0.324634, 
  5.55465, 7.33444, 8.6}, {2., "blitzen", "marbles", "grinch", 
  4.68461, 0.621629, 5.45658, 9.4}, {3., "blitzen", "scrabble", 
  "santa", 0.321507, 2.40429, 2.35555, 9.3}, {4., "blitzen", 
  "scrabble", "santa", 6.78552, 0.691274, 6.69245, 8.7}, {5., 
  "donner", "marbles", "grinch", 1.58886, 4.88366, 7.42987, 
  10.3}, {6., "donner", "marbles", "grinch", 4.33855, 8.9008, 0.88771,
   10.6}, {7., "donner", "scrabble", "santa", 2.33365, 8.39023, 
  5.03501, 8.5}, {8., "donner", "scrabble", "santa", 3.0631, 3.81142, 
  3.34073, 7.8}, {9., "rudolf", "marbles", "grinch", 0.483345, 
  4.90253, 6.51428, 6.9}, {10., "rudolf", "marbles", "santa", 5.84364,
   4.42051, 4.11002, 7.6}, {11., "rudolf", "scrabble", "grinch", 
  0.408628, 0.84849, 0.979443, 7.5}, {12., "rudolf", "scrabble", 
  "santa", 8.83514, 0.326181, 7.61163, 9.9}}

Now, How do I get to table 2 from table 1, THEN, get the Multipliers of var 1,2 and 3 for each GROUP to Predict targetValue for that GROUP?

So now each 'group' consists of all rows that have the same value for all three vars (1,2 and3) FINAL RESULT : to predict targetValue of, for example, the blitzen-marbles-grinch group , the multipliers are : (3.4 * var1) + (.87 * var2) - (.06 * var3) = (PREDICTED targetValue) OR for the donner-scrabble-santa group , the multipliers are : (2.7 * var1) + (.91 * var2) - (.26 * var3) = (PREDICTED targetValue) I need the Function(s) that give me the 3.4, .87 .06 (or the 2.7, .91, .26) whether a linear or non-linear Function, whichever may apply. Finally, what references (web,Book,blog etc) cover this type of analysis and Data Analysis in general using Mathematica ? Thanks in Advance.

$\endgroup$
  • $\begingroup$ "From here, how do I group this table by multiple columns (Ex: "gen","style","state","mode")" seems ambiguous/vague. Can you provide an example of the sort of output you are seeking? $\endgroup$ – Mike Honeychurch Nov 14 '18 at 6:40
  • 2
    $\begingroup$ Could you put the example data in a form that we can copy and paste; that makes it a lot easier to answer your questions. myDataset//Normal will do what's needed and then paste into your question. Then we can give you some direction. $\endgroup$ – mdc18550 Nov 14 '18 at 15:22
1
$\begingroup$

I think the best way to convert the "before" data would look like this, assuming that the data list above is set to raw, and both header and data are defined as above:

ds1 = AssociationThread[header -> #] & /@ data // Dataset

This can then be grouped as follows:

ds2 = ds1[GroupBy["attrib1"],GroupBy["attrib2"],GroupBy["attrib3"]]

Which produces this dataset:

enter image description here

Extracting the data from this is a little complicated, and the best I have come up with is to do something like this (to extract the first set of data):

KeyTake[Values[ds2[1][1]]//Normal//Flatten,{"var1", "var2","var3", "targetValue"}]

Which gives:

{<|"var1" -> 3.73664, "var2" -> 3.13719, "var3" -> 5.45106, "targetValue" -> 8.5|>, <|"var1" -> 2.29372, "var2" -> 6.97873, "var3" -> 4.71533, "targetValue" -> 7.8|>}

Another approach would be to construct the list of unique tuples that you want to look at, and then sort them:

vL = DeleteDuplicates[Values[KeyTake[ds1,{"attrib1","attrib2","attrib3"}]]]//Normal
vL = Sort[vL]

Producing:

{{"blitzen", "marbles", "grinch"}, {"blitzen", "scrabble",
"santa"}, {"donner", "marbles", "grinch"}, {"donner", "scrabble",
"santa"}, {"rudolf", "marbles", "grinch"}, {"rudolf", "marbles",
"santa"}, {"rudolf", "scrabble", "grinch"}, {"rudolf", "scrabble",
"santa"}}

Then this function can extract any one of the unique tuples:

getValues[qL_, ds1_] :=
 Module[{},
  Query[Select[#attrib1 == qL[[1]] && 
       #attrib2 == qL[[2]] && 
       #attrib3 == qL[[3]] &], 
       {"attrib1", "attrib2", "attrib3", 
        "var1", "var2", "var3", "targetValue"}]@ds1
  ]

You can choose any of the elements of vL that interest you or all of them like this:

getValues[#, ds1] & /@ vL

The resulting dataset for any one value looks like this:

enter image description here

Which can then be converted to a list through Normal and Values and passed to your regression routine or whatever you wish.

$\endgroup$

Your Answer

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