Pandas DataFrame排序异常求助：多Excel合并后排序失效

Hey there! It sounds like you're dealing with a frustrating quirk where part of your Product column sorts correctly, but other entries end up out of place after converting to strings and sorting. Let's break down the most common reasons this happens and how to fix them:

1. Hidden Whitespace in Product Names #

This is the #1 culprit for wonky string sorting! Excel cells often have invisible leading/trailing spaces, tabs, or line breaks that you can't spot at a glance. When you convert values to strings with astype(str), those hidden characters stick around—so "WidgetA" and " WidgetA" (note the leading space) get treated as completely separate values, leading to messy sorting.

Fix: Clean up the whitespace first with str.strip():

result['Product'] = result['Product'].astype(str).str.strip()

2. Inconsistent Capitalization #

String sorting in Python is case-sensitive (it uses ASCII values, where uppercase letters come before lowercase). So "widgetA" and "WidgetA" will sort separately, making your list look disjointed even though the product names are otherwise identical.

Fix: Standardize the case before sorting—pick either all lowercase or all uppercase:

# Option 1: Convert to lowercase
result['Product'] = result['Product'].astype(str).str.strip().str.lower()

# Option 2: Convert to uppercase
result['Product'] = result['Product'].astype(str).str.strip().str.upper()

3. Residual Mixed Data Types #

Even after using astype(str), some values might have come from Excel cells with non-string types (like numbers, dates, or errors) that convert to unexpected string formats. For example, an Excel date might turn into a serial number string like "45432" instead of a readable date, or a number might become "123.0" instead of "123"—these odd strings will throw off your sort order.

Fix: First inspect what your converted strings actually look like:

# Check unique values to spot weird formats
print(result['Product'].unique())

# Check string lengths to catch unexpected variations
print(result['Product'].astype(str).apply(len).value_counts())

If you spot date serial numbers, you can convert them properly during Excel reading:

# When reading Excel, parse dates in the Product column if applicable
pd.read_excel(file, parse_dates=['Product'])
# Then convert to a consistent string format
result['Product'] = result['Product'].dt.strftime('%Y-%m-%d').str.strip()

4. Multi-Index Quirks #

Since you used pd.concat with keys=t_list, your DataFrame has a multi-index (with the t level plus the original Excel indices). While this shouldn't break sorting directly, sometimes the index can make it look like sorting is off when it's actually not. Try resetting the index temporarily to verify:

# Reset index to sort without multi-index interference
temp_df = result.reset_index()
temp_df['Product'] = temp_df['Product'].astype(str).str.strip().str.lower()
temp_sorted = temp_df.sort_values('Product')
# If this looks correct, you can re-set the index if needed
result_sorted = temp_sorted.set_index(['t', 'index'])

Full Workflow Example #

Here's a consolidated version of the fixes to apply:

# Combine your Excel files as before
result = pd.concat([pd.read_excel(file) for file in filenames], keys=t_list, names=['t'])

# Clean and standardize the Product column
result['Product'] = result['Product'].astype(str).str.strip().str.lower()

# Sort the DataFrame
result_sorted = result.sort_values('Product', ascending=True)

Before sorting, always double-check the cleaned Product values to make sure inconsistencies are gone—this will save you a lot of head-scratching!

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