如何从Pandas DataFrame中减去Series并保留所有列？

Got it, let's sort out this pandas subtraction problem where you're seeing unexpected NaNs and missing columns. The core issue is that when you use sub(), pandas only aligns columns that exist in both your DataFrame (first_df) and Series (second_s), leaving any non-matching columns as NaN or excluded entirely.

To get your exact desired output—preserving original values from first_df where there's no matching Series column, subtracting values where columns do match, and adding new columns from the Series with values calculated as 0 - second_s[col]—here's a dynamic solution that works no matter how many unknown columns you have:

Working Code Implementation #

import pandas as pd

# Your original data setup
second_df = pd.DataFrame([[1, 1], [2, 2], [3, 3]], columns=['a', 'c'])
second_s = second_df.iloc[0]
first_df = pd.DataFrame([[0.0, 0.1], [1.0, 1.1], [2.0, 2.1]], columns=['a', 'b'])

# 1. Gather all unique columns from both the DataFrame and Series
all_columns = first_df.columns.union(second_s.index)

# 2. Align both objects to include every column, filling missing values strategically
# For the DataFrame: add missing Series columns filled with 0 (since we need 0 - second_s[col])
first_df_aligned = first_df.reindex(all_columns, fill_value=0)
# For the Series: add missing DataFrame columns filled with 0 (so subtracting 0 leaves original values intact)
second_s_aligned = second_s.reindex(all_columns, fill_value=0)

# 3. Perform column-wise subtraction
result = first_df_aligned.sub(second_s_aligned, axis=1)

print(result)

Expected Output #

a    b    c
0 -1.0  0.1 -1.0
1  0.0  1.1 -1.0
2  1.0  2.1 -1.0

Breakdown of the Solution #

• Collect All Columns: first_df.columns.union(second_s.index) grabs every unique column name from both objects, so we never miss a column even if it's only present in one of them.
• Reindex with Smart Filling:
  • We expand first_df to include any columns from second_s that aren't already there, filling those new columns with 0 (since we need to compute 0 - second_s[col] for these columns).
  • We expand second_s to include any columns from first_df that aren't in the Series, filling those with 0—this way, subtracting 0 from first_df's original values leaves them unchanged.
• Aligned Subtraction: Using sub() with axis=1 ensures we subtract column-by-column across our aligned objects, giving you exactly the result you wanted.

This approach is fully dynamic, so it'll work no matter how many unknown columns you have—you just need to access the column/index info from your existing DataFrame and Series.

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