title_wip

### 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'
)
```

### Melt (Unpivot)[​](#melt-unpivot "Direct link to Melt (Unpivot)")

```
# Pandas and DataStore - identical
df.melt(
    id_vars=['name'],
    value_vars=['score1', 'score2', 'score3'],
    var_name='test',
    value_name='score'
)
```

### Explode[​](#explode "Direct link to Explode")

```
# Pandas and DataStore - identical
df.explode('tags')  # Expand array column
```

---

## Window Functions[​](#window "Direct link to Window Functions")

### Rolling[​](#rolling "Direct link to Rolling")

```
# Pandas and DataStore - identical
df['rolling_avg'] = df['price'].rolling(window=7).mean()
df['rolling_sum'] = df['amount'].rolling(window=30).sum()
```

### Expanding[​](#expanding "Direct link to Expanding")

```
# Pandas and DataStore - identical
df['cumsum'] = df['amount'].expanding().sum()
df['cummax'] = df['amount'].expanding().max()
```

### Shift[​](#shift "Direct link to Shift")

```
# Pandas and DataStore - identical
df['prev_value'] = df['value'].shift(1)   # Lag
df['next_value'] = df['value'].shift(-1)  # Lead
```

### Diff[​](#diff "Direct link to Diff")

```
# Pandas and DataStore - identical
df['change'] = df['value'].diff()
df['pct_change'] = df['value'].pct_change()
```

---

## Output[​](#output "Direct link to Output")

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

```
# Pandas and DataStore - identical
df.to_csv("output.csv", index=False)
```

### To Parquet[​](#to-parquet "Direct link to To Parquet")

```
# Pandas and DataStore - identical
df.to_parquet("output.parquet")
```

### To pandas DataFrame[​](#to-pandas-dataframe "Direct link to To pandas DataFrame")

```
# DataStore specific
pandas_df = ds.to_df()
pandas_df = ds.to_pandas()
```

---

## DataStore Extras[​](#extras "Direct link to DataStore Extras")

### View SQL[​](#view-sql "Direct link to View SQL")

```
# DataStore only
print(ds.to_sql())
```

### Explain Plan[​](#explain-plan "Direct link to Explain Plan")

```
# DataStore only
ds.explain()
```

### ClickHouse Functions[​](#clickhouse-functions "Direct link to ClickHouse Functions")

```
# DataStore only - extra accessors
df['domain'] = df['url'].url.domain()
df['json_value'] = df['data'].json.get_string('key')
df['ip_valid'] = df['ip'].ip.is_ipv4_string()
```

### Universal URI[​](#universal-uri "Direct link to Universal URI")

```
# DataStore only - read from anywhere
ds = DataStore.uri("s3://bucket/data.parquet")
ds = DataStore.uri("mysql://user:pass@host/db/table")
```

- [Data Loading](#loading)
  - [Read CSV](#read-csv)- [Read Multiple Files](#read-multiple-files)- [Filtering](#filtering)
    - [Single Condition](#single-condition)- [Multiple Conditions](#multiple-conditions)- [Using query()](#using-query)- [isin()](#isin)- [between()](#between)- [Selecting Columns](#selecting)
      - [Single Column](#single-column-select)- [Multiple Columns](#multiple-columns-select)- [Select and Filter](#select-and-filter)- [Sorting](#sorting)
        - [Single Column](#single-column-sort)- [Multiple Columns](#multiple-columns-sort)- [Get Top/Bottom N](#get-top-bottom-n)- [GroupBy and Aggregation](#groupby)
          - [Simple GroupBy](#simple-groupby)- [Multiple Aggregations](#multiple-aggregations)- [Named Aggregations](#named-aggregations)- [Multiple GroupBy Keys](#multiple-groupby-keys)- [Joining Data](#joining)
            - [Inner Join](#inner-join)- [Left Join](#left-join)- [Join on Different Columns](#join-on-different-columns)- [Concat](#concat)- [String Operations](#string)
              - [Case Conversion](#case-conversion)- [Substring](#substring)- [Search](#search)- [Replace](#replace)- [Split](#split)- [Length](#length)- [DateTime Operations](#datetime)
                - [Extract Components](#extract-components)- [Formatting](#formatting)- [Missing Data](#missing)
                  - [Check Missing](#check-missing)- [Drop Missing](#drop-missing)- [Fill Missing](#fill-missing)- [Creating New Columns](#new-columns)
                    - [Simple Assignment](#simple-assignment)- [Using assign()](#using-assign)- [Conditional (where/mask)](#conditional-where-mask)- [apply() for Custom Logic](#apply-for-custom-logic)- [Reshaping](#reshaping)
                      - [Pivot Table](#pivot-table)- [Melt (Unpivot)](#melt-unpivot)- [Explode](#explode)- [Window Functions](#window)
                        - [Rolling](#rolling)- [Expanding](#expanding)- [Shift](#shift)- [Diff](#diff)- [Output](#output)
                          - [To CSV](#to-csv)- [To Parquet](#to-parquet)- [To pandas DataFrame](#to-pandas-dataframe)- [DataStore Extras](#extras)
                            - [View SQL](#view-sql)- [Explain Plan](#explain-plan)- [ClickHouse Functions](#clickhouse-functions)- [Universal URI](#universal-uri)

Was this page helpful?