Python网页数据提取后规整为13列目标格式技术求助

Hey there! Let’s get that messy single-column data sorted back into the proper 13-column table that matches your source website. Below are three straightforward methods to fix this, depending on your comfort level with tools:

Method 1: Excel’s Built-in Text to Columns (Quickest for Simple Cases) #

This is the easiest go-to if your merged data uses a consistent separator:

• Select the entire column containing your merged data.
• Go to the Data tab → click Text to Columns.
• Choose the correct split type:
  • Pick Delimited if your data uses a specific separator (like commas, tabs, or pipes) to separate each column’s content.
  • Pick Fixed Width if each original column has a consistent number of characters.
• Preview the split in the wizard to ensure all 13 columns are correctly separated. Adjust the split lines or separator as needed.
• Click Finish—your data will now be in the proper column structure.

Method 2: Excel Power Query (For More Complex Data) #

Power Query is great if your separator is inconsistent or you need to clean up data while splitting:

• Select your merged data column → go to Data → click From Table/Range (check "My table has headers" if your first row is a title).
• In the Power Query Editor, go to Transform → Split Column → By Delimiter.
• Enter the separator that’s used in your merged data (e.g., a comma, space, or custom symbol) and choose to split into columns.
• Rename the resulting columns to match the 13 columns from your source website.
• Click Close & Load to export the fixed table back to Excel.

Method 3: Python Script (For Batch/Automated Processing) #

If you’re comfortable with coding, using Pandas will let you automate this for future scrapes too:

import pandas as pd

# Load the scraped Excel file
df = pd.read_excel("your_scraped_file.xlsx")

# Split the merged column into 13 separate columns (replace ";" with your actual separator)
split_data = df["Merged_Column_Name"].str.split(";", expand=True)

# Rename columns to match your source website's 13 columns
split_data.columns = [
    "Column_Title_1", "Column_Title_2", "Column_Title_3",
    "Column_Title_4", "Column_Title_5", "Column_Title_6",
    "Column_Title_7", "Column_Title_8", "Column_Title_9",
    "Column_Title_10", "Column_Title_11", "Column_Title_12",
    "Column_Title_13"
]

# Save the fixed table to a new Excel file
split_data.to_excel("fixed_13column_table.xlsx", index=False)

• Pro Tip: Double-check your merged data first to find the correct separator—open a cell in Excel and look for symbols that consistently separate each original column’s value (e.g., \t for tabs, | for pipes).

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