Excel 行列轉(zhuǎn)換的最簡方法

問題描述

??在工作中時常會遇到對 Excel 表格的處理。當編輯一張 Excel 表格時，發(fā)現(xiàn)表格的列數(shù)太多，而行數(shù)較少，為方便打印，這時你或許會希望將該表格行列轉(zhuǎn)換；或許是為了做進一步做統(tǒng)計分析，當前格式不太方便，這時也會用到行列轉(zhuǎn)換。

??下面這種交叉式的 Excel 表是很常見的格式，用來填寫和查看都比較方便：
??

??但是，如果想做進一步的統(tǒng)計分析，這種格式就不方便了，需要行列轉(zhuǎn)換，變成如下格式的明細表：
??

??顯然，手工操作會非常麻煩，若數(shù)據(jù)量小還可以，數(shù)據(jù)量大了會耗費大量時間，簡直就是災難。

??我們就以此為例，舉例說明幾種常見的解決方法。

解決方法

方法 1：Excel 數(shù)據(jù)透視表

??Excel 可以通過數(shù)據(jù)透視表支持行列轉(zhuǎn)換功能，效果如下圖：
??

??顯然，這并不是我們想要的格式。Excel 的數(shù)據(jù)透視表可以滿足簡單格式的行列轉(zhuǎn)換，但如果格式稍微復雜，轉(zhuǎn)換效果往往是不盡人意。

方法 2：編程語言

??以寫程序來解決，思路也很簡單：

??· 加載 excel 文件，裝載需要的 sheet 工作表。

??· 讀取“賬套名”所在行，將其轉(zhuǎn)換成字符串數(shù)組。

??· 讀取“科目編碼”所在列，將其轉(zhuǎn)換成字符串數(shù)組。

??· 按“科目編碼”分組，與“賬套名”數(shù)組構造一張表。

??· 根據(jù)“賬套名”對應的數(shù)據(jù)，遍歷所有的明細值填充到相應的表中。

??· 這樣就可以構造出對應的明細表來。

??如果用 Java 來實現(xiàn)，初步估計代碼量也不會少于 200 行，若需要結(jié)果輸出成 excel 文件則開發(fā)工作量會更多。雖然 Excel 自己提供了 VBA，但那個麻煩程度誰用誰知道，不提也罷。那其它的語言呢？傳說 python 有處理行列轉(zhuǎn)換的功能（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() #獲取數(shù)據(jù)頭信息

    #移去前兩列，只保留需要行列轉(zhuǎn)換的列
    cols.remove('科目編碼')
    cols.remove('科目明細')

    #構造一個 list.
    frames=[]
    for col in cols:
        df1 = df.pivot_table(index = ['科目編碼','科目明細'], values = [col])
        df1.rename(columns={col: '數(shù)值'}, inplace=True)
        df1[3]=col
        #轉(zhuǎn)換后的數(shù)據(jù)追加到 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='科目明細')    import pandas as pd    import numpy as np

    df = pd.read_excel("D:\\excel\\pandas.xlsx", 0, 3)
    cols = df.columns.values.tolist() #獲取數(shù)據(jù)頭信息

    #移去前兩列，只保留需要行列轉(zhuǎn)換的列
    cols.remove('科目編碼')
    cols.remove('科目明細')    #構造一個 list.
    frames=[]    for col in cols:
        df1 = df.pivot_table(index = ['科目編碼','科目明細'], values = [col])
        df1.rename(columns={col: '數(shù)值'}, inplace=True)
        df1[3]=col        #轉(zhuǎn)換后的數(shù)據(jù)追加到 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，我們要將“科目編碼”，“科目明細”移到與轉(zhuǎn)換列標題所在同一行上，變成下面的樣子。否則在代碼上就得特殊 “照顧”，反正只有一行，手工做一下就算了，比寫代碼省事。
??

??無論如何，python 的這個細節(jié)處理的小“瑕疵”并不影響其方便性。python 確實名不虛傳，雖然使用了循環(huán)，但整個代碼也就只有 10 來行的樣子。

??還能更簡單嗎？

??嘿嘿，能！

方法 3：集算器編程

??下面我們來看集算器的代碼：

??代碼很簡單，我們把每一步的中間結(jié)果列出來看看：

??A1：加載 excel 文件工作表 1，提取指定范圍的數(shù)據(jù) (從 3 行到 40 行)，其中選項 @ t 表示首行為標題，載入數(shù)據(jù), 生成表格如下：
??

??A2：刪除非數(shù)據(jù)行
??

??A3：更換列名稱
??

??A4：把從第 3 列開始的列名稱連成字符串，用“,”分開
??

??A5：pivot 函數(shù)將行列數(shù)據(jù)進行轉(zhuǎn)換，把 A4 中對應的列數(shù)據(jù)置放到“數(shù)值”列
??

??A6：將整理好的數(shù)據(jù)另存儲為 xlsx 文件
??

??集算器腳本只有 6 行，木有啥循環(huán)、判斷之類的玩意兒，也不像 Python 那樣要先手工倒騰一下，就把這看似有點“亂”的數(shù)據(jù)表格處理好了。相比之下，Python 采用列優(yōu)先轉(zhuǎn)換多次循環(huán) “N”字方式，集算器則用行優(yōu)先一次性處理，在處理數(shù)據(jù)上，集算器對細節(jié)處理及使用習慣更專業(yè)。而且集算器的開發(fā)環(huán)境也容易調(diào)試，可以看到每一步運算的中間結(jié)果，方便挑出錯誤，開發(fā)更為便捷。在這種常規(guī)數(shù)據(jù)處理的任務中，集算器要比 Python 更為優(yōu)越。

優(yōu)勢總結(jié)

??就這個問題，關于 python 與集算器的差異，再說說自己的一點心得體會：

1. 多列轉(zhuǎn)換

??對于需要多列行列轉(zhuǎn)換并匯集成“長”列的場景時，python 需要將每個數(shù)據(jù)列構造成數(shù)組，并增加一列記錄當前列名，再追加到一個大的列表中，最后合并，合并中去掉非首個數(shù)組中的 title；

??集算器就容易些，它直接把想要轉(zhuǎn)換的列匯集在一塊就行。相對于 python 的繁瑣，集算器至少能省幾個腦細胞。

2. 名稱更改

??python 對于需要轉(zhuǎn)換列的名稱不能更改, 如 cols[0]=’天津’，此時 python 找不到修改前的關鍵字，“哪個朋友挖的坑，別以為我發(fā)現(xiàn)不了”，欺負大爺眼花，給報個異常行不？

??但對應的集算器來說則很方便, 如：>A1.rename(_1: 科目編碼,_2: 科目明細,4 成都: 成都)

3. 標題空值問題

??Python 讀取 excel 表中的轉(zhuǎn)換行標題時，前面兩列為空 (對應原來的 excel 中的“科目編碼，科目明細”)，此時標題 cols 中的空值就沒有了, 這個“坑”有點隱蔽啊，我真沒有發(fā)現(xiàn), 把其中的兩列弄丟了，真有點丟臉 ;

??但集算器能識別出來，會自動加上對應的標識 _1、_2，這樣處理數(shù)據(jù)時，就能找到其中對應的兩列。

4. 網(wǎng)格式編程

??集算器使用網(wǎng)格 A1 這種格式，它自動與所在位置的對象關聯(lián)起來，這點非常方便, 感覺很有特色；Python 就只能望洋興嘆了。