<jupyter_start><jupyter_text>Section Four - Slicing through noisy data to find high level groupingsTo present our data in an understandable way and a manageable volume, we need to group and aggregate our data using different cross sections.<jupyter_code>import pandas as pd
import numpy as np<jupyter_output><empty_output><jupyter_text>Splitting and clustering seemingly random data points<jupyter_code>df = pd.DataFrame(
    {'A': np.arange(8), 'B': np.random.randn(8)},
    index=pd.MultiIndex.from_arrays(
          [['bar', 'bar', 'bar', 'bar', 'foo', 'foo', 'qux', 'qux'],
          ['bar', 'bar', 'baz', 'baz', 'foo', 'foo', 'qux', 'qux']],
        names=['X', 'Y'])
)
df                
grouped = df.groupby('X')
for name, group in grouped:
    print(f"Group: {name}")
    display(group)
grouped = df.groupby(['X', 'Y'])
for name, group in grouped:
    print(f"Group: {name}")
    display(group)
grouped.get_group(('bar', 'bar'))
grouped.aggregate(np.sum)
# equivalent to this
df.groupby(['X', 'Y']).sum()
# get summary stats
df.groupby(['X']).aggregate([np.mean, np.std, np.min, np.max])<jupyter_output><empty_output><jupyter_text>Resolving incorrect data collection with lambdas and functions<jupyter_code># standard numpy/pandas functions are not enough
grouped = df.groupby("X")
grouped.aggregate([np.sum, lambda x: np.std(x, ddof=1)])
# name our lambda columns
(grouped
     .aggregate([np.sum, lambda x: np.std(x, ddof=1)])
     .rename(columns={"sum": "sum", "<lambda>": "custom_std"}))
# use lambdas to filter the dataframe
df = pd.DataFrame({'A': np.arange(8), 'B': list('xxxyyddy')})
df
# find rows where the value of B has more than 2 instances
df.groupby('B').filter(lambda x: len(x) > 2)
grouped['A'].apply(lambda x: x.describe())
# more generally, we apply() a function to an axis of a dataframe
# i.e. axis-wise apply

df = pd.DataFrame(
    {"A": [16] * 3, "B": [25] * 3}
)
df
df.apply(np.sqrt)
df.apply(lambda x: (x[0], x[1]), axis=1, result_type="expand")
# element-wise apply
df.applymap(lambda x: x+5)
# function chaining
(df.pipe(lambda x: x +5)
     .pipe(lambda x: x * 2))<jupyter_output><empty_output><jupyter_text>Cleaning up misaggregated statistics and bad pivot tables<jupyter_code># pivot tables?
# dummy data setup
import pandas.util.testing as tm
df = pd.melt(tm.makeTimeDataFrame().reset_index(), id_vars=["index"])
# we can inspect variables one by one
df[df['variable'] == 'A']
df.pivot(index='index', columns='variable', values='value')
df['value_2'] = df['value'] / 2
df
df.pivot(index='index', columns='variable')
df = pd.DataFrame(
    {'A': np.arange(8), 'B': np.random.randn(8)},
    index=pd.MultiIndex.from_arrays(
          [['bar', 'bar', 'bar', 'bar', 'foo', 'foo', 'qux', 'qux'],
          ['bar', 'bar', 'baz', 'baz', 'foo', 'foo', 'qux', 'qux']],
        names=['X', 'Y'])
)
df
df.stack()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.18726070309780718, 'fraction_numerical': 0.008005569091541943, 'mean_word_length': 4.759519038076152, 'pattern_counts': {'":': 4, '<': 18, '<?xml version=': 0, '>': 19, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '586131', 'license': 'MIT', 'n_tokens_mistral': 1076, 'n_tokens_neox': 1043, 'n_words': 283, 'path': 'Section 4/Chapter 4.ipynb', 'repo_name': 'PacktPublishing/Troubleshooting-Pandas', 'size': '2873'}