<jupyter_start><jupyter_text>You will learn:- Split a pnadas object into pieces using one or more keys (in the forms of functions, arrays, or DataFrame column names)- Calculate group summary statistics, like count, mean, or standard deviation, or a user-defined function.- Within-group trandformation or other manpulations, like normalization, linear regression, rank , or subset selection.- Pivot tables and cross tabulation- Quantile analysis and other statistical group analysis.  10.1 GroupBy Mechanics Split-Apply-CombineKeys do not have to be all of the same type.- A list or array of values that is the same length as the axis being grouped.- A value indicating a column name in a DataFrame.- A dict or Series giving a correspondence b/w the values on the axis being grouped and the group names.- A function to be involved on the axis index or the individual labels in the index.<jupyter_code>import pandas as pd
import numpy as np<jupyter_output><empty_output><jupyter_text>Groupby using series.<jupyter_code>df = pd.DataFrame({'key1': ['a', 'a', 'b', 'b', 'a'], 
                  'key2': ['one', 'two', 'one', 'two', 'one'],
                  'data1': np.random.randn(5),
                  'data2': np.random.randn(5)})
df
# computing mean of 'data1' by labels from 'key1'
grouped = df['data1'].groupby(df['key1'])
grouped   # Groupby object
grouped.mean()<jupyter_output><empty_output><jupyter_text>Grouped the data using two keys, resulting in a hierarchia index, **consisting of unique pairs of keys**<jupyter_code>mean = df['data1'].groupby([df['key1'], df['key2']]).mean()
mean
mean.unstack()<jupyter_output><empty_output><jupyter_text>Group keys could be any arrays of the right length.<jupyter_code>states = np.array(['Ohio', 'California', 'California', 'Ohio', 'Ohio'])
years = np.array([2005, 2005, 2006, 2005, 2006])
df['data1'].groupby([states, years]).mean()
# No 'key2' column; since it not numeric data, it is said to be a nuisance column.
# therefore excluded from the result.
df.groupby('key1').mean()
df.groupby(['key1', 'key2']).mean()<jupyter_output><empty_output><jupyter_text>`size` GroupBy method returns a Series containing group sizes.<jupyter_code>df.groupby(['key1', 'key2']).size()<jupyter_output><empty_output><jupyter_text>Iterating Over GroupsA sequence of 2-tuples containing the group name along with the chunk of data.<jupyter_code>for name, group in df.groupby('key1'):
    print(name)
    print(group)
# In case of multiple groupby keys, the first element in the tuple will bea tuple of key values
for (k1, k2), group in df.groupby(['key1', 'key2']):
    print((k1, k2))
    print(group)
# computing a dict of the data pieces
pieces = dict(list(df.groupby('key1')))
pieces['b']
df.dtypes<jupyter_output><empty_output><jupyter_text>Grouping columns of df by their dtype<jupyter_code>grouped = df.groupby(df.dtypes, axis=1)

for dtype, group in grouped:
    print(dtype)
    print(group)<jupyter_output>float64
      data1     data2
0 -1.618715 -1.962461
1  0.286862 -0.972393
2 -0.986526 -0.320113
3  0.486519 -0.877098
4  0.359921 -0.940876
object
  key1 key2
0    a  one
1    a  two
2    b  one
3    b  two
4    a  one<jupyter_text>Selecting a column or Subset of columnsIndexing a groupby object created from a dataframe with a column name or array of column names has the effect of column subsetting for aggregation.i.e.- `df.groupby('key1')['data1']` is same as `df['data1'].groupby('key1')`- `df.groupby('key1')[['data1']]` is same as `df[['data1']].groupby('key1')` Use `df[['data1', 'data2']].groupby('key1')` or `df.groupby('key1')[['data1', 'data2']]` Note:1. `df['data1']` return series.2. `df[['data1']]` return DataFrame<jupyter_code># Return Grouped DataFrame
df.groupby(['key1', 'key2'])[['data2']].mean()
# return a Series
df.groupby(['key1', 'key2'])['data2'].mean()<jupyter_output><empty_output><jupyter_text>Grouping with Dicts and Series<jupyter_code>people = pd.DataFrame(np.random.randn(5, 5), 
                      columns=['a', 'b', 'c', 'd', 'e'],
                      index=['Joe', 'Steve', 'Wes', 'Jim', 'Travis'])
