如何用Pandas拆分DataFrame数据并筛选指定长度值存入Excel

Got it, let's break down how to handle this data processing task with Pandas step by step. I'll walk you through each part so you understand exactly what's happening:

Step 1: Set Up Dependencies #

First, make sure you have the required libraries installed. If not, run these commands in your terminal:

pip install pandas openpyxl

(We use openpyxl for reading and writing Excel files in the newer .xlsx format.)

Step 2: Full Code Implementation #

Here's the complete code that covers all your requirements — just replace the file paths with your actual ones:

import pandas as pd

# 1. Load your Excel file
df = pd.read_excel("your_input_file.xlsx")

# 2. Process the KF column: split, clean, filter
# Split each cell's value by commas, expand into individual rows, and trim whitespace
split_clean_values = df['KF'].str.split(',').explode().str.strip()

# Filter only values with exactly 8 characters, then convert to a list (if needed)
filtered_8char_values = split_clean_values[split_clean_values.str.len() == 8].tolist()

# 3. Export the filtered list to Excel
# Convert the list to a DataFrame for easy Excel export
result_df = pd.DataFrame(filtered_8char_values, columns=['Filtered_KF'])
result_df.to_excel("your_output_file.xlsx", index=False)

Let's Break Down the Key Parts #

• str.split(','): Splits each string in the KF column into a list of substrings using commas as separators.
• .explode(): Turns each element in those lists into its own row — this makes it easy to process each value individually.
• .str.strip(): Removes any leading/trailing whitespace from each split value (handles cases where there's a space after a comma, like in your sample data).
• str.len() == 8: Filters out any values that aren't exactly 8 characters long.
• tolist(): Converts the filtered Series into a Python list if you need to use it elsewhere, but we convert it back to a DataFrame for Excel export since Pandas works best with DataFrames for file I/O.

Edge Case Handling #

If your KF column has empty cells or values that split into empty strings, don't worry — the .str.strip() will turn those into empty strings, and str.len() == 8 will automatically exclude them.

Let me know if you need to adjust this for any specific edge cases or file formatting quirks!

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