# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# ## ATCO inspection data
#
# We've been given some excel spreadsheets from ATCO which have information about their pipeline failures and updates. Let's take a dig into this data and see what we can pull out...
#
# ### Tidy data
#
# Before we can really do anyhing useful we need to load up the data. We've put all the data for this into the data directory - let's take a look at the files we've got 

# +
import os            # library for interacting with your system
from os import path  # module for manipulating paths

# Let's find out where we're execting this at the moment
os.getcwd()
# -

# Our data are in the data folder one directory above the notebook directory.

data_folder = path.join(
    path.abspath('..'),  # '..' means the directory above this one
    'data')
data_folder

os.listdir(data_folder)

# Pandas will do most of the loading for us from a range of formats, including Excel. Let's load up the faults data

import pandas as pd

# +
# This points to the location of the fault data file on my computer
fault_file = path.join(data_folder, 'Mains Faults Data_SAP_2010-2016.xlsx')

# First we need to see what sheets we have available in the book
faults_xl = pd.ExcelFile(fault_file)
faults_xl.sheet_names
# -

# Now we can pick which sheet we want to play with. There's a few pivot tables (which we can recreate pretty simply in Python if required). However `All Notifications_2010-2016` looks most promising. 

df = faults_xl.parse('All Notifications_2010-2016')

# > Note: We could have gone straight to this if we knew the names of the sheets by using `pandas.read_excel` and passing the sheet name. However it's useful sometimes to have the list of sheets in Python hence the approach we've taken above. You can try the alternative approach yourself
#
# Let's take a look at this data

df.head()

# We can also get basic info about this dataframe

df.info()

# Pandas has worked out a few things for us - it's identified which columns hold dates, and that the Notification ID is an integer. There are a lot of other columns which have variable data that Pandas doesn't want to guess what type would fit everything. Since most columns are 'objects' we are going to have to handle them individually.
#
# So first we need to sort on basic data types. We could have columns that are:
#
# 1. floating-point numbers (`numpy.float`)
# 2. integers (`numpy.int`)
# 3. categories (`pandas.Categorical`)
# 4. strings (`str`)
# 5. dates/times (`pandas.DateTime`)
# 6. something else? (`pandas.Object`)
#
# Pandas has seperate datatypes for each of these things. When cleaning our data we should try and map the columns to the relevant datatype. As we do this we will have to think about how to label invalid values, and what we'll do with records which have invalid values.
#
# We'll show an example of this for a numeric value and one for a categorical distribution. In this dataset there are lots of misplaced values (probably due to bugs in the ingest software) so we will need to flag these as we go.
#
# ### Numeric values
#
# The basic idea is to coerce the values to numeric and see what gets tossed out as a NaN (meaning Pandas couldn't infer a numerical value from that entry).

df.keys()

# Let's try pipe size

# +
pipe_size = pd.to_numeric(df['Pipe Size'], errors='coerce')

# We can make an array which is True for NaNs 
nans = pd.isna(pipe_size)
print('We have', nans.sum(), 'non-numeric entries')

# We can get a random sample of these entries to see what
# sort of non-numeric values are in this dataset
print('Five random entries:')
for entry in df['Pipe Size'][nans].sample(5):
    print(f' -- {entry}')


# -

# So it looks like theres a few misplaced fields in this dataset. We can check this for a few other columns as well. If it's not too bad we can just coerce everything we expect to be numeric and then drop rows with NaNs in later on.
#
# Before we go too much further lets write our little exploration script into a function that we can call on a column in the dataframe.

def report_non_numeric_values(df, column, show=5):
    """
    Report on values in a given column which can't be coerced to 
    numeric values.
    
    Parameters:
        df - the dataframe holding our data
        column - the key denoting which column we'd like to 
            generate a report for
        show - how many random inavlid values to show. 
            Default is five random samples
    """
    # Make the NaN array all in one hit
    nans = pd.isna(pd.to_numeric(df[column], errors='coerce'))
    
    # Print our little report
    print(f'For column "{column}"')
    n_nans = nans.sum()
    if n_nans == 0:
        print('No non-numeric records found')
    else:
        print('We have', nans.sum(), 'non-numeric records')
        print('Some randomly-selected non-numeric records:')
        for entry in df[column][nans][:5]:
            print(f' -- {entry}')


# We can also have a look at a categorical column and see what's going on. Usually you can spot these using the `pandas.nunique` function - we're looking for columns where there is a small number of unique values

df.nunique()

# Some of those make sense although pressure rating seems a bit strange - there are 158 values when there should only be a few of these. 

# How do we know what to keep? There's a few ways of proceeding. Pandas has a useful `describe` method to quickly see what's happening in a column

df['Pressure Rating'].describe()

# We can also count up how many of each we have

df['Pressure Rating'].value_counts()

# So looks like there are some useful categories and then a bunch of mis-matched categories. 
#
# We can clean this up by coercing the column to a categorical datatype. If we specify the categories ahead of time then anything that doesn't match will be assigned to the NaN category.

