使用Python/Pandas清洗并重构CSV格式的丰田业务数据

我需要处理一份结构混乱的丰田业务CSV原始数据，通过Python的Pandas库将其清洗、重构为规范的结构化数据。以下是原始数据、目标数据的结构字典及相关上下文：

原始数据结构（字典形式） #

{
 'Company Name ': {0: 'Report Month',  1: 'Report Year',  2: nan,  3: nan,  4: nan,  5: nan,  6: nan,  7: nan,  8: nan,  9: nan},
 'Toyota': {0: 'Jan ',  1: '2023',  2: nan,  3: nan,  4: nan,  5: nan,  6: nan,  7: nan,  8: nan,  9: nan},
 'Unnamed: 2': {0: nan,  1: nan,  2: nan,  3: nan,  4: 'Total Inventory Cost',  5: 'Sold Inventory Cost',  6: 'Total Profit Incurred',  7: 'Total Manpower Expense',8: 'Total Infra Expense',9: 'Total Transaction Expense'},
 'Unnamed: 3': {0: nan,  1: nan,  2: nan,  3: nan,  4: nan,  5: nan,  6: nan,  7: nan,  8: nan,  9: nan}, 
 'Unnamed: 4': {0: nan,  1: nan,  2: 'Sales in',  3: 'Ohio Showroom',  4: '344469',  5: '300690',  6: '43779',  7: '15000',  8: '500',9: '110'},
 'Unnamed: 5': {0: nan,  1: nan,  2: 'Sales in',  3: 'Wincosin Showroom ',  4: '11261',  5: '9050',  6: '2211',  7: '1000',  8: '200',  9: '55'}, 
 'Unnamed: 6': {0: nan,  1: nan,  2: 'Service in',  3: 'Ohio Showroom',  4: '659923',  5: '612231',  6: '47692',  7: '12000',  8: '400',  9: '110'},
 'Unnamed: 7': {0: nan,  1: nan,  2: 'Service in',  3: 'Wincosin Showroom ',  4: '15656',  5: '12812',  6: '2844',  7: '1200',  8: '250',  9: '45'}
}

目标规范数据结构（字典形式） #

{
 'Company Name': {0: 'Toyota ',  1: 'Toyota ',  2: 'Toyota ',  3: 'Toyota ',  4: 'Toyota ',  5: 'Toyota '},
 'Report Month': {0: 'January',  1: 'January',  2: 'January',  3: 'January',  4: 'January',  5: 'January'},
 'Report Year ': {0: 2023, 1: 2023, 2: 2023, 3: 2023, 4: 2023, 5: 2023},
 'Parameter': {0: 'Total Inventory Cost',  1: 'Sold Inventory Cost',  2: 'Total Profit Incurred',  3: 'Total Manpower Expense',  4: 'Total Infra Expense',  5: 'Total Transaction Expense'},
 'Sales in Ohio Showroom': {0: 344469,  1: 300690,  2: 43779,  3: 15000,  4: 500,  5: 110}, 
 'Sales in Wincosin Showroom': {0: 11261,  1: 9050,  2: 2211,  3: 1000,  4: 200,  5: 55},
 'Service in Ohio Showroom': {0: 659923,  1: 612231,  2: 47692,  3: 12000,  4: 400,  5: 110},
 'Service in Wincosin Showroom ': {0: 15656,  1: 12812,  2: 2844,  3: 1200,  4: 250,  5: 45}
 }

清洗与重构代码实现 #

下面是完整的Pandas处理代码，每一步都做了明确的处理：

import pandas as pd

# 文件路径配置
file_path = r"C:\Users\abcd\OneDrive\Documents"
messy_data_file_name = 'messy_data.csv'
cleaned_data_file_name = 'cleaned_Data.csv'

# 读取原始数据
messy_df = pd.read_csv(file_path + '\\' + messy_data_file_name)

# 1. 提取元数据：公司名、报告月份、年份
company_name = messy_df.columns[1].strip()  # 提取Toyota并去除空格
report_month = messy_df.iloc[0, 1].strip()
# 月份缩写转全称
month_map = {'Jan': 'January', 'Feb': 'February', 'Mar': 'March', 'Apr': 'April',
             'May': 'May', 'Jun': 'June', 'Jul': 'July', 'Aug': 'August',
             'Sep': 'September', 'Oct': 'October', 'Nov': 'November', 'Dec': 'December'}
report_month_full = month_map.get(report_month[:3], report_month)
report_year = int(messy_df.iloc[1, 1].strip())

# 2. 提取参数列表（Unnamed:2列的第4到第9行）
parameters = messy_df.iloc[4:10, 2].tolist()

# 3. 提取各展厅的指标数据
# Sales in Ohio Showroom
sales_ohio = messy_df.iloc[4:10, 4].astype(int).tolist()
# Sales in Wincosin Showroom
sales_wincosin = messy_df.iloc[4:10, 5].astype(int).tolist()
# Service in Ohio Showroom
service_ohio = messy_df.iloc[4:10, 6].astype(int).tolist()
# Service in Wincosin Showroom
service_wincosin = messy_df.iloc[4:10, 7].astype(int).tolist()

# 4. 构建清洗后的DataFrame
cleaned_data = {
    'Company Name': [company_name] * len(parameters),
    'Report Month': [report_month_full] * len(parameters),
    'Report Year': [report_year] * len(parameters),
    'Parameter': parameters,
    'Sales in Ohio Showroom': sales_ohio,
    'Sales in Wincosin Showroom': sales_wincosin,
    'Service in Ohio Showroom': service_ohio,
    'Service in Wincosin Showroom': service_wincosin
}

cleaned_df = pd.DataFrame(cleaned_data)

# 保存清洗后的数据
cleaned_df.to_csv(file_path + '\\' + cleaned_data_file_name, index=False)

# 验证结果
print(cleaned_df.to_dict())

代码说明： #

• 元数据提取：从原始数据的表头和前两行提取公司名、月份（将缩写转为全称）、年份，并做类型转换。
• 参数提取：直接从Unnamed:2列中提取业务指标名称。
• 指标数据提取：分别从对应列提取各展厅的数值数据，转换为整数类型。
• DataFrame构建：将所有提取的数据整合成规范的键值对，生成结构化DataFrame后保存。

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