# import numpy,pandas
import numpy as np
import pandas as pd

# set some pandas options for controlling output
pd.set_option('display.notebook_repr_html',False)
pd.set_option('display.max_columns',10)
pd.set_option('display.max_rows',10)

# inline graphics
%matplotlib inline

"""
Explanation: Grouping and Aggregating Data
pandas library provides a flexible and high-performance "groupby" facility that enables you to slice,dice and summarize data sets. This follows a pattern known as split-apply-combine. This pattern data is first categorized into groups based on a criteria such as the indexes or values within the columns. Each group is then processed with an aggregation or transformation function, returning a set of data with transformed values or a single aggregate summary for each group. pandas then combines all of these results and presents it in a single data structure.
Setting up the notebook
End of explanation
"""


# load the sensors data
sensors = pd.read_csv("../../data/sensors.csv")
sensors

"""
Explanation: Split-Apply-Combine (SAC) Pattern
Many data analysis problems utilize a pattern of processing data known as split-apply-combine. In this pattern, three steps are taken to analyze data:
1. A data set is split into smaller pieces
2. Each of these pieces are operated upon independently
3. All of the results are combined back together and presented as a single unit
This patttern is actually similar to the concepts in MapReduce. In MapReduce, massive sets of data, that are too big for a single computer, are divivided into pieces and dispatched to many systems to spread the load in manageable pieces (split). Each system then performs analysis on the data and calculates a result(apply). The results are then collected from each system and used for decision making (combined).
Once the data is split into groups one or more of the following three operations are applied:

Aggregation - This calculates a summary statistics such as group means or counts of the items in each group
Transformation - This performs group or item specific calculations and returns a set of like-indexed results
Filtration - This removes entire groups of data based on a group level computation.

The combine stage of the pattern is performed automatically by pandas, which will collect the results of the apply stage on all of the groups and construct a single merged result.
To Read: http://www.jstatsoft.org/v40/i01/paper
Split
End of explanation
"""


# group this data by the sensor column / variable
# returns a DataFrameGroupBy object
grouped = sensors.groupby('sensor')
grouped

"""
Explanation: Grouping by a single column value
The sensor data consists of three categorical variables (sensor, interval and axis) and one continuous variable (reading). In pandas, it is possible to group by any single categorical variable by passing its name to .groupby().
End of explanation
"""


# get the number of groups that this will create
grouped.ngroups

# what are the groups that were found?
grouped.groups

"""
Explanation: The result of calling .groupby() on DataFrame is not the actual grouped data, but a DataFrameGroupBy object. The actual process of grouping is a deferred / lazy process in pandas, and at this point the grouping has not actually been performed.
End of explanation
"""


# a helper function to print the contents of the groups
def print_groups(groupobject):
    # loop over all groups, printing the group name
    # and group details
    for name, group in groupobject:
        print(name)
        print(group)
        
# examine the content of the groups we created
print_groups(grouped)

"""
Explanation: Accessing the results of grouping
End of explanation
"""


grouped.size()

# get the count of items in each column of each group
grouped.count()

# get the data in one specific group
grouped.get_group('accel')

# get the first three items in each group
grouped.head(3)

# get the first item in each group
grouped.nth(0)

# get the second item in each group
grouped.nth(1)

grouped.nth(2)

"""
Explanation: The examination of these results gives us some insight into how pandas has performed the split that we specified. A group has been created for each distinct value in the sensors columns and has been named with that value. The group contains a DataFrame object whose content is the rows where the sensor value matched the name of the group.
End of explanation
"""


# group by both sensor and axis values
mcg = sensors.groupby(['sensor','axis'])
print_groups(mcg)

# get descriptive statistics for each
mcg.describe()

"""
Explanation: Grouping can also be performed on multiple columns by passing a list of column names. The following groups the data by both sensor and axis variables:
End of explanation
"""


# make a copy of the data and reindex the copy
mi = sensors.copy()
mi = mi.set_index(['sensor','axis'])
mi

# group by first level of index
mig_11 = mi.groupby(level=0)
print_groups(mig_11)

# group by multiple levels of the index
mig_112 = mi.groupby(level=['sensor','axis'])
print_groups(mig_112)

"""
Explanation: By default, groups are sorted by their group name in an ascending order. If you want to prevent sorting during grouping use the sort=False option.
Grouping using index levels
End of explanation
"""


# calculate the mean for each sensor / axis
mig_112.agg(np.mean)

