SQL for pandas Users

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/chdb/guides/pandas-to-sql.md)

DataStore compiles pandas-style operations into optimized SQL. This guide helps pandas users understand the SQL behind their operations.

## Viewing Generated SQL[​](#viewing-sql "Direct link to Viewing Generated SQL")

```
from pathlib import Path
Path("sales.csv").write_text("""\
region,product,category,amount,quantity,price,date,order_id
East,Widget,Electronics,5200,10,120,2024-01-15,1001
West,Gadget,Electronics,800,5,160,2024-02-20,1002
East,Gizmo,Home,6500,3,100,2024-03-10,1003
North,Widget,Electronics,4500,6,150,2024-06-18,1004
West,Gadget,Electronics,2000,8,250,2024-09-14,1005
""")

from chdb import datastore as pd

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

query = (ds
    .filter(ds['amount'] > 1000)
    .groupby('region')
    .agg({'amount': ['sum', 'mean']})
    .sort('sum', ascending=False)
    .head(10)
)

# View the SQL
print(query.to_sql())
```

Output:

```
SELECT region, SUM(amount) AS sum, AVG(amount) AS mean
FROM file('sales.csv', 'CSVWithNames')
WHERE amount > 1000
GROUP BY region
ORDER BY sum DESC
LIMIT 10
```

---

## Basic Operations Mapping[​](#basic "Direct link to Basic Operations Mapping")

### Filtering (WHERE)[​](#filtering-where "Direct link to Filtering (WHERE)")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| pandas SQL|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `df[df['age'] > 25]` `WHERE age > 25`| `df[df['city'] == 'NYC']` `WHERE city = 'NYC'`| `df[(df['x'] > 10) & (df['y'] < 20)]` `WHERE x > 10 AND y < 20`| `df[(df['a'] == 1) | (df['b'] == 2)]` `WHERE a = 1 OR b = 2`| `df[~(df['status'] == 'inactive')]` `WHERE NOT status = 'inactive'`| `df[df['col'].isin([1, 2, 3])]` `WHERE col IN (1, 2, 3)`| `df[df['val'].between(10, 20)]` `WHERE val BETWEEN 10 AND 20`| `df[df['name'].str.contains('John')]` `WHERE position('John' IN name) > 0` | | | | | | | | | | | | | | | | | |

### Selection (SELECT)[​](#selection-select "Direct link to Selection (SELECT)")

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| pandas SQL|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `df['col']` `SELECT col`| `df[['a', 'b', 'c']]` `SELECT a, b, c`| `df.head(10)` `LIMIT 10`| `df.tail(10)` Complex (ORDER BY ... DESC LIMIT 10)|  |  | | --- | --- | | `df.drop_duplicates()` `SELECT DISTINCT *` | | | | | | | | | | | |

### Sorting (ORDER BY)[​](#sorting-order-by "Direct link to Sorting (ORDER BY)")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| pandas SQL|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `df.sort_values('col')` `ORDER BY col ASC`| `df.sort_values('col', ascending=False)` `ORDER BY col DESC`| `df.sort_values(['a', 'b'])` `ORDER BY a ASC, b ASC`| `df.sort_values(['a', 'b'], ascending=[True, False])` `ORDER BY a ASC, b DESC`| `df.nlargest(10, 'col')` `ORDER BY col DESC LIMIT 10`| `df.nsmallest(5, 'col')` `ORDER BY col ASC LIMIT 5` | | | | | | | | | | | | | |

---

## GroupBy and Aggregation[​](#groupby "Direct link to GroupBy and Aggregation")

### Basic GroupBy[​](#basic-groupby "Direct link to Basic GroupBy")

|  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| pandas SQL|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | `df.groupby('city')['sales'].sum()` `SELECT city, SUM(sales) FROM ... GROUP BY city`| `df.groupby('city')['sales'].mean()` `SELECT city, AVG(sales) FROM ... GROUP BY city`| `df.groupby('city').size()` `SELECT city, COUNT(*) FROM ... GROUP BY city`| `df.groupby(['a', 'b'])['c'].sum()` `SELECT a, b, SUM(c) FROM ... GROUP BY a, b` | | | | | | | | | |

### Aggregation Functions[​](#aggregation-functions "Direct link to Aggregation Functions")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| pandas SQL|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `sum()` `SUM()`| `mean()` `AVG()`| `count()` `COUNT()`| `min()` `MIN()`| `max()` `MAX()`| `std()` `stddevPop()`| `var()` `varPop()`| `median()` `MEDIAN()`| `nunique()` `COUNT(DISTINCT col)`| `first()` `any()`| `last()` `anyLast()` | | | | | | | | | | | | | | | | | | | | | | | |

### Multiple Aggregations[​](#multiple-aggregations "Direct link to Multiple Aggregations")

```
# pandas
df.groupby('city').agg({
    'sales': ['sum', 'mean'],
    'quantity': 'sum'
})

# SQL
SELECT city, 
       SUM(sales) AS sales_sum, 
       AVG(sales) AS sales_mean,
       SUM(quantity) AS quantity_sum
FROM data
GROUP BY city
```

### HAVING Clause[​](#having-clause "Direct link to HAVING Clause")

```
# pandas style
df.groupby('city')['sales'].sum().query('sales > 10000')

# DataStore style
ds.groupby('city').agg({'sales': 'sum'}).having(ds['sum'] > 10000)

# SQL
SELECT city, SUM(sales) AS sum
FROM data
GROUP BY city
HAVING sum > 10000
```

---

## Joins[​](#joins "Direct link to Joins")

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| pandas SQL|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `pd.merge(df1, df2, on='id')` `JOIN df2 ON df1.id = df2.id`| `pd.merge(df1, df2, on='id', how='left')` `LEFT JOIN df2 ON ...`| `pd.merge(df1, df2, on='id', how='right')` `RIGHT JOIN df2 ON ...`| `pd.merge(df1, df2, on='id', how='outer')` `FULL OUTER JOIN df2 ON ...`| `pd.merge(df1, df2, left_on='a', right_on='b')` `JOIN df2 ON df1.a = df2.b` | | | | | | | | | | | |

### Join Example[​](#join-example "Direct link to Join Example")

```
# pandas
result = pd.merge(employees, departments, on='dept_id', how='left')

# SQL equivalent
SELECT *
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
```

---

## String Operations[​](#string "Direct link to String Operations")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| pandas SQL|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `df['col'].str.upper()` `upper(col)`| `df['col'].str.lower()` `lower(col)`| `df['col'].str.len()` `length(col)`| `df['col'].str.strip()` `trim(col)`| `df['col'].str.contains('x')` `position('x' IN col) > 0`| `df['col'].str.startswith('x')` `startsWith(col, 'x')`| `df['col'].str.endswith('x')` `endsWith(col, 'x')`| `df['col'].str.replace('a', 'b')` `replace(col, 'a', 'b')`| `df['col'].str[:5]` `substring(col, 1, 5)` | | | | | | | | | | | | | | | | | | | |

---

## 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")