people.iloc[2:3, [1, 2]] = np.nan # add a few NA values
people<jupyter_output><empty_output><jupyter_text>Group correspondence for the columns and want to sum together the columns by group<jupyter_code>mapping = {
    'a': 'red',
    'b': 'red',
    'c': 'blue',
    'd': 'blue',
    'e': 'red',
    'f': 'orange',  # its OK if you have unused key
}
by_column = people.groupby(mapping, axis=1)
by_column.sum()
# Doing the grouping WITH pd.Series
mapping_series = pd.Series(mapping)
mapping_series
people.groupby(mapping_series, axis=1).sum()<jupyter_output><empty_output><jupyter_text>Grouping with FunctionsAny function passed as a group key will be called once per index value, with the return values being used as the group names.<jupyter_code># grouping by the length of anmes;
# return value: len(string)
people.groupby(len).sum()<jupyter_output><empty_output><jupyter_text>mixing functions with array, dicts, or Series is not a problem as everything gets converted to arrays internally<jupyter_code>key_list = ['one', 'one', 'one', 'two', 'two']
people.groupby([len, key_list]).min()<jupyter_output><empty_output><jupyter_text>Grouping by Index Levels Ability to aggregate using one of the levels of an axis index in hierarchically indexed datasets.<jupyter_code>columns = pd.MultiIndex.from_arrays([['US', 'US', 'US', 'JP', 'JP'],
                                     [1, 3, 5, 1, 3]],
                                    names=['city', 'tenor'])
hier_df = pd.DataFrame(np.random.randn(4, 5), columns=columns)
hier_df<jupyter_output><empty_output><jupyter_text>To group by level, pass the number or name using the level keyword.<jupyter_code>hier_df.groupby(level='city', axis=1).count()<jupyter_output><empty_output><jupyter_text>10.2 Data Aggregation**Aggregation**: refers to any data transformation that produces scalar values from arrays.<jupyter_code>df
grouped = df.groupby('key1')
grouped['data1'].quantile(0.9)<jupyter_output><empty_output><jupyter_text>To use your own aggregation functions, pass any function that aggregates an array to `aggregate` or `agg` method.<jupyter_code>def peak_to_peak(arr):
    return arr.max() - arr.min()

grouped.agg(peak_to_peak)
# some methods like describe also work, but stictly speaking they are not aggregations
grouped.describe()<jupyter_output><empty_output><jupyter_text>Column-Wise and Multiple Function Application<jupyter_code>tips = pd.read_csv('datasets/tips.csv')
# add tip `percentage to total bill
tips['tip_pct'] = tips['tip'] / tips['total_bill']
tips[: 6]<jupyter_output><empty_output><jupyter_text>for descriptive analysis, you can pass the name of function as a string also.<jupyter_code>grouped = tips.groupby(['day', 'smoker'])
grouped_pct = grouped['tip_pct']

grouped_pct.agg('mean')
grouped_pct.mean()<jupyter_output><empty_output><jupyter_text>If you pass a list of functions or function names instead, you get back a DataFrame with column names taken from the functions<jupyter_code>grouped_pct.agg(['mean', 'std', peak_to_peak])<jupyter_output><empty_output><jupyter_text>(col_name, function) to agg method<jupyter_code>grouped_pct.agg([('foo', 'mean'), ('bar', np.std)])<jupyter_output><empty_output><jupyter_text>For DataFramesSuppose we wanteed to compute the same three statistics for the `tip_pct` and `total_bill`<jupyter_code># you would get the same as aggregating (applying the function to each column) and then using concat on index
functions = ['count', 'mean', 'max']
result = grouped[['tip_pct', 'total_bill']].agg(functions)
result
result['tip_pct']
# passing a list of tuples to column names for the result of agg functions

ftuples = [('Durchschnitt', 'mean'), ('Abweichung', np.var)]
grouped[['tip_pct', 'total_bill']].agg(ftuples)<jupyter_output><empty_output><jupyter_text>To apply different functions to one or more of the columns; pass a dict to agg that contains a mapping of column names to any of the function specification listed so far.<jupyter_code>grouped.agg({'tip': np.max, 'size': sum})
grouped.agg({'tip_pct': ['min', 'max', 'mean', 'std'], 'size': 'sum'})<jupyter_output><empty_output><jupyter_text>Returning Aggregated Data Without Row Indexes on which they were grouped*`as_index`*`= False` to `groupby`.Although you can use `reset_index`; but that adds additional computational overhead.<jupyter_code>tips.groupby(['day', 'smoker'], as_index=False).mean()
tips.groupby(['day', 'smoker']).mean()<jupyter_output><empty_output><jupyter_text>10.3 Apply: General split-apply-combineSuppose you wanted to select top five `tip_pct` values by group.<jupyter_code># First, write  function that selects the rows
# with the largest values in a particular column

def top(df, n=5, column='tip_pct'):
    return df.sort_values(by=column, ascending=False)[:n]
