<jupyter_start><jupyter_text>Using ticdat to wrangle data from the diet problem This example is based on the classic diet problem. We will demonstrate how the `ticdat` data library can be used for wrangling problematic input data.Please note that this notebook references a [diet.py](https://github.com/ticdat/ticdat/blob/master/examples/gurobipy/diet/diet.py) demonstration file. This file in turn is based on Gurobis own [dietmodel.py](http://www.gurobi.com/documentation/6.0/examples/dietmodel_py.html) example. The latter uses the Python `dict` to store the input and output data, whereas the former uses a `TicDat` object.First import a `TicDatFactory`. This will allow us to generate a schema for the diet problem.<jupyter_code>from ticdat import TicDatFactory<jupyter_output><empty_output><jupyter_text>Next, we create a `TicDatFactory` object that defines the tables, primary key fields, and data fields for the diet problem.<jupyter_code>dataFactory = TicDatFactory (
     categories = [["name"],["minNutrition", "maxNutrition"]],
     foods  = [["name"],["cost"]],
     nutritionQuantities = [["food", "category"], ["qty"]])<jupyter_output><empty_output><jupyter_text>Add the foreign key relationships - the `nutritionQuantities` table is a child to `foods` and `categories`.<jupyter_code>dataFactory.add_foreign_key("nutritionQuantities", "foods", ["food", "name"])
dataFactory.add_foreign_key("nutritionQuantities", "categories", ["category", "name"])<jupyter_output><empty_output><jupyter_text>The default default of zero is fine everywhere, except for `maxNutrition`<jupyter_code>dataFactory.set_default_value("categories", "maxNutrition", float("inf"))<jupyter_output><empty_output><jupyter_text>By default, none of the data fields are typed. We want to add types to our data fields, to facilitate data wrangling. That is to say, to recognize data entries of the wrong type. (`TicDat` objects will accept data of any type regardless of field type specifications).  First, we simply call `set_data_type` for every data field with the default arguments. This specifies the most common data type - a non-negative, non-infinite number that has no integrality restrictions.<jupyter_code>for table, fields in dataFactory.data_fields.items():
    for field in fields:
        dataFactory.set_data_type(table, field)<jupyter_output><empty_output><jupyter_text>The `maxNutrition` field for the `categories` table is different. Here, we want to accept positive infinity as valid. (The Atkins diet having taught us we can basically eat as much protein and fat as we want, so long as we don't eat too many calories).<jupyter_code>dataFactory.set_data_type("categories", "maxNutrition", max=float("inf"), 
                                                        inclusive_max=True)<jupyter_output><empty_output><jupyter_text>We've specified our tables, fields, foreign keys, and data types. We're ready to wrangle!  The `moreFood.xls` file has `categories`, `foods` and `nutritionQuantities` sheets that we think can populate a sound diet model. First, we inspect this file to insure there are no duplicated records.<jupyter_code>dups = dataFactory.xls.find_duplicates("moreFood.xls")
dups<jupyter_output><empty_output><jupyter_text>So we found two duplicate rows. The amount of protein in pizza and the amount of protein in salad has been specified twice. There is, in general, no magic solution for dealing with this sort of problem. (They call it "data wrangling" because you should expect to exert some effort). In this instance, it's easy enough to verify that the protein quantity is the same for both duplicates, and thus there is no problem in simply ignoring the duplication. Next, we read the all three tables into a `TicDat` data object.<jupyter_code>dat = dataFactory.xls.create_tic_dat("moreFood.xls")<jupyter_output><empty_output><jupyter_text>Some basic Python lets us get a row count for each table. Since `4*37==148`, it looks like the `nutritionQuantities` table is fully populated, but there is an easy `ticdat` way to be sure.<jupyter_code>{t:len(getattr(dat, t)) for t in dataFactory.all_tables}<jupyter_output><empty_output><jupyter_text>To be fully confident that `nutritionQuantities` is populated correctly, we will check for foreign key reference failures. Typically, databases will simply reject data records that fail to match the required foreign keys. `ticdat` is a relational data library, not a database, so it accepts such records and then helps you find any failed matches.<jupyter_code>foreign_key_failed = dataFactory.find_foreign_key_failures(dat)
foreign_key_failed<jupyter_output><empty_output><jupyter_text>So there are foreign key failures. Good thing we checked! The `foreign_key_failed` dictionary doesn't render very nicely here in iPython. That's because its using a `namedtuple` both for keys and values. We can make it prettier by filtering out the self documentation.<jupyter_code>{tuple(k[:2]):v.native_values for k,v in foreign_key_failed.items()}
{tuple(k[:2]):v.native_pks for k,v in foreign_key_failed.items()}<jupyter_output><empty_output><jupyter_text>I think I see what's happening here. When the `nutritionQuantities` table was trying to match into the `foods` table the "Baked Cods" string wasn't matching with any of the food names. "Baked Cods" is the only string that failed to match, but it failed 4 times (once for every nutrition category). Lets poke around a bit to see if we can figure out what "Baked Cods" is supposed to match.<jupyter_code>[k for k in dat.foods if "cod" in k.lower()]<jupyter_output><empty_output><jupyter_text>Aha! I see what's going on. "Baked Cods" is just misspelled everywhere. It's supposed to be "Baked Cod". (Did I mention already that it's a good thing we checked? Without fixing it, the MIP I run below will throw an exception, or, if using an unfrozen `TicDat` object, simply assign "Baked Cod" the default value of zero for all nutrition categories). Luckily, this is just the sort of wrangling Python is good at. Lets replace `dat` with a copy that fixes this misspelling.<jupyter_code>dat = dataFactory.TicDat(categories = dat.categories, 
                         foods = dat.foods,
                         nutritionQuantities = { 
                                ("Baked Cod" if f == "Baked Cods" else f,c):v
                                for (f,c),v in dat.nutritionQuantities.items()})<jupyter_output><empty_output><jupyter_text>Lets just double check that this fixes up all the foreign key problems. "Only The Paranoid Survive", amirite?<jupyter_code>assert not dataFactory.find_foreign_key_failures(dat)<jupyter_output><empty_output><jupyter_text>Might as well double check the row counts.<jupyter_code>{t:len(getattr(dat, t)) for t in dataFactory.all_tables}<jupyter_output><empty_output><jupyter_text>Duplicates, foreign keys, what's left to check? Oh, that's right, the actual data.<jupyter_code>data_failed = dataFactory.find_data_type_failures(dat)
