### Initializing Sample Data for Examples

Source: https://github.com/machow/siuba/blob/main/examples/examples-siu.ipynb

Initializes a simple list `data` to be used as input for subsequent examples demonstrating `siuba`'s symbolic operations.

```python
data = ['a','b','c']
```

--------------------------------

### Installing siuba

Source: https://github.com/machow/siuba/blob/main/README.md

This snippet provides the command-line instruction for installing the siuba library using pip, the standard package installer for Python. It is the first step to begin using siuba in a Python environment.

```Shell
pip install siuba
```

--------------------------------

### Setup for Performance Comparison

Source: https://github.com/machow/siuba/blob/main/examples/examples-siu.ipynb

Defines a helper function `lmap` and initializes a large list `l` of dictionaries. This setup is used to benchmark the performance of `siuba` symbolic expressions against traditional lambda functions for repeated operations.

```python
def lmap(*args, **kwargs): return list(map(*args, **kwargs))
l = [dict(a = 1) for ii in range(10*6)]
```

--------------------------------

### Filtering Data by String Prefix with siuba.sql

Source: https://github.com/machow/siuba/blob/main/examples/examples-sql.ipynb

This example shows how `siuba.sql.filter` can leverage SQLAlchemy's `ColumnElement` methods for more complex filtering. It filters the 'users' table to include rows where the 'fullname' starts with the letter 'm', demonstrating string-based filtering.

```python
# currently, you can use any method exposed by sqlalchemy ColumnElement class
# but I plan to implement a standard set of functions like in dplyr, so pandas
# or sql queries psshh no matter.
tbl = filter(tbl_users, _.fullname.startswith("m"))
print(tbl.last_op)
```

--------------------------------

### Creating a Sample DataFrame

Source: https://github.com/machow/siuba/blob/main/examples/examples-dplyr-funcs.ipynb

Initializes a pandas DataFrame named `df` with sample data representing repositories, their owners, languages, stars, and an additional column `x`. This DataFrame serves as the basis for subsequent data manipulation examples.

```python
df = DataFrame({
    "repo": ["pandas", "dplyr", "ggplot2", "plotnine"],
    "owner": ["pandas-dev", "tidyverse", "tidyverse", "has2k1"],
    "language": ["python", "R", "R", "python"],
    "stars": [17800, 2800, 3500, 1450],
    "x": [1,2,3,None]
    })
```

--------------------------------

### Simple Symbolic Binary Operation Example

Source: https://github.com/machow/siuba/blob/main/examples/examples-siu.ipynb

A straightforward example of a symbolic binary operation `_.a + _.b`, illustrating the concise syntax for defining operations on attributes `a` and `b` of an implicit object.

```python
_.a + _.b
```

--------------------------------

### Selecting Columns by Prefix with var_select in Python

Source: https://github.com/machow/siuba/blob/main/examples/examples-varspec.ipynb

This snippet illustrates selecting columns using string methods within `var_select`. It selects all columns that start with 'home' using `v.startswith("home")` and also explicitly includes the 'misc' column, demonstrating a combination of pattern-based and explicit selection.

```python
colnames = ["home_phone", "home_address", "other", "misc"]
var_select(
    colnames,
    v.startswith("home"),
    v.misc
)
```

--------------------------------

### Chaining Mutate Operations with siuba.sql

Source: https://github.com/machow/siuba/blob/main/examples/examples-sql.ipynb

This example showcases the ability of `siuba.sql.mutate` to create multiple new columns in a single operation, where subsequent new columns can refer to previously created columns within the same `mutate` call. It creates 'wow' and then 'wow2' based on 'wow'.

```python
# using previous col created in mutate
tbl = mutate(tbl_users, wow = _.id + 1, wow2 = _.wow + 2)
print(tbl.last_op)
```

--------------------------------

### Arranging DataFrame Rows

Source: https://github.com/machow/siuba/blob/main/examples/examples-dplyr-funcs.ipynb

Shows how to use `arrange` to sort DataFrame rows. The first example sorts by 'owner' in descending order and then by 'repo' in ascending order. The second example sorts by the length of the 'owner' string, demonstrating sorting by a derived value.

```python
arrange(df, -_.owner, _.repo)

arrange(df, _.owner.str.len())
```

--------------------------------

### Example of a Ready-to-Call siu Expression

Source: https://github.com/machow/siuba/blob/main/examples/examples-siu.ipynb

Presents a simple symbolic expression `(_.a + _.b) / 2` that is considered 'ready to call' by `siuba`. This expression can be directly applied to data, as it represents a complete, executable operation.

```python
(_.a + _.b) / 2
```

--------------------------------

### Initializing SQLAlchemy and Inserting Data

Source: https://github.com/machow/siuba/blob/main/examples/examples-sql.ipynb

This snippet sets up an in-memory SQLite database using SQLAlchemy, defines 'users' and 'addresses' tables with specified columns and relationships, creates these tables in the database, and then populates them with sample data using various insertion methods, including single and bulk inserts.

```python
from sqlalchemy import sql
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:', echo=False)


metadata = MetaData()
users = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('fullname', String),
)


addresses = Table('addresses', metadata,
  Column('id', Integer, primary_key=True),
  Column('user_id', None, ForeignKey('users.id')),
  Column('email_address', String, nullable=False)
 )

metadata.create_all(engine)

conn = engine.connect()

ins = users.insert().values(name='jack', fullname='Jack Jones')
result = conn.execute(ins)


ins = users.insert()
conn.execute(ins, id=2, name='wendy', fullname='Wendy Williams')


res = conn.execute(addresses.insert(), [
   {'user_id': 1, 'email_address' : 'jack@yahoo.com'},
   {'user_id': 1, 'email_address' : 'jack@msn.com'},
   {'user_id': 2, 'email_address' : 'www@www.org'},
   {'user_id': 2, 'email_address' : 'wendy@aol.com'},
])
```

--------------------------------

### Creating a LazyTbl with siuba.sql

