Michael Brenndoerfer | Data & AI, Private Equity, Technology

Michael Brenndoerfer

Data Handling and Visualization › Data Manipulation

Michael BrenndoerferNovember 7, 20258 min read

Quantitative FinanceAll chapters

Part 1 of 15Mathematical Foundations

Chapter 9Data Handling and Visualization › Data Manipulation

Reading section 4 of 9 · Data Manipulation · 8 minSection 4 of 9 · 8 min

Section 4 of 9 · 8 min read

Data Handling and Visualization › Data ManipulationOne section · Sections in this chapter4 / 9 ↓

Sections in this chapter9

Reading tools Definitions & inline links

Choose your expertise level to adjust how many terms are explained. Beginners see more tooltips, experts see fewer to maintain reading flow. Hover over underlined terms for instant definitions.

Article links

Make inline references clickable

Beyond loading and time series operations, you need to filter, transform, and aggregate data for analysis. These manipulation skills form the bridge between raw data and actionable insights.

Computing ReturnsLink Copied

Converting prices to returns is a common first transformation. Returns are comparable across price levels and often have more stable location than price levels, although their distributions and volatility can still change over time.

Simple price returns measure the percentage price change from one period to the next. For an investment that moves from price $P_{t-1}$ to $P_t$ , the simple price return is:

R_t = \frac{P_t - P_{t-1}}{P_{t-1}}

where:

$R_t$ : simple return at time $t$ (fractional change, e.g., 0.05 for a 5% gain)
$P_t$ : price at time $t$
$P_{t-1}$ : price at the previous time period

The numerator $(P_t - P_{t-1})$ is the price change. For example, a stock moving from $100 to $105 gains $5 per share, so the price return is $5 / $100 = 0.05, or 5%. Price-only returns omit dividends, distributions, fees, financing, taxes, and corporate-action adjustments. Use adjusted prices or explicit cash flows when the objective is investor total return.

The simple-return formula requires a nonzero previous price. Financial price series are normally strictly positive, but imported data should still be checked before division.

Log returns use the natural logarithm of the price ratio and have attractive mathematical properties. Importantly, log returns sum across time periods:

r_t = \ln\left(\frac{P_t}{P_{t-1}}\right)

where:

$r_t$ : log return at time $t$ (continuous compounding rate)
$P_t$ : price at time $t$
$P_{t-1}$ : price at the previous time period $t-1$
$\ln$ : natural logarithm (base $e$ )

The logarithm transforms multiplicative price changes to additive returns, bridging discrete and continuous-time financial theory. Since $\ln(ab) = \ln(a) + \ln(b)$ , multi-period log returns sum simply. This additive property greatly simplifies calculations involving cumulative returns over many periods.

Mathematically, a real-valued logarithm requires a strictly positive price ratio and a nonzero denominator; two negative prices can also produce a positive ratio. For the financial price-return convention used here, require both prices to be finite and strictly positive. Reject or investigate invalid observations before applying np.log, rather than treating arbitrary replacement values as observed prices.

In[23]:

Code

# Calculate simple returns: (P_t - P_{t-1}) / P_{t-1}
simple_returns = multi_prices.pct_change()

# Calculate log returns: ln(P_t / P_{t-1})
log_returns = np.log(multi_prices / multi_prices.shift(1))

Out[24]:

Console

Simple returns (first 5 rows):
                AAPL     GOOGL      MSFT
2020-01-01       NaN       NaN       NaN
2020-01-02 -0.025254 -0.016012 -0.035917
2020-01-03  0.013200  0.023861  0.014130
2020-01-06  0.016007  0.008129  0.017067
2020-01-07  0.019225  0.005378  0.008473

Mean daily return:
AAPL     0.001398
GOOGL   -0.000494
MSFT     0.000621
dtype: float64

The first five daily price returns appear for each simulated series. The first row is NaN because no earlier price is available. The displayed means summarize this constructed sample only; their signs describe the realized average daily changes, not a general empirical property of the named securities.

Out[25]:

Visualization

Scatter plot comparing simple returns versus log returns with reference line. — Simulated AAPL-labelled simple and log returns lie close to the diagonal over the displayed range. They are not mathematically identical: log(1+R) equals R only to first order near zero.

Scatter plot showing difference between simple and log returns. — Difference between simulated simple and log returns in basis points. The leading approximation error is R²/2, so whether the discrepancy is negligible depends on the return size and the application's tolerance.

Simple vs. Log Returns

Simple returns computed here are price returns. With beginning-of-period weights, a fully invested portfolio, and explicit cash or financing legs where needed, same-period simple total returns aggregate across assets as:

R_p = \sum_i w_i R_i

where:

$R_p$ : total portfolio return, which is the weighted average of individual returns
$w_i$ : weight of asset $i$ in the portfolio, which is the fraction of total portfolio value where $\sum_i w_i = 1$
$R_i$ : simple return of asset $i$ over the period
$\sum_i$ : sum over all assets in the portfolio, where index $i$ ranges over all holdings

