SQL for pandas Users

---

## DateTime Operations[​](#datetime "Direct link to DateTime Operations")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| pandas SQL|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `df['date'].dt.year` `toYear(date)`| `df['date'].dt.month` `toMonth(date)`| `df['date'].dt.day` `toDayOfMonth(date)`| `df['date'].dt.hour` `toHour(date)`| `df['date'].dt.dayofweek` `toDayOfWeek(date)`| `df['date'].dt.quarter` `toQuarter(date)` | | | | | | | | | | | | | |

---

## Arithmetic Operations[​](#arithmetic "Direct link to Arithmetic Operations")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| pandas SQL|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `df['a'] + df['b']` `a + b`| `df['a'] - df['b']` `a - b`| `df['a'] * df['b']` `a * b`| `df['a'] / df['b']` `a / b`| `df['a'] // df['b']` `intDiv(a, b)`| `df['a'] % df['b']` `a % b`| `df['a'] ** 2` `pow(a, 2)`| `df['a'].abs()` `abs(a)`| `df['a'].round(2)` `round(a, 2)` | | | | | | | | | | | | | | | | | | | |

---

## NULL Handling[​](#null "Direct link to NULL Handling")

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| pandas SQL|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `df['col'].isna()` `isNull(col)`| `df['col'].notna()` `isNotNull(col)`| `df.dropna()` `WHERE col IS NOT NULL` (for each col)| `df.fillna(0)` `ifNull(col, 0)`| `df.fillna({'a': 0, 'b': 'x'})` `ifNull(a, 0), ifNull(b, 'x')` | | | | | | | | | | | |

---

## Complete Example[​](#example "Direct link to Complete Example")

### pandas Code[​](#pandas-code "Direct link to pandas Code")

```
import pandas as pd

df = pd.read_csv("sales.csv")

result = (df
    [df['date'] >= '2024-01-01']              # Filter
    [df['amount'] > 100]                      # Filter
    [['region', 'category', 'amount']]        # Select columns
    .groupby(['region', 'category'])          # Group
    .agg({
        'amount': ['sum', 'mean', 'count']
    })
    .reset_index()                            # Flatten
    .query('amount_sum > 10000')              # Having
    .sort_values('amount_sum', ascending=False)  # Sort
    .head(20)                                 # Limit
)
```

### Equivalent SQL[​](#equivalent-sql "Direct link to Equivalent SQL")

```
SELECT 
    region,
    category,
    SUM(amount) AS amount_sum,
    AVG(amount) AS amount_mean,
    COUNT(amount) AS amount_count
FROM file('sales.csv', 'CSVWithNames')
WHERE date >= '2024-01-01'
  AND amount > 100
GROUP BY region, category
HAVING amount_sum > 10000
ORDER BY amount_sum DESC
LIMIT 20
```

### DataStore Code[​](#datastore-code "Direct link to DataStore Code")

```
from chdb import datastore as pd

ds = pd.read_csv("sales.csv")

result = (ds
    .filter(ds['date'] >= '2024-01-01')
    .filter(ds['amount'] > 100)
    .select('region', 'category', 'amount')
    .groupby('region', 'category')
    .agg({'amount': ['sum', 'mean', 'count']})
    .having(ds['sum'] > 10000)
    .sort('sum', ascending=False)
    .head(20)
)

# View the generated SQL
print(result.to_sql())
```

---

## SQL Keywords Summary[​](#summary "Direct link to SQL Keywords Summary")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| pandas Operation SQL Clause|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `df[condition]` `WHERE`| `df[['a', 'b']]` `SELECT a, b`| `df.groupby('x')` `GROUP BY x`| `.agg({'col': 'sum'})` `SUM(col)`| `.sort_values('x')` `ORDER BY x`| `.head(n)` `LIMIT n`| `pd.merge()` `JOIN`| `.drop_duplicates()` `DISTINCT`| `.having()` `HAVING` | | | | | | | | | | | | | | | | | | | |

---

## Tips for pandas Users[​](#tips "Direct link to Tips for pandas Users")

### 1. Think in SQL Operations[​](#think-in-sql "Direct link to 1. Think in SQL Operations")

When writing DataStore code, think about what SQL you'd want:

```
# If you want: SELECT ... WHERE ... GROUP BY ... ORDER BY ... LIMIT
# Write:
ds.filter(...).groupby(...).agg(...).sort(...).head(...)
```

### 2. Use to\_sql() to Learn[​](#use-to-sql "Direct link to 2. Use to_sql() to Learn")

```
# See how your pandas code becomes SQL
query = ds.filter(ds['x'] > 10).groupby('y').sum()
print(query.to_sql())
```

### 3. Leverage SQL Features[​](#leverage-sql-features "Direct link to 3. Leverage SQL Features")

DataStore gives you SQL power with pandas syntax:

```
# Window functions
ds['rank'] = F.row_number().over(partition_by='category', order_by='score')

# Conditional aggregation
ds.groupby('region').agg({
    'high_value': ('amount', F.sum_if(Field('amount') > 1000))
})
```

- [Viewing Generated SQL](#viewing-sql)- [Basic Operations Mapping](#basic)
    - [Filtering (WHERE)](#filtering-where)- [Selection (SELECT)](#selection-select)- [Sorting (ORDER BY)](#sorting-order-by)- [GroupBy and Aggregation](#groupby)
      - [Basic GroupBy](#basic-groupby)- [Aggregation Functions](#aggregation-functions)- [Multiple Aggregations](#multiple-aggregations)- [HAVING Clause](#having-clause)- [Joins](#joins)
        - [Join Example](#join-example)- [String Operations](#string)- [DateTime Operations](#datetime)- [Arithmetic Operations](#arithmetic)- [NULL Handling](#null)- [Complete Example](#example)
                  - [pandas Code](#pandas-code)- [Equivalent SQL](#equivalent-sql)- [DataStore Code](#datastore-code)- [SQL Keywords Summary](#summary)- [Tips for pandas Users](#tips)
                      - [1. Think in SQL Operations](#think-in-sql)- [2. Use to\_sql() to Learn](#use-to-sql)- [3. Leverage SQL Features](#leverage-sql-features)

Was this page helpful?