Repository: ticdat/ticdat (language: python)

Issue:
optional argument for copy_to_pandas to create a simplified index
```
for t in self.all_tables:
    getattr(rtn, t).reset_index(inplace=True, drop=True)
```
If argument is truthy, and argument can't be truthy unless drop_pk_columns is also Truthy 
(or could simply have a better flag - perhaps a special string).
More ml examples



Discussion / hints:

Fix patch:
diff --git a/examples/expert_section/little_league/little_league.py b/examples/expert_section/little_league/little_league.py
new file mode 100644
index 00000000..c64824ab
--- /dev/null
+++ b/examples/expert_section/little_league/little_league.py
@@ -0,0 +1,173 @@
+# this version of the file uses gurobipy
+#
+# 450 objective value
+
+# !!!!!!!! NB !!!!!!!!!!!!!!
+# see r["Consecutive Innings Only"] == "True" line for last remaining outstanding issue
+
+# OK now can clean up code and json data to exploit x-to-many fks which are new!
+
+import gurobipy as gu
+from ticdat import TicDatFactory,  standard_main, gurobi_env, Slicer
+from ticdat.utils import verify
+from math import floor
+from itertools import product, combinations
+
+input_schema = TicDatFactory (
+    roster = [["Name"],["Grade", "Arrival Inning", "Departure Inning", "Min Innings Played", "Max Innings Played"]],
+    positions = [["Position"],["Position Importance", "Position Group", "Consecutive Innings Only"]],
+    player_ratings = [["Name", "Position Group"], ["Rating"]],
+    innings = [["Inning"],["Inning Group"]],
+    position_constraints = [["Position Group", "Inning Group", "Grade"],["Min Players", "Max Players"]],
+    parameters = [["Key"],["Value"]])
+
+input_schema.set_data_type("roster", "Min Innings Played", min=0, max=9, inclusive_min=True, inclusive_max=True)
+input_schema.set_data_type("roster", "Max Innings Played", min=0, max=9, inclusive_min=False, inclusive_max=True)
+input_schema.add_data_row_predicate(
+    "roster", predicate_name="Roster Min Max Check",
+    predicate=lambda row : row["Max Innings Played"] >= row["Min Innings Played"])
+input_schema.add_foreign_key("roster", "innings", ["Arrival Inning", "Inning"])
+input_schema.add_foreign_key("roster", "innings", ["Departure Inning", "Inning"])
+
+input_schema.set_data_type("positions", "Position Importance",min=0, max=float("inf"),
+                           inclusive_min=True, inclusive_max=False)
+input_schema.set_data_type("positions", "Consecutive Innings Only", strings_allowed=['True', 'False'],
+                           number_allowed=False)
+
+input_schema.add_foreign_key("player_ratings", "roster", ["Name", "Name"])
+input_schema.add_foreign_key("player_ratings", "positions", ["Position Group", "Position Group"])
+input_schema.set_data_type("player_ratings", "Rating", min=0, max=float("inf"),
+                           inclusive_min=True, inclusive_max=False)
+
+# since we're going to be sorting innings need them to be numeric
+input_schema.set_data_type("innings", "Inning", min=1, max=9, inclusive_min=True, inclusive_max=True)
+
+input_schema.add_foreign_key("position_constraints", "positions", ["Position Group", "Position Group"])
+input_schema.add_foreign_key("position_constraints", "innings", ["Inning Group", "Inning Group"])
+input_schema.add_foreign_key("position_constraints", "roster", ["Grade", "Grade"])
+input_schema.set_data_type("position_constraints", "Min Players", min=0, max=float("inf"),
+                           inclusive_min=True, inclusive_max=True)
+input_schema.set_data_type("position_constraints", "Max Players", min=0, max=float("inf"),
+                           inclusive_min=False, inclusive_max=True)
+input_schema.add_data_row_predicate(
+    "position_constraints", predicate_name="Position Min Max Check",
+    predicate=lambda row : row["Max Players"] >= row["Min Players"])
+
+default_parameters = {"Balanced Playing Time": "True", "Limit Outfield Play": "True",
+                      "Max Consecutive Bench": 9, "Outfield Group Label": "Outfield",
+                      "Bench Roster Label":"Bench"}
+def _good_parameter_key_value(key, value):
+    if key == "Max Consecutive Bench":
+        try:
+            return (1 <= value <= 9) and floor(value) == value
+        except:
+            return False
+    if key in ["Balanced Playing Time", "Limit Outfield Play"]:
+        return value  in ["True", "False"]
+    if key in ["Outfield Group Label", "Bench Roster Label"]:
+        return True
+
+assert all(_good_parameter_key_value(k,v) for k,v in default_parameters.items())
+
+input_schema.set_data_type("parameters", "Key", number_allowed=False,
+                           strings_allowed=default_parameters)
+input_schema.add_data_row_predicate("parameters", predicate_name="Good Parameter Value for Key",
+    predicate=lambda row : _good_parameter_key_value(row["Key"], row["Value"]))
+
+
+solution_schema = TicDatFactory(
+    lineup = [["Inning", "Position"],["Name"]])
+
+def solve(dat):
+    assert input_schema.good_tic_dat_object(dat)
+    assert not input_schema.find_foreign_key_failures(dat)
+    assert not input_schema.find_data_type_failures(dat)
+    assert not input_schema.find_data_row_failures(dat)
+
+    # use default parameters, unless they are overridden by user-supplied parameters
+    full_parameters = dict(default_parameters, **{k: v["Value"] for k,v in dat.parameters.items()})
+
+    bench = full_parameters["Bench Roster Label"]
+    verify(bench in dat.positions, "%s needs to be one of the positions"%bench)
+
+    verify(len(dat.roster) >= len(dat.positions) - 1, "Model is infeasible due to shortage of players!")
+
+    mdl = gu.Model("little_league", env=gurobi_env())
+
+    lineup = {(i,p,pl):mdl.addVar(vtype = gu.GRB.BINARY, name="lineup_%s_%s_%s"%(i, p, pl))
+              for i, p, pl in product(dat.innings, dat.positions, dat.roster)}
+    l_slicer = Slicer(lineup)
+    # 1 player assigned to each active position per inning
+    for i,p in product(dat.innings, dat.positions):
+        if p != bench:
+            mdl.addConstr(gu.quicksum(lineup[k] for k in l_slicer.slice(i, p, '*')) == 1,
+                          name = "all_positions_filled_per_inning_%s_%s"%(i,p))
+    # each player must be assigned to one position (including the bench) per inning
+    for i,pl in product(dat.innings, dat.roster):
+        mdl.addConstr(gu.quicksum(lineup[k] for k in l_slicer.slice(i, '*', pl)) == 1,
+                      name = "at_most_one_position_per_inning_%s_%s"%(i,pl))
+
+    grade_slice = Slicer([(pl, r["Grade"]) for pl, r in dat.roster.items()])
+    position_slice = Slicer([(p, r["Position Group"]) for p, r in dat.positions.items()])
+    innings_slice = Slicer([(i, r["Inning Group"]) for i,r in dat.innings.items()])
+    # Position_Constraints satisfied
+    for (pg, ig, g), r in dat.position_constraints.items():
+        total_players = gu.quicksum(lineup[i[0], p[0], pl[0]] for i, p, pl in
+                                    product(innings_slice.slice('*', ig), position_slice.slice('*', pg),
+                                            grade_slice.slice('*', g)))
+        mdl.addConstr(total_players >= r["Min Players"], name="min_players_%s_%s_%s"%(pg, ig, g))
+        mdl.addConstr(total_players <= r["Max Players"], name="max_players_%s_%s_%s"%(pg, ig, g))
+
+    # Enforce consecutive innings constraints
+    sorted_innings = list(sorted(dat.innings))
+    for p,r in dat.positions.items():
+        if r["Consecutive Innings Only"] == "True":
+            for pl in dat.roster:
+                for pos1, pos2 in combinations(range(len(sorted_innings)), 2):
+                    if pos2 < pos1:
+                        pos1, pos2 = pos2, pos1
+                    if pos2-pos1 > 1:
+                        pass
+                        # need Derek's determination of what it means
+
+    # Balanced Playing time = a min playing time for each player
+    if full_parameters["Balanced Playing Time"] == "True":
+        for pl, r in dat.roster.items():
+            mdl.addConstr(gu.quicksum(lineup[k] for k in l_slicer.slice('*', '*', pl)) >=
+                          floor((r["Departure Inning"] - r["Arrival Inning"] + 1) /
+                                 float(len(dat.positions) * len(dat.innings))),
+                          name= "balanced_pt_%s"%pl)
+
+    if full_parameters["Limit Outfield Play"] == "True":
+        of = full_parameters["Outfield Group Label"]
+        for pl in dat.roster:
+            mdl.addConstr(gu.quicksum(lineup[i, p[0], pl] for p in position_slice.slice('*', of)
+                                      for i in dat.innings) <=
+                          gu.quicksum(lineup[i, p, pl] for i,p in product(dat.innings, dat.positions))*0.5 + 0.5,
+                          name= "limit_OF_play_%s"%pl)
+
+    max_bench = full_parameters["Max Consecutive Bench"]
+    for pos,i in enumerate(sorted_innings):
+        if max_bench+1+pos <= len(sorted_innings):
+            for pl in dat.roster:
+                mdl.addConstr(gu.quicksum(lineup[k] for i_ in sorted_innings[pos:pos+max_bench+1]
+                                          for k in l_slicer.slice(i_, '*', pl)) >= 1,
+                              name = "max_consecutive_bench_%s_%s"%(i, pl))
+
+    mdl.setObjective(gu.quicksum(dat.positions[p]["Position Importance"] * dat.player_ratings[pl, pg]["Rating"] * v
+                                 for (i, p, pl), v in lineup.items() for pg in [dat.positions[p]["Position Group"]]
+                                 if (pl, pg) in dat.player_ratings),
+                     sense=gu.GRB.MAXIMIZE)
+
+    mdl.optimize()
+
+    if mdl.status == gu.GRB.OPTIMAL:
+        sln = solution_schema.TicDat()
+        for (i, p, pl), v in lineup.items():
+            if abs(v.x - 1) < 0.0001:
+                sln.lineup[i,p] = pl
+        return sln
+
+
+if __name__ == "__main__":
+    standard_main(input_schema, solution_schema, solve)
diff --git a/examples/expert_section/little_league/little_model.json b/examples/expert_section/little_league/little_model.json
new file mode 100644
index 00000000..61f91fd9
--- /dev/null
+++ b/examples/expert_section/little_league/little_model.json
@@ -0,0 +1,543 @@
+{
+  "innings": [
+    [
+      1.0, 
+      "First 4"
+    ], 
+    [
+      2.0, 
+      "First 4"
+    ], 
+    [
+      3.0, 
+      "First 4"
+    ], 
+    [
+      4.0, 
+      "First 4"
+    ], 
+    [
+      5.0, 
+      "Five & Six"
+    ], 
+    [
+      6.0, 
+      "Five & Six"
+    ]
+  ], 
+  "parameters": [
+    [
+      "Limit Outfield Play", 
+      "True"
+    ], 
+    [
+      "Max Consecutive Bench", 
+      1
+    ], 
+    [
+      "Balanced Playing Time", 
+      "True"
+    ]
+  ], 
+  "player_ratings": [
+    [
+      "Sam MacKay", 
+      "OF", 
+      1.0
+    ], 
+    [
+      "Andrew Rich", 
+      "OF", 
+      1.0
+    ], 
+    [
+      "Abigail Perry", 
+      "IF", 
+      1.0
+    ], 
+    [
+      "Henry Brownell", 
+      "P", 
+      5.0
+    ], 
+    [
+      "Henry Rogowski", 
+      "C", 
+      1.0
+    ], 
+    [
+      "Jake Everds", 
+      "OF", 
+      1.0
+    ], 
+    [
+      "Devin Neumann", 
+      "P", 
+      1.0
+    ], 
+    [
+      "Henry Rogowski", 
+      "P", 
+      1.0
+    ], 
+    [
+      "Devin Neumann", 
+      "IF", 
+      1.0
+    ], 
+    [
+      "Andrew Rich", 
+      "IF", 
+      1.0
+    ], 
+    [
+      "Charles Lubinsky", 
+      "OF", 
+      1.0
+    ], 
+    [
+      "James Bauman", 
+      "P", 
+      5.0
+    ], 
+    [
+      "Devin Neumann", 
+      "C", 
+      1.0
+    ], 
+    [
+      "Abigail Perry", 
+      "P", 
+      5.0
+    ], 
+    [
+      "Miles Lyons", 
+      "OF", 
+      1.0
+    ], 
+    [
+      "Henry Brownell", 
+      "OF", 
+      1.0
+    ], 
+    [
+      "Cash Nelson", 
+      "OF", 
+      1.0
+    ], 
+    [
+      "Alex Byhre", 
+      "C", 
+      1.0
+    ], 
+    [
+      "Dylan Stiller", 
+      "IF", 
+      1.0
+    ], 
+    [
+      "Dylan Stiller", 
+      "OF", 
+      1.0
+    ], 
+    [
+      "Cash Nelson", 
+      "P", 
+      5.0
+    ], 
+    [
+      "Henry Rogowski", 
+      "OF", 
+      1.0
+    ], 
+    [
+      "Cash Nelson", 
+      "C", 
+      1.0
+    ], 
+    [
+      "Charles Lubinsky", 
+      "P", 
+      1.0
+    ], 
+    [
+      "Jake Everds", 
+      "P", 
+      1.0
+    ], 
+    [
+      "Alex Byhre", 
+      "P", 
+      1.0
+    ], 
+    [
+      "Henry Rogowski", 
+      "IF", 
+      1.0
+    ], 
+    [
+      "Charles Lubinsky", 
+      "C", 
+      1.0
+    ], 
+    [
+      "Devin Neumann", 
+      "OF", 
+      1.0
+    ], 
+    [
+      "Henry Brownell", 
+      "C", 
+      1.0
+    ], 
+    [
+      "Miles Lyons", 
+      "C", 
+      1.0
+    ], 
+    [
+      "Cash Nelson", 
+      "IF", 
+      1.0
+    ], 
+    [
+      "Henry Brownell", 
+      "IF", 
+      1.0
+    ], 
+    [
+      "Abigail Perry", 
+      "OF", 
+      1.0
+    ], 
+    [
+      "Ryder Bingaman", 
+      "P", 
+      5.0
+    ], 
+    [
+      "James Bauman", 
+      "IF", 
+      1.0
+    ], 
+    [
+      "Sam MacKay", 
+      "P", 
+      1.0
+    ], 
+    [
+      "Dylan Stiller", 
+      "C", 
+      1.0
+    ], 
+    [
+      "Alex Byhre", 
+      "IF", 
+      1.0
+    ], 
+    [
+      "Alex Byhre", 
+      "OF", 
+      1.0
+    ], 
+    [
+      "Sam MacKay", 
+      "C", 
+      1.0
+    ], 
+    [
+      "James Bauman", 
+      "OF", 
+      1.0
+    ], 
+    [
+      "Ryder Bingaman", 
+      "C", 
+      1.0
+    ], 
+    [
+      "Jake Everds", 
+      "IF", 
+      1.0
+    ], 
+    [
+      "Aaron Shalin", 
+      "IF", 
+      1.0
+    ], 
+    [
+      "Andrew Rich", 
+      "C", 
+      1.0
+    ], 
+    [
+      "Ryder Bingaman", 
+      "IF", 
+      1.0
+    ], 
+    [
+      "Dylan Stiller", 
+      "P", 
+      5.0
+    ], 
+    [
+      "Sam MacKay", 
+      "IF", 
+      1.0
+    ], 
+    [
+      "Aaron Shalin", 
+      "C", 
+      1.0
+    ], 
+    [
+      "Jake Everds", 
+      "C", 
+      1.0
+    ], 
+    [
+      "James Bauman", 
+      "C", 
+      5.0
+    ], 
+    [
+      "Andrew Rich", 
+      "P", 
+      1.0
+    ], 
+    [
+      "Aaron Shalin", 
+      "P", 
+      5.0
+    ], 
+    [
+      "Ryder Bingaman", 
+      "OF", 
+      1.0
+    ], 
+    [
+      "Miles Lyons", 
+      "P", 
+      1.0
+    ], 
+    [
+      "Aaron Shalin", 
+      "OF", 
+      1.0
+    ], 
+    [
+      "Abigail Perry", 
+      "C", 
+      0.0
+    ], 
+    [
+      "Charles Lubinsky", 
+      "IF", 
+      1.0
+    ], 
+    [
+      "Miles Lyons", 
+      "IF", 
+      1.0
+    ]
+  ], 
+  "position_constraints": [
+    [
+      "P", 
+      "First 4", 
+      "3rd", 
+      2.0, 
+      99.0
+    ]
+  ], 
+  "positions": [
+    [
+      "LF", 
+      2.0, 
+      "OF", 
+      "False"
+    ], 
+    [
+      "C", 
+      5.0, 
+      "C", 
+      "True"
+    ], 
+    [
+      "SS", 
+      3.0, 
+      "IF", 
+      "False"
+    ], 
+    [
+      "Bench", 
+      0.0, 
+      "Bench", 
+      "False"
+    ], 
+    [
+      "LCF", 
+      3.0, 
+      "OF", 
+      "False"
+    ], 
+    [
+      "P", 
+      5.0, 
+      "P", 
+      "True"
+    ], 
+    [
+      "RF", 
+      2.0, 
+      "OF", 
+      "False"
+    ], 
+    [
+      "1B", 
+      5.0, 
+      "IF", 
+      "False"
+    ], 
+    [
+      "2B", 
+      4.0, 
+      "IF", 
+      "False"
+    ], 
+    [
+      "3B", 
+      3.0, 
+      "IF", 
+      "False"
+    ], 
+    [
+      "RCF", 
+      3.0, 
+      "OF", 
+      "False"
+    ]
+  ], 
+  "roster": [
+    [
+      "Abigail Perry", 
+      "4th", 
+      1.0, 
+      6.0, 
+      3.0, 
+      6.0
+    ], 
+    [
+      "Devin Neumann", 
+      "3rd", 
+      1.0, 
+      6.0, 
+      3.0, 
+      6.0
+    ], 
+    [
+      "Andrew Rich", 
+      "4th", 
+      1.0, 
+      6.0, 
+      3.0, 
+      6.0
+    ], 
+    [
+      "Henry Brownell", 
+      "3rd", 
+      1.0, 
+      6.0, 
+      3.0, 
+      6.0
+    ], 
+    [
+      "Alex Byhre", 
+      "3rd", 
+      1.0, 
+      6.0, 
+      3.0, 
+      6.0
+    ], 
+    [
+      "Jake Everds", 
+      "4th", 
+      1.0, 
+      6.0, 
+      3.0, 
+      6.0
+    ], 
+    [
+      "Dylan Stiller", 
+      "4th", 
+      1.0, 
+      6.0, 
+      3.0, 
+      6.0
+    ], 
+    [
+      "Aaron Shalin", 
+      "4th", 
+      1.0, 
+      6.0, 
+      3.0, 
+      6.0
+    ], 
+    [
+      "Cash Nelson", 
+      "4th", 
+      1.0, 
+      6.0, 
+      3.0, 
+      6.0
+    ], 
+    [
+      "James Bauman", 
+      "4th", 
+      1.0, 
+      6.0, 
+      3.0, 
+      6.0
+    ], 
+    [
+      "Henry Rogowski", 
+      "3rd", 
+      1.0, 
+      6.0, 
+      3.0, 
+      6.0
+    ], 
+    [
+      "Ryder Bingaman", 
+      "3rd", 
+      1.0, 
+      6.0, 
+      3.0, 
+      6.0
+    ], 
+    [
+      "Miles Lyons", 
+      "4th", 
+      1.0, 
+      6.0, 
+      3.0, 
+      6.0
+    ], 
+    [
+      "Charles Lubinsky", 
+      "3rd", 
+      1.0, 
+      6.0, 
+      3.0, 
+      6.0
+    ], 
+    [
+      "Sam MacKay", 
+      "3rd", 
+      1.0, 
+      6.0, 
+      3.0, 
+      6.0
+    ]
+  ]
+}
\ No newline at end of file
diff --git a/examples/expert_section/little_league/simpler_little_league.py b/examples/expert_section/little_league/simpler_little_league.py
new file mode 100644
index 00000000..ac89ce04
--- /dev/null
+++ b/examples/expert_section/little_league/simpler_little_league.py
@@ -0,0 +1,89 @@
+# this version of the file uses gurobipy
+
+# 2360.0 obj value
+
+import gurobipy as gu
+from ticdat import TicDatFactory,  standard_main, gurobi_env, Slicer
+from math import floor
+from itertools import product, combinations
+
+input_schema = TicDatFactory (
+    roster = [["Name"],["Grade"]],
+    positions = [["Position"],["Position Importance", "Position Group"]],
+    player_ratings = [["Name", "Position Group"], ["Rating"]],
+    innings = [["Inning"],["Inning Group"]],
+    position_constraints = [["Position Group", "Inning Group", "Grade"],["Min Players", "Max Players"]])
+
+input_schema.set_data_type("positions", "Position Importance",min=0, max=float("inf"),
+                           inclusive_min=True, inclusive_max=False)
+
+input_schema.add_foreign_key("player_ratings", "roster", ["Name", "Name"])
+input_schema.add_foreign_key("player_ratings", "positions", ["Position Group", "Position Group"])
+input_schema.set_data_type("player_ratings", "Rating", min=0, max=float("inf"),
+                           inclusive_min=True, inclusive_max=False)
+
+input_schema.add_foreign_key("position_constraints", "positions", ["Position Group", "Position Group"])
+input_schema.add_foreign_key("position_constraints", "innings", ["Inning Group", "Inning Group"])
+input_schema.add_foreign_key("position_constraints", "roster", ["Grade", "Grade"])
+input_schema.set_data_type("position_constraints", "Min Players", min=0, max=float("inf"),
+                           inclusive_min=True, inclusive_max=True)
+input_schema.set_data_type("position_constraints", "Max Players", min=0, max=float("inf"),
+                           inclusive_min=False, inclusive_max=True)
+input_schema.add_data_row_predicate(
+    "position_constraints", predicate_name="Position Min Max Check",
+    predicate=lambda row : row["Max Players"] >= row["Min Players"])
+
+solution_schema = TicDatFactory(
+    lineup = [["Inning", "Position"],["Name"]])
+
+def solve(dat):
+    assert input_schema.good_tic_dat_object(dat)
+    assert not input_schema.find_foreign_key_failures(dat)
+    assert not input_schema.find_data_type_failures(dat)
+    assert not input_schema.find_data_row_failures(dat)
+
+    if len(dat.roster) < len(dat.positions):
+        print("\n****\nModel is infeasible due to shortage of players!\n****\n")
+
+    mdl = gu.Model("simpler little_league", env=gurobi_env())
+
+    lineup = {(i,p,pl):mdl.addVar(vtype = gu.GRB.BINARY, name="lineup_%s_%s_%s"%(i, p, pl))
+              for i, p, pl in product(dat.innings, dat.positions, dat.roster)}
+    l_slicer = Slicer(lineup)
+    # 1 player assigned to each position per inning
+    for i,p in product(dat.innings, dat.positions):
+        mdl.addConstr(gu.quicksum(lineup[k] for k in l_slicer.slice(i, p, '*')) == 1,
+                      name = "all_positions_filled_per_inning_%s_%s"%(i,p))
+    # each player can play at most one position per inning
+    for i,pl in product(dat.innings, dat.roster):
+        mdl.addConstr(gu.quicksum(lineup[k] for k in l_slicer.slice(i, '*', pl)) <= 1,
+                      name = "at_most_one_position_per_inning_%s_%s"%(i,pl))
+
+    grade_slice = Slicer([(pl, r["Grade"]) for pl, r in dat.roster.items()])
+    position_slice = Slicer([(p, r["Position Group"]) for p, r in dat.positions.items()])
+    innings_slice = Slicer([(i, r["Inning Group"]) for i,r in dat.innings.items()])
+    # Position_Constraints satisfied
+    for (pg, ig, g), r in dat.position_constraints.items():
+        total_players = gu.quicksum(lineup[i[0], p[0], pl[0]] for i, p, pl in
+                                    product(innings_slice.slice('*', ig), position_slice.slice('*', pg),
+                                            grade_slice.slice('*', g)))
+        mdl.addConstr(total_players >= r["Min Players"], name="min_players_%s_%s_%s"%(pg, ig, g))
+        mdl.addConstr(total_players <= r["Max Players"], name="max_players_%s_%s_%s"%(pg, ig, g))
+
+    mdl.setObjective(gu.quicksum(dat.positions[p]["Position Importance"] * dat.player_ratings[pl, pg]["Rating"] * v
+                                 for (i, p, pl), v in lineup.items() for pg in [dat.positions[p]["Position Group"]]
+                                 if (pl, pg) in dat.player_ratings),
+                     sense=gu.GRB.MAXIMIZE)
+
+    mdl.optimize()
+
+    if mdl.status == gu.GRB.OPTIMAL:
+        sln = solution_schema.TicDat()
+        for (i, p, pl), v in lineup.items():
+            if abs(v.x - 1) < 0.0001:
+                sln.lineup[i,p] = pl
+        return sln
+
+
+if __name__ == "__main__":
+    standard_main(input_schema, solution_schema, solve)
diff --git a/examples/expert_section/little_league/simpler_little_league_ampl.py b/examples/expert_section/little_league/simpler_little_league_ampl.py
new file mode 100644
index 00000000..24b952c9
--- /dev/null
+++ b/examples/expert_section/little_league/simpler_little_league_ampl.py
@@ -0,0 +1,132 @@
+# this version of the file uses amplpy
+
+# should be 2360.0 obj value
+
+from ticdat import TicDatFactory,  standard_main
+from amplpy import AMPL
+from ticdat.utils import verify
+
+
+input_schema = TicDatFactory (
+    roster = [["Name"],["Grade"]],
+    positions = [["Position"],["Position Importance", "Position Group"]],
+    player_ratings = [["Name", "Position Group"], ["Rating"]],
+    innings = [["Inning"],["Inning Group"]],
+    position_constraints = [["Position Group", "Inning Group", "Grade"],["Min Players", "Max Players"]])
+
+input_schema.set_data_type("positions", "Position Importance",min=0, max=float("inf"),
+                           inclusive_min=True, inclusive_max=False)
+
+# input_schema.add_foreign_key("player_ratings", "roster", ["Name", "Name"])
+# input_schema.add_foreign_key("player_ratings", "positions", ["Position Group", "Position Group"])
+input_schema.set_data_type("player_ratings", "Rating", min=0, max=float("inf"),
+                           inclusive_min=True, inclusive_max=False)
+
+# input_schema.add_foreign_key("position_constraints", "positions", ["Position Group", "Position Group"])
+# input_schema.add_foreign_key("position_constraints", "innings", ["Inning Group", "Inning Group"])
+# input_schema.add_foreign_key("position_constraints", "roster", ["Grade", "Grade"])
+input_schema.set_data_type("position_constraints", "Min Players", min=0, max=float("inf"),
+                           inclusive_min=True, inclusive_max=True)
+input_schema.set_data_type("position_constraints", "Max Players", min=0, max=float("inf"),
+                           inclusive_min=False, inclusive_max=True)
+input_schema.add_data_row_predicate(
+    "position_constraints", predicate_name="Position Min Max Check",
+    predicate=lambda row : row["Max Players"] >= row["Min Players"])
+
+solution_schema = TicDatFactory(
+    lineup = [["Inning", "Position"],["Name"]],
+    parameters = [["Key"],["Value"]])
+
+def solve(dat):
+    assert input_schema.good_tic_dat_object(dat)
+    assert not input_schema.find_foreign_key_failures(dat)
+    assert not input_schema.find_data_type_failures(dat)
+    assert not input_schema.find_data_row_failures(dat)
+
+    dat = input_schema.copy_to_ampl(dat, field_renamings={
+            ("roster", "Grade"):"grade",
+            ("positions", "Position Importance"): "position_importance",
+            ("positions", "Position Group"): "position_group",
+            ("player_ratings", "Rating"): "player_rating",
+            ("innings", "Inning Group"): "inning_group",
+            ("position_constraints", "Min Players"): "min_players",
+            ("position_constraints", "Max Players"): "max_players"
+        })
+
+    ampl = AMPL()
+    ampl.setOption('solver', 'gurobi')
+
+    mod_str = """
+    # Players and grades
+
+    set PLAYERS;
+    param grade {PLAYERS} symbolic;
+    set GRADES = setof {pl in PLAYERS} grade[pl];
+    set IN_GRADE {g in GRADES} = {pl in PLAYERS: grade[pl] = g};
+
+    # Positions and position groups; player ratings
+
+    set POSITIONS;
+    param position_importance {POSITIONS};
+    param position_group {POSITIONS} symbolic;
+    set POSITION_GROUPS = setof {p in POSITIONS} position_group[p];
+    set IN_POSITION_GROUP {pg in POSITION_GROUPS} = {p in POSITIONS: position_group[p] = pg};
+
+    param player_rating {PLAYERS,POSITION_GROUPS};
+
+    # Innings and inning groups; player limits
+
+    set INNINGS;
+    param inning_group {INNINGS} symbolic;
+    set INNING_GROUPS = setof {i in INNINGS} inning_group[i];
+    set IN_INNING_GROUP {ig in INNING_GROUPS} = {i in INNINGS: inning_group[i] = ig};
+
+    set POSITION_CONSTRAINTS within {POSITION_GROUPS,INNING_GROUPS,GRADES};
+    param min_players {POSITION_CONSTRAINTS};
+    param max_players {POSITION_CONSTRAINTS};
+
+    # Decision variables: 1 ==> assignment of a player to a position in an inning
+
+    var Play {INNINGS,POSITIONS,PLAYERS} binary;
+
+    # Objective function: Maximize desirability of the assignment
+
+    maximize TotalImportance:
+       sum {i in INNINGS, p in POSITIONS, pl in PLAYERS}
+          position_importance[p] * player_rating[pl,position_group[p]] * Play[i,p,pl];
+
+    # Exactly one player per position per inning
+
+    subject to AllPositionsFilledPerInning {i in INNINGS, p in POSITIONS}:
+       sum {pl in PLAYERS} Play[i,p,pl] = 1;
+
+    # At most one possition per player per inning
+
+    subject to AtMostOnePositionPerInning {i in INNINGS, pl in PLAYERS}:
+       sum {p in POSITIONS} Play[i,p,pl] <= 1;
+
+    # Total roster slots assigned, for listed combinations of
+    # position group, inning group, and grade, must be within specified limits
+
+    subject to MinMaxPlayers {(pg,ig,g) in POSITION_CONSTRAINTS}:
+       min_players[pg,ig,g] <=
+         sum {p in IN_POSITION_GROUP[pg], i in IN_INNING_GROUP[ig], pl in IN_GRADE[g]} Play[i,p,pl]
+           <= max_players[pg,ig,g];
+    """
+    ampl.eval(mod_str)
+
+    input_schema.set_ampl_data(dat, ampl, {"roster": "PLAYERS", "positions": "POSITIONS",
+                                           "innings": "INNINGS", "position_constraints": "POSITION_CONSTRAINTS"})
+    ampl.solve()
+
+    if ampl.getValue("solve_result") != "infeasible":
+        sln = solution_schema.TicDat()
+        for (i,p,pl),x in ampl.getVariable("Play").getValues().toDict().items():
+            if round(x[0]) > 0:
+                sln.lineup[i,p] = pl
+        sln.parameters["Total Cost"] = ampl.getObjective('TotalImportance').value()
+        return sln
+
+if __name__ == "__main__":
+    standard_main(input_schema, solution_schema, solve)
+
diff --git a/examples/expert_section/little_league/simpler_little_model.json b/examples/expert_section/little_league/simpler_little_model.json
new file mode 100644
index 00000000..775db718
--- /dev/null
+++ b/examples/expert_section/little_league/simpler_little_model.json
@@ -0,0 +1,331 @@
+{
+  "innings": [
+    [
+      1.0, 
+      "First Half"
+    ], 
+    [
+      2.0, 
+      "First Half"
+    ], 
+    [
+      3.0, 
+      "First Half"
+    ], 
+    [
+      4.0, 
+      "First Half"
+    ], 
+    [
+      5.0, 
+      "Second Half"
+    ], 
+    [
+      6.0, 
+      "Second Half"
+    ], 
+    [
+      7.0, 
+      "Second Half"
+    ], 
+    [
+      8.0, 
+      "Second Half"
+    ], 
+    [
+      9.0, 
+      "Second Half"
+    ]
+  ], 
+  "player_ratings": [
+    [
+      "Jakey", 
+      "Outfield", 
+      5
+    ], 
+    [
+      "Bobby", 
+      "Outfield", 
+      3
+    ], 
+    [
+      "Louis", 
+      "Outfield", 
+      4
+    ], 
+    [
+      "Reggie", 
+      "Infield", 
+      4
+    ], 
+    [
+      "Dwight", 
+      "Infield", 
+      3
+    ], 
+    [
+      "Goofy", 
+      "Infield", 
+      1
+    ], 
+    [
+      "Izzy", 
+      "Outfield", 
+      2
+    ], 
+    [
+      "Louis", 
+      "Infield", 
+      5
+    ], 
+    [
+      "Erin", 
+      "Infield", 
+      2
+    ], 
+    [
+      "Ike", 
+      "Infield", 
+      2
+    ], 
+    [
+      "Dwight", 
+      "Outfield", 
+      2
+    ], 
+    [
+      "Jakey", 
+      "Infield", 
+      1
+    ], 
+    [
+      "Jenny", 
+      "Outfield", 
+      1
+    ], 
+    [
+      "Boswell", 
+      "Infield", 
+      3
+    ], 
+    [
+      "Juilia", 
+      "Outfield", 
+      1
+    ], 
+    [
+      "Jackie", 
+      "Infield", 
+      5
+    ], 
+    [
+      "Shula", 
+      "Outfield", 
+      4
+    ], 
+    [
+      "Erin", 
+      "Outfield", 
+      1
+    ], 
+    [
+      "Boswell", 
+      "Outfield", 
+      2
+    ], 
+    [
+      "Juilia", 
+      "Infield", 
+      2
+    ], 
+    [
+      "Eliot", 
+      "Infield", 
+      1
+    ], 
+    [
+      "Ike", 
+      "Outfield", 
+      1
+    ], 
+    [
+      "Jackie", 
+      "Outfield", 
+      4
+    ], 
+    [
+      "Shula", 
+      "Infield", 
+      5
+    ], 
+    [
+      "Julian", 
+      "Outfield", 
+      3
+    ], 
+    [
+      "Bobby", 
+      "Infield", 
+      4
+    ], 
+    [
+      "Izzy", 
+      "Infield", 
+      3
+    ], 
+    [
+      "Jenny", 
+      "Infield", 
+      2
+    ], 
+    [
+      "Reggie", 
+      "Outfield", 
+      3
+    ], 
+    [
+      "Eliot", 
+      "Outfield", 
+      5
+    ], 
+    [
+      "Goofy", 
+      "Outfield", 
+      5
+    ], 
+    [
+      "Julian", 
+      "Infield", 
+      4
+    ]
+  ], 
+  "position_constraints": [
+    [
+      "Infield", 
+      "Second Half", 
+      "fourth", 
+      7, 
+      Infinity
+    ], 
+    [
+      "Infield", 
+      "First Half", 
+      "fifth", 
+      0, 
+      9
+    ]
+  ], 
+  "positions": [
+    [
+      "Second Base", 
+      5, 
+      "Infield"
+    ], 
+    [
+      "Short Stop", 
+      5, 
+      "Infield"
+    ], 
+    [
+      "Catcher", 
+      10, 
+      "Infield"
+    ], 
+    [
+      "Left Field", 
+      3, 
+      "Outfield"
+    ], 
+    [
+      "First Base", 
+      7, 
+      "Infield"
+    ], 
+    [
+      "Third Base", 
+      5, 
+      "Infield"
+    ], 
+    [
+      "Pitcher", 
+      15, 
+      "Infield"
+    ], 
+    [
+      "Right Field", 
+      3, 
+      "Outfield"
+    ], 
+    [
+      "Center", 
+      3, 
+      "Outfield"
+    ]
+  ], 
+  "roster": [
+    [
+      "Ike", 
+      "fifth"
+    ], 
+    [
+      "Bobby", 
+      "fifth"
+    ], 
+    [
+      "Jakey", 
+      "fifth"
+    ], 
+    [
+      "Jackie", 
+      "fifth"
+    ], 
+    [
+      "Goofy", 
+      "fourth"
+    ], 
+    [
+      "Boswell", 
+      "sixth"
+    ], 
+    [
+      "Juilia", 
+      "fifth"
+    ], 
+    [
+      "Dwight", 
+      "fourth"
+    ], 
+    [
+      "Shula", 
+      "fourth"
+    ], 
+    [
+      "Jenny", 
+      "fourth"
+    ], 
+    [
+      "Julian", 
+      "sixth"
+    ], 
+    [
+      "Erin", 
+      "fourth"
+    ], 
+    [
+      "Izzy", 
+      "sixth"
+    ], 
+    [
+      "Louis", 
+      "fifth"
+    ], 
+    [
+      "Reggie", 
+      "sixth"
+    ], 
+    [
+      "Eliot", 
+      "sixth"
+    ]
+  ]
+}
\ No newline at end of file
diff --git a/examples/expert_section/ml_soda_promotion/price_optimization_demo.ipynb b/examples/expert_section/ml_soda_promotion/price_optimization_demo.ipynb
new file mode 100644
index 00000000..14ef1c41
--- /dev/null
+++ b/examples/expert_section/ml_soda_promotion/price_optimization_demo.ipynb
@@ -0,0 +1,1108 @@
+{
+ "cells": [
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "## Price Optimization Soda Demonstration\n",
+    "This notebook demonstrates in fine grain detail how the Opalytics Price Optimization application works. We demonstrate the functionality with toy-sized data, and also with the Soda Promotion example data set.\n"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {
+    "collapsed": true
+   },
+   "source": [
+    "-------\n",
+    "To begin let's create a toy example data set. "
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 1,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "data": {
+      "text/html": [
+       "<div>\n",
+       "<style>\n",
+       "    .dataframe thead tr:only-child th {\n",
+       "        text-align: right;\n",
+       "    }\n",
+       "\n",
+       "    .dataframe thead th {\n",
+       "        text-align: left;\n",
+       "    }\n",
+       "\n",
+       "    .dataframe tbody tr th {\n",
+       "        vertical-align: top;\n",
+       "    }\n",
+       "</style>\n",
+       "<table border=\"1\" class=\"dataframe\">\n",
+       "  <thead>\n",
+       "    <tr style=\"text-align: right;\">\n",
+       "      <th></th>\n",
+       "      <th>Column 1</th>\n",
+       "      <th>Column 2</th>\n",
+       "      <th>Column 3</th>\n",
+       "    </tr>\n",
+       "  </thead>\n",
+       "  <tbody>\n",
+       "    <tr>\n",
+       "      <th>0</th>\n",
+       "      <td>1</td>\n",
+       "      <td>3</td>\n",
+       "      <td>100</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>1</th>\n",
+       "      <td>2</td>\n",
+       "      <td>5</td>\n",
+       "      <td>99</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>2</th>\n",
+       "      <td>3</td>\n",
+       "      <td>7</td>\n",
+       "      <td>98</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>3</th>\n",
+       "      <td>4</td>\n",
+       "      <td>9</td>\n",
+       "      <td>97</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>4</th>\n",
+       "      <td>5</td>\n",
+       "      <td>11</td>\n",
+       "      <td>96</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>5</th>\n",
+       "      <td>6</td>\n",
+       "      <td>13</td>\n",
+       "      <td>95</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>6</th>\n",
+       "      <td>7</td>\n",
+       "      <td>15</td>\n",
+       "      <td>94</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>7</th>\n",
+       "      <td>8</td>\n",
+       "      <td>17</td>\n",
+       "      <td>93</td>\n",
+       "    </tr>\n",
+       "  </tbody>\n",
+       "</table>\n",
+       "</div>"
+      ],
+      "text/plain": [
+       "   Column 1  Column 2  Column 3\n",
+       "0         1         3       100\n",
+       "1         2         5        99\n",
+       "2         3         7        98\n",
+       "3         4         9        97\n",
+       "4         5        11        96\n",
+       "5         6        13        95\n",
+       "6         7        15        94\n",
+       "7         8        17        93"
+      ]
+     },
+     "execution_count": 1,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "from pandas import DataFrame\n",
+    "df = DataFrame({\"Column 1\": list(range(1, 9)), \"Column 2\": [x*2+1 for x in range(1,9)], \n",
+    "                \"Column 3\": [100-x for x in range(8)]})\n",
+    "df"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "I'm not going to identify Dependent/Indepdenent columns at this point, because we're just showing how the different subroutines work.\n",
+    "\n",
+    "First lets look at `train_test_split` which is used for the \"Single Trial\" field on the Experiments report."
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 2,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "data": {
+      "text/html": [
+       "<div>\n",
+       "<style>\n",
+       "    .dataframe thead tr:only-child th {\n",
+       "        text-align: right;\n",
+       "    }\n",
+       "\n",
+       "    .dataframe thead th {\n",
+       "        text-align: left;\n",
+       "    }\n",
+       "\n",
+       "    .dataframe tbody tr th {\n",
+       "        vertical-align: top;\n",
+       "    }\n",
+       "</style>\n",
+       "<table border=\"1\" class=\"dataframe\">\n",
+       "  <thead>\n",
+       "    <tr style=\"text-align: right;\">\n",
+       "      <th></th>\n",
+       "      <th>Column 1</th>\n",
+       "      <th>Column 2</th>\n",
+       "      <th>Column 3</th>\n",
+       "    </tr>\n",
+       "  </thead>\n",
+       "  <tbody>\n",
+       "    <tr>\n",
+       "      <th>6</th>\n",
+       "      <td>7</td>\n",
+       "      <td>15</td>\n",
+       "      <td>94</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>2</th>\n",
+       "      <td>3</td>\n",
+       "      <td>7</td>\n",
+       "      <td>98</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>0</th>\n",
+       "      <td>1</td>\n",
+       "      <td>3</td>\n",
+       "      <td>100</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>4</th>\n",
+       "      <td>5</td>\n",
+       "      <td>11</td>\n",
+       "      <td>96</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>7</th>\n",
+       "      <td>8</td>\n",
+       "      <td>17</td>\n",
+       "      <td>93</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>3</th>\n",
+       "      <td>4</td>\n",
+       "      <td>9</td>\n",
+       "      <td>97</td>\n",
+       "    </tr>\n",
+       "  </tbody>\n",
+       "</table>\n",
+       "</div>"
+      ],
+      "text/plain": [
+       "   Column 1  Column 2  Column 3\n",
+       "6         7        15        94\n",
+       "2         3         7        98\n",
+       "0         1         3       100\n",
+       "4         5        11        96\n",
+       "7         8        17        93\n",
+       "3         4         9        97"
+      ]
+     },
+     "execution_count": 2,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "from sklearn import model_selection\n",
+    "split_one = model_selection.train_test_split(df, test_size = 0.25)\n",
+    "assert len(split_one) == 2\n",
+    "split_one[0]"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 3,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "data": {
+      "text/html": [
+       "<div>\n",
+       "<style>\n",
+       "    .dataframe thead tr:only-child th {\n",
+       "        text-align: right;\n",
+       "    }\n",
+       "\n",
+       "    .dataframe thead th {\n",
+       "        text-align: left;\n",
+       "    }\n",
+       "\n",
+       "    .dataframe tbody tr th {\n",
+       "        vertical-align: top;\n",
+       "    }\n",
+       "</style>\n",
+       "<table border=\"1\" class=\"dataframe\">\n",
+       "  <thead>\n",
+       "    <tr style=\"text-align: right;\">\n",
+       "      <th></th>\n",
+       "      <th>Column 1</th>\n",
+       "      <th>Column 2</th>\n",
+       "      <th>Column 3</th>\n",
+       "    </tr>\n",
+       "  </thead>\n",
+       "  <tbody>\n",
+       "    <tr>\n",
+       "      <th>1</th>\n",
+       "      <td>2</td>\n",
+       "      <td>5</td>\n",
+       "      <td>99</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>5</th>\n",
+       "      <td>6</td>\n",
+       "      <td>13</td>\n",
+       "      <td>95</td>\n",
+       "    </tr>\n",
+       "  </tbody>\n",
+       "</table>\n",
+       "</div>"
+      ],
+      "text/plain": [
+       "   Column 1  Column 2  Column 3\n",
+       "1         2         5        99\n",
+       "5         6        13        95"
+      ]
+     },
+     "execution_count": 3,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "split_one[1]"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 4,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "data": {
+      "text/html": [
+       "<div>\n",
+       "<style>\n",
+       "    .dataframe thead tr:only-child th {\n",
+       "        text-align: right;\n",
+       "    }\n",
+       "\n",
+       "    .dataframe thead th {\n",
+       "        text-align: left;\n",
+       "    }\n",
+       "\n",
+       "    .dataframe tbody tr th {\n",
+       "        vertical-align: top;\n",
+       "    }\n",
+       "</style>\n",
+       "<table border=\"1\" class=\"dataframe\">\n",
+       "  <thead>\n",
+       "    <tr style=\"text-align: right;\">\n",
+       "      <th></th>\n",
+       "      <th>Column 1</th>\n",
+       "      <th>Column 2</th>\n",
+       "      <th>Column 3</th>\n",
+       "    </tr>\n",
+       "  </thead>\n",
+       "  <tbody>\n",
+       "    <tr>\n",
+       "      <th>3</th>\n",
+       "      <td>4</td>\n",
+       "      <td>9</td>\n",
+       "      <td>97</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>0</th>\n",
+       "      <td>1</td>\n",
+       "      <td>3</td>\n",
+       "      <td>100</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>1</th>\n",
+       "      <td>2</td>\n",
+       "      <td>5</td>\n",
+       "      <td>99</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>4</th>\n",
+       "      <td>5</td>\n",
+       "      <td>11</td>\n",
+       "      <td>96</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>6</th>\n",
+       "      <td>7</td>\n",
+       "      <td>15</td>\n",
+       "      <td>94</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>5</th>\n",
+       "      <td>6</td>\n",
+       "      <td>13</td>\n",
+       "      <td>95</td>\n",
+       "    </tr>\n",
+       "  </tbody>\n",
+       "</table>\n",
+       "</div>"
+      ],
+      "text/plain": [
+       "   Column 1  Column 2  Column 3\n",
+       "3         4         9        97\n",
+       "0         1         3       100\n",
+       "1         2         5        99\n",
+       "4         5        11        96\n",
+       "6         7        15        94\n",
+       "5         6        13        95"
+      ]
+     },
+     "execution_count": 4,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "split_two = model_selection.train_test_split(df, test_size = 0.25)\n",
+    "assert len(split_two) == 2\n",
+    "split_two[0]"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 5,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "data": {
+      "text/html": [
+       "<div>\n",
+       "<style>\n",
+       "    .dataframe thead tr:only-child th {\n",
+       "        text-align: right;\n",
+       "    }\n",
+       "\n",
+       "    .dataframe thead th {\n",
+       "        text-align: left;\n",
+       "    }\n",
+       "\n",
+       "    .dataframe tbody tr th {\n",
+       "        vertical-align: top;\n",
+       "    }\n",
+       "</style>\n",
+       "<table border=\"1\" class=\"dataframe\">\n",
+       "  <thead>\n",
+       "    <tr style=\"text-align: right;\">\n",
+       "      <th></th>\n",
+       "      <th>Column 1</th>\n",
+       "      <th>Column 2</th>\n",
+       "      <th>Column 3</th>\n",
+       "    </tr>\n",
+       "  </thead>\n",
+       "  <tbody>\n",
+       "    <tr>\n",
+       "      <th>2</th>\n",
+       "      <td>3</td>\n",
+       "      <td>7</td>\n",
+       "      <td>98</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>7</th>\n",
+       "      <td>8</td>\n",
+       "      <td>17</td>\n",
+       "      <td>93</td>\n",
+       "    </tr>\n",
+       "  </tbody>\n",
+       "</table>\n",
+       "</div>"
+      ],
+      "text/plain": [
+       "   Column 1  Column 2  Column 3\n",
+       "2         3         7        98\n",
+       "7         8        17        93"
+      ]
+     },
+     "execution_count": 5,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "split_two[1]"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "Every time I call `train_test_split` the rows are shuffled, and `test_size` proportion of them are randomly selected to be the testing rows. The remainder are the training rows. `train_test_split` then returns this segmentation of rows as a pair of (training set, testing set) matrices."
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "---- \n",
+    "Now lets use the toy data set to examine how `KFold` works."
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 6,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "data": {
+      "text/plain": [
+       "[(array([2, 3, 4, 5, 6, 7]), array([0, 1])),\n",
+       " (array([0, 1, 4, 5, 6, 7]), array([2, 3])),\n",
+       " (array([0, 1, 2, 3, 6, 7]), array([4, 5])),\n",
+       " (array([0, 1, 2, 3, 4, 5]), array([6, 7]))]"
+      ]
+     },
+     "execution_count": 6,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "kf = model_selection.KFold(n_splits=4)\n",
+    "splits = list(kf.split(df))\n",
+    "splits"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "`KFold` is doing the same sort thing, except it is doing it all at once, and returning row indicies instead of actually data rows. So it is creating 4 train-test splits. The first split sets the first 2 rows as the testing set, the second split sets the second two rows, and so forth.\n",
+    "\n",
+    "If you want, you can shuffle things, but there is no need to. There is no rational reason to believe the set of splits below will yield a more accurate assessment than the split of splits above."
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 7,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "data": {
+      "text/plain": [
+       "[(array([1, 2, 3, 4, 5, 7]), array([0, 6])),\n",
+       " (array([0, 1, 2, 4, 5, 6]), array([3, 7])),\n",
+       " (array([0, 3, 4, 5, 6, 7]), array([1, 2])),\n",
+       " (array([0, 1, 2, 3, 6, 7]), array([4, 5]))]"
+      ]
+     },
+     "execution_count": 7,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "list(model_selection.KFold(n_splits=4, shuffle=True).split(df))"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "----\n",
+    "### Connect to Predictive Analytics Application\n",
+    "\n",
+    "Now lets load some realistic historical data and recreate some of the results we'd see in the Predictive Analytics application."
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 8,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "data": {
+      "text/html": [
+       "<div>\n",
+       "<style>\n",
+       "    .dataframe thead tr:only-child th {\n",
+       "        text-align: right;\n",
+       "    }\n",
+       "\n",
+       "    .dataframe thead th {\n",
+       "        text-align: left;\n",
+       "    }\n",
+       "\n",
+       "    .dataframe tbody tr th {\n",
+       "        vertical-align: top;\n",
+       "    }\n",
+       "</style>\n",
+       "<table border=\"1\" class=\"dataframe\">\n",
+       "  <thead>\n",
+       "    <tr style=\"text-align: right;\">\n",
+       "      <th></th>\n",
+       "      <th>Product</th>\n",
+       "      <th>Sales</th>\n",
+       "      <th>Cost Per Unit</th>\n",
+       "      <th>Easter Included</th>\n",
+       "      <th>Super Bowl Included</th>\n",
+       "      <th>Christmas Included</th>\n",
+       "      <th>Other Holiday</th>\n",
+       "      <th>4 Wk Avg Temp</th>\n",
+       "      <th>4 Wk Avg Humidity</th>\n",
+       "      <th>Sales M-1 weeks</th>\n",
+       "      <th>Sales M-2 weeks</th>\n",
+       "      <th>Sales M-3 weeks</th>\n",
+       "      <th>Sales M-4 Weeks</th>\n",
+       "      <th>Sales M-5 weeks</th>\n",
+       "    </tr>\n",
+       "  </thead>\n",
+       "  <tbody>\n",
+       "    <tr>\n",
+       "      <th>0</th>\n",
+       "      <td>11 Down</td>\n",
+       "      <td>51.9</td>\n",
+       "      <td>1.6625</td>\n",
+       "      <td>No</td>\n",
+       "      <td>No</td>\n",
+       "      <td>Yes</td>\n",
+       "      <td>No</td>\n",
+       "      <td>80.69</td>\n",
+       "      <td>69.19</td>\n",
+       "      <td>17.0</td>\n",
+       "      <td>22.4</td>\n",
+       "      <td>13.5</td>\n",
+       "      <td>14.5</td>\n",
+       "      <td>28.0</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>1</th>\n",
+       "      <td>Alpine Stream</td>\n",
+       "      <td>55.8</td>\n",
+       "      <td>2.2725</td>\n",
+       "      <td>No</td>\n",
+       "      <td>No</td>\n",
+       "      <td>Yes</td>\n",
+       "      <td>No</td>\n",
+       "      <td>80.69</td>\n",
+       "      <td>69.19</td>\n",
+       "      <td>2.4</td>\n",
+       "      <td>2.2</td>\n",
+       "      <td>2.0</td>\n",
+       "      <td>1.4</td>\n",
+       "      <td>0.5</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>2</th>\n",
+       "      <td>Bright</td>\n",
+       "      <td>3385.6</td>\n",
+       "      <td>1.3475</td>\n",
+       "      <td>No</td>\n",
+       "      <td>No</td>\n",
+       "      <td>Yes</td>\n",
+       "      <td>No</td>\n",
+       "      <td>80.69</td>\n",
+       "      <td>69.19</td>\n",
+       "      <td>301.8</td>\n",
+       "      <td>188.8</td>\n",
+       "      <td>101.4</td>\n",
+       "      <td>81.6</td>\n",
+       "      <td>213.8</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>3</th>\n",
+       "      <td>Crisp Clear</td>\n",
+       "      <td>63.5</td>\n",
+       "      <td>1.6600</td>\n",
+       "      <td>No</td>\n",
+       "      <td>No</td>\n",
+       "      <td>Yes</td>\n",
+       "      <td>No</td>\n",
+       "      <td>80.69</td>\n",
+       "      <td>69.19</td>\n",
+       "      <td>73.8</td>\n",
+       "      <td>69.4</td>\n",
+       "      <td>72.8</td>\n",
+       "      <td>75.4</td>\n",
+       "      <td>57.4</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>4</th>\n",
+       "      <td>Popsi Kola</td>\n",
+       "      <td>181.1</td>\n",
+       "      <td>1.8725</td>\n",
+       "      <td>No</td>\n",
+       "      <td>No</td>\n",
+       "      <td>Yes</td>\n",
+       "      <td>No</td>\n",
+       "      <td>80.69</td>\n",
+       "      <td>69.19</td>\n",
+       "      <td>23.1</td>\n",
+       "      <td>22.6</td>\n",
+       "      <td>22.1</td>\n",
+       "      <td>19.9</td>\n",
+       "      <td>23.2</td>\n",
+       "    </tr>\n",
+       "  </tbody>\n",
+       "</table>\n",
+       "</div>"
+      ],
+      "text/plain": [
+       "         Product   Sales  Cost Per Unit Easter Included Super Bowl Included  \\\n",
+       "0        11 Down    51.9         1.6625              No                  No   \n",
+       "1  Alpine Stream    55.8         2.2725              No                  No   \n",
+       "2         Bright  3385.6         1.3475              No                  No   \n",
+       "3    Crisp Clear    63.5         1.6600              No                  No   \n",
+       "4     Popsi Kola   181.1         1.8725              No                  No   \n",
+       "\n",
+       "  Christmas Included Other Holiday  4 Wk Avg Temp  4 Wk Avg Humidity  \\\n",
+       "0                Yes            No          80.69              69.19   \n",
+       "1                Yes            No          80.69              69.19   \n",
+       "2                Yes            No          80.69              69.19   \n",
+       "3                Yes            No          80.69              69.19   \n",
+       "4                Yes            No          80.69              69.19   \n",
+       "\n",
+       "   Sales M-1 weeks  Sales M-2 weeks  Sales M-3 weeks  Sales M-4 Weeks  \\\n",
+       "0             17.0             22.4             13.5             14.5   \n",
+       "1              2.4              2.2              2.0              1.4   \n",
+       "2            301.8            188.8            101.4             81.6   \n",
+       "3             73.8             69.4             72.8             75.4   \n",
+       "4             23.1             22.6             22.1             19.9   \n",
+       "\n",
+       "   Sales M-5 weeks  \n",
+       "0             28.0  \n",
+       "1              0.5  \n",
+       "2            213.8  \n",
+       "3             57.4  \n",
+       "4             23.2  "
+      ]
+     },
+     "execution_count": 8,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "import pandas\n",
+    "df_hist = pandas.read_excel(\"soda_sales_historical_data.xlsx\")\n",
+    "df_hist[:5]"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 9,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "data": {
+      "text/plain": [
+       "(596, 14)"
+      ]
+     },
+     "execution_count": 9,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "df_hist.shape"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "Converting categorical data to numeric is one of the \"grunt tasks\" automated by the Predictive Analytics app."
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 10,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "data": {
+      "text/html": [
+       "<div>\n",
+       "<style>\n",
+       "    .dataframe thead tr:only-child th {\n",
+       "        text-align: right;\n",
+       "    }\n",
+       "\n",
+       "    .dataframe thead th {\n",
+       "        text-align: left;\n",
+       "    }\n",
+       "\n",
+       "    .dataframe tbody tr th {\n",
+       "        vertical-align: top;\n",
+       "    }\n",
+       "</style>\n",
+       "<table border=\"1\" class=\"dataframe\">\n",
+       "  <thead>\n",
+       "    <tr style=\"text-align: right;\">\n",
+       "      <th></th>\n",
+       "      <th>Sales</th>\n",
+       "      <th>Cost Per Unit</th>\n",
+       "      <th>4 Wk Avg Temp</th>\n",
+       "      <th>4 Wk Avg Humidity</th>\n",
+       "      <th>Sales M-1 weeks</th>\n",
+       "      <th>Sales M-2 weeks</th>\n",
+       "      <th>Sales M-3 weeks</th>\n",
+       "      <th>Sales M-4 Weeks</th>\n",
+       "      <th>Sales M-5 weeks</th>\n",
+       "      <th>dmy_Product_11 Down</th>\n",
+       "      <th>...</th>\n",
+       "      <th>dmy_Product_Koala Kola</th>\n",
+       "      <th>dmy_Product_Mr. Popper</th>\n",
+       "      <th>dmy_Product_Popsi Kola</th>\n",
+       "      <th>dmy_Easter Included_No</th>\n",
+       "      <th>dmy_Easter Included_Yes</th>\n",
+       "      <th>dmy_Super Bowl Included_No</th>\n",
+       "      <th>dmy_Super Bowl Included_Yes</th>\n",
+       "      <th>dmy_Christmas Included_No</th>\n",
+       "      <th>dmy_Christmas Included_Yes</th>\n",
+       "      <th>dmy_Other Holiday_No</th>\n",
+       "    </tr>\n",
+       "  </thead>\n",
+       "  <tbody>\n",
+       "    <tr>\n",
+       "      <th>0</th>\n",
+       "      <td>51.9</td>\n",
+       "      <td>1.6625</td>\n",
+       "      <td>80.69</td>\n",
+       "      <td>69.19</td>\n",
+       "      <td>17.0</td>\n",
+       "      <td>22.4</td>\n",
+       "      <td>13.5</td>\n",
+       "      <td>14.5</td>\n",
+       "      <td>28.0</td>\n",
+       "      <td>1</td>\n",
+       "      <td>...</td>\n",
+       "      <td>0</td>\n",
+       "      <td>0</td>\n",
+       "      <td>0</td>\n",
+       "      <td>1</td>\n",
+       "      <td>0</td>\n",
+       "      <td>1</td>\n",
+       "      <td>0</td>\n",
+       "      <td>0</td>\n",
+       "      <td>1</td>\n",
+       "      <td>1</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>1</th>\n",
+       "      <td>55.8</td>\n",
+       "      <td>2.2725</td>\n",
+       "      <td>80.69</td>\n",
+       "      <td>69.19</td>\n",
+       "      <td>2.4</td>\n",
+       "      <td>2.2</td>\n",
+       "      <td>2.0</td>\n",
+       "      <td>1.4</td>\n",
+       "      <td>0.5</td>\n",
+       "      <td>0</td>\n",
+       "      <td>...</td>\n",
+       "      <td>0</td>\n",
+       "      <td>0</td>\n",
+       "      <td>0</td>\n",
+       "      <td>1</td>\n",
+       "      <td>0</td>\n",
+       "      <td>1</td>\n",
+       "      <td>0</td>\n",
+       "      <td>0</td>\n",
+       "      <td>1</td>\n",
+       "      <td>1</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>2</th>\n",
+       "      <td>3385.6</td>\n",
+       "      <td>1.3475</td>\n",
+       "      <td>80.69</td>\n",
+       "      <td>69.19</td>\n",
+       "      <td>301.8</td>\n",
+       "      <td>188.8</td>\n",
+       "      <td>101.4</td>\n",
+       "      <td>81.6</td>\n",
+       "      <td>213.8</td>\n",
+       "      <td>0</td>\n",
+       "      <td>...</td>\n",
+       "      <td>0</td>\n",
+       "      <td>0</td>\n",
+       "      <td>0</td>\n",
+       "      <td>1</td>\n",
+       "      <td>0</td>\n",
+       "      <td>1</td>\n",
+       "      <td>0</td>\n",
+       "      <td>0</td>\n",
+       "      <td>1</td>\n",
+       "      <td>1</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>3</th>\n",
+       "      <td>63.5</td>\n",
+       "      <td>1.6600</td>\n",
+       "      <td>80.69</td>\n",
+       "      <td>69.19</td>\n",
+       "      <td>73.8</td>\n",
+       "      <td>69.4</td>\n",
+       "      <td>72.8</td>\n",
+       "      <td>75.4</td>\n",
+       "      <td>57.4</td>\n",
+       "      <td>0</td>\n",
+       "      <td>...</td>\n",
+       "      <td>0</td>\n",
+       "      <td>0</td>\n",
+       "      <td>0</td>\n",
+       "      <td>1</td>\n",
+       "      <td>0</td>\n",
+       "      <td>1</td>\n",
+       "      <td>0</td>\n",
+       "      <td>0</td>\n",
+       "      <td>1</td>\n",
+       "      <td>1</td>\n",
+       "    </tr>\n",
+       "    <tr>\n",
+       "      <th>4</th>\n",
+       "      <td>181.1</td>\n",
+       "      <td>1.8725</td>\n",
+       "      <td>80.69</td>\n",
+       "      <td>69.19</td>\n",
+       "      <td>23.1</td>\n",
+       "      <td>22.6</td>\n",
+       "      <td>22.1</td>\n",
+       "      <td>19.9</td>\n",
+       "      <td>23.2</td>\n",
+       "      <td>0</td>\n",
+       "      <td>...</td>\n",
+       "      <td>0</td>\n",
+       "      <td>0</td>\n",
+       "      <td>1</td>\n",
+       "      <td>1</td>\n",
+       "      <td>0</td>\n",
+       "      <td>1</td>\n",
+       "      <td>0</td>\n",
+       "      <td>0</td>\n",
+       "      <td>1</td>\n",
+       "      <td>1</td>\n",
+       "    </tr>\n",
+       "  </tbody>\n",
+       "</table>\n",
+       "<p>5 rows × 25 columns</p>\n",
+       "</div>"
+      ],
+      "text/plain": [
+       "    Sales  Cost Per Unit  4 Wk Avg Temp  4 Wk Avg Humidity  Sales M-1 weeks  \\\n",
+       "0    51.9         1.6625          80.69              69.19             17.0   \n",
+       "1    55.8         2.2725          80.69              69.19              2.4   \n",
+       "2  3385.6         1.3475          80.69              69.19            301.8   \n",
+       "3    63.5         1.6600          80.69              69.19             73.8   \n",
+       "4   181.1         1.8725          80.69              69.19             23.1   \n",
+       "\n",
+       "   Sales M-2 weeks  Sales M-3 weeks  Sales M-4 Weeks  Sales M-5 weeks  \\\n",
+       "0             22.4             13.5             14.5             28.0   \n",
+       "1              2.2              2.0              1.4              0.5   \n",
+       "2            188.8            101.4             81.6            213.8   \n",
+       "3             69.4             72.8             75.4             57.4   \n",
+       "4             22.6             22.1             19.9             23.2   \n",
+       "\n",
+       "   dmy_Product_11 Down          ...           dmy_Product_Koala Kola  \\\n",
+       "0                    1          ...                                0   \n",
+       "1                    0          ...                                0   \n",
+       "2                    0          ...                                0   \n",
+       "3                    0          ...                                0   \n",
+       "4                    0          ...                                0   \n",
+       "\n",
+       "   dmy_Product_Mr. Popper  dmy_Product_Popsi Kola  dmy_Easter Included_No  \\\n",
+       "0                       0                       0                       1   \n",
+       "1                       0                       0                       1   \n",
+       "2                       0                       0                       1   \n",
+       "3                       0                       0                       1   \n",
+       "4                       0                       1                       1   \n",
+       "\n",
+       "   dmy_Easter Included_Yes  dmy_Super Bowl Included_No  \\\n",
+       "0                        0                           1   \n",
+       "1                        0                           1   \n",
+       "2                        0                           1   \n",
+       "3                        0                           1   \n",
+       "4                        0                           1   \n",
+       "\n",
+       "   dmy_Super Bowl Included_Yes  dmy_Christmas Included_No  \\\n",
+       "0                            0                          0   \n",
+       "1                            0                          0   \n",
+       "2                            0                          0   \n",
+       "3                            0                          0   \n",
+       "4                            0                          0   \n",
+       "\n",
+       "   dmy_Christmas Included_Yes  dmy_Other Holiday_No  \n",
+       "0                           1                     1  \n",
+       "1                           1                     1  \n",
+       "2                           1                     1  \n",
+       "3                           1                     1  \n",
+       "4                           1                     1  \n",
+       "\n",
+       "[5 rows x 25 columns]"
+      ]
+     },
+     "execution_count": 10,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "from pandas import DataFrame, get_dummies\n",
+    "categorical_columns = ['Product','Easter Included','Super Bowl Included', \n",
+    "                       'Christmas Included', 'Other Holiday']\n",
+    "df_hist = get_dummies(df_hist, prefix={k:\"dmy_%s\"%k for k in categorical_columns},\n",
+    "                      columns = list(categorical_columns))\n",
+    "df_hist[:5]"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "Lets assume we're only doing one experiment, and thats with Ordinary Least Squares for the '`*all*`' slice."
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 11,
+   "metadata": {
+    "collapsed": true
+   },
+   "outputs": [],
+   "source": [
+    "from sklearn.linear_model import LinearRegression"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 12,
+   "metadata": {
+    "collapsed": true
+   },
+   "outputs": [],
+   "source": [
+    "train_data, test_data = model_selection.train_test_split(df_hist, test_size=0.25)"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 13,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "data": {
+      "text/plain": [
+       "LinearRegression(copy_X=True, fit_intercept=True, n_jobs=1, normalize=False)"
+      ]
+     },
+     "execution_count": 13,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "obj = LinearRegression()\n",
+    "obj.fit(y = train_data[\"Sales\"], X = train_data.drop(\"Sales\", axis=1))"
+   ]
+  },
+  {
+   "cell_type": "markdown",
+   "metadata": {},
+   "source": [
+    "### Here is your single trial result"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 14,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "data": {
+      "text/plain": [
+       "0.68132683960789886"
+      ]
+     },
+     "execution_count": 14,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "single_trial = obj.score(y = test_data[\"Sales\"], X = test_data.drop(\"Sales\", axis=1))\n",
+    "single_trial"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": true
+   },
+   "outputs": [],
+   "source": []
+  }
+ ],
+ "metadata": {
+  "kernelspec": {
+   "display_name": "Python 2",
+   "language": "python",
+   "name": "python2"
+  },
+  "language_info": {
+   "codemirror_mode": {
+    "name": "ipython",
+    "version": 2
+   },
+   "file_extension": ".py",
+   "mimetype": "text/x-python",
+   "name": "python",
+   "nbconvert_exporter": "python",
+   "pygments_lexer": "ipython2",
+   "version": "2.7.11"
+  }
+ },
+ "nbformat": 4,
+ "nbformat_minor": 0
+}
diff --git a/examples/expert_section/ml_soda_promotion/readme b/examples/expert_section/ml_soda_promotion/readme
index 593fcbb5..8472a6a8 100644
--- a/examples/expert_section/ml_soda_promotion/readme
+++ b/examples/expert_section/ml_soda_promotion/readme
@@ -7,5 +7,7 @@ Repository for Gurobi webinar on ML and Optimization.
                                prescriptive portion of the predict-and-prescribe demo.
  * soda_sales_historical_data.json The historical data in human-readable .json format.
  * super_bowl_promotion_data.json The Super Bowl (i.e. current) data in .json format.
+ * price_optimization_demo.ipynb : A notebook the relates this example to the functionality
+                                   in the Opalytics Price Optimization application.
 
 See http://bit.ly/2qV2WEr for a detailed walk-through on using this directory.
diff --git a/ticdat/__init__.py b/ticdat/__init__.py
index b3992f19..27f3d73d 100644
--- a/ticdat/__init__.py
+++ b/ticdat/__init__.py
@@ -14,7 +14,7 @@
 from ticdat.opl import opl_run, create_opl_mod_text, create_opl_mod_output_text
 from ticdat.model import Model
 from ticdat.pandatfactory import PanDatFactory
-__version__ = '0.2.20.1'
+__version__ = '0.2.20.2'
 __all__ = ["TicDatFactory", "PanDatFactory", "freeze_me", "LogFile", "Sloc", "Slicer", "Progress", "standard_main",
            "Model", "opl_run", "create_opl_mod_text", "create_opl_mod_output_text", "ampl_format",
            "gurobi_env", "verify", "dat_restricted", "sln_restricted"]
diff --git a/ticdat/model.py b/ticdat/model.py
index cf8aff38..c47354e7 100644
--- a/ticdat/model.py
+++ b/ticdat/model.py
@@ -150,7 +150,7 @@ def optimize(self, *args, **kwargs):
         Optimize the model.
         :param args: Optional engine-specific arguments to pass to the model_type API
         :param kwargs: Optional engine-specific arguments to pass to the model_type API
-        :return: Turthy if the model solves successfully, Falsey otherwise.
+        :return: Truthy if the model solves successfully, Falsey otherwise.
                  For a cplex model, a sucessful optimization returns the cplex.Model.solve result
         """
         if self.model_type == "gurobi":
diff --git a/ticdat/pandatfactory.py b/ticdat/pandatfactory.py
index 69cd7ea3..deeda4dd 100644
--- a/ticdat/pandatfactory.py
+++ b/ticdat/pandatfactory.py
@@ -8,6 +8,7 @@
 from ticdat.utils import lupish, deep_freeze, containerish, FrozenDict, safe_apply, stringish
 import ticdat.pandatio as pandatio
 from itertools import count
+import math
 try:
     from pandas import isnull
     import numpy
@@ -37,6 +38,30 @@ def _faster_df_apply(df, func, trip_wire_check=None):
     # will default to float for empty Series, like original pandas
     return pd.Series(data, index=index, **({"dtype": numpy.float64} if not data else {}))
 
+def _is_last_rows_all_nan(df, last_rows):
+    assert last_rows > 0
+    # quick last row check to make faster
+    all_nan_row =  lambda row: all(map(pd.isnull, row.values()))
+    return _faster_df_apply(df.tail(1), all_nan_row).all() and \
+           _faster_df_apply(df.tail(last_rows), all_nan_row,
+                            trip_wire_check=lambda x: all_nan_row if x else lambda row: False).all()
+
+def _find_number_all_nan_last_rows(df, min_rtn, max_rtn):
+    assert max_rtn >= min_rtn
+    if min_rtn == max_rtn:
+        assert min_rtn == 0 or _is_last_rows_all_nan(df, min_rtn)
+        return min_rtn
+    med_rtn =  math.ceil((min_rtn+max_rtn)/2)
+    if _is_last_rows_all_nan(df, med_rtn):
+        return _find_number_all_nan_last_rows(df, med_rtn, max_rtn)
+    return _find_number_all_nan_last_rows(df, min_rtn, med_rtn-1)
+
+def remove_trailing_all_nan(df):
+    all_nan_last_rows = _find_number_all_nan_last_rows(df, 0, len(df))
+    if all_nan_last_rows:
+        return df.head(len(df)-all_nan_last_rows).copy(deep=True)
+    return df
+
 class PanDatFactory(object):
     """
      Defines a schema for a collection of pandas.DataFrame objects.
@@ -94,7 +119,8 @@ def schema(self, include_ancillary_info = False):
                 "default_values" : self.default_values,
                 "data_types" : self.data_types,
                 "parameters": self.parameters,
-                "infinity_io_flag": self.infinity_io_flag}
+                "infinity_io_flag": self.infinity_io_flag,
+                "xlsx_trailing_empty_rows": self.xlsx_trailing_empty_rows}
     @staticmethod
     def create_from_full_schema(full_schema):
         """
@@ -109,7 +135,7 @@ def create_from_full_schema(full_schema):
         """
         old_schema = {"tables_fields", "foreign_keys", "default_values", "data_types"}
         verify(dictish(full_schema) and set(full_schema).issuperset(old_schema) and set(full_schema) in
-               utils.all_subsets(old_schema.union({"parameters", "infinity_io_flag"})),
+               utils.all_subsets(old_schema.union({"parameters", "infinity_io_flag", "xlsx_trailing_empty_rows"})),
                "full_schema should be the result of calling schema(True) for some PanDatFactory")
         fks = full_schema["foreign_keys"]
         verify( (not fks) or (lupish(fks) and all(lupish(_) and len(_) >= 3 for _ in fks)),
@@ -144,6 +170,8 @@ def create_from_full_schema(full_schema):
                 rtn.add_parameter(p, *((df,) + tuple(dt)), enforce_type_rules=True)
         if "infinity_io_flag" in full_schema:
             rtn.set_infinity_io_flag(full_schema["infinity_io_flag"])
+        if "xlsx_trailing_empty_rows" in full_schema:
+            rtn.set_xlsx_trailing_empty_rows(full_schema["xlsx_trailing_empty_rows"])
         return rtn
     def clone(self, table_restrictions=None):
         """
@@ -174,6 +202,25 @@ def data_types(self):
     def parameters(self):
         return FrozenDict(self._parameters)
     @property
+    def xlsx_trailing_empty_rows(self):
+        """
+        see __doc__ for set_xlsx_trailing_empty_rows
+        """
+        return self._xlsx_trailing_empty_rows[0]
+    def set_xlsx_trailing_empty_rows(self, value):
+        """
+        Set the xlsx_trailing_empty_rows for the PanDatFactory. Choices are:
+        --> 'prune' : (the default) when reading an xlsx/xlsm file, look for trailing all pd.isnull rows in each table,
+                      and prune them
+        --> 'ignore': retain such rows
+        With the move to openpyxl for xlsx/xlsm file reading, its more likely that Excel users accidentally creating
+        trailing all none rows.
+        :param value: either 'prune' or 'ignore'
+        :return:
+        """
+        verify(value in ["prune", "ignore"], f"bad value {value}")
+        self._xlsx_trailing_empty_rows[0] = value
+    @property
     def infinity_io_flag(self):
         """
         see __doc__ for set_infinity_io_flag
@@ -330,13 +377,16 @@ def _pre_write_adjustment(self, dat):
             for f in self.primary_key_fields.get(t, ()) + self.data_fields.get(t, ()):
                 if utils.numericish(self.infinity_io_flag):
                     fixme = apply(df, lambda row: utils.numericish(row[f]) and row[f] >= self.infinity_io_flag)
-                    df.loc[fixme, f] = self.infinity_io_flag
+                    if fixme.any():
+                        df.loc[fixme, f] = self.infinity_io_flag
                     fixme = apply(df, lambda row: utils.numericish(row[f]) and row[f] <= -self.infinity_io_flag)
-                    df.loc[fixme, f] = -self.infinity_io_flag
+                    if fixme.any():
+                        df.loc[fixme, f] = -self.infinity_io_flag
                 elif utils.numericish(self._none_as_infinity_bias(t, f)):
                     assert self.infinity_io_flag is None
                     fixme = apply(df, lambda row: row[f] == float("inf") * self._none_as_infinity_bias(t, f))
-                    df.loc[fixme, f] = None
+                    if fixme.any():
+                        df.loc[fixme, f] = None
         return rtn
     def set_data_type(self, table, field, number_allowed = True,
                       inclusive_min = True, inclusive_max = False, min = 0, max = float("inf"),
@@ -711,12 +761,18 @@ def __init__(self, **init_fields):
         self._foreign_keys = clt.defaultdict(set)
         self._parameters = {}
         self._infinity_io_flag = ["N/A"]
+        self._xlsx_trailing_empty_rows = ["prune"]
         self._none_as_infinity_bias_cache = {}
 
 
         self.all_tables = frozenset(init_fields)
         superself = self
         class PanDat(object):
+            def _len_dict(self):
+                '''
+                :return: a dictionary summarizing table lengths. Zero length tables omitted. Safe to use, I won't change
+                '''
+                return {t: l for t in superself.all_tables for l in [len(getattr(self, t))] if l}
             def __repr__(self):
                 tlen = lambda t: len(getattr(self, t)) if isinstance(getattr(self, t), DataFrame) else None
                 return "pd: {" + ", ".join("%s: %s"%(t, tlen(t)) for t in sorted(superself.all_tables)) + "}"
diff --git a/ticdat/pandatio.py b/ticdat/pandatio.py
index e30f10f6..2b7f2a38 100644
--- a/ticdat/pandatio.py
+++ b/ticdat/pandatio.py
@@ -7,7 +7,7 @@
 
 import os
 from ticdat.utils import freezable_factory, verify, case_space_to_pretty, pd, TicDatError, FrozenDict, all_fields
-from ticdat.utils import all_underscore_replacements, stringish, dictish
+from ticdat.utils import all_underscore_replacements, stringish, dictish, debug_break
 from itertools import product, chain
 from collections import defaultdict
 import inspect
@@ -493,8 +493,12 @@ def create_pan_dat(self, xls_file_path, fill_missing_fields=False):
         This problem is even worse with df = pd.DataFrame({"a":["0100", "1200", "2300"]})
         """
         rtn = {}
-        for t, s in self._get_sheet_names(xls_file_path).items():
-            rtn[t] = pd.read_excel(xls_file_path, s, dtype=self.pan_dat_factory._dtypes_for_pandas_read(t))
+        try :
+            xl = pd.ExcelFile(xls_file_path)
+        except Exception as e:
+            raise TicDatError("Unable to open %s as xls file : %s"%(xls_file_path, e))
+        for t, s in self._get_sheet_names(xl).items():
+            rtn[t] = pd.read_excel(xl, s, dtype=self.pan_dat_factory._dtypes_for_pandas_read(t))
         missing_tables = {t for t in self.pan_dat_factory.all_tables if t not in rtn}
         if missing_tables:
             print ("The following table names could not be found in the %s file.\n%s\n"%
@@ -506,14 +510,17 @@ def create_pan_dat(self, xls_file_path, fill_missing_fields=False):
                 rtn[t][f] = self.pan_dat_factory.default_values[t][f]
         verify(fill_missing_fields or not missing_fields,
                "The following are (table, field) pairs missing from the %s file.\n%s" % (xls_file_path, missing_fields))
-        return _clean_pandat_creator(self.pan_dat_factory, rtn, print_missing_tables=True)
+        xl.close()
+        rtn = _clean_pandat_creator(self.pan_dat_factory, rtn, print_missing_tables=True)
+        if self.pan_dat_factory.xlsx_trailing_empty_rows == "prune":
+            from ticdat.pandatfactory import remove_trailing_all_nan
+            for t in self.pan_dat_factory.all_tables:
+                setattr(rtn, t, remove_trailing_all_nan(getattr(rtn, t)))
+        return rtn
+
 
-    def _get_sheet_names(self, xls_file_path):
+    def _get_sheet_names(self, xl):
         sheets = defaultdict(list)
-        try :
-            xl = pd.ExcelFile(xls_file_path)
-        except Exception as e:
-            raise TicDatError("Unable to open %s as xls file : %s"%(xls_file_path, e))
         for table, sheet in product(self.pan_dat_factory.all_tables, xl.sheet_names) :
             if table.lower()[:_longest_sheet] == sheet.lower().replace(' ', '_')[:_longest_sheet]:
                 sheets[table].append(sheet)
@@ -550,3 +557,4 @@ def write_file(self, pan_dat, file_path, case_space_sheet_names=False):
             getattr(pan_dat, t).to_excel(writer, case_space_to_pretty(t) if case_space_sheet_names else t,
                                          index=False)
         writer.save()
+        writer.close()
diff --git a/ticdat/ticdatfactory.py b/ticdat/ticdatfactory.py
index 41176308..21636cc6 100644
--- a/ticdat/ticdatfactory.py
+++ b/ticdat/ticdatfactory.py
@@ -87,7 +87,8 @@ def schema(self, include_ancillary_info = False):
                 "default_values" : self.default_values,
                 "data_types" : self.data_types,
                 "parameters": self.parameters,
-                "infinity_io_flag": self.infinity_io_flag}
+                "infinity_io_flag": self.infinity_io_flag,
+                "xlsx_trailing_empty_rows": self.xlsx_trailing_empty_rows}
     @staticmethod
     def create_from_full_schema(full_schema):
         """
@@ -102,7 +103,7 @@ def create_from_full_schema(full_schema):
         """
         old_schema = {"tables_fields", "foreign_keys", "default_values", "data_types"}
         verify(dictish(full_schema) and set(full_schema).issuperset(old_schema) and  set(full_schema) in
-               utils.all_subsets(old_schema.union({"parameters", "infinity_io_flag"})),
+               utils.all_subsets(old_schema.union({"parameters", "infinity_io_flag", "xlsx_trailing_empty_rows"})),
                "full_schema should be the result of calling schema(True) for some TicDatFactory")
         fks = full_schema["foreign_keys"]
         verify( (not fks) or (lupish(fks) and all(lupish(_) and len(_) >= 3 for _ in fks)),
@@ -136,6 +137,8 @@ def create_from_full_schema(full_schema):
                 rtn.add_parameter(p, *((df,) + tuple(dt)), enforce_type_rules=True)
         if "infinity_io_flag" in full_schema:
             rtn.set_infinity_io_flag(full_schema["infinity_io_flag"])
+        if "xlsx_trailing_empty_rows" in full_schema:
+            rtn.set_xlsx_trailing_empty_rows(full_schema["xlsx_trailing_empty_rows"])
         return rtn
     @property
     def generator_tables(self):
@@ -694,6 +697,11 @@ def __repr__(self):
                 tlen = lambda t: utils.safe_apply(len)(getattr(self, t))
                 return "td: {" + ", ".join("%s: %s"%(t, tlen(t)) for t in sorted(superself.all_tables)) + "}"
         class TicDat(_TicDat) :
+            def _len_dict(self):
+                '''
+                :return: a dictionary summarizing table lengths. Zero length tables omitted. Safe to use, I won't change
+                '''
+                return {t: l for t in superself.all_tables for l in [len(getattr(self, t))] if l}
             def _generatorfactory(self, data, tableName):
                 return generatorfactory(data, tableName)
             def __init__(self, **init_tables):
@@ -824,9 +832,30 @@ def _try_make_foreign_links(self):
         self._prepends = {}
         self._parameters = {}
         self._infinity_io_flag = ["N/A"]
+        self._xlsx_trailing_empty_rows = ["prune"]
         self._none_as_infinity_bias_cache = {}
         self._isFrozen=True
 
+    @property
+    def xlsx_trailing_empty_rows(self):
+        """
+        see __doc__ for set_xlsx_trailing_empty_rows
+        """
+        return self._xlsx_trailing_empty_rows[0]
+    def set_xlsx_trailing_empty_rows(self, value):
+        """
+        Set the xlsx_trailing_empty_rows for the TicDatFactory. Choices are:
+        --> 'prune' : (the default) when reading an xlsx/xlsm file, look for trailing all None rows in each table, and
+                      prune them
+        --> 'ignore': retain such rows
+        With the move to openpyxl for xlsx/xlsm file reading, its more likely that Excel users accidentally creating
+        trailing all none rows.
+        :param value: either 'prune' or 'ignore'
+        :return:
+        """
+        verify(value in ["prune", "ignore"], f"bad value {value}")
+        self._xlsx_trailing_empty_rows[0] = value
+
     @property
     def infinity_io_flag(self):
         """
@@ -1351,7 +1380,8 @@ def set_ampl_data(self, tic_dat, ampl, table_to_set_name = None):
                                                 if t in table_to_set_name else []))
             except:
                 raise utils.TicDatError(t + " cannot be passed as an argument to AMPL.setData()")
