求助：如何在Python中清洗CSV财务数据的交易名称并同步至Google Sheets

Hey there! Let's fix that transaction name cleaning issue you're dealing with—bank CSV exports always throw those messy, coded names at us, but we can automate this with some simple string handling in Python.

Step 1: Create a Name-Cleaning Function #

First, let's build a function that takes those messy transaction names and extracts the merchant name you care about. We'll handle your specific examples first, then make it flexible for other cases:

def clean_transaction_name(raw_name):
    # Split the raw name into individual words, and trim any extra whitespace
    words = raw_name.strip().split()
    
    # Handle empty or invalid names gracefully
    if not words:
        return "Unknown"
    
    # List of merchants we want to explicitly capture (add more as you need!)
    target_merchants = {"NETFLIX", "STEAM", "AMAZON", "GOOGLE", "UBER"}
    
    # Check from the end of the word list first (merchants are usually last)
    for word in reversed(words):
        if word in target_merchants:
            return word
    
    # Handle cases where merchant is followed by a generic term like "PURCHASE"
    common_suffixes = {"PURCHASE", "PAYMENT", "CHARGE", "NET"}
    if len(words) >= 2 and words[-1] in common_suffixes:
        return words[-2]
    
    # Fallback: if none of the above match, return the last word (adjust if needed)
    return words[-1]

Step 2: Integrate the Function into Your Existing Code #

Now, update your hdfcFin function to use this cleaner when processing each transaction's name:

import csv
import gspread
import time

MONTH = 'June' # Set month name
file = f'HDFC_{MONTH}_2022.csv' #the file we need to extract data from
transactions = [] # Create empty list to add data to

def clean_transaction_name(raw_name):
    # Split the raw name into individual words, and trim any extra whitespace
    words = raw_name.strip().split()
    
    # Handle empty or invalid names gracefully
    if not words:
        return "Unknown"
    
    # List of merchants we want to explicitly capture (add more as you need!)
    target_merchants = {"NETFLIX", "STEAM", "AMAZON", "GOOGLE", "UBER"}
    
    # Check from the end of the word list first (merchants are usually last)
    for word in reversed(words):
        if word in target_merchants:
            return word
    
    # Handle cases where merchant is followed by a generic term like "PURCHASE"
    common_suffixes = {"PURCHASE", "PAYMENT", "CHARGE", "NET"}
    if len(words) >= 2 and words[-1] in common_suffixes:
        return words[-2]
    
    # Fallback: if none of the above match, return the last word (adjust if needed)
    return words[-1]

def hdfcFin(file):
    '''Create a function that allows us to export data to google sheets'''
    with open(file, mode = 'r') as csv_file:
        csv_reader = csv.reader(csv_file)
        for row in csv_reader:
            date = row[0]
            # Use our cleaning function here!
            cleaned_name = clean_transaction_name(row[1])
            expense = float(row[2])
            income = float(row[3])
            category = 'other'
            transaction = ((date, cleaned_name, expense, income, category))
            transactions.append(transaction)
    return transactions

sa = gspread.service_account() # connect json to api
sh = sa.open('Personal Finances')
wks = sh.worksheet(f'{MONTH}')
rows = hdfcFin(file)
for row in rows:
    wks.insert_row([row[0], row[1], row[4], row[2], row[3]], 8)
    time.sleep(2) # time delay because of api restrictions

How This Works #

• Splitting Words: We break the raw name into individual words using split()—this turns "ME DC SI XXXXXXXXXXXXXXXX NETFLIX" into a list like ["ME", "DC", "SI", "XXXXXXXXXXXXXXXX", "NETFLIX"].
• Target Merchant Check: We loop backwards through the word list (since merchants are almost always at the end) to find exact matches for merchants you care about.
• Generic Suffix Handling: For cases like "POS XXXXXXXXXXXXXXXX STEAM PURCHASE", we check if the last word is a generic term (like "PURCHASE") and return the previous word instead.
• Fallback: If none of our rules match, we default to the last word—you can tweak this if you want to handle other cases later.

Next Steps to Expand #

As you encounter more transaction names, you can:

• Add more merchants to the target_merchants set (e.g., "SPOTIFY", "EBAY").
• Add more generic suffixes to common_suffixes if you see terms like "BILL" or "FEE".
• For trickier cases (like names with symbols or partial matches), you could use simple regex patterns—for example, to capture "NETFLIX" even if it's written as "NETFLIX.COM".

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