import pandas as pd

import numpy as np
import matplotlib.pyplot as plt

"""
Explanation: <p><font size="6"><b> Pandas: Methods for data cleaning</b></font></p>


© 2021, Joris Van den Bossche and Stijn Van Hoey  (&#106;&#111;&#114;&#105;&#115;&#118;&#97;&#110;&#100;&#101;&#110;&#98;&#111;&#115;&#115;&#99;&#104;&#101;&#64;&#103;&#109;&#97;&#105;&#108;&#46;&#99;&#111;&#109;, &#115;&#116;&#105;&#106;&#110;&#118;&#97;&#110;&#104;&#111;&#101;&#121;&#64;&#103;&#109;&#97;&#105;&#108;&#46;&#99;&#111;&#109;). Licensed under CC BY 4.0 Creative Commons
End of explanation
"""


countries = pd.DataFrame({'county name': ['Belgium', 'Flance', 'Germany', 'Netherlands', ['United Kingdom', 'Germany']],
                          'population': [11.3, 64.3, 81.3, 16.9, 64.9],
                          'area': [30510, 671308, 357050, 41526, [244820, np.nan]],
                          'capital': ['Brussels', ' Paris         ', 'Barlin', 'Amsterdam', 'London']})
countries

"""
Explanation: A number of Pandas functions are useful when cleaning up raw data and converting it to a data set ready for analysis and visualisation. In this notebook a selection of methods are introduced:

drop
rename
replace
explode
drop_duplicates/duplicates
astype
unique
.str.-methods

Note: Working with missing values is tackled in a dedicated notebook pandas_07_missing_values.
We showcase using a dirty example data:
End of explanation
"""


countries.drop(columns=["area", "capital"])

"""
Explanation: drop
Drop columns (or rows) by name (this can also be achieved by selecting the columns you want to keep, but if you only want to drop a few columns, drop() is easier). Specify a list of column names to drop:
End of explanation
"""


countries = countries.rename(columns={"county name": "country"})

"""
Explanation: rename
Use a dict with the dictionary keys the old column/index name and the dictionary values the new column/index name:
End of explanation
"""


countries["capital"].replace("Barlin", "Berlin")

"""
Explanation: replace
Replace values in a column. Different inputs can be used. The most basic one is providing a value to_replace and a new value:
End of explanation
"""


countries = countries.replace({"Barlin": "Berlin", "Flance": "France"})
countries

"""
Explanation: Similar to rename, one can use a dict with the dictionary keys the old data and the dictionary values the new data:
End of explanation
"""


countries_exploded = countries.explode(["country", "area"])
countries_exploded

"""
Explanation: explode
explode multiple values in a cell to individual records (rows). Not regularly required, but very powerful when in case:
End of explanation
"""


countries_exploded.duplicated(subset=["country"])

countries_exploded = countries_exploded.drop_duplicates(subset=["country"], keep="first").copy()  # More on this copy later
countries_exploded

"""
Explanation: drop_duplicates
Checking duplicate values in a data set with duplicated or remove duplicate values with drop_duplicates:
End of explanation
"""


countries_exploded.dtypes

countries_exploded["area"] = countries_exploded["area"].astype(int)

countries_exploded.dtypes

"""
Explanation: astype
Pandas read functions might not always use the most appropriate data type for each of the columns. Converting them to a different data type can also improve the memory usage of the DataFrame (e.g. int16 versus float64). The astype  method supports data type conversion to both Numpy data types as well as Pandas specific data types.
End of explanation
"""


countries_exploded["capital"].unique()

"""
Explanation: unique
Working with larger data sets, knowing which values are in a column:
End of explanation
"""


countries_exploded["capital"] = countries_exploded["capital"].str.strip()
countries_exploded["capital"].unique()

"""
Explanation: .str.-methods
Noticed the redundant spaces in the capital column? 
Whereas replace could work for this specific case (it also accepts regular expressions):
python
countries_exploded["capital"].replace(r"^\s+|\s+", "", regex=True)
Pandas provides a set of convenient string methods to handle these (element-wise) cleaning of strings, each of them accessible with the .str. accessor (e.g. str.split, str.startswith, removeprefix):
End of explanation
"""


casualties_raw = pd.read_csv("./data/TF_ACCIDENTS_VICTIMS_2020.zip", 
                         compression='zip', 
                         sep="|", 
                         low_memory=False)
casualties_raw.head()