Source: https://github.com/machow/siuba/blob/main/examples/examples-sql.ipynb

This snippet imports necessary functions from `siuba.sql` and `siuba.siu` and then initializes a `LazyTbl` object. The `LazyTbl` wraps an SQLAlchemy connection and table, enabling lazy evaluation of data manipulation operations, and its `last_op` attribute shows the initial state.

```python
from siuba.sql import filter, mutate, select, LazyTbl, arrange, lift_inner_cols
from siuba.siu import _

tbl_users = LazyTbl(conn, users)

print(tbl_users.last_op)
```

--------------------------------

### Summarizing DataFrames

Source: https://github.com/machow/siuba/blob/main/examples/examples-dplyr-funcs.ipynb

Demonstrates the `summarize` function for aggregating data. It shows summarizing an entire DataFrame to get the minimum `stars` and summarizing a grouped DataFrame to calculate the total `stars` and minimum `stars` for each language group.

```python
# summarize DataFrame
summarize(df, min_stars = _.stars.min())

# summarize grouped DataFrame
gdf = group_by(df, "language")

summarize(gdf, ttl_stars = _.stars.sum(), wat = _.stars.min())
```

--------------------------------

### Performing Select Operation with siuba.sql

Source: https://github.com/machow/siuba/blob/main/examples/examples-sql.ipynb

This example demonstrates how to use the `select` function from `siuba.sql` to specify which columns to include or exclude from a `LazyTbl`. It selects the 'fullname' column and explicitly excludes the 'id' column, showcasing column projection.

```python
tbl = select(tbl_users, _.fullname, -_.id)
print(tbl.last_op)
```

--------------------------------

### Applying a siuba Predicate Function in Python

Source: https://github.com/machow/siuba/blob/main/examples/examples-varspec.ipynb

This snippet shows how to create a reusable predicate function using `siuba`'s `_` object. The function `f` checks if a variable starts with 'a', and then applies this predicate to a `VarList` object `v`, which would typically be used within `var_select` or similar functions.

```python
f = _.startswith("a")
f(v)
```

--------------------------------

### Arranging Data with siuba.sql

Source: https://github.com/machow/siuba/blob/main/examples/examples-sql.ipynb

This snippet demonstrates the `siuba.sql.arrange` function, used to sort the rows of a `LazyTbl` based on a specified expression. It arranges the 'users' table by the sum of the 'id' and 'name' columns, showcasing data ordering.

```python
tbl = arrange(tbl_users, _.id + _.name)
print(tbl.last_op)
```

--------------------------------

### Slicing Columns with var_select in Python

Source: https://github.com/machow/siuba/blob/main/examples/examples-varspec.ipynb

