pandas导入Excel文档后，建立层次化索引的请教

nh_wzg

@疾风怪盗

现在想请教另外一个处理Excel文档常见问题：
如何快速完成层次化索引列的建立
样例文件：ym.xlsx
样例文件下载： https://pan.baidu.com/s/1y4EkSVp35Ikm-wVTzvr6cg 提取码: tq6d
样例文件初始截图：https://s3.ax1x.com/2020/12/10/rPBNw9.jpg

                               
登录/注册后可看大图

样例文件目标截图：https://s3.ax1x.com/2020/12/10/rPBwJx.jpg

                               
登录/注册后可看大图

目标是变成带层次索引的，规则的数据文件，准备作下一步的归类与透视处理。
在Jupyter中，导入pandas库后，运行查看样例文件基本概况、调入第一个worksheet语句的截图：https://s3.ax1x.com/2020/12/10/rPBdF1.jpg

                               
登录/注册后可看大图

对需要进行层次化索引的条目内容与明细条目相区别开，进行观察的语句与结果截图：https://s3.ax1x.com/2020/12/10/rPBUoR.jpg

                               
登录/注册后可看大图

以NaN作为条件，把层次化的索引内容，列出来后，可以看到：

目标处理方式：

先生成[层次_1]的列名，
对于行：5-15之间，以行：5上面的[Unamed:3]内容填充，
对于行：16-28之间，以行：16上面的[Unamed:3]内容填充，
对于行：29-31之间，以行：29上面的[Unamed:3]内容填充，
对于行：32-54（最末行）之间，以行：32上面的[Unamed:3]内容填充，

然后生成[层次_2]的列名，
对于行：4-54（最末行）之间，以行：4上面的[Unamed:1]内容填充，

最后生成[层次_3]的列名，
对于行：3-54（最末行）之间，以行：3上面的[unamed:1]内容填充，

这里样例中，至少分三级层次的索引列。怎样高效生成。

暂时考虑的方向：
[层次]属性列
如果问题1：已经解决，行索引号与上一条、下一条记录的索引号的关系，决定它所处的层次。
与下一条记录的行索引号是非+1关系，与上一条记录的行索引号是+1关系，为最末端层次，可记为1。
与下一条记录的行索引号是+1关系，则是下一条记录层次数+1,这里是1+1=2,
与下一条记录的行索引号是+1关系，则是下一条记录层次数+1,这里是1+2=3.



【附加的问题】
行：0,1,我可以直接删除
行：2,3,前面的空白单元格，不知道由pandas读入时，不知为何与NaN填充的空白单元格不一致。无法与下面的行：4,5,。。。。作区别。
问题1：读入的excel文件内worksheet内单元格，无内容，但又不显示NaN时，怎样统一化处理为NaN以方便后面的数据处理？
问题2：如果已经解决问题1,则这里样例中，至少分三级层次的索引列。怎样高效生成。

【处理流程】
1、以pandas导入样例文件:ym.xlsx，并调用其中一张worksheet。
2、观察无关的行，其对应列上面的特性，作为与真正要保留数据行区分的依据。
3、逐次清理无关行。
4、补充层次索引用字段内容到新增列。

疾风怪盗

你这个，完全看不懂你的意思？你是要复合索引？还是什么，看看其他大佬有没有解决方法吧

疾风怪盗

我看你这表格设计的也有问题啊，抬头和页码部分可以写入页眉和页脚，这样表格内容部分就完整了，然后再用pd读取，应该很容易达到你要的效果吧
如果你一定要这样设计表格，如果表格单元格的位置都是固定的，那直接用openpyxl来处理，应该更好

nh_wzg

疾风怪盗 发表于 2020-12-10 09:00
我看你这表格设计的也有问题啊，抬头和页码部分可以写入页眉和页脚，这样表格内容部分就完整了，然后再用pd ...

1、这种表格是某种行业预算软件直接导出的结果，基本不会有标准地把表头放入页眉，页脚这样的处理，手工处理，不胜其烦。

2、现在就是想看到清洗数据环节中，对数据记录所具有的位置属性信息，按符合数据整理的方式进行信息补全。