-    def copy_to_pandas(self, tic_dat, table_restrictions = None, drop_pk_columns = None):
+    def copy_to_pandas(self, tic_dat, table_restrictions = None, drop_pk_columns = None,
+                       reset_index=False):
         """
         copies the tic_dat object into a new object populated with pandas.DataFrame objects
         performs a deep copy
@@ -1365,12 +1395,15 @@ def copy_to_pandas(self, tic_dat, table_restrictions = None, drop_pk_columns = N
                                 from the data frames after they have been incorporated
                                 into the index.
                                 If None, then pk fields will be dropped only for tables with data fields
+        :param reset_index: boolean. If true, then drop_pk_columns is ignored and the returned DataFrames have
+                                     a simple integer index with both primary key and data fields as columns.
 
         :return: a deep copy of the tic_dat argument into DataFrames
-                 If table_restrictions is falsey and drop_pk_columns is False, then the return object
-                 will be a valid pan_dat object. I.e.
-                    assert PanDatFactory(**self.schema()).good_pan_dat_object(rtn)
-                 works.
+                 To get a valid pan_object object, either set drop_pk_columns to False or set reset_index to True.
+                 I.e.
+                    copy_1 = tdf.copy_to_pandas(dat, drop_pk_columns=False)
+                    copy_2 = tdf.copy_to_pandas(dat, reset_index=True)
+                    assert all(PanDatFactory(**tdf.schema()).good_pan_dat_object(_) for _ in [copy_1, copy_2])
 
                 Note that None will be converted to nan in the returned object (as is the norm for pandas.DataFrame)
 
@@ -1408,7 +1441,8 @@ def __repr__(self):
                 df = DataFrame([ (list(k) if containerish(k) else [k]) + [v[_] for _ in dfs]
                               for k,v in _sorted(getattr(tic_dat, tname).items())],
                               columns =cols)
-                df.set_index(list(pks), inplace=True,
+                if not reset_index:
+                    df.set_index(list(pks), inplace=True,
                              drop= bool(dfs if drop_pk_columns == None else drop_pk_columns))
                 utils.Sloc.add_sloc(df)
             else :
diff --git a/ticdat/utils.py b/ticdat/utils.py
index afef3ee0..a1d8d241 100644
--- a/ticdat/utils.py
+++ b/ticdat/utils.py
@@ -120,7 +120,7 @@ def clone_a_anchillary_info_schema(schema, table_restrictions):
         elif k == "parameters":
             rtn[k] = v if k in table_restrictions else {}
         else:
-            assert k == "infinity_io_flag", f"{k} is unpexted part of schema"
+            assert k in {"infinity_io_flag", "xlsx_trailing_empty_rows"}, f"{k} is unexpected part of schema"
             rtn[k] = v
     return rtn
 
diff --git a/ticdat/xls.py b/ticdat/xls.py
index 45befaca..ee4e5888 100644
--- a/ticdat/xls.py
+++ b/ticdat/xls.py
@@ -21,19 +21,69 @@
     import xlsxwriter as xlsx
 except:
     xlsx=None
+try:
+    import openpyxl
+except:
+    openpyxl=None
 
-_can_unit_test = xlrd and xlwt and xlsx
+_the_openpyxl_ext = {".xlsx", ".xlsm"}
+_is_openpyxl_ext = lambda s : any(s.endswith(_) for _ in _the_openpyxl_ext)
+_can_unit_test = xlrd and xlwt and xlsx and openpyxl
 
 # https://github.com/jmcnamara/XlsxWriter/issues/150 ...
 # the xlsxwriter doesn't handle infinity as seamlessly as xls
 _longest_sheet = 30
 
+class _XlrdSheetWrapper(object): # main purpose of this routine is to enforce the use of
+    def __init__(self, sheet, datemode):   # a limited set of xlrd.sheet functions
+        self._sheet = sheet
+        self._datemode = datemode
+    @property
+    def nrows(self):
+        return self._sheet.nrows
+    def row_values(self, row_index):
+        return self._sheet.row_values(row_index)
+    def col_values(self, col_index):
+        return self._sheet.col_values(col_index)
+    def xldate_as_tuple_munge(self, x): # only needed for xlrd
+        rtn = utils.safe_apply(lambda: xlrd.xldate_as_tuple(x, self._datemode))()
+        if rtn is not None:
+            f = datetime.datetime
+            if utils.pd:
+                f = utils.pd.Timestamp
+            return f(year=rtn[0], month=rtn[1], day=rtn[2], hour=rtn[3], minute=rtn[4], second=rtn[5])
+
+class _OpenPyxlSheetWrapper(object):
+    '''
+    This file initially used xlrd for xls and xlsx files. This class allows an openpyxl.sheet to present as a
+    limited xlrd.sheet. Although this choice of abstractions is historical, the resulting code is at least
+    free from lots of "if/else" silliness
+    '''
+    def __init__(self, sheet, prune_trailing_empty_rows):
+        self._sheet = sheet
+        self._max_col = sheet.max_column # for performance reasons, need to capture the range on __init__
+        self._max_row = sheet.max_row
+        if prune_trailing_empty_rows:
+            i = self.nrows-1
+            while i >= 0 and all(x is None for x in self.row_values(i)):
+                i -= 1
+            self._max_row = i+1
+    @property
+    def nrows(self):
+        return self._sheet.max_row-self._sheet.min_row+1
+    def row_values(self, row_index): # openpyxl used 1 based indexing
+        return tuple(x.value for x in next(iter(self._sheet.iter_rows(min_row=row_index+1, max_row=row_index+1,
+                                                                      min_col=1, max_col=self._max_col))))
+    def col_values(self, col_index): # openpyxl used 1 based indexing
+        return tuple(x.value for x in next(iter(self._sheet.iter_cols(min_col=col_index+1, max_col=col_index+1,
+                                                                      min_row=1, max_row=self._max_row))))
+
 class XlsTicFactory(freezable_factory(object, "_isFrozen")) :
     """
     Primary class for reading/writing Excel files with TicDat objects.