pressure_rating = pd.Categorical(
    df['Pressure Rating'],
    categories=('MP', 'HP', 'MLP', 'PEHP', 'AMP', 
                'CHP', 'LP', 'FHP')
)
pressure_rating.describe()

# We can also plot the data up using Seaborn. Seaborn is a plotting library that knows how to generate a lot of different exploratory plots for you from pandas dataframes.

import seaborn
seaborn.set()  # let seaborn set up the notebook for plotting

# In particular seaborn has a countplot instance that is useful here

seaborn.countplot(pressure_rating)

# We can do this for a bunch of the other categories as well

# ### Cleaned dataset
#
# In addition to changing these columns 

# +
df.set_index('Notification')
clean = pd.DataFrame(index=df.index)

# Stuff that we just copy across under a new name
make_copy = (
    ('Functional Loc.', 'functional_location'),
    ('Notif.date', 'date')
)
for orig, new in make_copy:
    clean[new] = df[orig]

# Numeric columns
clean['pipe_size'] = pd.to_numeric(df['Pipe Size'], errors='coerce')

# Categorical columns
make_categorical = (  # has column, new_name, category pairs
    ('Code group', 'code_group_0', None),  # category = None means make everything into a category
    ('Code group.1', 'code_group_1', None),
#     ('Code group.2', 'code_group_2', None),  # only has MNOBJECT whatever that means
    ('Pressure Rating', 'pressure_rating', 
         ('MP', 'HP', 'MLP', 'PEHP', 'AMP', 
                         'CHP', 'LP', 'FHP')),
    ('Pipe Material', 'pipe_material', ('PVC', 'ST', 'PE', 'GI', 'CI')),
    ('Damage Code', 'damage_code', None),
    ('Cause code', 'cause_code', None),
    ('ObjectPartCode', 'object_part_code', None),
    ('Location', 'location', None)
)
for column, new_name, cats in make_categorical:
    clean[new_name] = pd.Categorical(df[column], categories=cats)
    
# Suburbs turns out to be tricky - here we hack together a list of valid categories to
# pass to pandas.Categorical. Alternative is to put together a manual list of values.
suburbs = df['Suburb '].str.rstrip(';')
ok_suburbs = [
    s for s in suburbs.unique()
    if not(isinstance(s, int) or isinstance(s, float))
    and len(list(filter(str.isdigit, s))) == 0  # will be > 0 if any digits in the string
]
clean['suburb'] = pd.Categorical(suburbs, categories=ok_suburbs)
# -

# Now we can check our clean data 

clean.info()

# As a nice bonus our dataframe requires an order of magnitude less memory than before (~0.5 MB not ~5 MB)

seaborn.stripplot(x='pipe_material', y='pipe_size', data=clean, jitter=True)

seaborn.catplot(x='pipe_size', y='damage_code', data=clean)

# Now we can look at aggregating the counts by timeseries

# Let's aggregate all counts by month
counts = pd.DataFrame(index=clean.date)
counts['count'] = 1
month_counts = counts.resample('M', label='left').sum()
week_counts = counts.resample('W', label='left').sum()
month_counts.plot()

week_counts.plot()

faults_by_suburb = pd.DataFrame(
    data=clean[['date', 'suburb', 'damage_code']]
)
faults_by_suburb['count'] = 1

faults_by_suburb.head()

# +
fault_pivot = faults_by_suburb \
    .pivot_table(
        values='count', 
        index='date', 
        columns='damage_code',
        aggfunc=sum) \
    .resample('M').sum()

fault_pivot.head()
# -

# and we can then generate plots of each of the damage codes seperately to look for interesting patterns

# +
import matplotlib.pyplot as plt

fig, axes = plt.subplots(nrows=len(fault_pivot.keys()))
for damage_code, ax in zip(fault_pivot, axes):
    fault_pivot[damage_code].plot(ax=ax)
    ax.set_title(damage_code)
fig.set_size_inches(8, 41)
# -

# ### Writing out our tidy data to file
#
# We need to serialize this data somehow. If we write it back out to CSV we lose all the work we've put into making these categorical data types. Instead we're going to use [feather](https://github.com/wesm/feather) - an open interoperable binary dataframe format that can be read in R, and Python and whatever else you want to use (Hadoop and friends spring to mind). 

feather_file = path.join(data_folder, 'faults_data_2010_2016.feather')
clean.to_feather(feather_file)

# And we can read it straight back in with:

pd.read_feather(feather_file).info()

# Notice that our categories are being maintained which is nice. 
#
# As an aside you could now take this data over to R if you are more comfortable in that environment. To load the dataframe in R you could use something like:
#
# ```R
# # install.packages("feather")
# library(feather)
# clean <- read_feather('/path/to/faults_data_2010_2016.feather')
# ```



StarcoderdataJupyterScriptsDedupFiltered