Pandas DataFrame分组内数值数据标准化：策略与实践

import pandas as pd
from sklearn.preprocessing import StandardScaler

df = pd.DataFrame({
    "cost": [30, 15, 100, 65, 75, 55, 29, 45],
    "sales": [80, 88, 70, 80, 999, 70, 8, 95],
    "da_value": ["low", "low", "high", "medium", "high", "medium", "low", "medium"],
    "names": ["Jo", "Andrew", "AI", "Michael", "Nikola", "Jim", "Bojan", "Vurce"]
})

print(df)

   cost  sales da_value    names
0    30     80      low       Jo
1    15     88      low   Andrew
2   100     70     high       AI
3    65     80   medium  Michael
4    75    999     high   Nikola
5    55     70   medium      Jim
6    29      8      low    Bojan
7    45     95   medium    Vurce

# 错误尝试
# df_dast = df.groupby("da_value")
# scaler = StandardScaler()
# df_dast_scaled = scaler.fit_transform(df_dast)

# 识别数值列和非数值列
numerical_cols = df.select_dtypes(include=['number']).columns.tolist()
non_numerical_cols = df.select_dtypes(exclude=['number']).columns.tolist()

scaled_dfs = []
for name, group in df.groupby("da_value"):
    # 创建StandardScaler实例
    scaler = StandardScaler()

    # 仅对分组内的数值列进行标准化
    scaled_data = scaler.fit_transform(group[numerical_cols])

    # 将标准化后的数据转换回DataFrame，并保留原始索引
    scaled_group_df = pd.DataFrame(scaled_data, columns=numerical_cols, index=group.index)

    # 将非数值列与标准化后的数值列合并
    # 确保合并时保留分组列本身，如果需要的话
    # 这里我们直接将原始非数值列与标准化后的数值列拼接
    combined_group_df = pd.concat([group[non_numerical_cols], scaled_group_df], axis=1)

    scaled_dfs.append(combined_group_df)

# 将所有处理过的分组DataFrame拼接回一个完整的DataFrame
df_standardized_iterative = pd.concat(scaled_dfs).sort_index()

print("\n--- 迭代处理后的DataFrame ---")
print(df_standardized_iterative)

def standardize_numerical_columns_in_group(group_df):
    """
    对传入的DataFrame（一个分组）中的数值列进行标准化。
    """
    scaler = StandardScaler()

    # 识别当前分组中的数值列
    current_numerical_cols = group_df.select_dtypes(include=['number']).columns.tolist()
    # 识别当前分组中的非数值列
    current_non_numerical_cols = group_df.select_dtypes(exclude=['number']).columns.tolist()

    if not current_numerical_cols:
        # 如果没有数值列，直接返回原始分组
        return group_df

    # 对数值列进行fit_transform
    scaled_numerical_data = scaler.fit_transform(group_df[current_numerical_cols])

    # 将标准化后的数据转换为DataFrame，并保持原始索引
    scaled_df_part = pd.DataFrame(scaled_numerical_data, 
                                  columns=current_numerical_cols, 
                                  index=group_df.index)

    # 将非数值列与标准化后的数值列合并
    # 确保原始列的顺序和类型得到保留
    # 使用pd.concat并指定轴为1，确保列的正确拼接
    # 保持原始DataFrame的列顺序，将非数值列放在前面

    # 首先获取原始DataFrame的列顺序
    original_cols_order = df.columns.tolist()

    # 创建一个包含非数值列和标准化数值列的新DataFrame
    recombined_df = pd.concat([group_df[current_non_numerical_cols], scaled_df_part], axis=1)

    # 按照原始DataFrame的列顺序重新排列列
    return recombined_df[original_cols_order]

# 应用groupby().apply()
# group_keys=False 避免将分组键作为新的索引层级
df_standardized_apply = df.groupby('da_value', group_keys=False).apply(standardize_numerical_columns_in_group)

print("\n--- 使用groupby().apply() 标准化后的DataFrame ---")
print(df_standardized_apply)

df_encoded = pd.get_dummies(df, columns=['da_value'], prefix='da_value')
print("\n--- One-Hot Encoding 后的DataFrame ---")
print(df_encoded)

   cost  sales    names  da_value_high  da_value_low  da_value_medium
0    30     80       Jo              0             1                0
1    15     88   Andrew              0             1                0
2   100     70       AI              1             0                0
3    65     80  Michael              0             0                1
4    75    999   Nikola              1             0                0
5    55     70      Jim              0             0                1
6    29      8    Bojan              0             1                0
7    45     95    Vurce              0             0                1