-    Your system will need the xlrd package to read .xls and .xlsx files,
-    the xlwt package to write .xls files, and the xlsxwriter package to
-    write .xlsx files.
+    Your system will need the xlrd package to read .xls files, the openpyxl package
+    to read xlsx/xlsm/xltx/xltm files, the xlwt package to write .xls files, and the xlsxwriter
+    package to write .xlsx files.
     Don't create this object explicitly. A XlsTicDatFactory will
     automatically be associated with the xls attribute of the parent
     TicDatFactory.
@@ -86,7 +136,13 @@ def create_tic_dat(self, xls_file_path, row_offsets=None, headers_present = True
                      is to set must_be_int to true in data_types.
         """
         self._verify_differentiable_sheet_names()
-        verify(xlrd, "xlrd needs to be installed to use this subroutine")
+        verify(utils.safe_apply(os.path.isfile)(xls_file_path), f"{xls_file_path} not a file path")
+        verify(xls_file_path.endswith(".xls") or _is_openpyxl_ext(xls_file_path),
+               f"invalid file extension for {xls_file_path}")
+        if xls_file_path.endswith(".xls"):
+            verify(xlrd, "xlrd needs to be installed to use this subroutine")
+        else:
+            verify(openpyxl, "openpyxl needs to be installed to use this subroutine")
         tdf = self.tic_dat_factory
         verify(not(treat_inf_as_infinity and tdf.generator_tables),
                "treat_inf_as_infinity not implemented for generator tables")
@@ -122,17 +178,23 @@ def _get_sheets_and_fields(self, xls_file_path, all_tables, row_offsets, headers
         verify(utils.stringish(xls_file_path) and os.path.exists(xls_file_path),
                "xls_file_path argument %s is not a valid file path."%xls_file_path)
         try :
-            book = xlrd.open_workbook(xls_file_path)
+            book = xlrd.open_workbook(xls_file_path) if xls_file_path.endswith(".xls") else \
+                openpyxl.load_workbook(xls_file_path, data_only=True)
         except Exception as e:
             raise TicDatError("Unable to open %s as xls file : %s"%(xls_file_path, e))
+        sheet_name = lambda sheet: sheet.name if xls_file_path.endswith(".xls") else sheet.title
         sheets = defaultdict(list)
-        for table, sheet in product(all_tables, book.sheets()) :
-            if table.lower()[:_longest_sheet] == sheet.name.lower().replace(' ', '_')[:_longest_sheet]:
+        book_sheets = lambda: book.sheets() if xls_file_path.endswith(".xls") else book.worksheets
+        for table, sheet in product(all_tables, book_sheets()) :
+            if table.lower()[:_longest_sheet] == sheet_name(sheet).lower().replace(' ', '_')[:_longest_sheet]:
                 sheets[table].append(sheet)
         duplicated_sheets = tuple(_t for _t,_s in sheets.items() if len(_s) > 1)
-        verify(not duplicated_sheets, "The following sheet names were duplicated : " +
+        verify(not duplicated_sheets, "The following sheet names were duplicated s: " +
                ",".join(duplicated_sheets))
-        sheets = FrozenDict({k:v[0] for k,v in sheets.items() })
+        wrapped_sheet = lambda sheet: _XlrdSheetWrapper(sheet, book.datemode) if xls_file_path.endswith(".xls") else \
+                                      _OpenPyxlSheetWrapper(sheet, prune_trailing_empty_rows=
+                                        self.tic_dat_factory.xlsx_trailing_empty_rows == "prune")
+        sheets = FrozenDict({k: wrapped_sheet(v[0]) for k,v in sheets.items()})
         missing_tables = {t for t in all_tables if t not in sheets}
         if missing_tables and print_missing_tables:
             print ("The following table names could not be found in the %s file.\n%s\n"%
@@ -147,12 +209,12 @@ def _get_sheets_and_fields(self, xls_file_path, all_tables, row_offsets, headers
         verify(not any(_ for _ in dup_fields.values()),
                "The following field names were duplicated : \n" +
                "\n".join("%s : "%t + ",".join(bf) for t,bf in dup_fields.items() if bf))
-        return sheets, field_indicies, book.datemode
+        return sheets, field_indicies
     def _create_generator_obj(self, xlsFilePath, table, row_offset, headers_present, treat_inf_as_infinity):
         tdf = self.tic_dat_factory
         ho = 1 if headers_present else 0
         def tableObj() :
-            sheets, field_indicies, datemode = self._get_sheets_and_fields(xlsFilePath,
+            sheets, field_indicies = self._get_sheets_and_fields(xlsFilePath,
                                         (table,), {table:row_offset}, headers_present)
             if table in sheets :
                 sheet = sheets[table]
@@ -160,7 +222,7 @@ def tableObj() :
                                for field in tdf.data_fields[table])
                 for x in (sheet.row_values(i) for i in range(table_len)[row_offset+ho:]):
                     yield self._sub_tuple(table, tdf.data_fields[table],
-                                          field_indicies[table], treat_inf_as_infinity, datemode)(x)
+                                          field_indicies[table], treat_inf_as_infinity, sheet)(x)
         return tableObj
 
     def _create_tic_dat_dict(self, xls_file_path, row_offsets, headers_present, treat_inf_as_infinity):
@@ -172,7 +234,7 @@ def _create_tic_dat_dict(self, xls_file_path, row_offsets, headers_present, trea
         row_offsets = dict({t:0 for t in self.tic_dat_factory.all_tables}, **row_offsets)
         tdf = self.tic_dat_factory
         rtn = {}
-        sheets, field_indicies, dm = self._get_sheets_and_fields(xls_file_path,
+        sheets, field_indicies = self._get_sheets_and_fields(xls_file_path,
                                     set(tdf.all_tables).difference(tdf.generator_tables),
                                     row_offsets, headers_present, print_missing_tables=True)
         ho = 1 if headers_present else 0
@@ -183,14 +245,14 @@ def _create_tic_dat_dict(self, xls_file_path, row_offsets, headers_present, trea
             table_len = min(len(sheet.col_values(indicies[field]))
                             for field in (fields or indicies))
             if tdf.primary_key_fields.get(tbl, ()) :
-                tableObj = {self._sub_tuple(tbl, tdf.primary_key_fields[tbl], indicies, tiai, dm)(x):
-                            self._sub_tuple(tbl, tdf.data_fields.get(tbl, ()), indicies, tiai, dm)(x)
+                tableObj = {self._sub_tuple(tbl, tdf.primary_key_fields[tbl], indicies, tiai, sheet)(x):
+                            self._sub_tuple(tbl, tdf.data_fields.get(tbl, ()), indicies, tiai, sheet)(x)
                             for x in (sheet.row_values(i) for i in
                                         range(table_len)[row_offsets[tbl]+ho:])}
             elif tbl in tdf.generic_tables:
                 tableObj = None # will be read via PanDatFactory
             else :
-                tableObj = [self._sub_tuple(tbl, tdf.data_fields.get(tbl, ()), indicies, tiai, dm)(x)
+                tableObj = [self._sub_tuple(tbl, tdf.data_fields.get(tbl, ()), indicies, tiai, sheet)(x)
                             for x in (sheet.row_values(i) for i in
                                         range(table_len)[row_offsets[tbl]+ho:])]
             if tableObj is not None:
@@ -225,7 +287,10 @@ def find_duplicates(self, xls_file_path, row_offsets={}, headers_present = True)
                  Row counts smaller than 2 are pruned off, as they aren't duplicates
         """
         self._verify_differentiable_sheet_names()
-        verify(xlrd, "xlrd needs to be installed to use this subroutine")
+        if xls_file_path.endswith(".xls"):
+            verify(xlrd, "xlrd needs to be installed to use this subroutine")
+        else:
+            verify(openpyxl, "openpyxl needs to be installed to use this subroutine")
         verify(utils.dictish(row_offsets) and
                set(row_offsets).issubset(self.tic_dat_factory.all_tables) and
                all(utils.numericish(x) and (x>=0) for x in row_offsets.values()),
@@ -234,7 +299,7 @@ def find_duplicates(self, xls_file_path, row_offsets={}, headers_present = True)
         tdf = self.tic_dat_factory
         pk_tables = tuple(t for t,_ in tdf.primary_key_fields.items() if _)
         rtn = {t:defaultdict(int) for t in pk_tables}
-        sheets, fieldIndicies, dm = self._get_sheets_and_fields(xls_file_path, pk_tables,
+        sheets, fieldIndicies  = self._get_sheets_and_fields(xls_file_path, pk_tables,
                                         row_offsets, headers_present)
         ho = 1 if headers_present else 0
         for table, sheet in sheets.items() :
@@ -243,7 +308,7 @@ def find_duplicates(self, xls_file_path, row_offsets={}, headers_present = True)
             table_len = min(len(sheet.col_values(indicies[field])) for field in fields)
             for x in (sheet.row_values(i) for i in range(table_len)[row_offsets[table]+ho:]) :
                 rtn[table][self._sub_tuple(table, tdf.primary_key_fields[table],
-                                           indicies, treat_inf_as_infinity=True, datemode=dm)(x)] += 1
+                                           indicies, treat_inf_as_infinity=True, sheet=sheet)(x)] += 1
         for t in list(rtn.keys()):
             rtn[t] = {k:v for k,v in rtn[t].items() if v > 1}
             if not rtn[t]:
@@ -257,7 +322,7 @@ def _get_dv_dt(self, table, field):
                 self.tic_dat_factory.data_types.get(table, {}).get(field)
             )
         return self._dv_dt[table, field]
