早下班系列:轻松玩转Excel行列转换

发布时间:2018-02-13 分类:产品文章 Tag:,

早下班系列2

下面这种交叉式的Excel表是很常见的格式,用来填写和查看都比较方便:

但是,如果想做进一步的统计分析,这种格式就不方便了,需要行列转换,变成如下格式的明细表:

显然,手工操作会非常麻烦,若数据量小还可以,数据量大了会耗费大量时间,简直就是灾难。

Excel也可以通过数据透视表支持行列转换功能,效果如下图:

但这并不是我们想要的格式。

看来只能写个程序来解决了,思路也很简单:

  1. 加载excel文件,装载需要的sheet工作表。
  2. 读取账套名所在的行,将其转换成字符串数组。
  3. 读取科目编码所在列,将其转换成字符串数组。
  4. 按科目编码分组,与账套名数组构造一张表。
  5. 根据账套名对应的数据,遍历所有的明细值填充到相应的表中。
  6. 这样就构造出对应的明细表来。

 

如果用Java来实现,初步估计代码量也不会少于200行,若需要结果输出成excel文件则开发工作量会更多。Excel自己虽然提供了VBA,但那个麻烦度谁用谁知道,不提也罢。其它的语言呢?传说python有处理行列转换的功能(pandas包里有pivot功能),代码量相对于java会少很多, 我们来试一下:

import pandas as pd
import numpy as np

 

df = pd.read_excel(“D:\\excel\\pandas.xlsx”, 0, 3)

cols = df.columns.values.tolist() #获取数据头信息

#移去前两列,只保留需要行列转换的列

cols.remove(‘科目编码’)

cols.remove(‘科目明细’)

 

#构造一个list.

frames=[]

for col in cols:

df1 = df.pivot_table(index = [‘科目编码’,’科目明细’], values = [col])

df1.rename(columns={col: ‘数值’}, inplace=True)

df1[3]=col

#转换后的数据追加到frames中.

frames.append(df1)

 

# concat将相同字段的表首尾相接

result=pd.concat(frames)

result.rename(columns={3: ‘帐套名’}, inplace=True)

result.to_excel(‘D:\\excel\\pandas_n.xlsx’, sheet_name=’科目明细’)

 

嗯,还不错,果然比较简洁!这是Python生成的excel文件:

不过,这里有点小问题,这个excel格式有点特殊,想用Python的 pivot,我们要将“科目编码”,“科目明细”移到与转换列标题所在同一行上,变成下面的样子。否则在代码上就得特殊 “照顾”,反正只有一行,手工做一下就算了,比写代码省事。

无论如何,python的这个细节处理的小”瑕疵”并不影响其方便性。python确实名不虚传,虽然使用了循环,但整个代码也就只有10来行的样子。

 

还能更简单吗?

嘿嘿,能!

我们来看集算器的代码:

A B
1 =file(“D:/excel/明细.xlsx”).importxls@t(;1,3:40) //读入excel文件
2 >A1.delete(A1.select(_1==”科目编码”)) //清除首列为”科目编码”所在的行
3 >A1.rename(_1:科目编码,_2:科目明细) //更换列1名称为科目编码,列2名称为科目明细
4 =A1.fname().to(3,).concat(“,”) //将从第三列的列名连成字符串,用,分开
5 =A1.pivot@r(科目编码,科目明细;账套名,数值;${A4}) //用pivot函数进行行列转换
6 =file(“D:/excel/明细2.xlsx”).exportxls@t(A5;”科目明细”) //将整理好的数据另存储为xlsx文件

 

代码很简单,我们把每一步的中间结果列出来看看:

A1:加载excel文件工作表1,提取指定范围的数据(从3行到40行),其中选项@ t表示首行为标题,载入数据,生成表格如下:

A2:删除非数据行

A3:更换列名称

A4:把从第三列开始的列名称连成字符串,用“,”分开

A5:pivot函数将行列数据进行转换,把A4中对应的列数据置放到“数值”列

A6:将整理好的数据另存储为xls文件

集算器脚本只6行,而且木有啥循环、判断之类的玩意儿,也不像Python那样要先手工倒腾一下,就把这看似有点“乱”的数据表格处理好了。相比之下,Python采用列优先转换多次循环 “N”字方式,集算器则用行优先一次性处理,在处理数据上,集算器对细节处理及使用习惯更专业。而且集算器的开发环境也容易调试,可以看到每一步运算的中间结果,方便挑出错误,开发更为便捷。在这种常规数据处理的任务中,集算器要比Python更为优越。

 

就这个问题,关于 python与集算器的差异,再说说自己的一点体会:

1.多列转换:

对于需要多列行列转换并汇集成“长”列的场景时,python需要将每个数据列构造成数组,并增加一列记录当前列名,再追加到一个大的列表中,最后合并,合并中去掉非首个数组中的title;

集算器就容易些,它直接把想要转换的列汇集在一块就行。相对于python的繁琐,集算器至少能省几个脑细胞。

2.名称更改:

python对于需要转换列的名称不能更改,如cols[0]=’天津’,此时python找不到修改前的关键字,“哪个朋友挖的坑,别以为我发现不了”,欺负大爷眼花,给报个异常行不?

但对应的集算器来说则很方便,如:>A1.rename(_1:科目编码,_2:科目明细,4成都: 成都)

3.标题空值问题:

Python读取excel表中的转换行标题时,前面两列为空(对应原来的excel中的“科目编码,科目明细”),此时标题cols中的空值就没有了,这个“坑”有点隐蔽啊,我真没有发现,把其中的两列弄丢了,真有点丢脸 ;

但集算器能识别出来,会自动加上对应的标识_1、_2,这样处理数据时,就能找到其中对应的两列。

4.网格式编程

集算器使用网格A1这种格式,它自动与所在位置的对象关联起来,这点非常方便,感觉很有特色;Python就只能望洋兴叹了。