Skip to main content
Skip to content
Agentic Finance

9 Practical Pandas Workflows for Onchain and Crypto Market Data

Nine reproducible Pandas workflows for cleaning blockchain data, resampling prices, joining wallets, measuring liquidity, funding, gas, impermanent loss and market regimes.

Python Pandas workflow stack for cleaning, joining, resampling and testing onchain and crypto market datasets
Reading options: audio, text size & sections

What Matters

Pandas is most valuable when it turns messy time-stamped crypto data into a reproducible table before any model makes a claim. The core work is not drawing charts. It is fixing timestamps, token identifiers, decimals, duplicate events, missing blocks, chain reorgs, survivorship bias and inconsistent market definitions.

DN Evidence Block

Reviewed: 18 November 2026. Primary evidence: current Pandas documentation for merge, groupby, rolling and resample operations; current API documentation from CoinGecko, Dune and DefiLlama.

Boundary: examples are educational and simplified. API schemas, rate limits and endpoint access change. Verify licensing and commercial-use rights before deploying a dataset or product.

The Nine Workflows

Workflow Pandas primitives Input Output Main error to catch
1. Normalize RPC and API JSON json_normalize, astype, to_datetime Nested transactions, logs or API responses Typed event table Hex values, token decimals and duplicated rows
2. Resample trades to OHLCV set_index, resample, agg DEX or CEX trades Minute/hour/day candles Timezone, gaps and wash volume
3. Join wallets and token metadata merge, join, map Transfers, labels, token registries Enriched wallet flow table Many-to-many explosions and symbol collisions
4. Measure gas-event windows rolling, shift, merge_asof Gas series plus market events Before/after event study Look-ahead bias and timestamp mismatch
5. Calculate exchange-flow velocity groupby, resample, diff Labeled inflows/outflows Net flow and acceleration Incomplete labels and internal transfers
6. Test funding-rate regimes groupby, rolling, cut, corr Funding, basis, OI and returns Regime statistics Changing contract definitions and annualization
7. Aggregate cross-DEX liquidity concat, groupby, pivot_table Quotes or pool depth Executable depth by route and size Comparing nominal TVL instead of executable price
8. Model impermanent loss pct_change, cumprod, vectorized formulas Asset prices, fees and positions LP versus hold return Ignoring range changes, gas and fee reinvestment
9. Build a market-stress index rank, zscore, rolling, weighted sum Price, liquidity, funding, OI and stablecoin data Versioned composite signal Unstable weights and hidden missing-data rules

1. Normalize Nested Blockchain Data

Raw node and explorer responses often mix hexadecimal values, nested logs, strings and missing fields. Start by preserving the source payload and extracting a typed analytical table. Token amounts require contract-specific decimals; a symbol is not a unique identifier.

raw = pd.json_normalize(events)
raw[‘block_time’] = pd.to_datetime(raw[‘block_time’], utc=True)
raw[‘value_raw’] = raw[‘value’].map(parse_hex)
raw[‘amount’] = raw[‘value_raw’] / (10 ** raw[‘decimals’])
raw = raw.drop_duplicates([‘chain_id’,’tx_hash’,’log_index’])

For reorg-sensitive work, record block number and block hash, then re-query recent blocks before finalizing results.

2. Convert Trades Into OHLCV

CoinGecko’s onchain endpoints expose trades and OHLCV for supported pools. If using raw trades, Pandas can resample them into candles. Define the price direction consistently: token A per token B is not interchangeable with the inverse.

trades = trades.set_index(‘timestamp’).sort_index()
ohlcv = trades.resample(‘5min’).agg(open=(‘price’,’first’), high=(‘price’,’max’), low=(‘price’,’min’), close=(‘price’,’last’), volume=(‘usd_volume’,’sum’), trades=(‘price’,’count’))
ohlcv = ohlcv.dropna(subset=[‘open’])

Do not forward-fill price candles across a venue outage without labeling the gap. A flat line may be missing data rather than a stable market.

3. Join Wallets, Labels and Token Metadata

Wallet research often requires several identifier tables: addresses, entity labels, token contracts, decimals and chain IDs. Use contract address plus chain as the token key. A ticker such as USDC can represent native and bridged assets with different risk.

Before a merge, check uniqueness on the intended key. A many-to-many join can multiply rows and fabricate volume. Pandas merge validation helps identify unexpected relationships.

enriched = transfers.merge(token_registry, on=[‘chain_id’,’token_address’], how=’left’, validate=’many_to_one’)
enriched = enriched.merge(wallet_labels, left_on=[‘chain_id’,’to_address’], right_on=[‘chain_id’,’address’], how=’left’, validate=’many_to_one’)

4. Study Gas Spikes Around Events