-    def _sub_tuple(self, table, fields, field_indicies, treat_inf_as_infinity, datemode) :
+    def _sub_tuple(self, table, fields, field_indicies, treat_inf_as_infinity, sheet) :
         assert set(fields).issubset(field_indicies)
         if self.tic_dat_factory.infinity_io_flag != "N/A" or \
             (table == "parameters" and self.tic_dat_factory.parameters):
@@ -275,13 +340,8 @@ def _read_cell(x, field):
                 try_rtn = self.tic_dat_factory._general_read_cell(table, field, None) # None as infinity flagging
                 if utils.numericish(try_rtn):
                     return try_rtn
-            if utils.numericish(rtn) and dt and dt.datetime:
-                rtn = utils.safe_apply(lambda : xlrd.xldate_as_tuple(rtn, datemode))()
-                if rtn is not None:
-                    f = datetime.datetime
-                    if utils.pd:
-                        f = utils.pd.Timestamp
-                    return f(year=rtn[0], month=rtn[1], day=rtn[2], hour=rtn[3], minute=rtn[4], second=rtn[5])
+            if utils.numericish(rtn) and dt and dt.datetime and hasattr(sheet, "xldate_as_tuple_munge"):
+                rtn = sheet.xldate_as_tuple_munge(rtn)
             return self.tic_dat_factory._general_read_cell(table, field, rtn)
         def rtn(x) :
             if len(fields) == 1 :
