Pandas中替代df.stack().unstack()实现唯一索引与非NaN值整合的更优方法问询

Better Alternatives to df.stack().unstack() for Aggregating Duplicate Indices #

Great question! Your current approach gets the job done, but it's indeed a bit roundabout and not the most efficient option—especially with larger datasets. Let's look at a couple of cleaner, faster methods that achieve the exact same result:

1. Use groupby().first() (or .max()/.min())

Since each index group has exactly one non-NaN value per column, grouping by the index and taking the first non-NaN value (or max/min, since NaN is ignored in these operations) will perfectly consolidate your data. This is the most efficient approach because it avoids the data reshaping overhead of stack()/unstack().

import pandas as pd
import numpy as np

# Your sample DataFrame
nan = np.nan
df = pd.DataFrame([[1, nan, nan, nan], [nan, 2, nan, nan], [nan, nan, 3, nan], [nan, nan, nan, 4], [5, nan, nan, nan], [nan, 6, nan, nan]], index = [1, 1, 1, 1, 2, 2])

# Consolidate with groupby
df_consolidated = df.groupby(level=0).first()
print(df_consolidated)

Output:

0    1    2    3
1  1.0  2.0  3.0  4.0
2  5.0  6.0  NaN  NaN

You can also use .max() or .min() here—they'll produce identical results because there's only one valid value per column in each index group.

2. Use pivot_table() (Alternative for More Complex Scenarios)

If you prefer a pivot-style approach (though it's slightly less efficient than groupby), you can reset the index first and then pivot with an aggregation function:

df_consolidated = df.reset_index().pivot_table(index='index', aggfunc='first')

This works similarly, but requires an extra step to reset the index, so it's not the first choice for simple cases like yours.

Why These Are Better #

• Efficiency: groupby() operations are optimized in pandas to handle aggregation without reshaping the entire dataset, unlike stack()/unstack() which convert between wide and long formats (adding unnecessary overhead).
• Readability: The intent is clearer—you're explicitly grouping by index to consolidate values, whereas stack().unstack() feels like a workaround.

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