# do not create an index matching the original object
sensors.groupby(['sensor','axis'],as_index=False).agg(np.mean)

# can simply apply the agg function to the group by object
mig_112.mean()

# apply multiple aggregation functions at once
mig_112.agg([np.sum,np.std])

"""
Explanation: Apply
After the grouping is performed, we have the ability to perform either aggregate calculations on each group of data resulting in a single value from each group or to apply a transformation to each item in a group and return the combined result for each group.
We can also filter groups based on results of expressions to exclude the groups from being included in the combined results.
Applying aggregation functions to groups
pandas allows the application of an aggregation function to each group of data. Aggregation is performed using the .aggregate() method of the GroupBy object.
End of explanation
"""


# apply a different function to each column
mig_112.agg({'interval':len,'reading':np.mean})

"""
Explanation: A different function can be applied to each column in each group by passing a Python dictionary to .agg(), where the keys of the dictionary represent the column name that the function is to be applied to and the value is the function.
End of explanation
"""


# calculare the mean of the reading column
mig_112['reading'].mean()

"""
Explanation: Aggregation can also be performed on specific columns using the [] operator on the GroupBy object.
End of explanation
"""


# DataFrame for example
df = pd.DataFrame({'Label':['A','C','B','A','C'],
                  'Values':[0,1,2,3,4],
                  'Values2':[5,6,7,8,9],
                  'Noise': ['foo','bar','baz','foobar','barbaz']})
df

# group by label
grouped = df.groupby('Label')
print_groups(grouped)

# add ten to all values in all columns
grouped.transform(lambda x: x + 10)

"""
Explanation: Transformation of Group Data
The GroupBy objects provide a .transform() method, which applies a function to each group and returns either the Series or DataFrame that has the following parameters

Indexed identically to the concatenation of the indexes in all groups.
Number of rows is equal to the sum of the number of rows in all the groups.
Consists of non-noise, non-grouped columns to which pandas has applied the given function.
End of explanation
"""


# a function to print the input before we are adding 10 to it
def xplus10(x):
    print(x)
    return x + 10

# transform using xplus10
grouped.transform(xplus10)

"""
Explanation: To understand what is going on more clearly, we can change the function being passed to .transform() to write some diagnostic information.
End of explanation
"""


# sum returns existing as it is applied to each individual item
grouped.transform(lambda x:x.sum())

"""
Explanation: pandas has called the transformation function nine times, one time for every column in every group (3 x 3) and passed a Series object for each combination of group / rows / column and for each of these calls, pandas stores the results and when complete, does a merge of the results back in to DataFrame that is indexed identically to the original data.
End of explanation
"""


# data to demonstrate replacement on NaN
df = pd.DataFrame({'Label':list("ABABAB"),
                   'values':(10,20,11,np.NaN,12,22)},
                 index = ['i1','i2','i3','i4','i5','i6'])
df

# group by label
grouped = df.groupby('Label')
print_groups(grouped)

# calculate the mean of the two groups
grouped.mean()

# use transform to fill the NaN with mean of the group
filled_NaNs = grouped.transform(lambda x: x.fillna(x.mean()))
filled_NaNs

type(filled_NaNs)

"""
Explanation: Practical Examples of Transformation
A common transformation in statistical analysis, with grouped data, is to replace missing data within each group with the mean of the non-Nan values in the group.
End of explanation
"""


# overwrite old values with the new ones
df.values = filled_NaNs
df

"""
Explanation: The result appears odd at a first glance because of the following reasons:

It does not have a Label column
It has returned the Series object when we passed DataFrame to it
It does not provide an explicit statemtn of which values in which group have been filled with the mean of the group.

On the first two points, our original DataFrame has two columns one of which was used in the grouping process. This column is not passed to the .transform() method and in this scenario the only column processed is Values. Upon applying the .transform() method on each group, pandas merges the results which are all Series objects.
With respect to the third point, we do not know which values in which groups were changed, but we do know the index in the original data as the index labels are preserved through the process.
End of explanation
"""


# generate a rolling mean time series
np.random.seed(123456)
data = pd.Series(np.random.normal(0.5,2,365 * 3),
                pd.date_range('2001-01-01', periods=365*3))
rolling = pd.rolling_mean(data,100,100).dropna()
rolling

rolling.plot()

# calculate mean and std by year
groupkey = lambda x:x.year
groups = rolling.groupby(groupkey)
groups.agg([np.mean,np.std])

