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

# # Examples in pandas using groupby methods

# +
# Build sample data
import pandas as pd
import numpy as np
from collections import OrderedDict

COLS = ['id', 'votes']
dat = [['A', 4], ['B', 2], ['B', 3], ['A', 2], ['A', 9]]
df1 = pd.DataFrame(dat, columns=COLS)

print('Initial dataframe')
print(df1.to_string())

df = df1.copy()
# -

# ## Augment existing dataframe (same number of rows while adding columns)

# +
# Add cumulative count
df['id_cumcount'] = df.groupby('id').cumcount()

# Find the previous value per group and calculate difference
df['prev'] = df.groupby('id')['votes'].apply(lambda x: x.shift(1))
df['prev'] = df['prev']

def calc_diff(grp):
    grp['diff'] = grp['votes'] - grp['prev']
    return grp

df['diff'] = df.groupby('id').apply(calc_diff)['diff']
print(df)

# +
# Build stats for all rows in each group and assign to all rows
for v in ["sum", "count", "median", "mean", "std"]:
    df['grp_' + v] = df.groupby('id')['votes'].transform(v)

df['grp_std_score'] = df.groupby('id')['votes'].transform(lambda x: (x - x.mean()) / x.std())

# Sort by group and column values
df = df.sort_values(['id', 'votes']).reset_index(drop=True)
print(df.to_string())
# -

# Print the first value in each group
print(df.groupby('id').first().to_string())

# Print the last value in each group
print(df.groupby('id').last().to_string())

# Get a subset of dataframe columns
df = df[['id', 'votes', 'prev', 'diff']]
print(df)

# Get rows that match a single group (group B)
gb = df.groupby(['id'])
print("Number of groups:", len(df.groupby(['id'])))
print("DataFrame where group id is B:")
print(gb.get_group('B'))


# +
# Build new column with the last value of diff per group 'smeared' back to all rows in the group
def calc_last_diff(grp):
    grp['last_diff'] = grp.iloc[-1]['diff']
    return grp

df['last_diff'] = df.groupby('id').apply(calc_last_diff)['last_diff']
print(df.to_string())


# +
# Set series value based on each row within group
def calc_is_last_diff(grp):
    grp['is_last_diff'] = grp['diff'] == grp['last_diff']
    # OR: grp['is_last_diff'] = grp.apply(lambda r:  r['diff'] == r['last_diff'], axis=1)    
    return grp

df['is_last_diff'] = df.groupby('id').apply(calc_is_last_diff)['is_last_diff']
print(df.to_string())
# -

# ## Iterate over groups and then over rows within each group

igr = 0
for name, gr in df.groupby('id'): 
    # Iterate within group
    print("index: %d, group name: %s" % (igr, name))
    for i,x in gr.iterrows():
        print("row idx: %d, diff: %.3f" % (i, x['diff']))
    
    print()
    igr += 1


# ## Filter dataframe based on multiple column values

# +
def remove_rows(df):
    remaining_rows = df['diff'] == df['last_diff']
    return df[remaining_rows]

df_copy = df.copy()

# with group_keys=True
print(df.groupby("id", group_keys=True).apply(remove_rows))

# with group_keys=False
df = df.groupby("id", group_keys=False).apply(remove_rows)
print(df)
# -

# ## Use pandas agg method to summarize a table

# +
# Aggregated group operation
li_grades = [80,90,80,70,np.nan,70,90,90,70,np.nan]

# Get values count for a list (first convert to Series and use dropna=False to get NaN counts)
print(pd.Series(li_grades).value_counts(dropna=False))

di1 = OrderedDict({'ID': ["x%d" % r for r in range(10)],
                     'ExamYear':['2007','2007','2007','2008','2008','2008','2008','2009','2009','2009'],
                     'Tested': ['yes','yes','yes','yes','no','yes','yes','yes','yes','no'],
                     'Passed': ['yes' if x > 70 else 'no' for x in li_grades],
                     'Grade': li_grades})

di2 = OrderedDict({'Tested': lambda x: x.value_counts()['yes'],
                     'Passed': lambda x: sum(x == 'yes'),
                     'Grade': 'mean'})  # instead of:  lambda x : np.mean(x)

df1 = pd.DataFrame(di1)
df2 = df1.groupby('ExamYear').agg(di2)

print("Original table:")
print(df1)

print("---------------")
print("Summary table:")
print(df2)

StarcoderdataJupyterScriptsDedupFiltered