基于Python实现Excel中Joint Reactions数据的排序筛选与导出

Hey there! No worries at all—let's walk through this step by step, since you're new to Python. I'll break down each part with clear code and explanations so you can follow along easily.

First, we'll need two essential libraries to get this done:

• pandas: For handling data frames and working with Excel files
• openpyxl: A backend engine that lets pandas read/write .xlsx files

If you haven't installed them yet, run these commands in your terminal:

pip install pandas openpyxl

Step 1: Load your Excel data into a DataFrame #

This code will read your input Excel file and store the data in a pandas DataFrame (think of this as a super-powered spreadsheet in Python):

import pandas as pd

# Replace "your_input_file.xlsx" with the actual path/name of your Excel file
df = pd.read_excel("your_input_file.xlsx", engine="openpyxl")

# Optional: Print the first 5 rows to check if data loaded correctly
print(df.head())

Step 2: Sort the data to match your desired format #

The key here is enforcing the custom order for the LC column (SF-DL → OTH-DL → LL) and sorting each group by Joint in ascending order. Here's how:

# Define the custom order for the LC column
lc_custom_order = ["SF-DL", "OTH-DL", "LL"]

# Convert the LC column to a "category" type with our custom order (so pandas sorts it correctly)
df["LC"] = pd.Categorical(df["LC"], categories=lc_custom_order, ordered=True)

# Sort the data first by LC (using our custom order), then by Joint in ascending order
sorted_df = df.sort_values(by=["LC", "Joint"], ascending=[True, True])

# Optional: Check the sorted result
print(sorted_df.head(10))

Step 3: Export the processed data to Excel #

Finally, we'll save the sorted DataFrame back to an Excel file:

# Replace "your_output_file.xlsx" with your desired output file name/path
sorted_df.to_excel("your_output_file.xlsx", index=False, engine="openpyxl")

Note: The index=False argument ensures we don't export pandas' auto-generated row numbers into your Excel file—keeping your output clean and matching the format you want.

Quick Notes for Troubleshooting #

• If your input Excel has multiple sheets, add sheet_name="NameOfYourSheet" to the pd.read_excel() function to specify which sheet to load.
• If your file path has spaces (e.g., C:/My Files/input.xlsx), make sure to wrap the path in quotes.

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