@@ -323,7 +383,7 @@ def write_file(self, tic_dat, file_path, allow_overwrite = False, case_space_she
                           Needs to end in either ".xls" or ".xlsx"
                           The latter is capable of writing out larger tables,
                           but the former handles infinity seamlessly.
-                          If ".xlsx", then be advised that +/- float("inf") will be replaced
+                          If ".xlsx" then be advised that +/- float("inf") will be replaced
                           with "inf"/"-inf", unless infinity_io_flag is being applied.
 
         :param allow_overwrite: boolean - are we allowed to overwrite an


Test patch:
diff --git a/ticdat/testing/test_pgtd.py b/ticdat/testing/test_pgtd.py
index b239268a..ad088b11 100644
--- a/ticdat/testing/test_pgtd.py
+++ b/ticdat/testing/test_pgtd.py
@@ -309,11 +309,11 @@ def test_big_diet(self):
             big_dat.categories[str(k)] = [0,100]
         pgtf.write_schema(self.engine, test_schema)
         pgtf.write_data(big_dat, self.engine, test_schema, dsn=self.postgresql.dsn())
-        print(f"****{time.time()-now}****")
+        print(f"\n**** tdf writing {big_dat._len_dict()} : {time.time()-now}****\n")
         now = time.time()
         self.assertFalse(pgtf.find_duplicates(self.engine, test_schema))
         pg_tic_dat = pgtf.create_tic_dat(self.engine, test_schema)
-        print(f"****{time.time()-now}****")
+        print(f"**** tdf reading {big_dat._len_dict()} : {time.time()-now}****")
         self.assertTrue(diet_schema._same_data(big_dat, pg_tic_dat))
 
         med_dat = diet_schema.copy_tic_dat(diet_dat)
@@ -335,11 +335,11 @@ def test_big_diet_two(self):
             big_dat.categories[str(k)] = [0,100]
         pgtf.write_schema(self.engine, test_schema)
         pgtf.write_data(big_dat, self.engine, test_schema, dsn=self.postgresql.url())
-        print(f"**!!*{time.time()-now}*!!   *")
+        print(f"\ntdf writing {big_dat._len_dict()} {time.time()-now}*!!*\n")
         now = time.time()
         self.assertFalse(pgtf.find_duplicates(self.engine, test_schema))
         pg_tic_dat = pgtf.create_tic_dat(self.engine, test_schema)
-        print(f"****{time.time()-now}****")
+        print(f"\ntdf reading and dup {big_dat._len_dict()} {time.time()-now}****\n")
         self.assertTrue(diet_schema._same_data(big_dat, pg_tic_dat))
 
         med_dat = diet_schema.copy_tic_dat(diet_dat)
@@ -492,10 +492,10 @@ def test_big_diet_pd(self):
         now = time.time()
         pgpf.write_schema(self.engine, schema)
         pgpf.write_data(pan_dat, self.engine, schema)
-        print(f"**&&*{time.time()-now}**&&**")
+        print(f"\npdf writing {big_dat._len_dict()} : {time.time()-now}**&&**\n")
         now = time.time()
         pg_pan_dat = pgpf.create_pan_dat(self.engine, schema)
-        print(f"*&&*{time.time()-now}**&&**")
+        print(f"\npdf reading {big_dat._len_dict()} : {time.time()-now}**&&**\n")
         self.assertTrue(pdf._same_data(pan_dat, pg_pan_dat))
 
     def test_extra_fields_pd(self):
diff --git a/ticdat/testing/test_slow_bernardo.py b/ticdat/testing/test_slow_bernardo.py
index b1fc0b25..478bed1b 100644
--- a/ticdat/testing/test_slow_bernardo.py
+++ b/ticdat/testing/test_slow_bernardo.py
@@ -13,13 +13,14 @@
     import kehaar
 except:
     kehaar = None
-from ticdat.testing.ticdattestutils import flagged_as_run_alone, fail_to_debugger
+from ticdat.testing.ticdattestutils import flagged_as_run_alone, fail_to_debugger, makeCleanDir
 import ticdat.utils as utils
 import unittest
 import os
 import inspect
 from functools import wraps
 import time
+import shutil
 from ticdat import PanDatFactory
 
 def _codeFile() :
@@ -63,11 +64,14 @@ def setUp(self):
             for test_schema in test_schemas:
                 if utils.safe_apply(lambda: test_schema in sa.inspect(self.engine).get_schema_names())():
                     self.engine.execute(sa.schema.DropSchema(test_schema, cascade=True))
+        makeCleanDir(_scratchDir)
 
     def tearDown(self):
         if self.postgresql:
             self.engine.dispose()
             self.postgresql.stop()
+        shutil.rmtree(_scratchDir)
+
 
     def test_tdf(self):
         tdf = kehaar.input_schema
@@ -77,6 +81,12 @@ def test_tdf(self):
         _timeit(tdf.pgsql.write_data, 90)(dat, self.engine, test_schemas[0], dsn=self.postgresql.dsn())
         _timeit(tdf.pgsql.create_tic_dat, 50)(self.engine, test_schemas[0])
 
+    def test_tdf_2(self):
+        tdf = kehaar.input_schema
+        dat = _timeit(tdf.csv.create_tic_dat, 90)(os.path.join(_codeDir(), "bernardo_slowby"))
+        _timeit(tdf.xls.write_file, 150)(dat, os.path.join(_scratchDir, "bernslow.xlsx"))
+        _timeit(tdf.xls.create_tic_dat, 150)(os.path.join(_scratchDir, "bernslow.xlsx"))
+
     def test_pdf(self):
         pdf = PanDatFactory.create_from_full_schema(kehaar.input_schema.schema(include_ancillary_info=True))
         dat = _timeit(pdf.csv.create_pan_dat, 90)(os.path.join(_codeDir(), "bernardo_slowby"))
@@ -97,6 +107,7 @@ def test_pdf_2(self):
         _timeit(pdf.pgsql.create_pan_dat, 5)(self.engine, test_schemas[2])
 
 test_schemas = [f"test_schema_{_}" for _ in range(5)]
+_scratchDir = TestSlowBernardo.__name__ + "_scratch"
 
 # Run the tests.
 if __name__ == "__main__":
diff --git a/ticdat/testing/testpandat_io.py b/ticdat/testing/testpandat_io.py
index 8dd1bf43..d92df0e1 100644
--- a/ticdat/testing/testpandat_io.py
+++ b/ticdat/testing/testpandat_io.py
@@ -76,7 +76,7 @@ def getTable(self, t, includeActive=False):
             return rtn
     return RtnObject()
 
-#uncomment decorator to drop into debugger for assertTrue, assertFalse failures
+#uncomment decorator to drop into debugger for assertTrue, assertFalse failures (also hides a bunch of warnings)
 #@fail_to_debugger
 class TestIO(unittest.TestCase):
     can_run = False
@@ -275,7 +275,6 @@ def make_dat(l):
             dat_inf = make_dat([[float("-inf"), 100], [200, 109], [0, 300], [300, -float("inf")], [400, 0]])
             self.assertTrue(pdf._same_data(dat_inf, dat_1))
 
-
     def testXlsSpacey(self):
         if not self.can_run:
             return
diff --git a/ticdat/testing/testpandat_utils.py b/ticdat/testing/testpandat_utils.py
index 24556f05..07546061 100644
--- a/ticdat/testing/testpandat_utils.py
+++ b/ticdat/testing/testpandat_utils.py
@@ -1,5 +1,5 @@
 import unittest
-from ticdat.pandatfactory import PanDatFactory
+from ticdat.pandatfactory import PanDatFactory, remove_trailing_all_nan
 from ticdat.utils import DataFrame, numericish, ForeignKey, ForeignKeyMapping
 import ticdat.utils as utils
 from ticdat.testing.ticdattestutils import fail_to_debugger, flagged_as_run_alone, netflowPandasData
@@ -747,6 +747,34 @@ def test_fk_max_failures(self):
         errs = pdf.find_foreign_key_failures(pan_dat, max_failures=9)
         self.assertTrue(len(errs) == 1 and all(len(_) == 9 for _ in errs.values()))
 
+    def test_trailing_all_nan(self):
+        df = utils.pd.DataFrame({"a": [1, 2, None, None], "b": [10, 20, None, None]})
+        df2 = remove_trailing_all_nan(df)
+        df3 = remove_trailing_all_nan(df2)
+        self.assertTrue([tuple(x) for x in df2.itertuples(index=False)] ==
+                        [tuple(x) for x in df3.itertuples(index=False)] == [(1.0, 10.0), (2.0, 20.0)])
+        df = utils.pd.DataFrame({"a": [1, 2] * 10 + [None, None] * 3, "b": [10, 20] * 10 + [None, float("nan")] * 3})
+        df2 = remove_trailing_all_nan(df)
+        df3 = remove_trailing_all_nan(df2)
+        self.assertTrue([tuple(x) for x in df2.itertuples(index=False)] ==
+                        [tuple(x) for x in df3.itertuples(index=False)] == [(1.0, 10.0), (2.0, 20.0)]*10)
+        df = utils.pd.DataFrame({"a": [1, 2] * 10 + [None] + [2,1] + [None, None] * 3,
+                                 "b": [10, 20] * 10 + [None] + [20,11] + [None, None] * 3})
+        df2 = remove_trailing_all_nan(df)
+        df3 = remove_trailing_all_nan(df2)
+        _tuple = lambda x: tuple(None if utils.pd.isnull(_) else _ for _ in x)
+        self.assertTrue([_tuple(x) for x in df2.itertuples(index=False)] ==
+                        [_tuple(x) for x in df3.itertuples(index=False)] == [(1.0, 10.0), (2.0, 20.0)]*10 +
+                         [(None, None)] + [(2, 20), (1, 11)])
+        df = utils.pd.DataFrame({"a": [1, 2]*1000000 + [None, None]*30, "b": [10, 20]*1000000 +
+                                                                             [float("nan"), None]*30})
+        df2 = remove_trailing_all_nan(df)
+        df3 = remove_trailing_all_nan(df2)
+        self.assertTrue([tuple(x) for x in df2.itertuples(index=False)] ==
+                        [tuple(x) for x in df3.itertuples(index=False)] == [(1.0, 10.0), (2.0, 20.0)]*1000000)
+
+        self.assertTrue(len(remove_trailing_all_nan(utils.pd.DataFrame({"a": [None, float("nan"), None],
+                                                                        "b":[None]*3}))) == 0)
 # Run the tests.
 if __name__ == "__main__":
     if not DataFrame :
diff --git a/ticdat/testing/testutils.py b/ticdat/testing/testutils.py
index a09d1c8d..b3e5ff66 100644
--- a/ticdat/testing/testutils.py
+++ b/ticdat/testing/testutils.py
@@ -1146,6 +1146,7 @@ def testTwentyOne(self):
             new_sch = tdf.schema(include_ancillary_info=True)
             self.assertFalse(new_sch.pop("parameters"))
             self.assertTrue(new_sch.pop("infinity_io_flag") == "N/A")
+            self.assertTrue(new_sch.pop("xlsx_trailing_empty_rows") == "prune")
             new_sch = json.loads(json.dumps(new_sch))
             new_sch["foreign_keys"] = sorted(new_sch["foreign_keys"])
             self.assertTrue(new_sch == simple_sch)
@@ -1338,7 +1339,7 @@ def testTwentyEight(self):
                                                 'Max Nutrition': [True, True, True, 0, inf, False, [], False, False]},
                                  'nutrition_quantities': {'Quantity': [True, True, False, 0, inf, False, [], False,
                                                                        False]}},