To test whether gas spikes precede or follow market stress, align event timestamps with a continuous gas series using UTC and a declared tolerance. Use merge_asof for nearest prior observations rather than accidentally joining future data.

Separate base fee, priority fee and L2 data fee where available. A single “gas price” field may not describe the user’s total cost.

5. Measure Exchange-Flow Velocity

Net exchange flow is inflow minus outflow for labeled addresses. Velocity measures the change in that flow over time. The interpretation depends on label coverage: internal wallet reshuffling can look like deposits, while unlabeled custodial addresses can disappear from the series.

daily = flows.groupby([pd.Grouper(key=’timestamp’, freq=’1D’),’direction’])[‘usd_value’].sum().unstack(fill_value=0)
daily[‘net_inflow’] = daily.get(‘inflow’,0) – daily.get(‘outflow’,0)
daily[‘flow_velocity_7d’] = daily[‘net_inflow’].diff(7)

6. Test Funding-Rate Regimes

Funding analysis should combine rate, basis, open interest, volume and price returns. Annualizing one eight-hour rate by multiplication can exaggerate unstable conditions. Use realized cumulative funding over the actual interval and preserve exchange-specific payment schedules.

A practical regime table groups observations into funding bands and reports subsequent returns, drawdown, spread and sample count. Never report a mean without the number of observations and distribution.

7. Aggregate Cross-DEX Liquidity by Executable Size

TVL is not execution quality. Quote the same asset pair and trade size across venues, normalize gas and fees, and preserve the timestamp. Use a pivot table to compare output by venue and size. The best route for $1,000 may be poor for $100,000.

Quotes expire quickly. Store block number, route, pool, gas assumption and whether the quote was simulated or executed.

8. Compare LP Return With Holding

Impermanent loss analysis needs the exact pool type. Constant-product formulas do not describe concentrated liquidity positions. Track deposits, withdrawals, range changes, fees, rewards, gas and token prices. Compare the portfolio value of the actual LP position with a matched hold benchmark at each timestamp.

Do not call fee revenue “yield” without subtracting rebalancing and claim costs.

9. Build a Versioned Market-Stress Index

A composite index may combine drawdown, volatility, funding, open-interest change, liquidity depth and stablecoin deviation. Standardize each component on a trailing window, cap extreme values and document missing-data behavior.

Version the formula. If weights or data sources change, historical scores should not silently become incomparable.

DN Rule

Every crypto DataFrame needs an identity policy. Define chain, contract, venue, quote currency, timestamp, block and data-source version before calculating a signal.

DN Pandas Workflow Selector

Choose the analytical question. The tool recommends the core Pandas pattern and validation check.

Select the closest analytical task.

The recommendation does not validate the source data or economic interpretation.

Worked Example: Funding and Liquidation Stress

A researcher combines hourly BTC returns, funding, open interest and liquidation volume. Each source uses a different timezone and contract identifier. The correct sequence is to map contracts, convert timestamps to UTC, resample to one-hour windows, record missing periods, lag predictors and calculate future outcomes separately.

The researcher then groups funding into regimes and reports sample count, median future return, worst drawdown and liquidation intensity. This is more useful than training a model on a table where future values accidentally leaked into rolling features.

Data Sources and Commercial Paths

CoinGecko provides market and onchain API data; Dune exposes query-driven blockchain datasets; DefiLlama provides protocol and yield data, although endpoints and access terms changed in November 2026. DN has affiliate relationships with CoinStats and TradingView. Those services solve different parts of the workflow and do not replace source validation.

Methodology and Limitations

DN selected workflows that recur across onchain research, execution analysis and risk monitoring. Code is intentionally minimal and omits authentication, retries, pagination, schema validation and tests. Pandas is not always the right tool for tick-scale or very large distributed datasets.

Frequently Asked Questions

Is Pandas fast enough for live trading?

It is excellent for research and moderate-frequency pipelines, but latency-sensitive execution often needs narrower in-memory structures or compiled systems.

Should I use token symbols as keys?

No. Use chain ID and contract address, then attach symbols as labels.

How do I prevent look-ahead bias?

Use only information available at the decision timestamp, lag features where necessary and separate future outcomes.

Can I combine CEX and DEX prices directly?

Only after normalizing quote direction, fees, size, timestamp, venue quality and executable liquidity.

What should every exported dataset include?

Source, retrieval time, schema/version, chain, contract, block or timestamp, units, missing-data rules and transformation history.

Primary Sources

Commercial disclosure: DN may earn compensation from CoinStats and TradingView links. No data provider paid for inclusion.

Get the most talked about stories directly in your inbox

Join the Decentralised News briefing for independent crypto, DeFi and AI analysis. No spam, unsubscribe anytime.