问题
因为业务要求,需要将表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')
观察数据
导入数据之后,需要根据数据呈现出的格式进行相应的操作。
可以看出:
- excel中作为列信息的店铺在读取之后变成了行信息;
- 合并的单元格被拆分,并且拆分后只有一个单元格有信息。
处理思路及代码
- 将产品类别和产品质量字段设置为index,然后设置店铺等级和店铺为多层级columns;
- 使用stack()对表格进行转置操作,此时多层次的columns也变为了index;
- 使用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):
而当关系变得更加复杂时……就不适用啦:
导入&观察数据
还是可以看出:
- excel中作为列信息部分在读取之后变成了行信息;
- 合并的单元格被拆分,并且拆分后只有最左边的单元格有信息。
处理思路及代码
整体思路和初级方法中的思路类似,依旧是围绕数据框的索引进行操作,在进行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