Python处理CSV/Excel：替换逗号为点及解决转float报错问题

Hey there! Let's break down your two Python data processing needs and fix that annoying ValueError you're hitting with your Excel code.

You've got two solid options here, depending on whether your CSV has complex fields (like text with commas inside quotes) or not:

Option 1: Simple File Read/Write (for basic CSVs) #

If your CSV doesn't have fields with embedded commas (e.g., "Doe, Jane"), you can just read and rewrite the file directly:

# Replace commas with dots in a CSV
with open('input.csv', 'r', encoding='utf-8') as infile, open('output.csv', 'w', encoding='utf-8') as outfile:
    for line in infile:
        modified_line = line.replace(',', '.')
        outfile.write(modified_line)

Option 2: CSV Module (for structured/safe processing) #

If your CSV has fields with commas wrapped in quotes, use the csv module to avoid breaking the file structure. This lets you target individual fields instead of the entire line:

import csv

with open('input.csv', 'r', encoding='utf-8') as infile, open('output.csv', 'w', encoding='utf-8', newline='') as outfile:
    reader = csv.reader(infile)
    writer = csv.writer(outfile)
    
    for row in reader:
        # Replace commas with dots in every field
        modified_row = [field.replace(',', '.') for field in row]
        writer.writerow(modified_row)

Bonus: Only Replace Numeric Fields

If you want to leave text fields with commas untouched (like names), add a check to verify if the field is a numeric value first:

import csv

with open('input.csv', 'r', encoding='utf-8') as infile, open('output.csv', 'w', encoding='utf-8', newline='') as outfile:
    reader = csv.reader(infile)
    writer = csv.writer(outfile)
    
    for row in reader:
        modified_row = []
        for field in row:
            try:
                # Test if the field is numeric (after replacing comma with dot)
                float(field.replace(',', '.'))
                modified_row.append(field.replace(',', '.'))
            except ValueError:
                # Keep non-numeric fields as-is
                modified_row.append(field)
        writer.writerow(modified_row)

Your error ValueError: could not convert string to float: happens because the values in column 10 use commas as decimal separators (e.g., "123,45"), but Python's float() only recognizes dots. On top of that, your original code has a small logic bug with how you're fetching the ID values. Let's fix both:

Corrected Code #

Assuming you're using xlrd to read Excel files (common for .xls files), here's the revised code with explanations:

import xlrd

# Load your Excel file and target sheet
workbook = xlrd.open_workbook("your_excel_file.xlsx")  # Replace with your file path
feuille_1 = workbook.sheet_by_index(0)  # Use sheet_by_name("SheetName") if you know the name

id4 = []
absence = []

# Loop through rows (skip header row by starting at index 1)
for row_idx in range(1, 360):
    # Get the ID value from column 2 (index 1) - fixed from your original code
    id_value = feuille_1.cell_value(row_idx, 1)
    id4.append(id_value)
    
    # Get the absence value from column 11 (index 10), replace comma with dot
    absence_str = feuille_1.cell_value(row_idx, 10).replace(',', '.')
    
    # Add error handling for non-numeric values (prevents crashes)
    try:
        absence_float = float(absence_str)
        absence.append(absence_float)
    except ValueError:
        # Handle invalid values - adjust this based on your needs (e.g., set to 0 or skip)
        print(f"Warning: Could not convert '{absence_str}' to float at row {row_idx + 1}")
        absence.append(0.0)

# Calculate total absence hours per ID
result2 = {}
# Initialize all unique IDs with 0
for name2 in set(id4):
    result2[name2] = 0
# Accumulate hours for each ID
for i in range(len(id4)):
    hours2 = absence[i]
    name2 = id4[i]
    result2[name2] += hours2

print(result2)

Key Fixes & Improvements: #

• Comma-to-Dot Replacement: We use replace(',', '.') on the raw string value before converting to float — this eliminates the ValueError.
• Fixed ID Fetching: Your original code had id1 = row[1] outside the loop, where row was an integer (from range(1,360)). Now we correctly pull the ID from the corresponding cell in each row.
• Error Handling: The try/except block catches any non-numeric values (like empty cells or text) so your program doesn't crash unexpectedly.
• Clear Variable Names: Renamed row to row_idx to avoid confusion between the row index and actual row data.

内容的提问来源于stack exchange，提问作者samuel L-M