如何在DataFrame中基于上一行非空值与Amount列完成Balance Created列的空值计算填充？

Got it, let's break down how to solve this problem. First, I'll start with a concrete example to make the logic clear, then show you two approaches depending on whether you need to use the original last valid value or the updated filled values for subsequent rows.

Step 1: Create Example DataFrame #

Let's first set up a sample DataFrame that matches your scenario:

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'Balance Created': [100, np.nan, np.nan, 150, np.nan],
    'Amount': [20, 30, 40, 10, 5]
})

This gives us:

Approach 1: Use Original Last Valid Value (Non-Iterative) #

If you want each empty Balance Created value to be calculated using the original last non-null value (not the filled values from previous rows) plus the next row's Amount, you can use pandas' built-in functions for a clean, vectorized solution:

# Get the last valid balance carried forward from original data
df['last_valid_balance'] = df['Balance Created'].ffill()

# Get the Amount from the next row
df['next_amount'] = df['Amount'].shift(-1)

# Fill empty values with the sum
df.loc[df['Balance Created'].isna(), 'Balance Created'] = df['last_valid_balance'] + df['next_amount']

# Clean up auxiliary columns
df = df.drop(['last_valid_balance', 'next_amount'], axis=1)

Result: #

Approach 2: Iterative Fill (Use Updated Filled Values) #

If you need to chain the calculations (each filled value becomes the "last valid" value for the next empty row), you'll need an iterative approach since we're updating values as we go:

# Initialize variable to track the last valid balance
last_valid_balance = None

for idx, row in df.iterrows():
    # Update last valid balance if current row has a non-null value
    if pd.notna(row['Balance Created']):
        last_valid_balance = row['Balance Created']
    else:
        # Only fill if there's a next row to get Amount from
        if idx + 1 < len(df):
            next_amount = df.loc[idx + 1, 'Amount']
            filled_value = last_valid_balance + next_amount
            df.loc[idx, 'Balance Created'] = filled_value
            # Update last valid balance to the newly filled value
            last_valid_balance = filled_value

Result: #

Notes: #

• For the last row's empty value: If you want to handle it (e.g., use the last valid balance instead of leaving it NaN), you can add an else clause in the iterative approach to set it to last_valid_balance.
• Vectorized operations (Approach 1) are faster for large datasets, while the iterative approach is better for chained calculations.

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