如何按自定义规则重塑Pandas DataFrame？

Got it, let's tackle this reshaping problem. The goal is to take each row's last non-NaN ID value as num1, then create a new row for every other non-NaN ID value (in reverse order of their original columns) paired with num1 and the corresponding Node.

First, let's start with your sample data to verify the solution:

import pandas as pd
import numpy as np

# Sample DataFrame
df = pd.DataFrame({
    'Node': ['YYZ', 'DFW', 'DEN', 'BOS'],
    'ID11': [1,4,20,100],
    'ID10': [2,5,21,101],
    'ID9': [3,6,np.nan,102],
    'ID8': [np.nan,7,np.nan,103],
    'ID7': [np.nan,np.nan,np.nan,104],
    'ID6': [np.nan,np.nan,np.nan,105],
    'ID5': [np.nan,np.nan,np.nan,106],
    'ID4': [np.nan,np.nan,np.nan,np.nan],
    'ID3': [np.nan,np.nan,np.nan,np.nan],
    'ID2': [np.nan,np.nan,np.nan,np.nan],
    'ID1': [np.nan,np.nan,np.nan,np.nan],
    'ID0': [np.nan,np.nan,np.nan,np.nan]
})

Solution 1: Vectorized approach (best for large datasets) #

Since you mentioned dealing with a large DataFrame, we want to avoid slow row-wise apply calls. This method uses stacking and grouping to handle the data efficiently:

1. Reverse the ID columns: This lets us easily access the last non-NaN value as the first element in each row's non-NaN list.
2. Stack to long format: Collect all non-NaN ID values per Node.
3. Aggregate into lists: Group by row index to create lists of non-NaN values (ordered from last to first original non-NaN).
4. Split lists into num1 and num2: The first element of each list is num1, the rest are the values for num2.
5. Explode to create rows: Turn each element in the num2 list into its own row.

# Reverse the order of ID columns (from ID0 to ID11 instead of ID11 to ID0)
id_cols_reversed = df.filter(like='ID').columns[::-1]
df_id_reversed = df[id_cols_reversed]

# Stack non-NaN values, group by row index, and aggregate into lists
id_lists = df_id_reversed.stack() \
                        .reset_index(level=1, drop=True) \
                        .groupby(level=0) \
                        .agg(list) \
                        .rename('id_list')

# Join the lists back to the original DataFrame
df = df.join(id_lists)

# Extract num1 (first element of the list) and num2_list (remaining elements)
df['num1'] = df['id_list'].str[0]
df['num2_list'] = df['id_list'].str[1:]

# Explode the num2_list into individual rows and clean up the result
result = df.explode('num2_list') \
           .rename(columns={'num2_list': 'num2'}) \
           [['Node', 'num1', 'num2']] \
           .reset_index(drop=True)

Solution 2: Row-wise apply (simpler for small datasets) #

If you're working with a smaller dataset and prefer more readable code, you can use apply to process each row directly:

# Select all ID columns
id_cols = df.filter(like='ID').columns

def process_row(row):
    # Get list of non-NaN ID values in original column order
    id_vals = row[id_cols].dropna().tolist()
    # Skip rows with fewer than 2 non-NaN values (no pairs to create)
    if len(id_vals) < 2:
        return pd.Series([np.nan, []])
    # Last non-NaN value is num1, reverse the rest for num2
    num1 = id_vals[-1]
    num2_list = id_vals[:-1][::-1]
    return pd.Series([num1, num2_list])

# Apply the function to each row
df[['num1', 'num2_list']] = df.apply(process_row, axis=1)

# Explode and clean up
result = df.explode('num2_list') \
           .rename(columns={'num2_list': 'num2'}) \
           [['Node', 'num1', 'num2']] \
           .reset_index(drop=True)

Both solutions will produce exactly the output you need:

Node  num1  num2
0    YYZ     3     2
1    YYZ     3     1
2    DFW     7     6
3    DFW     7     5
4    DFW     7     4
5    DEN    21    20
6    BOS   106   105
7    BOS   106   104
8    BOS   106   103
9    BOS   106   102
10   BOS   106   101
11   BOS   106   100

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