This example shows how to select a range of columns using Python-like slicing syntax within `var_select`. It selects columns from `home_phone` up to (and including, typically in `siuba`'s `tidyselect` context) `home_address` based on their order in the `colnames` list.

```python
colnames = ["home_phone", "home_address", "other", "misc"]
var_select(
    colnames,
    v[v.home_phone:v.home_address]
)
```

--------------------------------

### Importing siuba and pandas

Source: https://github.com/machow/siuba/blob/main/examples/examples-dplyr-funcs.ipynb

Imports necessary modules and classes from `siuba` and `pandas` to enable data manipulation operations. This includes the core `siuba` functions, `meta_hook`, and `DataFrame` and `Series` from `pandas`.

```python
from siuba import *
from siuba import meta_hook

import pandas as pd

from pandas import DataFrame, Series
```

--------------------------------

### Initializing SQLAlchemy Select Statement for Column Access - Python

Source: https://github.com/machow/siuba/blob/main/docs/developer/sql-translators.ipynb

Demonstrates how to create a SQLAlchemy `select` statement object and access its columns. This setup is used throughout the vignette to conveniently reference columns for SQL translation examples. It prints the select statement, its columns type, and the columns themselves.

```python
from sqlalchemy import sql
col_names = ['id', 'x', 'y']
sel = sql.select([sql.column(x) for x in col_names])

print(sel)
print(type(sel.columns))
print(sel.columns)
```

--------------------------------

### Summarizing Data After Mutation in Siuba

Source: https://github.com/machow/siuba/blob/main/examples/examples-postgres.ipynb

This example shows a pipeline where a new column `id2` is first created by adding 1 to `id` using `mutate`, and then the mean of this new `id2` column is calculated using `summarize`. The generated SQL query is displayed.

```python
q = (tbl_addresses >> mutate(_, id2 = _.id + 1) >> summarize(_, m_id = _.id2.mean())) >> show_query()
```

--------------------------------

### Setting up an in-memory SQLite database for siuba

Source: https://github.com/machow/siuba/blob/main/README.md

This Python code demonstrates how to set up an in-memory SQLite database using SQLAlchemy and populate it with data from a pandas DataFrame. This setup is a prerequisite for showcasing siuba's ability to perform data analysis directly on SQL sources.

```Python
# Setup example data ----
from sqlalchemy import create_engine
from siuba.data import mtcars

# copy pandas DataFrame to sqlite
engine = create_engine("sqlite:///:memory:")
mtcars.to_sql("mtcars", engine, if_exists = "replace")
```

--------------------------------

### Selecting All Columns with var_select in Python

Source: https://github.com/machow/siuba/blob/main/examples/examples-varspec.ipynb

This snippet demonstrates how to select all columns from the `colnames` list using the full slice `v[:]` within `var_select`. This is equivalent to selecting all available variables.

```python
var_select(
    colnames,
    v[:]
)
```

--------------------------------

### Importing Core siuba and pandas Modules

Source: https://github.com/machow/siuba/blob/main/examples/examples-varspec.ipynb

This snippet imports essential classes and functions from `siuba.dply.tidyselect` like `Var`, `VarList`, and `var_select`, along with the `_` object from `siuba` for data manipulation. It also imports `pandas` and specific types `DataFrame` and `Series` for data structures.

```python
from siuba.dply.tidyselect import Var, VarList, var_select
from siuba import _
import pandas as pd
from pandas import DataFrame, Series
```

--------------------------------

### Adding a New Column with siuba.sql Mutate

Source: https://github.com/machow/siuba/blob/main/examples/examples-sql.ipynb

This snippet demonstrates the `siuba.sql.mutate` function to add a new calculated column to a `LazyTbl`. It creates a column named 'wow' by summing the values of the 'id' and 'name' columns, illustrating simple column creation.

```python
# simple
tbl = mutate(tbl_users, wow = _.id + _.name)
print(tbl.last_op)
```

--------------------------------

### Mutating with Chained siuba Methods

Source: https://github.com/machow/siuba/blob/main/examples/examples-dplyr-funcs.ipynb

Shows an alternative, more readable way to chain `siuba` operations using backslash for line continuation. It groups the DataFrame by 'language' and 'owner', mutates to add `rel_stars1`, and then ungroups the result, mirroring the previous example's functionality.

```python
df \
  .siu_group_by("language", "owner") \
  .siu_mutate(rel_stars1 = _.stars - _.stars.min()) \
  .siu_ungroup()
```

--------------------------------

### Analyzing Symbolic Expressions with strip_symbolic

Source: https://github.com/machow/siuba/blob/main/examples/examples-siu.ipynb

Illustrates how `strip_symbolic` can convert a symbolic expression into a callable object, enabling further analysis. The example shows extracting the 'operation variables' from a complex symbolic expression, highlighting `siuba`'s capability for introspection and analysis of deferred computations.

```python
symbol = _.a[_.b + 1] + _['c']

# hacky way to go from symbol to call for now
call = strip_symbolic(symbol)

call.op_vars()
```

--------------------------------

### Standalone case_when Expression in Siuba

Source: https://github.com/machow/siuba/blob/main/examples/examples-postgres.ipynb

This snippet provides a simple, standalone example of a `case_when` expression. It assigns 'yeah' if `id > 1` and 'no' otherwise, illustrating the basic syntax and functionality of `case_when` outside a full query pipeline.

```python
# NBVAL_IGNORE_OUTPUT
case_when(_, {_.id > 1: "yeah", True: "no"})
```

--------------------------------

### Filtering Data by Equality with siuba.sql

Source: https://github.com/machow/siuba/blob/main/examples/examples-sql.ipynb

This snippet illustrates the use of `siuba.sql.filter` to apply a simple equality condition to a `LazyTbl`. It filters the 'users' table to include only rows where the 'fullname' column is exactly 'michael', demonstrating basic row subsetting.

```python
tbl = filter(tbl_users, _.fullname == "michael")
print(tbl.last_op)
```

--------------------------------

### Setting Up Grouped Data with dplython for Performance Comparison (Python)

Source: https://github.com/machow/siuba/blob/main/docs/key_features.ipynb

This code sets up a grouped DataFrame using `dplython` for a performance comparison. It imports necessary components from `dplython` and groups a `students` DataFrame by `student_id`, illustrating the setup for operations that might be slower compared to siuba.

```python
# set up code for timing
from dplython import X, DplyFrame, sift, group_by as dply_group_by

g_students2 = DplyFrame(students) >> dply_group_by(X.student_id)
```

--------------------------------

### Referring to Replaced Column in siuba.sql Mutate

Source: https://github.com/machow/siuba/blob/main/examples/examples-sql.ipynb

This example highlights a specific behavior of `siuba.sql.mutate` where, if a column is replaced, subsequent calculations within the same `mutate` call that refer to that column will use its *original* value, not the newly replaced one. It replaces 'id' and then calculates 'id2' based on the original 'id'.

```python
# replacing column, then referring to replacement
tbl = mutate(tbl_users, id = _.id + 1, id2 = _.id + 2)
print(tbl.last_op)
```

--------------------------------

### Performing Complex Data Transformations with Siuba in PostgreSQL

Source: https://github.com/machow/siuba/blob/main/examples/examples-postgres.ipynb

This example showcases a multi-step data transformation pipeline using Siuba, including grouping by `user_id`, mutating a new column `num` using `dense_rank`, filtering based on `id` and `email_address` string patterns, and then collecting the results. It uses `LazyTbl` for deferred execution.

```python
from siuba import *
from siuba.sql.verbs import LazyTbl, collect, show_query
from siuba.sql.dply.vector import dense_rank
import siuba.meta_hook.sqlalchemy.sql.functions as F

from sqlalchemy import sql

tbl_addresses = LazyTbl(conn, addresses)
tbl_users = LazyTbl(conn, users)

#tbl_addresses >> mutate(_, num = dense_rank(_.id)) >> show_query(_)
q = (tbl_addresses
  >> group_by("user_id")
  >> mutate(num = dense_rank(_.id))
  >> filter(
       _.id > _.id.min(),
       _.email_address.str.startswith("jack")
     )
  >> ungroup()
  >> show_query(simplify = True)
  >> collect()
  )

q
```

--------------------------------

### Initializing Pandas DataFrame and GroupBy Object (Python)

Source: https://github.com/machow/siuba/blob/main/examples/architecture/007-how-pandas-groupby-works.ipynb

This snippet initializes a pandas DataFrame with 'g' (group) and 'x' (value) columns and then creates a GroupBy object on column 'g'. This sets up the data for subsequent aggregation and transformation examples.

```python
import pandas as pd

df = pd.DataFrame({
    "g": ['b', 'c', 'a', 'c'],
    "x": [10, 11, 12, 13]
    }, index = [100, 101, 102, 103]
)

gdf = df.groupby('g')
```

--------------------------------

### Listing Python Project Dependencies

Source: https://github.com/machow/siuba/blob/main/requirements-test.txt

This snippet provides a list of Python packages and their specific versions, commonly used in a `requirements.txt` file to ensure consistent development and deployment environments. It specifies direct dependencies for the project, allowing for reproducible installations.

```Python
appnope==0.1.0
attrs==19.3.0
backcall==0.1.0
coverage==5.0.3
decorator==4.4.2
hypothesis==5.6.0
importlib-metadata==1.5.0
ipykernel==5.1.4
ipython==7.13.0
ipython-genutils==0.2.0
jedi==0.16.0
jsonschema==3.2.0
jupyter-client==6.0.0
jupyter-core==4.6.3
more-itertools==8.2.0
nbformat==5.1.3
nbval==0.9.6
packaging==20.3
parso==0.6.2
pexpect==4.8.0
pickleshare==0.7.5
pluggy==0.13.1
prompt-toolkit==3.0.3
ptyprocess==0.6.0
py==1.11.0
Pygments==2.5.2
pyparsing==2.4.6
pyrsistent==0.15.7
pytest==6.2.5
python-dateutil==2.8.1
pyzmq==22.3.0
six==1.14.0
sortedcontainers==2.1.0
tornado==6.0.4
traitlets==4.3.3
wcwidth==0.1.8
zipp==3.1.0
```

--------------------------------

### Chaining siuba Operations with Pipe Syntax

Source: https://github.com/machow/siuba/blob/main/docs/key_features.ipynb

This example demonstrates siuba's pipe syntax (`>>`) for chaining data manipulation verbs like `mutate` and `arrange`. This approach allows for a more readable and functional style of data transformation, where operations are applied sequentially to the data.

```Python
# actions can be imported individually
from siuba import mutate, arrange

# they can be combined using a pipe
my_data >> mutate(y = _.x + 1) >> arrange(_.g, -_.x)
```

--------------------------------

### Instantiating CallTree2 with SpecialClass Dispatch (Python)

Source: https://github.com/machow/siuba/blob/main/examples/architecture/004-user-defined-functions.ipynb

This snippet instantiates `CallTree2`, passing an empty dictionary and `SpecialClass` as the `dispatch_cls`. This setup prepares the `CallTree2` instance to handle symbolic dispatches specifically for `SpecialClass` types.

```python
ctl2 = CallTree2({}, dispatch_cls = SpecialClass)
```

--------------------------------

### Selecting DataFrame Columns

Source: https://github.com/machow/siuba/blob/main/examples/examples-dplyr-funcs.ipynb

Illustrates the `select` function for choosing and renaming columns. It demonstrates selecting columns using `siuba`'s `_` syntax for column references, including renaming a column (`_.y == _.x`) and excluding a column (`-_.language`). The commented lines show alternative syntax considerations.

```python
# thoughts:
#  + can use dynamic values, e.g. colname == .x
#  + if select implements some name class, then nothing magic happening
#    e.g. _.y == _.x is equivalent to lambda cols: cols.y == cols.x
#  - long winded (==, _.y seems harder to read than "y")
select(df, _.y == _.x, -_.language)
# considered alternative with strings. E.g...
# select(df, "y = x", "language")
# select(df, dict(y = "x"), "language")
```

--------------------------------

### Loading Sample Iris Dataset into Pandas DataFrame

Source: https://github.com/machow/siuba/blob/main/examples/case-iris-select.ipynb

This code snippet creates a pandas DataFrame named `df_iris` with the first five rows of the Iris dataset. It provides a self-contained dataset for examples, avoiding the need to import from `sklearn.datasets`.

```python
## Rather than import the iris data from sklearn, I am just including the
## first 5 rows.

# from sklearn import datasets
# iris = datasets.load_iris()
# df_iris = pd.DataFrame(iris.data, columns = iris.feature_names)
# df_iris['species'] = iris.target_names[iris.target]

df_iris = pd.DataFrame({
    'sepal length (cm)': [5.1, 4.9, 4.7, 4.6, 5.0],
    'sepal width (cm)': [3.5, 3.0, 3.2, 3.1, 3.6],
    'petal length (cm)': [1.4, 1.4, 1.3, 1.5, 1.4],
    'petal width (cm)': [0.2, 0.2, 0.2, 0.2, 0.2],
    'species': ['setosa', 'setosa', 'setosa', 'setosa', 'setosa']
})
```

--------------------------------

### Defining Python Project Dependencies

Source: https://github.com/machow/siuba/blob/main/requirements.txt

This snippet lists the Python packages and their specific versions required for the project. These dependencies are typically installed using pip from a `requirements.txt` file to ensure a consistent development and deployment environment, preventing version conflicts.

```Python
numpy==1.19.1
pandas==1.2.5
psycopg2==2.8.5
PyMySQL==1.0.2
python-dateutil==2.8.1
pytz==2020.1
PyYAML==5.3.1
six==1.15.0
SQLAlchemy==1.3.18
```

--------------------------------

### Translating Datetime Methods with Siuba - Python

Source: https://github.com/machow/siuba/blob/main/examples/examples-postgres.ipynb

This example illustrates how `siuba` translates `dt` (datetime) methods, such as extracting the hour from a datetime column (`_.id.dt.hour`). It mutates the `tbl_addresses` table to add a new `hour` column and then displays the generated SQL query.

```python
q = tbl_addresses >> mutate(hour = _.id.dt.hour) >> show_query()
```

--------------------------------

### Getting Grouped Distinct Values with Transformation

Source: https://github.com/machow/siuba/blob/main/examples/examples-dplyr-funcs.ipynb

Demonstrates getting distinct values within groups after a transformation. It groups the DataFrame by 'language', then calculates distinct values of an uppercase version of 'language' (`lang2`) within each group, and finally ungroups the result.

```python
gdf = group_by(df, "language")

ungroup(distinct(gdf, lang2 = _.language.str.upper()))
```

--------------------------------

### Using if_else as a Callable

Source: https://github.com/machow/siuba/blob/main/examples/examples-dplyr-funcs.ipynb

Demonstrates `if_else` being used to create a callable function `f`. This function, when applied to a DataFrame, checks if the 'repo' string contains 'd' and returns the 'repo' value if true, otherwise 'wat'.

```python
f = if_else(_.repo.str.contains("d"), _.repo, "wat")

f(df)
```

--------------------------------

### Setting Up PostgreSQL and Inserting Data with SQLAlchemy

Source: https://github.com/machow/siuba/blob/main/examples/examples-postgres.ipynb

This snippet demonstrates how to establish a connection to a PostgreSQL database using SQLAlchemy, define database tables (`users` and `addresses`), create them, and insert initial data into these tables. It uses environment variables for port and password.

```python
# NBVAL_IGNORE_OUTPUT
from sqlalchemy import sql
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy import create_engine
import os

port = os.environ.get("SB_TEST_PGPORT", "5432")
pwd = os.environ.get("SB_TEST_PGPASSWORD", "")
engine = create_engine('postgresql://postgres:%s@localhost:%s/postgres'%(pwd, port), echo=False)


metadata = MetaData()
users = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('fullname', String),
)


addresses = Table('addresses', metadata,
  Column('id', Integer, primary_key=True),
  Column('user_id', None, ForeignKey('users.id')),
  Column('email_address', String, nullable=False)
 )

metadata.drop_all(engine)
metadata.create_all(engine)

conn = engine.connect()

ins = users.insert().values(name='jack', fullname='Jack Jones')
result = conn.execute(ins)


ins = users.insert()
conn.execute(ins, id=2, name='wendy', fullname='Wendy Williams')


conn.execute(addresses.insert(), [
   {'user_id': 1, 'email_address' : 'jack@yahoo.com'},
   {'user_id': 1, 'email_address' : 'jack@msn.com'},
   {'user_id': 2, 'email_address' : 'www@www.org'},
   {'user_id': 2, 'email_address' : 'wendy@aol.com'},
])
```

--------------------------------

### Applying PostgreSQL Specific Rounding with Siuba - Python

Source: https://github.com/machow/siuba/blob/main/examples/examples-postgres.ipynb

This example shows how `siuba` can translate operations that might be specific to certain SQL dialects, like PostgreSQL's `round()` function. It mutates the `tbl_addresses` table by rounding the `id` column to two decimal places, then displays the generated query and collects the results.

```python
(tbl_addresses
  >> mutate(id2 = _.id.round(2))
  >> show_query()
  >> collect()
)
```

--------------------------------

### Replacing an Existing Column with siuba.sql Mutate

Source: https://github.com/machow/siuba/blob/main/examples/examples-sql.ipynb

This snippet demonstrates how `siuba.sql.mutate` can be used to replace an existing column with a new calculated value. It redefines the 'id' column by adding 1 to its original value, illustrating in-place column modification.

```python
# replacing column
tbl = mutate(tbl_users, id = _.id + 1)
print(tbl.last_op)
```

--------------------------------

### Getting Distinct Values for a Column

Source: https://github.com/machow/siuba/blob/main/examples/examples-dplyr-funcs.ipynb

Returns a DataFrame containing only the distinct values from the 'language' column, effectively creating a Series of unique languages.

```python
distinct(df, "language")
```

--------------------------------

### Transmuting DataFrames

Source: https://github.com/machow/siuba/blob/main/examples/examples-dplyr-funcs.ipynb

Uses `transmute` to create new columns while dropping all other original columns except those explicitly specified. It shows transmuting on a regular DataFrame to keep 'repo' and add `rel_stars1`, and then on a grouped DataFrame, followed by ungrouping.

```python
transmute(df, "repo", rel_stars1 = _.stars - _.stars.min())

ungroup(transmute(gdf, "repo", rel_stars1 = _.stars - _.stars.min()))
```

--------------------------------

### Getting Distinct Rows (Keep All)

Source: https://github.com/machow/siuba/blob/main/examples/examples-dplyr-funcs.ipynb

Retrieves distinct rows based on the 'language' column, while keeping all other columns. If multiple rows have the same language, the first occurrence is typically kept.

```python
distinct(df, _.language, _keep_all = True)
```

--------------------------------

### Getting Distinct Values with Transformation

Source: https://github.com/machow/siuba/blob/main/examples/examples-dplyr-funcs.ipynb

Calculates distinct values after applying a transformation. It creates a new column `lang2` by converting 'language' to lowercase and then returns the distinct values of this new column.

```python
distinct(df, lang2 = _.language.str.lower())
```

--------------------------------

### Loading Sample Data with siuba (Python)

Source: https://github.com/machow/siuba/blob/main/docs/draft-old-pages/intro_sql_basic.ipynb

This code imports the `mtcars` dataset, a pandas DataFrame, from `siuba.data`. It then displays the first few rows of the DataFrame using the `.head()` method, providing a quick preview of the data structure and content.

```python
from siuba.data import mtcars

mtcars.head()
```

--------------------------------

### Importing Core siuba Symbolic Utilities

Source: https://github.com/machow/siuba/blob/main/examples/examples-siu.ipynb

Imports the essential `_` object for symbolic expressions, `explain` for visualizing expression trees, and `strip_symbolic` for converting symbolic expressions into callable functions from the `siuba.siu` module.

```python
from siuba.siu import _, explain, strip_symbolic
```

--------------------------------

### Creating New Columns with pandas assign vs. siuba mutate

Source: https://github.com/machow/siuba/blob/main/docs/key_features.ipynb

This example compares two methods for adding a new column to a pandas DataFrame: using pandas' `assign` method with a lambda function versus siuba's `mutate` verb with a lazy expression. It highlights siuba's more concise syntax for common data transformations.

```Python
import pandas as pd
from siuba import _, mutate

my_data = pd.DataFrame({
    'g': ['a', 'a', 'b'],
    'x': [1,2,3],
})

# pandas
my_data.assign(avg = lambda d: d.x.mean())

# siuba
mutate(my_data, avg = _.x.mean())
```

--------------------------------

### Contextualizing Call Shaping with `CallTreeLocal` in siuba

Source: https://github.com/machow/siuba/blob/main/examples/architecture/004-user-defined-functions.ipynb

This example illustrates how `CallTreeLocal` is used in `siuba` to manage and shape calls, particularly when dealing with symbolic expressions. It defines a simple `as_string` function and demonstrates how it's registered in a `CallTreeLocal` instance, preparing a symbolic call for processing within `siuba`'s expression evaluation framework.

```Python
from siuba.siu import CallTreeLocal, strip_symbolic

def as_string(x):
    return x.astype(str)

ctl = CallTreeLocal(local = {'as_string': as_string})

call = ctl.enter(strip_symbolic(_.as_string()))
```

--------------------------------

### Mutating a Grouped Column with Dense Rank in Siuba

Source: https://github.com/machow/siuba/blob/main/examples/examples-postgres.ipynb

This example illustrates how to perform a grouped mutation. It groups `tbl_addresses` by `user_id` and then creates a new `rank` column based on a comparison between `id` and `dense_rank` of `id` plus 1 within each group. The `show_query` function reveals the underlying SQL.

```python
q = (tbl_addresses
  >> group_by("user_id")
  >> mutate(rank = _.id > dense_rank(_.id) + 1)
  >> show_query()
  )
```

--------------------------------

### Basic `singledispatch` Example in Python

Source: https://github.com/machow/siuba/blob/main/examples/architecture/004-user-defined-functions.ipynb

This snippet demonstrates the fundamental usage of `functools.singledispatch` to create a generic function that dispatches to different implementations based on the type of its first argument. It shows a default implementation for any object and a specialized one for integers, illustrating how `singledispatch` allows functions to adapt their behavior based on input types.

```Python
from functools import singledispatch

# by default dispatches on object, which everything inherits from
@singledispatch
def cool_func(x):
    print("Default dispatch over:", type(x))
    
@cool_func.register(int)
def _cool_func_int(x):
    print("Special dispatch for an integer!")
    
cool_func('x')
cool_func(1)
```

--------------------------------

### Mutating a Grouped DataFrame

Source: https://github.com/machow/siuba/blob/main/examples/examples-dplyr-funcs.ipynb

Demonstrates how to use `mutate` with a grouped DataFrame (`gdf`) to add a new column `rel_stars1` calculated as the difference between `stars` and the minimum `stars` within each group. It also shows the equivalent method-chaining syntax using `siu_group_by`, `siu_mutate`, and `siu_ungroup`.

```python
gdf = group_by(df, 'language', "owner")

out = mutate(gdf, rel_stars1 = _.stars - _.stars.min())

ungroup(out)

df.siu_group_by("language", "owner").siu_mutate(rel_stars1 = _.stars - _.stars.min()).siu_ungroup()
```

--------------------------------

### Basic siuba Symbolic Expression Usage

Source: https://github.com/machow/siuba/blob/main/examples/examples-siu.ipynb

Demonstrates fundamental usage of the `_` object for creating symbolic expressions. It shows how to define expressions involving indexing (`_['a']`), binary operations (`+`), and method calls (`.min()`). It also illustrates how these expressions can be applied to data (e.g., a dictionary `d`) and how `explain` can be used to inspect the expression tree.

```python
f = _['a'] + _['b']

(_ + _)(1)
d = {'a': 1, 'b': 2}

explain(_.somecol.min())

(_['a'] + _['b'])(d)

f = _['a'] + 4
f(d)
```

--------------------------------

### Filtering Data using siu expressions vs. Lambda

Source: https://github.com/machow/siuba/blob/main/README.md

This example compares two methods for filtering a pandas DataFrame: using a traditional lambda function and using a siu expression. It highlights how siu expressions provide a more concise and readable shorthand for common data manipulation tasks, especially within siuba's piping syntax.

```Python
from siuba import _

# lambda approach
mtcars[lambda _: _.cyl == 4]

# siu expression approach
mtcars[_.cyl == 4]
```

--------------------------------

### Using `symbolic_dispatch` for Pandas Series in siuba

Source: https://github.com/machow/siuba/blob/main/examples/architecture/004-user-defined-functions.ipynb

This example illustrates `siuba`'s `symbolic_dispatch` decorator, which extends `singledispatch` to handle symbolic expressions, specifically for `pandas.Series`. It defines a function `add2` that adds 2 to a Series, demonstrating its application to a concrete `pd.Series` instance and showcasing how `symbolic_dispatch` enables type-specific function behavior.

```Python
from siuba.siu import symbolic_dispatch, _
import pandas as pd

@symbolic_dispatch(cls = pd.Series)
def add2(x):
    return x + 2

add2(pd.Series([1,2,3]))
```

--------------------------------

### Complex Escaping for Chained Symbolic Operations

Source: https://github.com/machow/siuba/blob/main/examples/examples-siu.ipynb

Provides a complex example demonstrating how to combine symbolic binary operations with attribute access and escaping. It shows how `~~` can be applied to a parenthesized symbolic expression `(_.x + _.y).imag` to ensure the `.imag` attribute is accessed directly after the sum, rather than symbolically.

```python
list(map(~~(_.x + _.y).imag, points))
```

--------------------------------

### Running siuba Tests with Pytest and Docker Compose

Source: https://github.com/machow/siuba/blob/main/README.md

This snippet provides instructions for setting up a PostgreSQL database using Docker Compose and then executing siuba's test suite with pytest. It ensures the necessary database environment is active before running tests.

```bash
# start postgres db
docker-compose up
pytest siuba
```

--------------------------------

### Summarizing Grouped Data with siuba summarize

Source: https://github.com/machow/siuba/blob/main/docs/key_features.ipynb

This example shows how siuba's `summarize` verb handles grouped aggregations. Unlike pandas, siuba's `summarize` automatically manages the index, returning a clean DataFrame with the grouped keys as regular columns, thus eliminating the need for `reset_index()`.

```Python
# good
summarize(g_cyl, hp = _.hp.mean(), mpg = _.mpg.mean())
```

--------------------------------

### Translating Complex Symbolic Expressions with CallTreeLocal - Python

Source: https://github.com/machow/siuba/blob/main/docs/developer/sql-translators.ipynb

Illustrates `CallTreeLocal`'s ability to handle more complex symbolic expressions, such as `_.id.mean() + 1`. This example highlights the challenge of integrating window functions within larger expressions and how `CallTreeLocal` processes the entire expression tree to produce the correct SQL.

```python
call2 = strip_symbolic(_.id.mean() + 1)
func_call2 = call_shaper.enter(call2)

func_call2(sel.columns)
```

--------------------------------

### Configuring Matplotlib and Plotnine Warnings (Python)

Source: https://github.com/machow/siuba/blob/main/docs/draft-old-pages/intro_sql_basic.ipynb

This snippet imports necessary libraries and configures warning filters for `plotnine` and `matplotlib` to suppress specific warnings, ensuring a cleaner output. It also includes the `%matplotlib inline` magic command for Jupyter environments to display plots directly.

```python
import matplotlib.cbook

import warnings
import plotnine
warnings.filterwarnings(module='plotnine*', action='ignore')
warnings.filterwarnings(module='matplotlib*', action='ignore')

%matplotlib inline
```

--------------------------------

### Previewing SQL Query with siuba (Python)

Source: https://github.com/machow/siuba/blob/main/docs/draft-old-pages/intro_sql_basic.ipynb

This code shows how to inspect the underlying SQL query generated by `siuba` without executing it. It applies `head(2)` to the `LazyTbl` and then uses `show_query()` to display the SQL statement that would be sent to the database.

```python
tbl_mtcars >> head(2) >> show_query()
```

--------------------------------

### Configuring Matplotlib and Plotnine Warnings (Python)

Source: https://github.com/machow/siuba/blob/main/docs/draft-old-pages/intro_sql_interm.ipynb

This snippet imports `matplotlib.cbook`, `warnings`, and `plotnine`. It then configures the `warnings` module to ignore specific warnings originating from `plotnine` and `matplotlib` modules, and sets up `matplotlib` for inline plotting, typically used in Jupyter environments.

```python
import matplotlib.cbook

import warnings
import plotnine
warnings.filterwarnings(module='plotnine*', action='ignore')
warnings.filterwarnings(module='matplotlib*', action='ignore')

%matplotlib inline
```

--------------------------------

### Connecting siuba to Existing PostgreSQL Database (Python)

Source: https://github.com/machow/siuba/blob/main/docs/draft-old-pages/intro_sql_basic.ipynb

This code illustrates how to connect `siuba` to an existing PostgreSQL database using a connection string. It initializes a `LazyTbl` object with the database URL and the target table name, enabling `siuba` to query data from a remote or local PostgreSQL instance.

```python
tbl = LazyTbl(
    "postgresql://username:password@localhost:5432/dbname",
    "tablename"
    )
```

--------------------------------

### Applying Window Functions with Siuba - Python

Source: https://github.com/machow/siuba/blob/main/examples/examples-postgres.ipynb

This snippet demonstrates the use of window functions in `siuba`, specifically `cumsum()`, along with `arrange()` for ordering. It first arranges the table by `id` in descending order, then calculates a cumulative sum of `user_id`, and finally arranges by the new `cumsum` column before showing the generated SQL query.

```python
from siuba.dply.vector import desc
(tbl_addresses
  >> arrange(desc(_.id))
  >> mutate(cumsum = _.user_id.cumsum())
  >> arrange(_.cumsum)
  >> show_query()
  )
```

--------------------------------

### Excluding Columns with var_select in Python

Source: https://github.com/machow/siuba/blob/main/examples/examples-varspec.ipynb

This example demonstrates how to use `var_select` to exclude columns from a list. It initializes `VarList` and then uses a negative `Var` object (`-v.d`) to deselect 'd', while also showing a conditional selection (`v.x == v.a`) which might be used for renaming or conditional inclusion.

```python
v = VarList()
colnames = ['a', 'b', 'c', 'd']
var_select(
    colnames,
    -v.d,    
    v.x == v.a,
    )
```

--------------------------------

### Initializing Airtable Connection and Fetching All Entries (Python)

Source: https://github.com/machow/siuba/blob/main/examples/architecture/005-spec-series-methods.ipynb

This snippet initializes an `Airtable` client with a specific base ID and table name. It then fetches all entries from the specified Airtable table, assuming the API key is available in the environment. The fetched data is stored in `air_entries`.

```python
# note, airtable API key is in my environment
airtable = Airtable('appErTNqCFXn6stSH', 'methods')

air_entries = airtable.get_all()
```

--------------------------------

### Demonstrating Pandas Groupby Limitations with `corr()` in Python

Source: https://github.com/machow/siuba/blob/main/examples/architecture/007-how-pandas-groupby-works.ipynb

This snippet illustrates a limitation in Pandas where the `corr()` method is available for Series but not directly for grouped Series. It imports `mtcars` data, groups it by 'cyl', and then shows a working example of `corr()` on ungrouped data and a commented-out example that fails on grouped data, highlighting the problem this document aims to address.

```Python
from siuba.data import mtcars

g_cyl = mtcars.groupby('cyl')

# works
mtcars.hp.corr(mtcars.mpg)

# doesn't work
# g_cyl.hp.corr(g_cyl.mpg)
```

--------------------------------

### Counting Grouped Occurrences

Source: https://github.com/machow/siuba/blob/main/examples/examples-dplyr-funcs.ipynb

Uses the `count` function to count the occurrences of unique combinations of 'language' and 'owner' in the DataFrame. This operation is similar to a `group_by` followed by a `summarize` with `n()`.

```python
count(df, "language", "owner")
```

--------------------------------

### Autoloading SQLAlchemy Table with Siuba - Python

Source: https://github.com/machow/siuba/blob/main/examples/examples-postgres.ipynb

This snippet demonstrates how to autoload an existing database table using SQLAlchemy's `Table` object with `autoload=True` and then wrap it with `siuba`'s `LazyTbl`. It then performs a simple mutation on a separate `tbl_users` object, showing how `siuba` interacts with SQLAlchemy table definitions to generate and execute queries.

```python
import sqlalchemy

metadata2 = MetaData()

users2 = sqlalchemy.Table('users', metadata2, autoload = True, autoload_with = engine)
tbl_users2 = LazyTbl(conn, users2)

(tbl_users
  >> mutate(id2 = _.id + 1)
  >> show_query()
  >> collect()
  )
```

--------------------------------

### Adding Count Column to DataFrame

Source: https://github.com/machow/siuba/blob/main/examples/examples-dplyr-funcs.ipynb

Applies `add_count` to the DataFrame, which adds a new column containing the count of observations for each unique combination of 'language' and 'owner', without collapsing the original DataFrame rows.

```python
add_count(df, "language", "owner")
```

--------------------------------

### Creating Siuba Table from String Name - Python

Source: https://github.com/machow/siuba/blob/main/examples/examples-postgres.ipynb

This snippet shows a more concise way to create a `LazyTbl` instance in `siuba` by directly providing the table name as a string, rather than an SQLAlchemy `Table` object. It then performs a simple mutation on a separate `tbl_users` object, demonstrating query generation and collection.

```python
import sqlalchemy

metadata3 = MetaData()

tbl_users3 = LazyTbl(conn, "users")

(tbl_users
  >> mutate(id2 = _.id + 1)
  >> show_query()
  >> collect()
  )
```

--------------------------------

### Mutating and Ungrouping Chained

Source: https://github.com/machow/siuba/blob/main/examples/examples-dplyr-funcs.ipynb

Applies another `mutate` operation to the previously created `out` DataFrame, adding `rel_stars2` by doubling the `stars` column, and then immediately ungroups the result. This illustrates chaining `mutate` and `ungroup`.

```python
ungroup(mutate(out, rel_stars2 = _.stars + _.stars))
```

--------------------------------

### Importing DataFrameGroupBy

Source: https://github.com/machow/siuba/blob/main/examples/examples-dplyr-funcs.ipynb

Imports `DataFrameGroupBy` from `pandas.core.groupby`, which is a class representing grouped DataFrame objects. This import might be implicitly used or required for certain `siuba` operations that interact with pandas' internal grouping mechanisms.

```python
from pandas.core.groupby import DataFrameGroupBy
```

--------------------------------

### Explaining Complex Symbolic Expressions

Source: https://github.com/machow/siuba/blob/main/examples/examples-siu.ipynb

Demonstrates the transparency benefit of `siuba` by using the `explain` function on a complex symbolic expression `f`. `explain` visualizes the underlying call tree, making it clear what operations the expression represents, unlike opaque lambda functions.

```python
f = _.a + _.b / 2 + _.c**_.d << _ & _

explain(f)
```

--------------------------------

### Grouping and Summarizing Data with siuba and SQL

Source: https://github.com/machow/siuba/blob/main/README.md

This snippet shows how siuba can apply the same data analysis logic (grouping and summarizing) to a SQL table as it does to a pandas DataFrame. It connects to the previously set up SQLite database and executes the query, demonstrating siuba's seamless support for SQL data sources.

```Python
# Demo SQL analysis with siuba ----
from siuba import _, tbl, group_by, summarize, filter

# connect with siuba
tbl_mtcars = tbl(engine, "mtcars")

(tbl_mtcars
  >> group_by(_.cyl)
  >> summarize(avg_hp = _.hp.mean())
  )
```

--------------------------------

### Filtering Grouped Data by ID in Siuba

Source: https://github.com/machow/siuba/blob/main/examples/examples-postgres.ipynb

This example shows how to apply a filter after grouping data. It groups `tbl_addresses` by `user_id` and then filters rows where `id` is greater than 1 within each group, displaying the resulting SQL query.

```python
q = (tbl_addresses
  >> group_by("user_id")
  >> filter(_.id > 1)
  >> show_query()
  )
```

--------------------------------

### Unnesting DataFrames

Source: https://github.com/machow/siuba/blob/main/examples/examples-dplyr-funcs.ipynb

Shows how to `unnest` a previously nested DataFrame. It first nests the DataFrame by 'language' into a 'data' column and then unnests the 'data' column, effectively reversing the nesting operation and restoring the original DataFrame structure.

```python
unnest(nest(df, -_.language, key = "data"), "data")
```

--------------------------------

### Previewing YAML Dump of Processed Spec - Python

Source: https://github.com/machow/siuba/blob/main/examples/architecture/005-spec-series-methods.ipynb

This snippet imports the `yaml` library and then uses `yaml.dump` to serialize the `out` dictionary (containing the processed `siuba` spec) into a YAML string. It then prints only the first 344 characters of the resulting YAML, providing a quick preview of the output without writing it to a file.

```python
import yaml
print(yaml.dump(out)[:344])
```

--------------------------------

### Using Metahooks for Symbolic Function Creation

Source: https://github.com/machow/siuba/blob/main/examples/examples-siu.ipynb

Demonstrates an advanced `siuba` feature, 'metahooks', which automatically transform imported functions (like `add` from `siuba.meta_hook.operator`) into functions that create symbolic expressions. This allows for defining symbolic operations using standard function syntax, which can then be explained and executed.

```python
import siuba.meta_hook
from siuba.meta_hook.operator import add, sub
from siuba.meta_hook.pandas import DataFrame

f = add(1, _['a'] + _['b'])
print(explain(f))

f({'a': 1, 'b': 2})
```

--------------------------------

### Applying if_else to DataFrame

Source: https://github.com/machow/siuba/blob/main/examples/examples-dplyr-funcs.ipynb

Applies the `if_else` helper function to a DataFrame column. It returns 'yeah' if the 'repo' column value is 'dplyr', otherwise 'no', effectively creating a new Series based on a conditional check.

```python
if_else(df.repo == "dplyr", "yeah", "no")
```