data_failed<jupyter_output><empty_output><jupyter_text>So there are data field problems as well. Similar to before, the `data_failed` dictionary doesn't render very nicely in iPython because of the (self documenting but cosmetically irritating) use of `namedtuple`. Easy enough to pretty it up.<jupyter_code>{tuple(k):v.bad_values for k,v in data_failed.items()}
{tuple(k):len(v.pks) for k,v in data_failed.items()}<jupyter_output><empty_output><jupyter_text>There are two types of data field problems. The first is the `'GRB.INFINITY'` string in the `maxNutrition` field of the `categories` table. (This isn't a weird `ticdat.xls` bug. `ticdat` can recognize Excel INFINITY. The QA engineer who built the `moreFood.xls` file put a string based on a `gurobipy` constant in there hoping to trip me up.) The second data problem is the blank string in the `qty` field of the `nutritionQuantities` table. Both of these problems are both showing up for only one record apiece, so I feel pretty confident they are just typos of some sort. The sensible thing to do here is replace the `'GRB.INFINITY'` string with positive infinity, and also to replace the blank string with the number zero. That is equivalent to replacing bad data values with the appropriate default values. (Remember, the default default is zero for all data fields, and we overrode this for the `maxNutrition` field earlier). Lucky for us, there is a `ticdat` function just for this purpose.<jupyter_code>dataFactory.replace_data_type_failures(dat)
assert not dataFactory.find_data_type_failures(dat)<jupyter_output><empty_output><jupyter_text>Might as well inspect the replacement values populated by `find_data_type_failures`.<jupyter_code>{dat.categories[f]['maxNutrition'] for f in data_failed['categories', 'maxNutrition'].pks}
{dat.nutritionQuantities[f,c]['qty'] for f,c in 
                                     data_failed['nutritionQuantities', 'qty'].pks}<jupyter_output><empty_output><jupyter_text>Now that the data is all cleaned up, we can solve it with a MIP. We happen to have the `dietmodel.py` file handy right here. As a well modularized solve engine, this file publishes both its input and output schema along with a `solve` function. However, we know our `dat` object is consistent with the `dietmodel` input schema, so we can go straight to `solve`.<jupyter_code>from diet import solve<jupyter_output><empty_output><jupyter_text>Now, I'm going to be a little fancy here, and freeze my `dat` object before passing it to `solve`. I don't want `solve` to even try to edit the input data. All `solve` should do is read from `dat`. After I freeze `dat`, any attempt to write to `dat` (deliberate or accidental) will throw an exception. If `solve` runs without an exception throw I'll know there weren't any such attempts to edit the input data.<jupyter_code>soln = solve(dataFactory.freeze_me(dat))<jupyter_output>Optimize a model with 4 rows, 41 columns and 151 nonzeros
Presolve removed 0 rows and 3 columns
Presolve time: 0.00s
Presolved: 4 rows, 38 columns, 148 nonzeros

Iteration    Objective       Primal Inf.    Dual Inf.      Time
       0    0.0000000e+00   3.179688e+00   0.000000e+00      0s
       4    6.8838775e+00   0.000000e+00   0.000000e+00      0s

Solved in 4 iterations and 0.00 seconds
Optimal objective  6.883877480e+00<jupyter_text>Good, no exception throw, even when running on frozen input. We've got some nice diagnostic output from `gurobipy`, which is also reassuring. Let's see what solution was created.<jupyter_code>soln.buyFood<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08143507972665148, 'fraction_numerical': 0.009870918754745633, 'mean_word_length': 5.126162790697674, 'pattern_counts': {'":': 0, '<': 88, '<?xml version=': 0, '>': 88, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '22', 'dir': 'jupyter-structured-clean-dedup', 'id': '540040', 'license': 'BSD-2-Clause', 'n_tokens_mistral': 3213, 'n_tokens_neox': 3020, 'n_words': 1241, 'path': 'examples/expert_section/notebooks/diet.ipynb', 'repo_name': 'adampkehoe/ticdat', 'size': '10536'}