import numpy as np
import pandas as pd
from IPython import display
import table_cleaner as tc

"""
Explanation: Tutorial
This tutorial will show you how to use the Table-Cleaner validation framework.
First, let's import the necessary modules. My personal style is to abbreviate
the scientific python libraries with two letters. This avoids namespace cluttering
on the one hand, and is still reasonably short.
End of explanation
"""


initial_df = pd.DataFrame(dict(name=["Alice", "Bob", "Wilhelm Alexander", 1, "Mary", "Andy"],
                            email=["alice@example.com", "bob@example.com", "blub", 4, "mary@example.com",
                            "andy k@example .com"],
                            x=[0,3.2,"5","hello", -3,11,],
                            y=[0.2,3.2,1.3,"hello",-3.0,11.0],
                            active=["Y", None, "T", "false", "no", "T"]
                            ))

display.display(initial_df)

"""
Explanation: IPython.display provides us with the means to display Python objects in a "rich" way, especially useful for tables.
Introduction
Validating tabular data, especially from CSV or Excel files is a very common task in data science and even generic programming. Many times this data isn't "clean" enough for further processing. Writing custom code to transform or clean up this kind of data quickly gets out of hand.
Table-Cleaner is a framework to generalize this cleaning process.
Basic Example
First, let's create a DataFrame with messy data.
End of explanation
"""


initial_df.dtypes

"""
Explanation: This dataframe contains several columns. Some of the cells don't look much like the other cells in the same column. For Example we have numbers in the email and name columns and strings in the number columns.
Looking at the dtypes assigned to the dataframe columns reveals a further issue with this mess:
End of explanation
"""


cleaner = tc.TableCleaner({'name': tc.String(min_length=2, max_length=10),
                           'email': tc.Email(),
                           'x': tc.Int(min_value=0, max_value=10),
                           'y': tc.Float64(min_value=0, max_value=10),
                           'active': tc.Bool(),
                            })


"""
Explanation: All columns are referred to as "object", which means they are saved as individual Python objects, rather than strings, integers or floats. This can make further processing inefficient, but also error prone, because different Python objects may not work with certain dataframe functionality.
Let's define a cleaner:
End of explanation
"""


output, verdicts = cleaner.validate(initial_df)

"""
Explanation: The TableCleaner constructor takes a dictionary for its first argument. This dictionary contains a mapping from column names to validator instances.
The tc.String instance validates every input to a string. Because most Python objects have some way of being represented as a string, this will usually work. Additionally, it can impose restrictions on minimum and maximum string length.
The tc.Int instance tries to turn the input into integer objects. This usually only works with numbers, or strings which look like integers. Here, also, minimum and maximum values can be optionally specified.
The cleaner object can now validate the input dataframe like this:
End of explanation
"""


display.display(output)

"""
Explanation: The validate method returns a tuple containing the validated output dataframe
and a dataframe containing the verdicts on the individual cells.
End of explanation
"""


output.dtypes

"""
Explanation: The DataFrame only contains completely valid rows, because the default behavior is to delete any rows containing an error. See below on how to use missing values instead.
The datatypes for the "x" column is now int64 instead of object. "y" is now float64. Pandas uses the dtype system specified in numpy, and numpy references strings as "object". The main reason for this is that numeric data is usually stored in a contiguous way, meaning every value has the same "width" of bytes in memory. Strings, not so much. Their size varies. So arrays containing strings have to reference a string object with a pointer. Then the array of pointers is contiguous with a fixed number of bytes per pointer.
The "active" column is validated as a boolean field. There is a dtype called bool, but it only allows True and False. If there are missing values, the column reverts to "object".
End of explanation
"""


verdicts

"""
Explanation: So far, we have ensured only valid data is in the output table. But Table Cleaner can do more: The errors themselves can be treated as data:
End of explanation
"""


errors = verdicts[~verdicts.valid]
display.display(errors)

"""
Explanation: In this case there is only one row per cell, or one per row and column. Except for the last row, where there are two warnings/errors for the Email column. In the current set of built-in validators this arises very rarely. Just keep in mind not to sum the errors up naively and call it the "number of invalid data points".
Let's filter the verdicts by validity:
End of explanation
"""


errors.groupby(["column", "reason"])["counter",].count()

"""
Explanation: As this is an ordinary DataFrame, we can do all the known shenanigans to it, for example:
End of explanation
"""


%%html
<style>
.tc-cell-invalid {
    background-color: #ff8080
}
.tc-highlight {
    color: red;
    font-weight: bold;
    margin: 3px solid black;
    background-color: #b0b0b0;
}

.tc-green {
    background-color: #80ff80
}
.tc-blue {
    background-color: #8080ff;
}
</style>

"""
Explanation: This functionality is the main reason why Table Cleaner was initially written. In reproducible datascience, it is important not only to validate input data, but also be aware of, analyze and present the errors present in the data.
The framework laid out in this project aims to provide this capability. It's still in its infancy, and the API may well be changed.
Markup Frames
Let's bring some color into our tables. First, define some CSS styles for the notebook, like so:
End of explanation
"""


