Python: 利用pandas包完成多层次表头excel数据的转置操作

问题

因为业务要求，需要将表A转化为表B方便后续关联其他信息，分析商品上货情况。

初级方法

使用的关键函数为：fillna()，MultiIndex.from_product()，stack()，reset_index()

导入数据

导入xlsx使用的是pd.read_excel()

需要注意的是pandas导入xlsx使用的是xlrd包，而只有旧版本的xlrd(1.2.0) 包支持导入xlsx文件，最新版本的xlrd包不支持xlsx而支持xls。

import pandas as pd
df_raw = pd.read_excel(r'XXXXX\practice.xlsx')

观察数据

导入数据之后，需要根据数据呈现出的格式进行相应的操作。

可以看出:

1. excel中作为列信息的店铺在读取之后变成了行信息;
2. 合并的单元格被拆分，并且拆分后只有一个单元格有信息。

处理思路及代码

1. 将产品类别和产品质量字段设置为index，然后设置店铺等级和店铺为多层级columns；
2. 使用stack()对表格进行转置操作，此时多层次的columns也变为了index；
3. 使用reset_index()将index恢复为列即完成。

第一步：

from collections import OrderedDict #该包用于生成去重并且顺序不变的列名列表
def remove_duplicates(lst):#定义列表去重并不改变顺序的函数
    return list(OrderedDict.fromkeys(lst))

df = df_raw.copy()
df.fillna(method = 'ffill',inplace = True)
#生成第二层级的列名列表
col_index = remove_duplicates(df.iloc[0,:][df.iloc[0,:].notna()].to_list())
#删除第一行
df = df.drop(0,axis = 0)
#set_index
df = df.set_index(['产品类别','产品质量'])
#添加columns index
col_index2 = [x for x in df.columns.to_list() if 'Unnamed'not in x ]#生成第一层级的列名列表，这一步要在set_index之后，否则会把多余的列名也包含在内
columns_multiindex = pd.MultiIndex.from_product([col_index2,col_index], names=['店铺等级', '店铺'])
df.columns=columns_multiindex

添加完index和多层级columns之后效果如下：

第二三步：

#stack，将column index 转化为index（类比excel里的行列转置操作），然后通过reset_index将所有index恢复为列
df = df.stack(['店铺等级', '店铺']).reset_index()
df

效果：

PS：这么一通操作总算是对dataframe的索引有了更深的了解了，iloc，loc什么的概念也都清楚了。

进阶方法

在实际工作中处理表格数据时，发现上面的方法只适用于以下有包含关系的列名（主要原因是使用了MultiIndex.from_product）：
而当关系变得更加复杂时……就不适用啦：

导入&观察数据

还是可以看出:

1. excel中作为列信息部分在读取之后变成了行信息;
2. 合并的单元格被拆分，并且拆分后只有最左边的单元格有信息。

处理思路及代码

整体思路和初级方法中的思路类似，依旧是围绕数据框的索引进行操作，在进行stack()之前分别提取和处理行索引、列索引和值，构建新的数据框，整体的步骤和函数反而更加简单。

分步操作版：

提取列索引

提取列索引（包括当前列索引）为值，行填充空值。

df_column = df.iloc[0:4,2:]
df_cols = pd.DataFrame([df_column.columns.to_list()],columns = df_column.columns.to_list(),index = [0])
df_column = pd.concat([df_cols,df_column]).reset_index(drop = True)
df_column.loc[0,df_column.loc[0,:].str.contains('Unnamed') == True] = np.nan
df_column.fillna(method = 'ffill',inplace = True,axis = 1)

提取行索引

提取行索引

df_index = df.loc[:,['列名1','列名2']]
#df_index.fillna(method = 'ffill',inplace = True)
df_index = df_index.drop([0,1,2,3],axis = 0).reset_index(drop = True)
df_index

注：将df切片并且传递给新变量df_index = df.loc[:,['列名1','列名2']]时，需要用iloc或者loc，否则会出现SettingWithCopyWarning。

提取值

df_value = df.iloc[4:,2:]
df_value

构建新数据框

df_value.index = df_index.T.values.tolist()
df_value.columns = df_column.values.tolist()
df_value.index.names = ['列名1','列名2']#这里命名的是行索引的名称
df_value.columns.names = ['colname1','colname2','colname3','colname4','colname5']#这里命名的是列索引的名称
#行索引名称和列索引名称在最终的结果表中都是作为列名出现

在jupyter中呈现的结果是这样的，看上去没有对齐可能会造成误解，实际上已经是想要的形式了，可以导出到csv里核实一下：

转置

df_new = df_value.stack(['colname1','colname2','colname3','colname4','colname5']).reset_index()

还自动剔除了NA行

函数版：

def multicolumnlevel_to_one(raw_dataframe,row_level_cnt,column_level_cnt,row_index_name,column_index_name):
    #raw_dataframe：待处理表格 dataframe 要求删除不需要的列
    #row_level_cnt：原始表格中行索引列的数量 int
    #column_level_cnt：原始表格中列名层次的数量 int
    #row_index_name：需要设置为行索引的列的列名 list 顺序是从左到右的要作为行索引列的名称
    #column_index_name：列索引名称 list 顺序是从上到下的不同列名
    if column_level_cnt >= 2:
        #step 1 提取列索引
        df_column = raw_dataframe.iloc[:(column_level_cnt-1),row_level_cnt:]
        df_cols = pd.DataFrame([df_column.columns.to_list()],columns = df_column.columns.to_list(),index = [0])
        df_column = pd.concat([df_cols,df_column]).reset_index(drop = True)
        df_column.loc[0,df_column.loc[0,:].str.contains('Unnamed') == True] = np.nan
        df_column.fillna(method = 'ffill',inplace = True,axis = 1)

        #step 2 提取行索引
        df_index = raw_dataframe.loc[:,row_index_name]
        df_index.fillna(method = 'ffill',inplace = True)
        df_index = df_index.drop(list(range(column_level_cnt-1)),axis = 0).reset_index(drop = True)

        #step 3 提取值
        df_value = raw_dataframe.iloc[(column_level_cnt-1):,row_level_cnt:]

        #step 4 构建新数据框
        df_value.index = df_index.T.values.tolist()
        df_value.columns = df_column.values.tolist()
        df_value.index.names = row_index_name
        df_value.columns.names = column_index_name

        #step 5 转置
        df_new = df_value.stack(column_index_name).reset_index()
    else: #如果只有一行的列名
        df_new = raw_dataframe.set_index(row_index_name)
        df_new.columns.name = column_index_name[0]
        df_new = df_new.stack().reset_index()
    return df_new

函数还满足了以下这种表格的转置：

End