如何使用Pandas内置功能计算会计折旧（含递推公式）

Great question! Your current loop approach gets the job done, but we can refactor this to use more idiomatic Pandas operations—avoiding manual list conversions while keeping the logic clear. Let's break down how to implement your depreciation formula efficiently with Pandas tools.

Background on the Formula #

First, let's restate your formula to make the Pandas approach easier to map:

depreciation(t) = depr_rate * [cumulative_investment(t) - cumulative_depreciation(t-1)]

Where:

• cumulative_investment(t) is the sum of all investments up to period t
• cumulative_depreciation(t-1) is the sum of all depreciation amounts from periods 1 to t-1

We can rewrite this as a recursive cumulative depreciation calculation:
cumulative_depreciation(t) = (1 - depr_rate) * cumulative_depreciation(t-1) + depr_rate * cumulative_investment(t)

Once we have the cumulative depreciation values, the period-specific depreciation is simply the difference between consecutive cumulative values (with cumulative_depreciation(0) = 0).

Step-by-Step Implementation #

Let's walk through this with your sample data:

1. Prepare the Data #

First, create your sample DataFrame and calculate cumulative investments using Pandas' built-in cumsum():

import pandas as pd

# Sample data matching your example
df_inv = pd.DataFrame({'investment': [100, 100, 0]})
depr_rate = 0.3

# Calculate cumulative investments (Pandas native function)
df_inv['investment_cumsum'] = df_inv['investment'].cumsum()

2. Calculate Cumulative Depreciation #

We'll use a custom accumulator function that leverages Pandas' Series structure to handle the recursive calculation. This avoids manual list iteration while keeping the logic tied to Pandas' data structures:

def calculate_cumulative_depreciation(cum_inv_series, rate):
    cum_depr = 0.0
    cum_depr_values = []
    for cum_inv in cum_inv_series:
        # Apply the recursive formula
        cum_depr = (1 - rate) * cum_depr + rate * cum_inv
        cum_depr_values.append(cum_depr)
    # Return as a Pandas Series to preserve indexing
    return pd.Series(cum_depr_values, index=cum_inv_series.index)

# Compute cumulative depreciation
df_inv['cumulative_depreciation'] = calculate_cumulative_depreciation(df_inv['investment_cumsum'], depr_rate)

3. Derive Period-Specific Depreciation #

Finally, calculate the period-by-period depreciation by taking the difference between consecutive cumulative depreciation values (using shift() to get the prior period's cumulative value):

df_inv['depreciations'] = df_inv['cumulative_depreciation'] - df_inv['cumulative_depreciation'].shift(fill_value=0)

4. Verify the Result #

Running this code will produce exactly the values you expected:

Why This Is Better Than Manual List Iteration #

• Idiomatic Pandas: Uses Series and built-in functions like cumsum() and shift() to maintain alignment with your DataFrame's index.
• Maintainable: The logic is encapsulated in a function that's easy to modify or reuse for other datasets.
• Scalable: For larger datasets, you can easily optimize the accumulator function with tools like numba (if needed) while keeping the Pandas integration intact.

内容的提问来源于stack exchange，提问作者tfranch