如何将格式混乱的TXT文件转换为Pandas多级索引DataFrame

Alright, let's tackle this messy text file and turn it into a nicely structured Pandas MultiIndex DataFrame. The key here is to first extract the invoice-level metadata and line-item details from the chaotic string, then organize it into a hierarchical index. Here's a step-by-step solution:

Step 1: Read the File & Handle Encoding #

First, we need to read the file properly—those weird garbled characters (like ÞÔÞæÇä) suggest encoding mismatches. Using latin-1 will let us read almost any byte without errors, which we can clean up later.

import pandas as pd
import re
from unidecode import unidecode  # Install with `pip install unidecode` if missing

# Read the messy text file
with open('file.txt', 'r', encoding='latin-1') as f:
    raw_content = f.read()

Step 2: Split Content into Invoice Blocks #

The repeated header string TYPE Invoice C AC DATE TIME Total Invoice Qty1 ITEMVG By Total acts as a natural separator for each invoice. We'll split the content using this header to isolate individual invoices.

# Define the header pattern to split invoices
header_regex = re.compile(r'TYPE Invoice C AC DATE TIME Total Invoice Qty1 ITEMVG By Total')
# Split content into invoice chunks (skip the first empty result from the split)
invoice_chunks = header_regex.split(raw_content)[1:]

Step 3: Extract Structured Data from Each Chunk #

Each chunk contains invoice metadata (invoice number, date, time, total amount) followed by line items. We'll use regex to pull out these structured pieces:

• Invoice Metadata: Match invoice number, date (dd/mm/yyyy format), time (hh:mm format), and total amount (formatted with commas).
• Line Items: Match quantity (integer or decimal), item name (any text until the amount), and item amount (formatted with commas).

# Regex patterns to extract metadata and line items
invoice_meta_regex = re.compile(r'(\d+) (\d{2}/\d{2}/\d{4}) (\d{2}:\d{2}) ([\d,]+\.\d{2})')
line_item_regex = re.compile(r'([\d.]+) (.+?) ([\d,]+\.\d{2})')

# Collect all structured data in a list
structured_data = []

for chunk in invoice_chunks:
    # Extract invoice-level metadata
    meta_match = invoice_meta_regex.search(chunk)
    if not meta_match:
        continue  # Skip invalid chunks with no recognizable metadata
    
    inv_num, inv_date, inv_time, inv_total = meta_match.groups()
    # Clean numeric values (remove commas, convert to float)
    inv_total = float(inv_total.replace(',', ''))
    
    # Extract all line items in the invoice
    line_items = line_item_regex.findall(chunk)
    for qty, item_name, item_amount in line_items:
        # Clean line item values
        qty = float(qty)
        item_amount = float(item_amount.replace(',', ''))
        # Clean messy garbled characters from item names
        clean_item_name = unidecode(item_name.strip())
        
        # Add to our data list
        structured_data.append({
            'Invoice Number': inv_num,
            'Date': inv_date,
            'Time': inv_time,
            'Invoice Total': inv_total,
            'Quantity': qty,
            'Item Name': clean_item_name,
            'Item Amount': item_amount
        })

Step 4: Create MultiIndex DataFrame #

Now we'll convert our structured list to a DataFrame, then add a line-item index within each invoice to build the hierarchical index.

# Convert to DataFrame
df = pd.DataFrame(structured_data)

# Add a line item number for each invoice (1, 2, 3...)
df['Line Item'] = df.groupby('Invoice Number').cumcount() + 1

# Set MultiIndex: Invoice Number first, then Line Item
df = df.set_index(['Invoice Number', 'Line Item'])

# Optional: Reorder columns for better readability
df = df[['Date', 'Time', 'Invoice Total', 'Quantity', 'Item Name', 'Item Amount']]

Step 5: Verify the Result #

You can check the final DataFrame with:

print(df.head())

It should look something like this (cleaned up):

Date      Time  Invoice Total  Quantity               Item Name  Item Amount
Invoice Number Line Item                                                                              
5696           1       01/03/2018  09:21        32501.35       1.0            Golden Plate      6517.52
               2       01/03/2018  09:21        32501.35       1.0  aa NaeCi EuaCia         4261.45
               3       01/03/2018  09:21        32501.35       1.0  Magic chef pop corn 907g      3509.43

Notes & Troubleshooting #

• Regex Adjustments: If some invoices don't parse correctly, tweak the regex patterns to match your actual data (e.g., if invoice numbers include letters, update invoice_meta_regex).
• Encoding: If latin-1 doesn't work, try utf-8-sig or cp1252 as alternatives.
• Missing Data: If some line items are skipped, check if they fit the regex pattern—you might need to adjust it to handle edge cases (like item names with embedded numbers).

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