Pandas DataFrame：创建新列获取满足累积和条件的最后行值

Hey there! Let's work through this problem together—since you're new to Python and Pandas, I'll break down each step clearly so you understand how we get to the desired output.

First, let's start by recreating your DataFrame to make sure we're on the same page:

import pandas as pd
import numpy as np

# Create the initial DataFrame
data = {
    'part_1': [4, 5, 6, 4, 8, 4],
    'part_2': [11, 12, 10, 12, 14, 13]
}
df = pd.DataFrame(data)

Your goal is to create a new column new where each value is the last part_1 entry from the cumulative sum starting at the current row, once that cumulative sum meets or exceeds the corresponding part_2 value (adjusting to "greater than or equal" matches your expected result for index 3). If the cumulative sum never reaches this threshold, we'll use NaN.

Step 1: Define a helper function #

We'll write a small function that, for each row, checks the cumulative sum starting from that row's index and finds the right part_1 value:

def get_target_part1(row):
    # Get the starting index of the current row
    start_idx = row.name
    # Extract part_1 values starting from this row to the end
    part1_subset = df.loc[start_idx:, 'part_1']
    # Calculate cumulative sums for this subset
    cumulative_sums = part1_subset.cumsum()
    # Find where the cumulative sum meets or exceeds the row's part_2 value
    meets_condition = cumulative_sums >= row['part_2']
    
    # If any entry meets the condition
    if meets_condition.any():
        # Get the first index where the condition is true
        target_index = meets_condition.idxmax()
        # Return the corresponding part_1 value
        return df.loc[target_index, 'part_1']
    else:
        # No entry meets the condition, return NaN
        return np.nan

Step 2: Apply the function to create the new column #

Use Pandas' apply() method to run this function on every row of the DataFrame:

df['new'] = df.apply(get_target_part1, axis=1)

Step 3: Check the result #

If you print df now, you'll get exactly the output you wanted:

part_1  part_2   new
0       4      11   6.0
1       5      12   4.0
2       6      10   8.0
3       4      12   8.0
4       8      14   NaN
5       4      13   NaN

Let's verify the logic matches your examples: #

• Index 0: Cumulative sums start at 4 → 9 → 15. 15 >= 11, so we take the part_1 value at index 2 (where this first happens), which is 6.
• Index 3: Cumulative sums start at 4 → 12. 12 >= 12, so we take the part_1 value at index 4, which is 8.
• Indexes 4 & 5: The cumulative sums never exceed their respective part_2 values, so we get NaN.

This approach is straightforward for small DataFrames like yours. If you work with larger datasets later, we can optimize this to avoid apply() (which can be slow for big data), but this is perfect for getting started.

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