Python处理ASCII时序数据：24列转单列及按天小时正确排序问题

Hey there! Great job troubleshooting the KeyError by fixing the delimiter in pd.read_csv—that's a super common gotcha with ASCII files. Now let's get your hour sorting working the way you want it.

The root issue here is that your hour labels (H1, H10, H11...) are strings, so pandas defaults to lexicographical (dictionary) sorting. Since "1" comes before "2" in string order, H10 gets placed right after H1 instead of H9. Here are three solid ways to fix this:

1. Extract Numeric Hour Values for Sorting #

Add a temporary numeric column to use as your sort key, then drop it once sorted:

# First, get your melted DataFrame as you already did
df = pd.read_csv("your_file.txt", sep='\t')
melted_df = df.melt(id_vars=['date_id'], var_name='hour', value_name='val').rename(columns={'date_id': 'day'})

# Extract the numeric part of the hour string and convert to integer
melted_df['hour_number'] = melted_df['hour'].str.extract(r'(\d+)').astype(int)

# Sort by day and the numeric hour, then clean up the temp column
sorted_df = melted_df.sort_values(by=['day', 'hour_number']).drop(columns='hour_number')

2. Use a Custom Sort Key (No Temporary Column) #

If you don't want to add an extra column, use pandas' key parameter in sort_values to generate a numeric sort key on the fly:

sorted_df = melted_df.sort_values(
    by=['day', 'hour'],
    # For the 'hour' column, extract digits and convert to int; leave 'day' as-is
    key=lambda col: col.str.extract(r'(\d+)').astype(int) if col.name == 'hour' else col
)

3. Convert Hour to a Categorical Column (Preserve Order Permanently) #

If you want the hour column to always maintain the H1→H24 order (not just for sorting), convert it to a categorical type with a predefined order:

# Define the exact order you want
hour_order = [f'H{i}' for i in range(1, 25)]

# Convert the hour column to a categorical with this ordered list
melted_df['hour'] = pd.Categorical(melted_df['hour'], categories=hour_order, ordered=True)

# Now sorting by 'hour' will use your custom order automatically
sorted_df = melted_df.sort_values(by=['day', 'hour'])

All three methods will give you the correct H1 → H2 → ... → H24 sorting order. Pick the one that fits your workflow best—categoricals are great if you'll be working with this hour order repeatedly, while the first two are quick one-off fixes.

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