Pandas透视表cumsum()：如何跨列传递非NaN值？

Absolutely! You can easily propagate the previous non-NaN value across columns in the same row after performing a cumsum() on your Pandas pivot table. This is exactly what the ffill() (forward fill) method was designed for—let’s break down how to implement it.

Step 1: Reproduce the Scenario #

First, let’s create a sample pivot table that mirrors your situation (with NaNs after cumsum()):

import pandas as pd

# Sample data matching your use case
data = {
    'Group': ['X', 'X', 'X', 'Y', 'Y'],
    'Period': ['P1', 'P3', 'P4', 'P2', 'P4'],
    'Value': [1, 0, 0, 1, 0]
}

df = pd.DataFrame(data)

# Create pivot table without filling missing periods (so NaNs exist)
pivot = df.pivot_table(index='Group', columns='Period', values='Value')

# Calculate cumulative sum across columns
pivot_cumsum = pivot.cumsum(axis=1)
print("Original cumsum result with NaNs:")
print(pivot_cumsum)

This will output something like:

Period   P1   P2   P3   P4
Group
X      1.0  NaN  1.0  1.0
Y      NaN  1.0  NaN  1.0

Notice the NaNs in row X (P2) and row Y (P1, P3)—these are the positions you want to fill with the previous non-NaN value from the same row.

Step 2: Forward Fill to Propagate Values #

Use ffill(axis=1) to fill NaNs with the nearest non-NaN value from the left (same row, previous column):

# Propagate previous values across columns
filled_pivot = pivot_cumsum.ffill(axis=1)
print("\nFilled result with propagated values:")
print(filled_pivot)

The output now becomes:

Period   P1   P2   P3   P4
Group
X      1.0  1.0  1.0  1.0
Y      NaN  1.0  1.0  1.0

Step 3: Handle Leading NaNs (Optional) #

If you have leading NaNs (like row Y’s P1), you can fill those with a default value (e.g., 0) or use bfill(axis=1) to propagate the first non-NaN value backward:

# Fill leading NaNs with 0
final_pivot = filled_pivot.fillna(0)
print("\nFinal result with leading NaNs filled:")
print(final_pivot)

Which gives:

Period   P1   P2   P3   P4
Group
X      1.0  1.0  1.0  1.0
Y      0.0  1.0  1.0  1.0

Key Notes #

• ffill(axis=1) works by iterating through each row from left to right, replacing NaNs with the last valid value it encountered. This perfectly matches your requirement to pass the known value from the previous column to subsequent NaNs in the same row.
• If you need to fill from right to left instead, use bfill(axis=1).

Hope this helps you get the exact pivot table output you’re looking for!

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