基于正则表达式字典创建Pandas数据类型列的问题

Let's break down why your current regex approach isn't correctly identifying Integers and Floats, and walk through fixes that work both for your test data and future larger datasets.

The Core Issue: Regex Matching Order & Pattern Precision #

Your regex dictionary has two critical flaws that are throwing off the results:

1. Matching priority is reversed: The String pattern ^[a-zA-Z0-9_ ]*$ matches any combination of letters, numbers, underscores, and spaces—including pure integers like 10101. Since Python processes regex replacements in dictionary order, it hits the broad String pattern first and never checks for Integer or Float.
2. Float pattern is too loose: [\d]+\. will match any string that contains a number followed by a dot (even invalid values like 123.abc), rather than a valid decimal number format.

Fix 1: Adjust Regex Order & Refine Patterns #

Rearrange your regex dictionary to check for the most specific patterns first (Float, then Integer), and save the broadest String match for last. Also tighten the Float pattern to ensure it only matches valid decimal numbers:

import os
import pandas as pd
import re

sample_file = 'C:/Users/951297/Documents/Python Scripts/DD\\Fund_Data.xlsx'
dataf = pd.read_excel(sample_file)

# Reshape the dataframe as you did before
stackdf = dataf.stack().reset_index()
stackdf = stackdf.rename(columns={'level_0':'index','level_1':'fh',0:'attribute'})

# Updated regex dictionary: specific patterns first, broadest last
repl_dict = {
    re.compile(r'^\d+\.\d+$'): 'Float',  # Matches valid floats like 2000.5
    re.compile(r'^\d+$'): 'Integer',     # Matches pure integers like 10101
    re.compile(r'^[a-zA-Z0-9_ ]+$'): 'String'  # Catches all valid string values
}

# Apply regex replacement directly to a new column (no need for a separate df)
stackdf['Data Type'] = stackdf['attribute'].replace(repl_dict, regex=True)

print(stackdf)

This will produce your expected output because:

• Floats are matched first (they can't be confused with integers)
• Integers are checked next (only pure numeric values pass)
• Strings get matched last, only if the value doesn't fit the numeric patterns

Fix 2: Use Type Conversion (More Robust for Real-World Data) #

Regex works for controlled test data, but it can fail edge cases (e.g., 1234.0 would be labeled Float but might be intended as Integer, or values with leading/trailing spaces). A more reliable method is to attempt converting the value to numeric types directly:

def detect_data_type(value):
    # Convert to string and strip whitespace to handle messy input
    str_val = str(value).strip()
    try:
        int(str_val)
        return 'Integer'
    except ValueError:
        try:
            float(str_val)
            return 'Float'
        except ValueError:
            return 'String'

# Apply the type detection function to your attribute column
stackdf['Data Type'] = stackdf['attribute'].apply(detect_data_type)

This approach is more flexible for future extensions (e.g., adding date detection by trying pd.to_datetime()), and avoids regex edge cases. For large datasets later on, you can optimize this with vectorized operations (like using pd.to_numeric with errors='coerce' to flag non-numeric values), but for testing purposes, apply() is perfectly functional.

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