title_wip

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

While DataStore is highly compatible with pandas, there are important differences to understand.

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

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Aspect pandas DataStore|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **Execution** Eager (immediate) Lazy (deferred)|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **Return types** DataFrame/Series DataStore/ColumnExpr|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **Row order** Preserved Preserved (automatic); not guaranteed in [performance mode](/docs/chdb/configuration/performance-mode)| **inplace** Supported Not supported|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | **Index** Full support Simplified|  |  |  | | --- | --- | --- | | **Memory** All data in memory Data at source | | | | | | | | | | | | | | | | | | | | |

---

## 1. Lazy vs Eager Execution[​](#lazy-execution "Direct link to 1. Lazy vs Eager Execution")

### pandas (Eager)[​](#pandas-eager "Direct link to pandas (Eager)")

Operations execute immediately:

```
import pandas as pd

df = pd.read_csv("data.csv")  # Loads entire file NOW
result = df[df['age'] > 25]   # Filters NOW
grouped = result.groupby('city')['salary'].mean()  # Aggregates NOW
```

### DataStore (Lazy)[​](#datastore-lazy "Direct link to DataStore (Lazy)")

Operations are deferred until results are needed:

```
from chdb import datastore as pd

ds = pd.read_csv("data.csv")  # Just records the source
result = ds[ds['age'] > 25]   # Just records the filter
grouped = result.groupby('city')['salary'].mean()  # Just records

# Execution happens here:
print(grouped)        # Executes when displaying
df = grouped.to_df()  # Or when converting to pandas
```

### Why It Matters[​](#why-lazy "Direct link to Why It Matters")

Lazy execution enables:

- **Query optimization**: Multiple operations compile to one SQL query
- **Column pruning**: Only needed columns are read
- **Filter pushdown**: Filters apply at the source
- **Memory efficiency**: Don't load data you don't need

---

## 2. Return Types[​](#return-types "Direct link to 2. Return Types")

### pandas[​](#pandas-return-types "Direct link to pandas")

```
df['col']           # Returns pd.Series
df[['a', 'b']]      # Returns pd.DataFrame
df[df['x'] > 10]    # Returns pd.DataFrame
df.groupby('x')     # Returns DataFrameGroupBy
```

### DataStore[​](#datastore-return-types "Direct link to DataStore")

```
ds['col']           # Returns ColumnExpr (lazy)
ds[['a', 'b']]      # Returns DataStore (lazy)
ds[ds['x'] > 10]    # Returns DataStore (lazy)
ds.groupby('x')     # Returns LazyGroupBy
```

### Converting to pandas Types[​](#converting-to-pandas-types "Direct link to Converting to pandas Types")

```
# Get pandas DataFrame
df = ds.to_df()
df = ds.to_pandas()

# Get pandas Series from column
series = ds['col'].to_pandas()

# Or trigger execution
print(ds)  # Automatically converts for display
```

---

## 3. Execution Triggers[​](#triggers "Direct link to 3. Execution Triggers")

DataStore executes when you need actual values:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Trigger Example Notes|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `print()` / `repr()` `print(ds)` Display needs data|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `len()` `len(ds)` Need row count|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `.columns` `ds.columns` Need column names|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `.dtypes` `ds.dtypes` Need type info|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `.shape` `ds.shape` Need dimensions|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `.values` `ds.values` Need actual data|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `.index` `ds.index` Need index|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `to_df()` `ds.to_df()` Explicit conversion|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | Iteration `for row in ds` Need to iterate|  |  |  | | --- | --- | --- | | `equals()` `ds.equals(other)` Need comparison | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

### Operations That Stay Lazy[​](#stay-lazy "Direct link to Operations That Stay Lazy")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Operation Returns|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `filter()` DataStore|  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `select()` DataStore|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `sort()` DataStore|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `groupby()` LazyGroupBy|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | `join()` DataStore|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `ds['col']` ColumnExpr|  |  |  |  | | --- | --- | --- | --- | | `ds[['a', 'b']]` DataStore|  |  | | --- | --- | | `ds[condition]` DataStore | | | | | | | | | | | | | | | | | |

---

## 4. Row Order[​](#row-order "Direct link to 4. Row Order")

### pandas[​](#pandas-row-order "Direct link to pandas")

Row order is always preserved:

```
df = pd.read_csv("data.csv")
print(df.head())  # Always same order as file
```

### DataStore[​](#datastore-row-order "Direct link to DataStore")

Row order is **automatically preserved** for most operations:

```
ds = pd.read_csv("data.csv")
print(ds.head())  # Matches file order

# Filter preserves order
ds_filtered = ds[ds['age'] > 25]  # Same order as pandas
```

DataStore automatically tracks original row positions internally (using `rowNumberInAllBlocks()`) to ensure order consistency with pandas.

### When Order Is Preserved[​](#order-preserved "Direct link to When Order Is Preserved")

- File sources (CSV, Parquet, JSON, etc.)
- pandas DataFrame sources
- Filter operations
- Column selection
- After explicit `sort()` or `sort_values()`
- Operations that define order (`nlargest()`, `nsmallest()`, `head()`, `tail()`)