数据结构与算法设计-作业4-excel表合并与数据整理

作业4

题目

aa文件夹里有若干个Excel表文件，看上去非常凌乱。请编写Python程序将其中相同类别的Excel表合并到一起，有利于今后管理和分析数据。

数据观察

1. 文件合并：先读取数据，遍历文件夹中的所有 Excel 文件，并将它们合并成一个数据框 combined_df。观察到文件的命名方式（1月到12月），可批量读取。为了合并时不出现错乱，在合并前还需要读取每个文件并检查列名是否一致：

   # 定义月份列表
   months = [f'{i}月' for i in range(1, 13)]
   
   # 存储所有文件的列名
   columns_set = set()
   
   # 依次读取每个 Excel 文件并检查列名
   for month in months:
       file_path = os.path.join(folder_path, f'{month}.xlsx')
       if os.path.exists(file_path):
           df = pd.read_excel(file_path)
           columns_set.add(tuple(df.columns))
       else:
           print(f'{file_path} 不存在')
   
   # 检查所有文件的列名是否一致
   if len(columns_set) == 1:
       print("所有文件的列名一致，开始合并文件")
       
       # 合并所有文件
       combined_df = pd.DataFrame()
       for month in months:
           file_path = os.path.join(folder_path, f'{month}.xlsx')
           if os.path.exists(file_path):
               df = pd.read_excel(file_path)
               combined_df = pd.concat([combined_df, df], ignore_index=True)
       
       # 保存合并后的文件
       combined_file_path = os.path.join(folder_path, '合并后的文件.xlsx')
       combined_df.to_excel(combined_file_path, index=False)
       print(f"文件已合并并保存到 {combined_file_path}")
   else:
       print("文件的列名不一致，无法合并")
   

2. 数据清洗：去除重复值

3. 缺失项检查：使用 isnull().sum()方法检查每一列中缺失值的数量。打印结果如下：

   

   由于仅在某几本书上存在首次入库时间的缺失，该数据无法查询补充，若整行删去又会导致书籍信息丢失，因此选择保持缺失项不改变。

4. 列名整理：观察列名，可将商品信息分为两大类，一是每本书的基本信息，每本书只有一个固定值，如商品名称、SKU、品牌、一级类目、二级类目、三级类目、ISBN、首次入库时间，可将它们排在一起，而将和日期有关的交易信息放在后面，使其更为直观。代码如下：

   # 固定信息的列名
   fixed_columns = ['商品名称', 'SKU', '品牌', '一级类目', '二级类目', '三级类目', 'ISBN', '首次入库时间']
   # 获取所有列名
   all_columns = combined_df.columns.tolist()
   # 交易信息等其他列
   other_columns = [col for col in all_columns if col not in fixed_columns]
   # 重新排列列顺序
   new_column_order = fixed_columns + other_columns
   combined_df = combined_df[new_column_order]
   

   继续观察列信息，发现品牌列存在大量重复值“其他品牌”，因此查找品牌列是否有不同的品牌名，若全部仅为“其他品牌”，证明该列的信息无效，可以删去：

   if combined_df['品牌'].nunique() == 1 and combined_df['品牌'].iloc[0] == '其他品牌':
       print("品牌列只有一个唯一值“其他品牌”，将删除该列")
       combined_df.drop(columns=['品牌'], inplace=True)
   else:
       print("品牌列包含多个品牌名，保留该列")
   

   打印结果：
   

   检查一级类目，二级类目，三级类目各包含什么不重复的值：

   unique_first_category = combined_df['一级类目'].unique()
   unique_second_category = combined_df['二级类目'].unique()
   unique_third_category = combined_df['三级类目'].unique()
   
   

   得到结果：

   

   由于三级类目有多种分类，因此可以先按三级类目和商品名称进行排序：

   combined_df.sort_values(by=['三级类目', '商品名称'], inplace=True)
   

数据排序与整理

按每本书的统计量对df进行分组和聚合操作，首先以年为单位统计每本书的浏览及销售情况：

grouped_df = combined_df.groupby(['商品名称', 'SKU', '品牌', '一级类目', '二级类目', '三级类目', 'ISBN', '首次入库时间']).agg({
    '浏览量': 'sum',
    '访客数': 'sum',
    '人均浏览量': 'mean',
    '平均停留时长': 'mean',
    '成交商品件数': 'sum',
    '成交码洋': 'sum',
    '加购人数': 'sum'
}).reset_index()

检查输出：

观察输出结果，可知当前的DataFrame存在两个问题，导致本该唯一的商品信息出现重复：

1. ISBN码存在错误，有的行在正常的13位ISBN码后还存在多余的0，应该去掉。修改方式：先转为str格式，去掉小数点，再保留前13位

   combined_df['ISBN'] = combined_df['ISBN'].astype(str).str.replace('.', '').str[:13]
   

   可输出正确的ISBN码：
   

2. 三级类目存在错误，某些书被错误归类到了“编程语言与程序设计”中，应该检查每个商品名称对应的所有三级类目，只要出现了“编程语言与程序设计”之外的三级目录名，将所有该商品名称的行的三级目录名更换为新目录名：

   def correct_category(group):
       if '编程语言与程序设计' in group['三级类目'].unique():
           other_categories = group[group['三级类目'] != '编程语言与程序设计']['三级类目'].unique()
           if len(other_categories) > 0:
               new_category = other_categories[0]
               group['三级类目'] = new_category
       return group
   combined_df = combined_df.groupby('商品名称').apply(correct_category)
   

按照三级类目对数进行分组排序，并按照总的成交商品件数从大到小在类目内排序：

grouped_df = combined_df.groupby('三级类目').apply(lambda x: x.sort_values('成交商品件数', ascending=False)).reset_index(drop=True)

结果

得到所有行的排序后结果，保存到文件“排序后的文件.xlsx”；
并得到每本书的统计量排序结果，保存到“书目的统计量.xlsx”：