title_wip

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

Common pandas patterns and their DataStore equivalents. Most code works unchanged!

## Data Loading[​](#loading "Direct link to Data Loading")

### Read CSV[​](#read-csv "Direct link to Read CSV")

```
# Pandas
import pandas as pd
df = pd.read_csv("data.csv")

# DataStore - same!
from chdb import datastore as pd
df = pd.read_csv("data.csv")
```

### Read Multiple Files[​](#read-multiple-files "Direct link to Read Multiple Files")

```
# Pandas
import glob
dfs = [pd.read_csv(f) for f in glob.glob("data/*.csv")]
df = pd.concat(dfs)

# DataStore - more efficient with glob pattern
df = pd.read_csv("data/*.csv")
```

---

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

### Single Condition[​](#single-condition "Direct link to Single Condition")

```
# Pandas and DataStore - identical
df[df['age'] > 25]
df[df['city'] == 'NYC']
df[df['name'].str.contains('John')]
```

### Multiple Conditions[​](#multiple-conditions "Direct link to Multiple Conditions")

```
# AND
df[(df['age'] > 25) & (df['city'] == 'NYC')]

# OR
df[(df['age'] < 18) | (df['age'] > 65)]

# NOT
df[~(df['status'] == 'inactive')]
```

### Using query()[​](#using-query "Direct link to Using query()")

```
# Pandas and DataStore - identical
df.query('age > 25 and city == "NYC"')
df.query('salary > 50000')
```

### isin()[​](#isin "Direct link to isin()")

```
# Pandas and DataStore - identical
df[df['city'].isin(['NYC', 'LA', 'SF'])]
```

### between()[​](#between "Direct link to between()")

```
# Pandas and DataStore - identical
df[df['age'].between(18, 65)]
```

---

## Selecting Columns[​](#selecting "Direct link to Selecting Columns")

### Single Column[​](#single-column-select "Direct link to Single Column")

```
# Pandas and DataStore - identical
df['name']
df.name  # attribute access
```

### Multiple Columns[​](#multiple-columns-select "Direct link to Multiple Columns")

```
# Pandas and DataStore - identical
df[['name', 'age', 'city']]
```

### Select and Filter[​](#select-and-filter "Direct link to Select and Filter")

```
# Pandas and DataStore - identical
df[df['age'] > 25][['name', 'salary']]

# DataStore also supports SQL-style
df.filter(df['age'] > 25).select('name', 'salary')
```

---

## Sorting[​](#sorting "Direct link to Sorting")

### Single Column[​](#single-column-sort "Direct link to Single Column")

```
# Pandas and DataStore - identical
df.sort_values('salary')
df.sort_values('salary', ascending=False)
```

### Multiple Columns[​](#multiple-columns-sort "Direct link to Multiple Columns")

```
# Pandas and DataStore - identical
df.sort_values(['city', 'salary'], ascending=[True, False])
```

### Get Top/Bottom N[​](#get-top-bottom-n "Direct link to Get Top/Bottom N")

```
# Pandas and DataStore - identical
df.nlargest(10, 'salary')
df.nsmallest(5, 'age')
```

---

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

### Simple GroupBy[​](#simple-groupby "Direct link to Simple GroupBy")

```
# Pandas and DataStore - identical
df.groupby('city')['salary'].mean()
df.groupby('city')['salary'].sum()
df.groupby('city').size()  # count
```

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

```
# Pandas and DataStore - identical
df.groupby('city')['salary'].agg(['sum', 'mean', 'count'])

df.groupby('city').agg({
    'salary': ['sum', 'mean'],
    'age': ['min', 'max']
})
```

### Named Aggregations[​](#named-aggregations "Direct link to Named Aggregations")

```
# Pandas and DataStore - identical
df.groupby('city').agg(
    total_salary=('salary', 'sum'),
    avg_salary=('salary', 'mean'),
    employee_count=('id', 'count')
)
```

### Multiple GroupBy Keys[​](#multiple-groupby-keys "Direct link to Multiple GroupBy Keys")

```
# Pandas and DataStore - identical
df.groupby(['city', 'department'])['salary'].mean()
```

---

## Joining Data[​](#joining "Direct link to Joining Data")

### Inner Join[​](#inner-join "Direct link to Inner Join")