"""
Explanation: <div class="alert alert-info">

__INFO__

For an overview of the available `.str.`-methods, see https://pandas.pydata.org/docs/user_guide/text.html#method-summary

</div>

Exercises: application on a real dataset
For these exercises, we will use data of road casualties in Belgium in 2020 made available by statbel. The metadata is available as well as a reference. The data contains the number of victims due to road causalities:

MS_VCT: Number of victims
MS_VIC_OK: Number of uninjured
MS_SLY_INJ: Number of slightly injured
MS_SERLY_INJ: Number of severely injured
MS_MORY_INJ: Number of mortally injured
MS_DEAD: Number of dead
MS_DEAD_30_DAYS: Number of dead after 30 days

Together with metadata about date and time, the victim and road type, light conditions, location,...
Pandas can load the data directly from the zip-file :
End of explanation
"""


pd.options.display.max_columns = 45

casualties_raw.head()

"""
Explanation: <div class="alert alert-info">

__INTERMEZZO - display options__

Pandas provides a number of configurable settings to display data, for example `display.max_rows`, `display.precision` and `display.max_columns`. When exploring a new data set, adjusting the `display.max_columns` setting is of particular interest to be able to scroll the full data set.

See https://pandas.pydata.org/docs/user_guide/options.html#options-and-settings for the documentation and an [overview of the available settings](https://pandas.pydata.org/docs/user_guide/options.html#available-options)

</div>
End of explanation
"""


column_names_with_fr = [col for col in casualties_raw.columns if col.endswith("_FR")]
column_names_with_fr

# %load _solutions/pandas_06_data_cleaning1.py

"""
Explanation: Whereas the data is already well organised and structured, some adjustments are required to support further analysis:

Combine the day and hour into a single datetime-aware data type.
Clean up the column names.
Metadata is always provided both in Dutch and French.
...

Let's apply the cleaning methods to clean up the data in the next set of exercises.
<div class="alert alert-success">

**EXERCISE 1**

Remove all the `_FR` metadata columns  from the `casualties_raw` data set and assign the result to a new variable `casualties_nl`. Use the `column_names_with_fr` variable derive in the next cell to remove the columns.

<details><summary>Hints</summary>

- Remove columns with the `drop()` method. The method works with one or more column names.
- Make sure to explicitly set the `columns=` parameter.

__NOTE__ The `column_names_with_fr` variable is created using the `df.columns` attribute of the DataFrame:
- Instead of enlisting the column names manually, a [list comprehension](https://docs.python.org/3/tutorial/datastructures.html#list-comprehensions) - a _feature of standard Python_ - is used to select the columns names ending on `_FR`.
- Within the list comprehension, the [`endswith()`](https://docs.python.org/3/library/stdtypes.html#str.endswith) standard string method is used to check if a column name ends on `_FR`. 
- ! Pandas also provides the `.str.endswith()` method, but this is for the data values inside a DataFrame. In this exercise we want to adjust the column names itself.    

</details>

</div>
End of explanation
"""


def clean_column_name(name):
    return name.removeprefix("TX_").removesuffix("_DESCR_NL")

# %load _solutions/pandas_06_data_cleaning2.py

"""
Explanation: <div class="alert alert-success">

**EXERCISE 2**

A number of the remaining metadata columns names have the `TX_` and the `_DESCR_NL` in the column name. Clean up these column names by removing the `TX_` at the start and the `_DESCR_NL` at the end of the column names using the helper function `clean_column_name` defined in the next cell. Update the `casualties_nl` variable, assign the result to the variable `casualties`.

<details><summary>Hints</summary>

- Use the `rename` method and apply the mapping on the `columns`.
- The input of the `rename` method van be a dictionary or a function. Use the `clean_column_name` as the function to rename the columns. 
- Make sure to explicitly set the columns= parameter.    

__NOTE__ The function `clean_column_name` takes as input a string and returns the string after removing the prefix and suffix. 

- The pandas method `rename` applies this function to each column name individually.    
- `removeprefix()` and `removesuffix()` are [Python string methods](https://docs.python.org/3/library/stdtypes.html#string-methods) to remove start/trailing characters if present.

</details>

</div>
End of explanation
"""


# %load _solutions/pandas_06_data_cleaning3.py

# %load _solutions/pandas_06_data_cleaning4.py

"""
Explanation: <div class="alert alert-success">

**EXERCISE 3**

Check the unique values of the `SEX` column.

Based on the the values, create a mapping dictionary to replace the values with the english version (`"male", "female"`). Use `None` for the unknown values (`Onbekend` in Dutch). Apply the mapping to overwrite the values in the `SEX` column with the new value.

<details><summary>Hints</summary>

- Create the mapping by hand and define a `dict`.
- Use the `replace()` method to update the values of the `SEX` column.

</details>

</div>
End of explanation
"""


# %load _solutions/pandas_06_data_cleaning5.py

# %load _solutions/pandas_06_data_cleaning6.py

# %load _solutions/pandas_06_data_cleaning7.py

