如何用Pandas自动将DataFrame非NaN数据向上对齐至顶端空单元格

Got it—you need a way to automatically shift all valid (non-NaN) values in every column of your DataFrame up to the top, replacing any empty cells above them. Your current hardcoded approach only works for specific cases and can't handle large datasets (100k+ rows), so let's fix that with a scalable, generalizable solution.

The Fast, One-Liner Solution #

This approach processes each column independently to pull valid values to the top, and it leverages Pandas' optimized internal operations to handle big data efficiently:

aligned_df = df.apply(lambda col: col.dropna().reset_index(drop=True))

How It Works (With Your Sample Data) #

Let's walk through what this does using your input:

Your Original Input

a  b  c  d  e  f  g   h   i   j   k   l   m   o
0  0  0  0  0  0  0  0 NaN NaN NaN NaN NaN NaN NaN
1  1  1  1  1  1  1  1 NaN NaN NaN NaN NaN NaN NaN
2  0  0  0  1  0  0  1 NaN NaN NaN NaN NaN NaN NaN
3  0  0  1  0  1  0  0 NaN NaN NaN NaN NaN NaN NaN
4 NaN NaN NaN NaN NaN NaN NaN   1   1   0   1   0   0   1

After Running the Code

a  b  c  d  e  f  g    h    i    j    k    l    m    o
0  0  0  0  0  0  0  0  1.0  1.0  0.0  1.0  0.0  0.0  1.0
1  1  1  1  1  1  1  1  NaN  NaN  NaN  NaN  NaN  NaN  NaN
2  0  0  0  1  0  0  1  NaN  NaN  NaN  NaN  NaN  NaN  NaN
3  0  0  1  0  1  0  0  NaN  NaN  NaN  NaN  NaN  NaN  NaN

• For each column, col.dropna() strips out all NaN values, leaving only the valid data points.
• reset_index(drop=True) reindexes those valid values to start at row 0, so the first valid value in each column moves straight to the top.
• Pandas automatically aligns all columns by row index—any column with fewer valid values gets filled with NaN in the extra rows at the bottom.

Why This Is Better Than Hardcoding #

• No guesswork: Works no matter where NaNs are (top, bottom, scattered in the middle of columns).
• Blazing fast: Uses Pandas' optimized C-backed operations, so it handles 100k+ rows without breaking a sweat—way faster than manual shifting or looping.
• Preserves structure: Keeps all your original columns, even if some have no valid data (those will just show all NaNs).

Quick Edge Case Fixes #

• If you want to remove columns that have no valid data at all (all NaN) after alignment, add this line:

  aligned_df = aligned_df.dropna(axis=1, how='all')
  

• If you need to convert float values back to integers (since NaN forces float dtype), you can use convert_dtypes():

  aligned_df = aligned_df.convert_dtypes()
  

内容的提问来源于stack exchange，提问作者D.I.