```
# Pandas
pd.merge(df1, df2, on='id')

# DataStore - same API
pd.merge(df1, df2, on='id')

# DataStore also supports
df1.join(df2, on='id')
```

### Left Join[​](#left-join "Direct link to Left Join")

```
# Pandas and DataStore - identical
pd.merge(df1, df2, on='id', how='left')
```

### Join on Different Columns[​](#join-on-different-columns "Direct link to Join on Different Columns")

```
# Pandas and DataStore - identical
pd.merge(df1, df2, left_on='emp_id', right_on='id')
```

### Concat[​](#concat "Direct link to Concat")

```
# Pandas and DataStore - identical
pd.concat([df1, df2, df3])
pd.concat([df1, df2], axis=1)
```

---

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

### Case Conversion[​](#case-conversion "Direct link to Case Conversion")

```
# Pandas and DataStore - identical
df['name'].str.upper()
df['name'].str.lower()
df['name'].str.title()
```

### Substring[​](#substring "Direct link to Substring")

```
# Pandas and DataStore - identical
df['name'].str[:3]        # First 3 characters
df['name'].str.slice(0, 3)
```

### Search[​](#search "Direct link to Search")

```
# Pandas and DataStore - identical
df['name'].str.contains('John')
df['name'].str.startswith('A')
df['name'].str.endswith('son')
```

### Replace[​](#replace "Direct link to Replace")

```
# Pandas and DataStore - identical
df['text'].str.replace('old', 'new')
df['text'].str.replace(r'\d+', '', regex=True)  # Remove digits
```

### Split[​](#split "Direct link to Split")

```
# Pandas and DataStore - identical
df['name'].str.split(' ')
df['name'].str.split(' ', expand=True)
```

### Length[​](#length "Direct link to Length")

```
# Pandas and DataStore - identical
df['name'].str.len()
```

---

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

### Extract Components[​](#extract-components "Direct link to Extract Components")

```
# Pandas and DataStore - identical
df['date'].dt.year
df['date'].dt.month
df['date'].dt.day
df['date'].dt.dayofweek
df['date'].dt.hour
```

### Formatting[​](#formatting "Direct link to Formatting")

```
# Pandas and DataStore - identical
df['date'].dt.strftime('%Y-%m-%d')
```

---

## Missing Data[​](#missing "Direct link to Missing Data")

### Check Missing[​](#check-missing "Direct link to Check Missing")

```
# Pandas and DataStore - identical
df['col'].isna()
df['col'].notna()
df.isna().sum()
```

### Drop Missing[​](#drop-missing "Direct link to Drop Missing")

```
# Pandas and DataStore - identical
df.dropna()
df.dropna(subset=['col1', 'col2'])
```

### Fill Missing[​](#fill-missing "Direct link to Fill Missing")

```
# Pandas and DataStore - identical
df.fillna(0)
df.fillna({'col1': 0, 'col2': 'Unknown'})
df.fillna(method='ffill')
```

---

## Creating New Columns[​](#new-columns "Direct link to Creating New Columns")

### Simple Assignment[​](#simple-assignment "Direct link to Simple Assignment")

```
# Pandas and DataStore - identical
df['total'] = df['price'] * df['quantity']
df['age_group'] = df['age'] // 10 * 10
```

### Using assign()[​](#using-assign "Direct link to Using assign()")

```
# Pandas and DataStore - identical
df = df.assign(
    total=df['price'] * df['quantity'],
    is_adult=df['age'] >= 18
)
```

### Conditional (where/mask)[​](#conditional-where-mask "Direct link to Conditional (where/mask)")

```
# Pandas and DataStore - identical
df['status'] = df['age'].where(df['age'] >= 18, 'minor')
```

### apply() for Custom Logic[​](#apply-for-custom-logic "Direct link to apply() for Custom Logic")

```
# Works, but triggers pandas execution
df['category'] = df['amount'].apply(lambda x: 'high' if x > 1000 else 'low')

# DataStore alternative (stays lazy)
df['category'] = (
    df.when(df['amount'] > 1000, 'high')
      .otherwise('low')
)
```

---

## Reshaping[​](#reshaping "Direct link to Reshaping")

### Pivot Table[​](#pivot-table "Direct link to Pivot Table")

```
# Pandas and DataStore - identical
df.pivot_table(
    values='amount',
    index='region',
    columns='product',
    aggfunc='sum'
)
```