#   return df.sort_values(by=column)[-n:]
top(tips, n=6)<jupyter_output><empty_output><jupyter_text>Up next...The `top` function is called  on each row group from the DataFrame, and then the results are glued together using `pandas.concat`, labeling the pieces with the group names.<jupyter_code># Then groupping by 'smoker' and applying top function we just wrote.
tips.groupby('smoker').apply(top)<jupyter_output><empty_output><jupyter_text>If you pass a function to apply that takes other arguments or keywords, you can pass these after the function.<jupyter_code>tips.groupby(['smoker', 'day']).apply(top, n=2, column='total_bill')
result = tips.groupby('smoker')['tip_pct'].describe()
result
result.unstack()<jupyter_output><empty_output><jupyter_text>Inside `GroupBy` when you invoke a method like `describe`, it actually just a shortcut for:<jupyter_code>f = lambda x: x.describe()
grouped.apply(f)<jupyter_output><empty_output><jupyter_text>Suppressing the Group Keys*`group_keys`*`= False`Disble a hierarchical index formed from the group keys along with the indexes of each piece of the original object.<jupyter_code>tips.groupby('smoker', group_keys=False).apply(top)<jupyter_output><empty_output><jupyter_text>Quantile and Bucket AnalysisThe `Categorical` object returned by `cut` can be passed directly to groupby. SO we could compute a set of statistics for the `data2` column like so.<jupyter_code>frame = pd.DataFrame({'data1': np.random.randn(1000),
                      'data2': np.random.randn(1000)})
quantiles = pd.cut(frame.data1, 4)
quantiles[:10]
def get_stats(group):
    return {'min': group.min(),
            'max': group.max(),
            'count': group.count(),
            'mean': group.mean()}
grouped = frame.data2.groupby(quantiles)
grouped.apply(get_stats).unstack()<jupyter_output><empty_output><jupyter_text>These were equal length buckets; to compute equal size buckets based on sample quantiles, use `qcut`.*`labels`*`= False` to just get quantile number<jupyter_code>grouping = pd.qcut(frame.data1, 10, labels=False)
grouped = frame.data2.groupby(grouping)
grouped.apply(get_stats).unstack()<jupyter_output><empty_output><jupyter_text>Example: Filling Missing Values with Group-Specific ValuesUsually we do it like this.<jupyter_code>s = pd.Series(np.random.randn(6))
s[::2] = np.nan
s
s.fillna(s.mean())<jupyter_output><empty_output><jupyter_text>Fill value that vary by group.One way to do is to group the data and use `apply` with a function that calls fillna on each data chunk.<jupyter_code>states = ['Ohio', 'New York', 'Vermont', 'Florida', 'Oregon', 'Nevada', 'California', 'Idaho']
group_key = ['East'] * 4 + ['West'] * 4
data = pd.Series(np.random.randn(8), index=states)
data
data[['Vermont', 'Nevada', 'Idaho']] = np.nan
data
# These calues are going to be filled in their respective group
data.groupby(group_key).mean()
fill_mean = lambda g: g.fillna(g.mean())
data.groupby(group_key).apply(fill_mean)<jupyter_output><empty_output><jupyter_text>In another case, you might have predefined fill values that vary by group. Use dict indicating group name as key and value you want to fill.Groups have a `name` attribut set internally, so use that.<jupyter_code>fill_values = {'East': 0.5, 'West': -1}
fill_func = lambda g: g.fillna(fill_values[g.name])

data.groupby(group_key).apply(fill_func)<jupyter_output><empty_output><jupyter_text>Example: Random Sampling and Permutation<jupyter_code># Hearts, Spades, Clubs, Diamonds
suits = ['H', 'S','C', 'D']
card_val = (list(range(1, 11)) + [10] * 3) * 4
base_names = ['A'] + list(range(2, 11)) + ['J', 'K', 'Q']

cards = []
for suit in suits:
    # (str(num) + suit) for num in base_names
    cards.extend(str(name) + suit for name in base_names)
    
deck = pd.Series(card_val, index=cards)
deck[:13]<jupyter_output><empty_output><jupyter_text>Drawing a hand of five cards from the deck could be written as<jupyter_code>def draw(deck, n=5):
    return deck.sample(n)

draw(deck)<jupyter_output><empty_output><jupyter_text>Drawing two random cards from each suit. Because the suit is the last character of each card name, we can group based on thos and use `apply`.<jupyter_code>suit_letter = lambda card: card[-1] # last letter is suit

deck.groupby(suit_letter).apply(draw, n=2)<jupyter_output><empty_output><jupyter_text>Alternatively, we could write:<jupyter_code>deck.groupby(suit_letter, group_keys=False).apply(draw, n=2)<jupyter_output><empty_output><jupyter_text>Example: Group Weighted Average and Correlation<jupyter_code>df = pd.DataFrame({'category': ['a', 'a', 'a', 'a', 'b', 'b', 'b', 'b'],
                   'data': np.random.randn(8),
                   'weights': np.random.randn(8)})