# normalize to the z score
zscore = lambda x: (x - x.mean()) / x.std()
normed = rolling.groupby(groupkey).transform(zscore)
normed.groupby(groupkey).agg([np.mean,np.std])

# plot original vs normalize
compared = pd.DataFrame({'Original': rolling,'Normed':normed})
compared.plot()

# check the distribution # within one std
# should be roughly 64.2%
normed_inlstd = normed[np.abs(normed) <= 1.0].count()
float(normed_inlstd) / len(normed)

"""
Explanation: Another common and practical example is that of using .transform() in statistical analysis and is the process of normalizing multiple groups of data to have a mean 0 and a standard deviation of 1 also referred to as creating a normalized z score of the data.
End of explanation
"""


# data for our examples
df = pd.DataFrame({'Label':list("AABBCC"),
                  'Values':(1,2,3,4,np.nan,8)})
df

# drop groups with one or fewer non-NaN values
f = lambda x:x.Values.count() > 1
df.groupby('Label').filter(f)

"""
Explanation: Filtering Groups
pandas GroupBy object provides a .filter() method, which can be used to make group level decisions on whether or not the entire group is included in the result after the combination. The function passed to .filter() should return True if the group is to be included in the result and False to exclude it.
End of explanation
"""


# drop any groups with NaN values
f = lambda x: x.Values.isnull().sum() == 0
df.groupby('Label').filter(f)

# select groups with a mean of 2.0 or greater
grouped = df.groupby('Label')
mean = grouped.mean().mean()
f = lambda x: abs(x.Values.mean() -mean) > 2.0
df.groupby('Label').filter(f)

# replace values in a group where the # of items is <=1
f = lambda x: x.Values.count() > 1
df.groupby('Label').filter(f,dropna=False)

"""
Explanation: Notice that there is a subtle difference when it comes to using .filter() as compared to .translate(). The data passed to the function specified in the call to .filter() is passed to the columns specified in the .groupby( method.
End of explanation
"""


# generate 10000 normal random #'s
np.random.seed(123456)
dist = np.random.normal(size=10000)

# show the mean and std
"{0} {1}".format(dist.mean(),dist.std())

dist

# split the data into 5 bins
bins = pd.cut(dist,5)
bins

# show the categories in bins
bins.categories

# demonstrate the math to calculate the bins
min = dist.min()
max = dist.max()
delta = max - min
iwidth = delta / 5
extra = delta * 0.001
intervals = np.arange(min,max*extra,iwidth)
intervals[0] -= delta*0.001
intervals

# codes tells us which bin each item is in
bins.codes

"""
Explanation: Discretization and Binning
Discretization is the process of slicing up continuous data into a set of "bins" where each bin represents a range of the continous sample and items are then placed into the appropriate bin hence the term "binning".
Discretization in pandas is performed using the pd.cut() and pd.qcut() functions.
End of explanation
"""


# move the closed side of the interval to the left
pd.cut(dist,5,right=False).categories

# generate 50 ages between 6 and 45
np.random.seed(123456)
ages = np.random.randint(6,45,50)
ages

# cut into ranges and then get descriptive stats
ranges = [6,12,18,35,50]
agebins = pd.cut(ages,ranges)
agebins.describe()

# add names for the bins
ranges = [6,12,18,35,50]
labels = ['Youth','Young Adult','Adult','Middle Aged']
agebins = pd.cut(ages, ranges, labels=labels)
agebins.describe()

"""
Explanation: The notation for the intervals follows standard mathematical intervals where a parenthesis represents that the end is open while square brackets are closed. Closed ends include values at that exact number. By default pandas closes the right-handed side of intervals.
End of explanation
"""


# cut into quantiles
# 5 bins with an equal quality of items
qbin = pd.qcut(dist,5)
# this will tell us the range of values in each quantile
qbin.describe()

# make the quantiles at the +/- 3,2 and 1 std deviations
quantiles = [0,0.001,0.021,0.5-0.341,0.5,0.5+0.341,1.0-0.021,1.0-0.001,1.0]
qbin = pd.qcut(dist,quantiles)
# this data should be a perfect normal distribution
qbin.describe()

"""
Explanation: Data can be sliced according to specified quantities using pd.qcut(). This is the process of placing the values into bins such that each bin has the same number of items. To do this, the ranges of the quantities must be determined during the process so that the distribution is even.
End of explanation
"""