"""
Explanation: <div class="alert alert-success">

**EXERCISE 4**

Check the unique values of the `DT_HOUR` column. Which of the data values is used as _not a number_ (not known)? Verify the amount of records that with the `DT_HOUR` not known.

A check with the data provider confirmed that the record(s) with value 99 did actually happen at 9 AM and are a typo instead of _not a number_ replacement value. Replace the 99 values with the real hour of the day in the `DT_HOUR` column.

<details><summary>Hints</summary>

- The number `99` is not a valid hour of the day and used as not a number data point.
- Only one data record has an unknown hour of the day. 

</details>

</div>
End of explanation
"""


# %load _solutions/pandas_06_data_cleaning8.py

# %load _solutions/pandas_06_data_cleaning9.py

# %load _solutions/pandas_06_data_cleaning10.py

"""
Explanation: <div class="alert alert-success">

**EXERCISE 5**

The day (`DT_DAY`) and hour (`DT_HOUR`) are two separate columns instead of a single `datetime` data type column. 

- Check the data types of the `DT_DAY` and `DT_HOUR` columns.
- Combine the two columns into a single column (using _string concatenation_) and use the `pd.to_datetime` function to convert the combined column (call the column `"datetime"`).

<details><summary>Hints</summary>

- The data type of columns is available as the `dtypes` attribute.
- String concatenation is done element-wise in Pandas using the `+` operator. Do not forget to convert the `DT_HOUR` column into a `str` column using `astype`.
- Without adding a minute level, the datetime conversion of `pd.to_datetime` won't work. Add `":00"` to provide minutes as well.

</details>

</div>
End of explanation
"""


# Conversion to english weekday names
casualties["DAY_OF_WEEK"] = casualties["DAY_OF_WEEK"].replace({"maandag": "Monday", 
                                                               "dinsdag": "Tuesday", 
                                                               "woensdag": "Wednesday", 
                                                               "donderdag": "Thursday", 
                                                               "vrijdag": "Friday", 
                                                               "zaterdag": "Saturday", 
                                                               "zondag": "Sunday"})

# %load _solutions/pandas_06_data_cleaning11.py

# %load _solutions/pandas_06_data_cleaning12.py

"""
Explanation: <div class="alert alert-success">

**EXERCISE 6**

For columns consistiong of a limited number of categories with (_ordinal data_) or without a logical order, Pandas has a specific data type: `Categorical`. An example in the data set is the `DAY_OF_WEEK` (from `Monday` -> `Sunday`). 

For this conversion, the `astype` is not sufficient. Use the `pd.Categorical` function (check the documentation) to create a new column `week_day` with the week days defined as a Categorical variable. Use Monday as the first day of the week and make sure the categories are ordered.

<details><summary>Hints</summary>

- See [Pandas categorical info](https://pandas.pydata.org/pandas-docs/stable/user_guide/categorical.html#object-creation) for more information
- Use `ordered=True` to define ordered data.  

</details>

</div>
End of explanation
"""


# %load _solutions/pandas_06_data_cleaning13.py

# verify outcome
casualties["AGE_CLS"].unique()

"""
Explanation: <div class="alert alert-success">

**(OPTIONAL) EXERCISE 7**

In the `AGE_CLS` column, the age is formatted as `X tot Y jaar` (i.e. _x till y year_). Remove the Dutch description and convert the data into a format `Y - Y` to define the age classes. 

Use the string methods as much as possible. The `Onbekend`, `  ` (empty string) and `75 jaar en meer` data values can be done by direct replacement into `None`, `None` and `> 75` respectively.

<details><summary>Hints</summary>

- Use the `.str.replace()` (note the difference with the Pandas `replace()` method) and the `str.removesuffix()` methods to convert the data format.
- Add an additional `str.strip` to get rid of the spaces and the 'unknown' number of spaces in the empty string case.
- Using the `replace()` method with a dictionary just works for the remaining two values:  `{"Onbekend": None, "75 jaar en meer": ">75"}`. It will leave other values (not specified in the dictionary) as is.

</details>

</div>
End of explanation
"""


# %load _solutions/pandas_06_data_cleaning14.py

# %load _solutions/pandas_06_data_cleaning15.py

"""
Explanation: <div class="alert alert-success">

**(OPTIONAL) EXERCISE 8**

The data set contains the number of victims. The link with the individual accidents is not available in the current data set and multiple records/rows of the data set can belong to a single accident. 

We can expect that records with the same day, hour, municipality , light condition, road type and build up area are probably linked to the same accident. Try to estimate the number of accidents.

<details><summary>Hints</summary>

- This exercise is a special case of the `drop_duplicates` method. When we drop duplicate records when `"DT_DAY", "DT_HOUR",  "CD_MUNTY_REFNIS", "BUILD_UP_AREA","LIGHT_COND", "ROAD_TYPE"` are all the same, we have an estimate on the number of accidents.
- Use the `subset` parameter to define a specific set of column names.

</details>

</div>
End of explanation
"""