mdf = tc.MarkupFrame.from_validation(initial_df, verdicts)
mdf

"""
Explanation: The MarkupFrame class is subclassed from Pandas' DataFrame class and is used to manipulate and render cell-specific markup. It behaves almost exactly the same as a DataFrame.
It can be created from a validation like this:
End of explanation
"""


mdf.x[1] += "tc-highlight"
mdf.y += "tc-green"
mdf.ix[0, :] += "tc-blue"
mdf


"""
Explanation: Note that we put in the initial_df table, because the verdicts always relate to the original dataframe, not the output, which has possibly been altered and shortened during the validation process.
Now watch this:
End of explanation
"""


np.bool(None)

"""
Explanation: Booleans
The trouble with Booleans
Boolean values are either True or False. In Pandas, and data science in general, things are a bit more tricky. There is a third state, which Pandas would refer to as a missing value. Numpy's Bool dtype does not support missing values though.
End of explanation
"""


bools = pd.Series([True, False, None, np.NaN])
bools

"""
Explanation: What's happening there is that many Python objects have a way of being interpreted as either True or False. An empty list, empty strings, and None, are all considered false, for example.
Now, let's try that in Pandas:
End of explanation
"""


bools.astype(bool)

"""
Explanation: The dtype is not "bool". Instead Pandas refers to the individual Python object, and thus dtype must be "object". We can make it bool, though:
End of explanation
"""


original = pd.Series(range(3))
original[bools]

"""
Explanation: Notice how np.NaN, which is normally interpreted as a missing value, has been converted to True?
If you try to index something with this sequence, this is what happens:
End of explanation
"""


bools = [True, False, None, np.NaN]

bool_df = pd.DataFrame(dict(a=bools, b=bools, c=bools, d=bools))
bool_df

"""
Explanation: Let's take a look at how to bring some sanity into this issue with Table Cleaner. First, define a messy DataFrame, with columns that are identical:
End of explanation
"""


bool_cleaner = tc.TableCleaner(dict(a=tc.Bool(),
                               b=tc.Bool(true_values=[True], false_values=[False], allow_nan=False),
                               c=tc.Bool(true_values=[True], false_values=[False, None], allow_nan=False),
                               d=tc.Bool(true_values=[True], false_values=[False, np.nan],
                                         nan_values=[None], allow_nan=False)))

bool_output, bool_verdicts = bool_cleaner.validate(bool_df, delete=False)
tc.MarkupFrame.from_validation(bool_output, bool_verdicts)

"""
Explanation: Now create a cleaner which validates each column differently:
End of explanation
"""


bool_verdicts[~bool_verdicts.valid]

"""
Explanation: Note that I used "delete=False" to keep rows with invalid data, while still converting available values. Then this dataframe has the same shape as MarkupFrame.from_validation expects. "allow_nan" defaults to True and controls whether or not missing values are considered an error.
End of explanation
"""


messy_bools_column =["T","t","on","yes", "No", "F"]
messy_bools = pd.DataFrame(dict(a=messy_bools_column, b=messy_bools_column))

bool_cleaner2 = tc.TableCleaner(dict(a=tc.Bool(),
                               b=tc.Bool(true_values=["T"], false_values=["F"], allow_nan=False),
                               ))

bool_output2, bool_verdicts2 = bool_cleaner2.validate(messy_bools, delete=False)
tc.MarkupFrame.from_validation(bool_output2, bool_verdicts2)

"""
Explanation: Tables coming from external sources, especially spreadsheet data is notorious for having all sorts of ways to indicate booleans or missing values. The Bool validator takes three arguments to handle these cases: true_values, false_values and nan_values.
End of explanation
"""


messy_emails =["alice@example.com", "bob@bob.com", "chris", "delta@localhost", "ernest@hemmingway@ernest.org", "fridolin@dev_server"]
email_df = pd.DataFrame(dict(email=messy_emails))
email_cleaner = tc.TableCleaner(dict(email=tc.Email()))

email_output, email_verdicts = email_cleaner.validate(email_df, delete=False)
tc.MarkupFrame.from_validation(email_output, email_verdicts)

"""
Explanation: Email validation
Email validation is a subject onto its own. Some frameworks offer validation by simple regular expressions, which sometimes isn't enough. Other libraries or programs go so far as to ask the corresponding mail server if it knows a particular address.
In almost all generic usecases, you expect email names to adhere to a very specific form, meaning a username "at" a particular globally identifiable domain name. It is assumed that every computer in the world can resolve this domain name to the same physical server. Email standards and most eail servers however, don't require "fully qualified domain names" or even globally resolvable domains. "root@localhost" is a perfectly valid email address, but completely useless in most circumstances where you want to collect or use email addresses.
TableCleaner's Email validator class is based on Django's validation method.
End of explanation
"""


