已完成数据排序，如何按首个非空字符串值分组且不改动其余数据？

Alright, let's figure out how to fix this grouping issue you're facing! The core problem here is that your current method fails when the first value in a sequence is empty, but you need that group to use the first non-empty string that comes after it (like in your example where ('',2) should group under 2). Below are tailored solutions depending on your data environment:

1. SQL Solution (Works for PostgreSQL, MySQL 8+, etc.) #

This approach uses window functions to first identify logical groups, then extracts the first non-empty string as the group key—even for leading empty values.

WITH grouped_data AS (
  SELECT
    *,
    -- Assign a group ID that increments every time we hit a non-empty string
    COUNT(CASE WHEN your_string_column <> '' THEN 1 END) 
      OVER (ORDER BY your_sort_column ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
  FROM your_table
)
SELECT
  -- Grab the first non-empty string in the group as our group key
  MAX(CASE WHEN your_string_column <> '' THEN your_string_column END) AS group_key,
  -- Keep other data using your preferred aggregation (adjust as needed)
  SUM(numeric_column) AS total_numeric,
  ARRAY_AGG(other_column) AS all_other_values
FROM grouped_data
GROUP BY group_id;

How it works: #

• The inner COUNT window function creates group IDs: every non-empty string triggers a new group, and empty values inherit the most recent group ID.
• The outer query uses MAX to pull the non-empty string from each group (since each group has at least one non-empty value, this will always return the first one in the sorted sequence).

2. Python Pandas Solution #

If you're working with pandas dataframes (already sorted), this streamlined approach handles leading empty values seamlessly:

import pandas as pd

# Replace empty strings with NaN, then forward-fill to propagate the first non-empty value
df['group_key'] = df['your_string_column'].replace('', pd.NA).ffill()

# Group by the generated key and retain other data (adjust aggregations as needed)
grouped_result = df.groupby('group_key').agg({
    'numeric_column': 'sum',
    'other_column': lambda x: list(x)
}).reset_index()

How it works: #

• replace('', pd.NA) converts empty strings to missing values, which ffill() (forward fill) can handle.
• ffill() propagates the first non-empty value backward to all preceding empty rows, ensuring leading empties are grouped under the correct non-empty key.

Both solutions preserve your original sorted order and don't alter the underlying raw data—they just generate the correct group keys for aggregation.

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