如何将DataFrame列中值统一为指定格式（保留已格式化值）

To solve this problem, we’ll retain existing properly formatted values while converting unformatted 7-digit codes to the specified XXXX-X/XX format. Here’s a practical, step-by-step approach:

Step 1: Distinguish Formatted vs. Unformatted Values #

We can identify formatted values using a regex pattern that matches the exact structure XXXX-X/XX (4 digits, hyphen, 1 digit, slash, 2 digits). Unformatted values are 7-digit strings or integers with no special characters.

Step 2: Create a Reusable Formatting Function #

Define a helper function that checks if a value is already formatted, then applies the conversion logic only to unformatted entries:

import pandas as pd

def format_cnae_code(value):
    # Convert to string to handle both integers and string inputs
    value_str = str(value).strip()
    
    # Check if value is already in the correct format using regex
    if pd.Series(value_str).str.contains(r'^\d{4}-\d/\d{2}$').iloc[0]:
        return value_str
    
    # Apply formatting if it's a valid 7-digit numeric value
    if len(value_str) == 7 and value_str.isdigit():
        return f"{value_str[:4]}-{value_str[4]}/{value_str[5:]}"
    
    # Return original value for edge cases (non-matching formats)
    return value_str

Step 3: Apply the Function to Your DataFrame #

Use apply() to run the function on every element in the target column:

# Sample DataFrame matching your example
data = {
    'Data': ['3101-2/00', '7319-0/02', '8800-6/00', 4772500, 7410299]
}
df = pd.DataFrame(data)

# Apply the formatting function
df['Data'] = df['Data'].apply(format_cnae_code)

print(df)

Output: #

Data
0  3101-2/00
1  7319-0/02
2  8800-6/00
3  4772-5/00
4  7410-2/99

Alternative: Vectorized Approach (For Large Datasets) #

For larger datasets, vectorized operations are more efficient than apply():

# Convert the column to string type first
df['Data'] = df['Data'].astype(str)

# Create a mask for unformatted values
mask = ~df['Data'].str.contains(r'^\d{4}-\d/\d{2}$')

# Apply formatting only to unformatted rows
df.loc[mask, 'Data'] = df.loc[mask, 'Data'].apply(lambda x: f"{x[:4]}-{x[4]}/{x[5:]}")

Key Notes: #

• Precision with Regex: The regex ^\d{4}-\d/\d{2}$ ensures we only match values exactly in the desired format, avoiding false positives from values that have - or / but aren’t correctly structured.
• Edge Case Handling: The function returns the original value for entries that don’t fit either category (e.g., non-7-digit strings, invalid formats) to prevent data loss.
• Integer Compatibility: Converting values to strings first works seamlessly whether your unformatted entries are stored as integers or strings.

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