Python（无Pandas）拆分CSV单列并分组数据技术求助

Hey there! Let's work through this problem without relying on Pandas. From what you've described, we need to:

1. Read a semicolon-separated CSV structured as a single column
2. Split that single column into multiple columns using your provided headers (COUNTRY, COUNTRY_TIME, COUNTRY_REF, PRODUCT)
3. Clean and group the data (I’ll focus on grouping by country and product region since your PRODUCT values like APPLE%BOX%LYON%022018 are clearly delimited)
4. Save the processed data to both CSV and Excel formats

Step 1: Read and Split the Single Column into Structured Rows #

First, we’ll read the CSV and organize the single-column data into rows matching your header structure. The first 4 entries are our headers, and every subsequent 4 entries form a data row.

import csv

# Read the single-column semicolon-separated CSV
input_file = "input.csv"
raw_data = []
with open(input_file, mode='r', encoding='utf-8') as f:
    reader = csv.reader(f, delimiter=';')
    for row in reader:
        if row:  # Skip empty lines
            raw_data.append(row[0].strip())

# Split into headers and structured rows
headers = raw_data[:4]
data_rows = [raw_data[i:i+4] for i in range(4, len(raw_data), 4)]

Step 2: Clean the PRODUCT Column #

Your PRODUCT values use % as a delimiter—let’s split these into meaningful sub-fields (product type, packaging, region, date) to make grouping easier.

cleaned_rows = []
for row in data_rows:
    country, time, ref, product = row
    # Split PRODUCT into components, handle malformed entries gracefully
    product_components = product.split('%')
    if len(product_components) == 4:
        prod_type, packaging, region, prod_date = product_components
    else:
        prod_type, packaging, region, prod_date = "", "", "", ""
    
    cleaned_rows.append([
        country, time, ref, prod_type, packaging, region, prod_date
    ])

# Update headers to include our new product fields
updated_headers = headers[:3] + ["PRODUCT_TYPE", "PACKAGING", "REGION", "PROD_DATE"]

Step 3: Group the Data #

Let’s group the data by COUNTRY and REGION as an example. We’ll use a dictionary to track groups, where each key is a tuple of (country, region) and the value is the list of rows in that group.

grouped_data = {}
for row in cleaned_rows:
    country = row[0]
    region = row[5]
    group_key = (country, region)
    
    if group_key not in grouped_data:
        grouped_data[group_key] = []
    grouped_data[group_key].append(row)

# Optional: Convert grouped data to a flat list with group labels for easier saving
flat_grouped_rows = []
for (country, region), rows in grouped_data.items():
    for row in rows:
        flat_grouped_rows.append([country, region] + row)
flat_grouped_headers = ["GROUP_COUNTRY", "GROUP_REGION"] + updated_headers

Step 4: Save to CSV #

Use Python’s built-in csv module to save both cleaned and grouped data to CSV files.

# Save cleaned data
cleaned_csv = "cleaned_data.csv"
with open(cleaned_csv, mode='w', newline='', encoding='utf-8') as f:
    writer = csv.writer(f)
    writer.writerow(updated_headers)
    writer.writerows(cleaned_rows)

# Save grouped data (if needed)
grouped_csv = "grouped_data.csv"
with open(grouped_csv, mode='w', newline='', encoding='utf-8') as f:
    writer = csv.writer(f)
    writer.writerow(flat_grouped_headers)
    writer.writerows(flat_grouped_rows)

Step 5: Save to Excel #

We’ll use openpyxl (a lightweight Excel library) since we can’t use Pandas. First install it with pip install openpyxl.

from openpyxl import Workbook

# Create workbook and sheets
wb = Workbook()
cleaned_sheet = wb.active
cleaned_sheet.title = "Cleaned Data"
grouped_sheet = wb.create_sheet(title="Grouped Data")

# Write cleaned data to first sheet
cleaned_sheet.append(updated_headers)
for row in cleaned_rows:
    cleaned_sheet.append(row)

# Write grouped data to second sheet
grouped_sheet.append(flat_grouped_headers)
for row in flat_grouped_rows:
    grouped_sheet.append(row)

# Save the Excel file
wb.save("processed_data.xlsx")

Quick Notes: #

• If your original CSV has each row as a semicolon-separated string of 4 values (instead of one value per line), adjust the reading step to split each row directly with row.split(';').
• Modify the grouping logic to fit your specific needs (e.g., group by PRODUCT_TYPE instead of region).
• Add extra error handling if you expect malformed rows or missing values in your input data.

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