df<jupyter_output><empty_output><jupyter_text>Then group weighted average by `category` would be:<jupyter_code>get_wavg = lambda g: np.average(g['data'], weights=g['weights'])

df.groupby('category').apply(get_wavg)<jupyter_output><empty_output><jupyter_text>Another example: Financial Dataset<jupyter_code>close_px = pd.read_csv('datasets/stock_px_2.csv', parse_dates=True, index_col=0)
close_px.info()
close_px[-4:]<jupyter_output><empty_output><jupyter_text>Computing a DataFrame consisting of the yearly correlations of daily returns (computed from percent changes) with SPXOne way to do this, we first create a function that computes the **pairwise correlation** of each column with `SPX` Column.<jupyter_code># spx_corr get every row in groups
spx_corr = lambda x: x.corrwith(x['SPX'])<jupyter_output><empty_output><jupyter_text>Then compute **percent changes** on `close_px` using `pct_change`<jupyter_code>rets = close_px.pct_change().dropna()<jupyter_output><empty_output><jupyter_text>Lastly we **group percent changes by year**, which can be extracted from each row with a on-line function that returns the `year` attribute of each `datetime` label.<jupyter_code>get_year = lambda x: x.year
by_year = rets.groupby(get_year)

rets.groupby(get_year).apply(spx_corr)<jupyter_output><empty_output><jupyter_text>Inter-column correlationsAnnual Correlations between Apple and Microsoft<jupyter_code>rets.groupby(get_year).apply(lambda g: g['AAPL'].corr(g['MSFT']))<jupyter_output><empty_output><jupyter_text>Example: Group-Wise Linear RegressionAs long as function returns a **pandas object** or **scalar value**, you can use `groupby` to perform more complex group-wise statistical anlysis.<jupyter_code>import statsmodels.api as sm

def regress(date, yvar, xvars):
    Y = data[yvar]
    X = data[xvar]
    X['itercept'] = 1
    result = sm.OLS(Y, X).fit()
    return result.params

by_year.apply(regress, 'AAPL', ['SPX'])<jupyter_output><empty_output><jupyter_text>10.4 Pivot Tables and Cross TabulationPivot Table aggregates a table of data by one or more keys, arranging the data in a rectangle with some of the group keys along the rows and some along the columns.- `pivot_table` can add partial totals, also known as *margins*Suppose you wanted to compute a table of group means (the default `pivot_table` aggregation type) arranged by `day` and `smoker` on the rows:<jupyter_code>tips[:2]
# this could have been produced by groupy directly
tips.pivot_table(index=['day', 'smoker'])<jupyter_output><empty_output><jupyter_text>Now, suppose we want to aggregate only `tip_pct` and `size`, and additionally group by `time`. Putting `smoker` in table columns and `day` in rows.<jupyter_code>tips.pivot_table(['tip_pct', 'size'], columns='smoker', index=['time', 'day'])<jupyter_output><empty_output><jupyter_text>Include Partial totals by passing *`margins`*`= True`. This has the effect of adding `All` row   and column labels, with corresponding values being the group statisitcs for all the data within a single tier.<jupyter_code># Here all means without taking into account smoker versus non-smoker (the All columns)
# OR
# any of the two levels of grouping on the rows (the All rows)
tips.pivot_table(['tip_pct', 'size'], index=['time', 'day'], columns='smoker', margins=True)<jupyter_output><empty_output><jupyter_text>To use different function, pass it to `aggfunc`.'`count`' or `len` will give you cross-tabulation (count or frequency) of group sizes.<jupyter_code>tips.pivot_table('tip_pct', index=['time', 'smoker'], columns='day', aggfunc=len, margins=True)<jupyter_output><empty_output><jupyter_text>If some combinations are empty, pass a `fill_value`.<jupyter_code>tips.pivot_table(values='tip_pct', index=['time', 'size', 'smoker'], columns='day', aggfunc='mean', fill_value=0)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13163799551234107, 'fraction_numerical': 0.014268454059029975, 'mean_word_length': 5.950019992003199, 'pattern_counts': {'":': 0, '<': 200, '<?xml version=': 0, '>': 200, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '50', 'dir': 'jupyter-structured-clean-dedup', 'id': '542453', 'license': 'Apache-2.0', 'n_tokens_mistral': 6008, 'n_tokens_neox': 5765, 'n_words': 1690, 'path': 'pydata/Ch 10 Data Aggregation and Group Operations.ipynb', 'repo_name': 'adhadse/ColabRepo', 'size': '17381'}