-                 'parameters': {}, 'infinity_io_flag': 'N/A'})
+                 'parameters': {}, 'infinity_io_flag': 'N/A', "xlsx_trailing_empty_rows": "prune"})
 
     def testTwentyNine(self):
         data_path = os.path.join(_scratchDir, "custom_module_three")
@@ -1598,6 +1599,23 @@ def test_fk_max_failures(self):
         errs = tdf.find_foreign_key_failures(dat, max_failures=9)
         self.assertTrue(len(errs) == 1 and all(len(_.native_pks) == 9 for _ in errs.values()))
 
+    def test_copy_to_pandas(self):
+        # needs pandas installed
+        tdf = TicDatFactory(**dietSchema())
+        dat = tdf.copy_tic_dat(dietData())
+        copy_1 = tdf.copy_to_pandas(dat, drop_pk_columns=False)
+        copy_2 = tdf.copy_to_pandas(dat, reset_index=True)
+        self.assertTrue(all(PanDatFactory(**tdf.schema()).good_pan_dat_object(_) for _ in [copy_1, copy_2]))
+        self.assertTrue(PanDatFactory(**tdf.schema())._same_data(copy_1, copy_2))
+        for t in ["categories", "foods"]:
+            df = getattr(copy_1, t)
+            self.assertTrue(all(isinstance(_, str) for _ in list(df.index)))
+        self.assertTrue(all(isinstance(_, str) for row in list(copy_1.nutritionQuantities.index) for _ in row))
+        for t in tdf.all_tables:
+            df = getattr(copy_2, t)
+            self.assertTrue(list(df.index) == list(range(len(df))))
+
+
 _scratchDir = TestUtils.__name__ + "_scratch"
 
 # Run the tests.