This linear aggregation holds for the stated one-period accounting setup. Each asset contributes in proportion to its beginning weight. For investor performance, the asset returns must include the relevant distributions and cash flows rather than unadjusted price changes alone.

Log returns aggregate additively across time. To find cumulative log return over multiple periods, sum the individual period log returns.

r_{0,T} = \sum_{t=1}^{T} r_t

where:

$r_{0,T}$ : cumulative log return from time 0 to time $T$
$r_t$ : log return for period $t$ , from time $t-1$ to time $t$
$T$ : final time period, which is the total number of periods
$\sum_{t=1}^{T}$ : sum over all periods from 1 to $T$

This additive property arises because $\ln(P_T/P_0) = \ln(P_T/P_{T-1}) + \ln(P_{T-1}/P_{T-2}) + \cdots + \ln(P_1/P_0)$ . Summing sub-period log returns gives cumulative log return over the horizon. Convert it to cumulative simple price return with $\exp(r_{0,T})-1$ , or np.expm1(log_returns.sum()) for the observed contiguous periods; the simple return itself is not a sum of period simple returns.

For $|R| \ll 1$ , $\ln(1+R) \approx R$ and the leading difference $R-\ln(1+R)$ is approximately $R^2/2$ . At a 10% simple return, the exact difference is about 46.9 basis points, which is not negligible for every use. Log returns are common in models because they add across time; simple returns are used for one-period portfolio accounting under the conditions above.

Filtering and SelectingLink Copied

Boolean indexing extracts data subsets that meet specific criteria, enabling event studies, anomaly detection, and conditional sample construction.

In[26]:

Code

# Find days where AAPL return exceeded 3% in absolute value
large_moves = simple_returns[simple_returns["AAPL"].abs() > 0.03]

# Find days where all three stocks moved in the same direction
same_direction = simple_returns[
    ((simple_returns > 0).all(axis=1)) | ((simple_returns < 0).all(axis=1))
]

Out[27]:

Console

Days with AAPL moves > 3%: 49
Days all stocks moved together: 127

The simulated AAPL-labelled series had moves exceeding 3% on several days. The same-sign filter counts dates when all three constructed returns have the same sign; it is a co-movement diagnostic, not a correlation estimate or evidence of market-wide dependence. Compute correlations separately and interpret them in light of the data-generating process.

Merging DatasetsLink Copied

Financial analysis often requires combining data from different sources such as prices with fundamentals, returns with factor exposures, or multiple time series with different starting dates.

In[28]:

Code

# Create a synthetic benchmark from the same constructed common factor
market_returns = pd.DataFrame(
    {"synthetic_market": np.expm1(market_factor)}, index=dates
)

# Join stock and market returns
combined = simple_returns.join(market_returns, how="inner")

Out[29]:

Console

Combined dataset:
                AAPL     GOOGL      MSFT  synthetic_market
2020-01-01       NaN       NaN       NaN          0.007040
2020-01-02 -0.025254 -0.016012 -0.035917         -0.012530
2020-01-03  0.013200  0.023861  0.014130          0.016441
2020-01-06  0.016007  0.008129  0.017067         -0.008715
2020-01-07  0.019225  0.005378  0.008473          0.007639

Shape: (252, 4)

Four columns appear: three simulated security returns plus the synthetic common-factor benchmark. The inner join keeps only index values present in both datasets; it does not by itself verify that timestamps, calendars, or return conventions are economically comparable. A real beta or attribution study would require sourced benchmark returns and those additional alignment checks.

Summary StatisticsLink Copied

Compute statistical summaries to understand your data's characteristics:

In[30]:

Code

# Descriptive statistics
stats = simple_returns.describe()

# Add skewness and kurtosis
stats.loc["skew"] = simple_returns.skew()
stats.loc["kurtosis"] = simple_returns.kurtosis()

Out[31]:

Console

Return statistics:
              AAPL     GOOGL      MSFT
count     251.0000  251.0000  251.0000
mean        0.0014   -0.0005    0.0006
std         0.0284    0.0249    0.0269
min        -0.0834   -0.0928   -0.0767
25%        -0.0133   -0.0128   -0.0136
50%        -0.0002    0.0011    0.0008
75%         0.0154    0.0119    0.0140
max         0.2197    0.1846    0.2060
skew        1.7774    1.3094    1.7720
kurtosis   13.6171   12.3428   13.2736

This view summarizes the constructed return sample. Mean and standard deviation describe its sample location and spread, while min and max show its observed endpoints. The three skewness estimates range from about 1.31 to 1.78, indicating pronounced positive sample asymmetry driven in part by a few large positive simulated observations. Positive sample excess kurtosis records a fourth standardized sample moment above the normal benchmark. These finite-sample quantities remain computable despite the transformed generator's population-moment limitation described earlier. Many empirical return series also exhibit heavy tails, but the synthetic result is not evidence about the named securities.