Excel能够实现的功能，目前Pandas都能够实现。倘若你已经能够熟练运用Pandas的"宽转长" wide_to_long()和透视表pivot_table()函数，建议您直接跳过本文。
这是工作中遇到的一个真实案例。
需求说明
在上一篇文章中实现了使用Pandas处理工作中的实际问题，Python Pandas比较BOM表数据。此次需求是将材料变更的多列记录数据转成多行，演示数据如下图：
演示数据
这是一些材料（P/N）更换供应商时，供应商（Supplier）和供应商料号（Supplier PN）变更记录表。从表中可以看出Supplier和Supplier PN是成对循环出现，每颗材料的变更次数都可能不同，有些材料变更可能多达20次，有些可能仅有1次。另外因为某些原因，一些变更只记录了Supplier信息，未记录Supplier PN。同样也存在相反的情况，即记录了Supplier PN但未记录Supplier。
本文要实现两个需求，它们分别是：
1，填充未记录的Supplier和Supplier PN 为NOT CLEAR，但Supplier和Supplier PN 都为空值则不填充，如下图：
填充数据 ‘NOT CLEAR’
2，将同一材料变更记录的多列数据转换成多行数据，即每行只记录一次变更数据，效果如下图：
多列转多行效果
◆ 读取Excel文件
sample_df = pd.read_excel(r'/users/dongdong/downloads/demo_data.xlsx',sheet_name='sample')
sample_df
下图中发现为了便于区分各列，Pandas已经自动将第二组和第三组的Supplier 和Supplier PN后加上了.数字。
列名自动加上 '.数字'
◆ 处理列名
要想使用wide_to_long()方法，我们需要将要转换的列名格式处理成一致，即将第一组的Supplier和Supplier PN 变成Supplier.0以及Supplier PN.0。
sample_df.columns = np.where(sample_df.columns.str.match('^Supp.*\D+$'),
sample_df.columns + '.0',
sample_df.columns)
这里用到了numpy.where(condition,[x, y])和正则表达式match()，前者是基于条件condition，返回值来自x或者y。上面代码主要将不含有.的列名加上.0，含有的保持不变。当然也通过直接暴力修改列名的方式实现：
sample_df.rename(columns={'Supplier':'Supplier.0','Supplier PN':'Supplier PN.0'},inplace=True)
注意列名变化
◆ wide_to_long()函数
Pandas有这样的一个函数，个人姑且称其为“宽到长格式”，详见使用文档。对于存根名称['A'，'B']，此函数希望查找格式为A-suffix1，A-suffix2…，B-suffix1，B-suffix2等的一组或多组列。显然我们的数据列名Supplier.0 Supplier.1 Supplier.2 和Supplier PN.0 Supplier PN.1 Supplier PN.2符合此规则。wide_to_long()基本语法如下：
pd.wide_to_long(df, stubnames(提取以指定字符串开头的列),
i（用作索引的列),
j（提取开头后剩余的部分会成一列,在此指定列名),
sep（分隔符),
suffix（捕获正则表达式匹配的后缀)
)
根据上面的基本语法，我们可实现下列代码：
unpivotted_df = (pd.wide_to_long(sample_df,
stubnames = ['Supplier','Supplier PN'],
i = ['P/N','Description'],
j = 'idx',
sep='.'))
来看下效果，idx列是Supplier.0，Supplier.1 …….号后面的数字部分：
使用wide_to_long函数后
◆ NaN个数判断
接着对每行进行判断，判断条件是每行只有1个不为空NaN，eq(1) = 1。
cond = unpivotted_df.notna().sum(axis = 1).eq(1)
cond
注意只有2行为True, 它们就是需要填充NOT CLEAR的行。
判断结果
◆ 填充NOT CLEAR
根据判断结果，对指定的cell进行填充：
unpivotted_df[:]=np.where(cond[:,None],unpivotted_df.fillna('NOT CLEAR'),unpivotted_df)
unpivotted_df
[:,None]，None表示该维不进行切片，将该维整体作为数组元素处理，即维数+1。
填充后的效果
◆ 填充后恢复到多列
如果需要恢复到原来多列样式，我们可以使用如下代码，这里有个
unstack()函数，值得研究。
out = unpivotted_df.unstack().sort_index(level=1,axis=1).reindex(df[['P/N','Description']])
out.columns=out.columns.map('{0[0]}.{0[1]}'.format)
out.reset_index()
修改后的结果
◆ 删除idx列
为实现本文最终目的，其实我们并不需要将其恢复到原来样式，在此只为了说明相关函数。我们还是在填充后的效果图基础上说明：
填充后的多行效果
删除包含空值的行，重新设置index，最后删掉多余的列
idx，如此即可得到我们想要的结果。
unpivotted_df.dropna().reset_index().drop('idx',axis=1)
最终效果
◆ 完整代码
sample_df = pd.read_excel(r'/users/dongdong/downloads/demo_data.xlsx',sheet_name='data')
sample_df.columns = np.where(sample_df.columns.str.match('^Supp.*\D+$'),
sample_df.columns + '.0',
sample_df.columns)
unpivotted_df = (pd.wide_to_long(sample_df,
stubnames = ['Supplier','Supplier PN'],
i = ['P/N','Description'],
j = 'idx',
sep='.'))
cond = unpivotted_df.notna().sum(1).eq(1)
unpivotted_df[:]=np.where(cond[:,None],unpivotted_df.fillna('NOT CLEAR'),unpivotted_df)
out = unpivotted_df.dropna().reset_index().drop('idx',axis=1)
out.to_excel('columns_to_rows.xlsx')
◆ 另一种处理方法
还可以使用Pandas melt()和pivot_table()完成多列到多行的转行，前提是已经进行了填充。相关示例如下：
方法二示例数据
(df.melt(['P/N','Description']).dropna()
.assign(stub=lambda x: x.variable.str.extract('([^\.]*)\.?'),
idx=lambda x: x.groupby('stub').cumcount())
.pivot_table(index=['P/N','Description','idx'],
columns='stub',
values='value',
aggfunc='first').reset_index().drop('idx', axis=1))
)
方法二示例转化结果
网友评论