diff --git a/ticdat/testing/testxls.py b/ticdat/testing/testxls.py
index ce67af0e..ad82da9d 100644
--- a/ticdat/testing/testxls.py
+++ b/ticdat/testing/testxls.py
@@ -5,7 +5,7 @@
 from ticdat.testing.ticdattestutils import sillyMeData, sillyMeSchema, makeCleanDir, fail_to_debugger
 from ticdat.testing.ticdattestutils import spacesData, spacesSchema, memo, flagged_as_run_alone
 from ticdat.testing.ticdattestutils import makeCleanPath, sillyMeDataTwoTables
-from ticdat.xls import _can_unit_test
+import ticdat.xls as ticdat_xlsx
 import shutil
 import unittest
 import datetime
@@ -214,6 +214,7 @@ def testSilly(self):
         self.assertTrue(hasattr(ticDat6, "d") and utils.dictish(ticDat6.d))
 
         def writeData(data, write_header = "same"):
+            assert filePath.endswith(".xls")
             assert not write_header or write_header in ("lower", "same", "duped")
             import xlwt
             book = xlwt.Workbook()
@@ -229,6 +230,23 @@ def writeData(data, write_header = "same"):
             if os.path.exists(filePath):
                 os.remove(filePath)
             book.save(filePath)
+            if write_header in ["lower", "same"]: # will use pandas to generate the xlsx file version
+                file_path_x = filePath + "x"
+                if os.path.exists(file_path_x):
+                    os.remove(file_path_x)
+                writer = utils.pd.ExcelWriter(file_path_x)
+                for t, (pks, dfs) in tdf.schema().items():
+                    fields = pks+dfs
+                    if write_header == "lower":
+                        fields = [_.lower() for _ in fields]
+                    d = {f:[] for f in fields}
+                    for row in data:
+                        for f, c in zip(fields, row):
+                            d[f].append(c)
+                    utils.pd.DataFrame(d).to_excel(writer, t, index=False)
+                writer.save()
+
+
 
         writeData([(1, 2, 3, 4), (1, 20, 30, 40), (10, 20, 30, 40)], write_header="duped")
         self.assertTrue(self.firesException(lambda : tdf.xls.create_tic_dat(filePath, freeze_it=True)))