nh_wzg

疾风怪盗 发表于 2020-12-10 07:49
你这个，完全看不懂你的意思？你是要复合索引？还是什么，看看其他大佬有没有解决方法吧

1、实际就是看起来如同一种复合型索引的形式。在Excel中，是表格的分级或叫分组。

2、具体来说就是把预算表格的记录中，所属分级、分部、分项、worksheet名称、workbook名称，统一进行补全。

菜鸟实在不太了解python下面对这种，已经把分级、分部、分项名称提取到各级顶部的表现形式，重做为，每一条记录，自带分级、分部、分项名称属性内容的形式。

疾风怪盗

nh_wzg 发表于 2020-12-10 20:03
1、实际就是看起来如同一种复合型索引的形式。在Excel中，是表格的分级或叫分组。

2、具体来说就是把 ...

太难了，放弃了

nh_wzg

思路：
1、列出Excel中的分级或叫分组标题。
2、标定分级/分组标题的级次。
3、按级数建立空值列。
4、按级数建立级数内循环结构。
5、在级数内循环结构，取本级所有标题索引号为一个list，
取第一条对应的索引号，对本级对应新建空值列赋值为第一条标题。
索引号加1,判断是否大于等于本级list中第二个索引号，
否，对本级对应新建空值列赋值为第一条标题。
是，用此索引号，取第二条标题，对本级对应新建空值列赋值为第二条标题。

nh_wzg

疾风怪盗 发表于 2020-12-10 21:37
太难了，放弃了

实际应该也不难，主要是对这种表格的处理熟悉程度不同而已。用手工的方式，基本知道如何处理出来，不会写完整的脚本。

希望得到你的指点。

import pandas as pd
import re
pd.ExcelFile("y:\ym.xlsx").sheet_names   #得到sheetnames:['汇总表', '变配电和外线部分', '低压和监控部分']
f2=pd.ExcelFile("y:\ym.xlsx")
f3=f2.parse(sheet_name='变配电和外线部分')  #导入一张表
f3[f3['Unnamed: 3'].notnull() & f3['Unnamed: 4'].isnull() & f3['Unnamed: 5'].isnull()]  #观察后找到含有【分组/分级标题】的行
f3.drop(f3[f3['分类分项工程量清单计价表'].isin(['合同编号:','工程名称：'])].index,inplace=True)  #删除多余行，这只是一种例子的状态，还有其他几种可删除状态，进行清洗。
f3['s1']=None  #第1级分组标题列初始化
f3['s2']=None  #第2级分组标题列初始化
f3['s3']=None  #第3级分组标题列初始化
f3.loc[f3['Unnamed: 3'].str.contains('\d\.') & f3['Unnamed: 4'].isnull() ,'s1']=f3['Unnamed: 3']  #为第1级分组标题列，按相应标题位置进行赋值。
f3.loc[:,'s1']=f3['s1'].fillna(method='ffill')  #为第1级分组标题列，按相应标题，对本级空值，用【forward fill】方式进行赋值
f3.loc[f3['Unnamed: 3'].str.contains('^一|^二') & f3['Unnamed: 4'].isnull() ,'s2']=f3['Unnamed: 3']  #为第2级分组标题列，按相应标题位置进行赋值。
f3.loc[:,'s2']=f3['s2'].fillna(method='ffill')  #为第2级分组标题列，按相应标题，对本级空值，用【forward fill】方式进行赋值
f3.loc[f3['Unnamed: 3'].str.contains('^第一|^第二') & f3['Unnamed: 4'].isnull() ,'s3']=f3['Unnamed: 3']  #为第3级分组标题列，按相应标题位置进行赋值。
f3.loc[:,'s3']=f3['s3'].fillna(method='ffill')  #为第3级分组标题列，按相应标题，对本级空值，用【forward fill】方式进行赋值
f3.drop(f3[f3['Unnamed: 3'].notnull() & f3['Unnamed: 4'].isnull()].index,inplace=True)  #把【仅】含有【分组/分级标题】的行，而无数据内容行，删除
f3.head(8)