@@ -237,15 +255,17 @@ def writeData(data, write_header = "same"):
         ticDatMan = tdf.xls.create_tic_dat(filePath, freeze_it=True)
         self.assertTrue(len(ticDatMan.a) == 2 and len(ticDatMan.b) == 3)
         self.assertTrue(ticDatMan.b[1, 20, 30]["bData"] == 40)
-        rowCount = tdf.xls.find_duplicates(filePath)
-        self.assertTrue(set(rowCount) == {'a'} and set(rowCount["a"]) == {1} and rowCount["a"][1]==2)
+        for f in [filePath, filePath+"x"]:
+            rowCount = tdf.xls.find_duplicates(f)
+            self.assertTrue(set(rowCount) == {'a'} and set(rowCount["a"]) == {1} and rowCount["a"][1]==2)
 
         writeData([(1, 2, 3, 4), (1, 20, 30, 40), (10, 20, 30, 40)], write_header="lower")
         ticDatMan = tdf.xls.create_tic_dat(filePath, freeze_it=True)
         self.assertTrue(len(ticDatMan.a) == 2 and len(ticDatMan.b) == 3)
         self.assertTrue(ticDatMan.b[1, 20, 30]["bData"] == 40)
-        rowCount = tdf.xls.find_duplicates(filePath)
-        self.assertTrue(set(rowCount) == {'a'} and set(rowCount["a"]) == {1} and rowCount["a"][1]==2)
+        for f in [filePath, filePath+"x"]:
+            rowCount = tdf.xls.find_duplicates(f)
+            self.assertTrue(set(rowCount) == {'a'} and set(rowCount["a"]) == {1} and rowCount["a"][1]==2)
 
 
         writeData([(1, 2, 3, 4), (1, 20, 30, 40), (10, 20, 30, 40)], write_header=False)
@@ -270,19 +290,20 @@ def writeData(data, write_header = "same"):
         self.assertTrue(tdf._same_data(ticDat, ticDatNone))
         self.assertTrue(ticDatNone.a["theboger"]["aData2"] == None)
 
-        # checking the same thing with .xlsx
+        # checking the same thing with .xlsx - using openpyxl, None is indeed recovered even without tdfwa munging!
         tdf.xls.write_file(ticDat, filePath+"x", allow_overwrite=True)
         ticDatNone = tdf.xls.create_tic_dat(filePath+"x", freeze_it=True)
-        self.assertFalse(tdf._same_data(ticDat, ticDatNone))
-        self.assertTrue(ticDatNone.a["theboger"]["aData2"] == "")
+        self.assertTrue(tdf._same_data(ticDat, ticDatNone))
+        self.assertTrue(ticDatNone.a["theboger"]["aData2"] == None)
         ticDatNone = tdfwa.xls.create_tic_dat(filePath+"x", freeze_it=True)
         self.assertTrue(tdf._same_data(ticDat, ticDatNone))
         self.assertTrue(ticDatNone.a["theboger"]["aData2"] == None)
 
         writeData([(1, 2, 3, 4), (1, 20, 30, 40), (10, 20, 30, 40), (1,20,30,12)])
-        rowCount = tdf.xls.find_duplicates(filePath)
-        self.assertTrue(set(rowCount) == {'a', 'b'} and set(rowCount["a"]) == {1} and rowCount["a"][1]==3)
-        self.assertTrue(set(rowCount["b"]) == {(1,20,30)} and rowCount["b"][1,20,30]==2)
+        for f in [filePath, filePath+"x"]:
+            rowCount = tdf.xls.find_duplicates(f)
+            self.assertTrue(set(rowCount) == {'a', 'b'} and set(rowCount["a"]) == {1} and rowCount["a"][1]==3)
+            self.assertTrue(set(rowCount["b"]) == {(1,20,30)} and rowCount["b"][1,20,30]==2)
 
     def testSpacey(self):
         if not self.can_run:
@@ -568,15 +589,21 @@ def testDateTime(self):
         self.assertTrue(all(isinstance(_, datetime.datetime) or _ is None for v in dat_1.table_with_stuffs.values()
                             for _ in v.values()))
 
-    def testDateTimeTwo(self):
+    def testDateTimeTwo(self): # this is good test for datetime stuff
         file = os.path.join(_scratchDir, "datetime_pd.xls")
         df = utils.pd.DataFrame({"a":list(map(utils.pd.Timestamp,
             ["June 13 1960 4:30PM", "Dec 11 1970 1AM", "Sept 11 2001 9:30AM"]))})
-        df.to_excel(file, "Cool Runnings")
         tdf = TicDatFactory(cool_runnings = [["a"],[]])
         tdf.set_data_type("cool_runnings", "a", datetime=True)
+        df.to_excel(file, "Cool Runnings")
         dat = tdf.xls.create_tic_dat(file)
         self.assertTrue(set(dat.cool_runnings) == set(df["a"]))
+        file = file + "x"
+        df.to_excel(file, "Cool Runnings")
+        dat = tdf.xls.create_tic_dat(file)
+        for x, y in zip(sorted(dat.cool_runnings), sorted(set(df["a"]))):
+            delta = x-y
+            self.assertTrue(abs(delta.total_seconds()) < 1e-4)
 
     def testIssue45(self):
         tdf = TicDatFactory(data=[["a"], ["b"]])
@@ -591,6 +618,49 @@ def testIssue45(self):
         # my hands are sort of tied here, xlrd has a disturbing tendency to read data as floats when it reads numbers
         # that said, not sure I really need to do more, since these two tests pass.
 
+    def testColumnsWithoutData(self):
+        tdf = TicDatFactory(data=[["a"], ["b"]])
+        for x in ["", "x"]:
+            file = os.path.join(_scratchDir, "no_data.xls" + x)
+            tdf.xls.write_file(tdf.TicDat(), file)
+            dat = tdf.xls.create_tic_dat(file)
+            self.assertFalse(dat._len_dict())
+
+    def testEmptyWorkbooks(self):
+        file = os.path.join(_scratchDir, "empty_wb.xlsx")
+        book = ticdat_xlsx.xlsx.Workbook(file)
+        book.add_worksheet("data")
+        book.close()
+        tdf = TicDatFactory(data=[["a"], ["b"]])
+        ex = []
+        try:
+            tdf.xls.create_tic_dat(file)
+        except utils.TicDatError as e:
+            ex.append(e)
+        self.assertTrue(ex)
+        self.assertTrue("The following field names could not be found :" in str(ex[0]))
+
+    def testEndingAllNones(self): # unfortunately, not really programmatically testing set_xlsx_trailing_empty_rows
+        # due to underlying library weirdness. I did some manually testing originally. Hopefully not going to bit
+        # on this one, its not mission critical functionality anyway.
+        file_path = os.path.join(_scratchDir, "ending_all_nones.xlsx")
+        tdf = TicDatFactory(data=[[],["a", "b"]])
+        dat_write = tdf.TicDat(data=[["x", "v"], ["y", "k"], ["xx", None], [None, None], [None, None]])
+        tdf.xls.write_file(dat_write, file_path)
+        dat = tdf.TicDat(data=[["x", "v"], ["y", "k"], ["xx", None]])
+        dat_2 = tdf.xls.create_tic_dat(file_path)
+        self.assertTrue(tdf._same_data(dat, dat_2))
+        tdf.set_xlsx_trailing_empty_rows("ignore")
+        dat_2 = tdf.xls.create_tic_dat(file_path)
+        # self.assertTrue(tdf._same_data(dat_2, dat_write)) # this is how it ought to work!
+        self.assertTrue(tdf._same_data(dat_2, dat))  # this is whats happening!
+        # to be more specific, here is the inner deal
+        book = ticdat_xlsx.openpyxl.load_workbook(file_path, data_only=True)
+        sheet = book["data"]
+        self.assertTrue(sheet.max_row == 4) # 1 based indexing, the column row, three data rows (BUT NOT 5 DATA ROWS)
+
+
+
 _scratchDir = TestXls.__name__ + "_scratch"
 
 # Run the tests.
@@ -598,7 +668,7 @@ def testIssue45(self):
     td = TicDatFactory()
     if not utils.DataFrame :
         print("!!!!!!!!!FAILING XLS UNIT TESTS DUE TO FAILURE TO LOAD PANDAS LIBRARIES!!!!!!!!")
-    elif not _can_unit_test :
+    elif not ticdat_xlsx._can_unit_test :
         print("!!!!!!!!!FAILING XLS UNIT TESTS DUE TO FAILURE TO LOAD XLS LIBRARIES!!!!!!!!")
     else:
         TestXls.can_run = True
diff --git a/ticdat/testing/ticdattestutils.py b/ticdat/testing/ticdattestutils.py
index 2bccd9b3..c9a4b039 100644
--- a/ticdat/testing/ticdattestutils.py
+++ b/ticdat/testing/ticdattestutils.py
@@ -289,10 +289,7 @@ class _(object) :
     return dat
 
 def copy_to_pandas_with_reset(tdf, dat):
-    rtn = tdf.copy_to_pandas(dat, drop_pk_columns=False)
-    for t in tdf.all_tables:
-        getattr(rtn, t).reset_index(drop=True, inplace=True)
-    return rtn
+    return tdf.copy_to_pandas(dat, reset_index=True)
 
 def netflowPandasData():
     tdf = TicDatFactory(**netflowSchema())