Python实例：Excel表格内容行列转置

原文链接：http://www.juzicode.com/python-example-excel-col-row-transpose

今天的例子实现对Excel表格的行列值完成转置。

原始表格为:
 A | B | C 
 1 | 2 | 3 
 * | ( | ) 

 经过转置后变为：
 A | 1 | *
 B | 2 | (
 C | 3 | )

方法1，用xlrd读取excel文件按照行方式存入到一个列表中，再用xlwt写入文件时，按照列方式取出：

#juzicode.com/VX公众号:juzicode
import xlrd
import xlwt

wb_r=xlrd.open_workbook('data_r.xls')  
ws_r=wb_r.sheet_by_name('juzicode')
data_list=[] 
for i in range(ws_r.nrows):
    data_list.append(ws_r.row_values(i))
print('data_list:',data_list)

wb_w = xlwt.Workbook()
ws_w=wb_w.add_sheet('juzicode')
for i in range(ws_r.ncols):
    for j in range(ws_r.nrows):
        ws_w.write(i,j,data_list[j][i])
wb_w.save('data_w_xlwt_xlrd.xls')

方法2，用pandas读出文件生成df实例后，使用它的T属性完成转置：

#juzicode.com/VX公众号:juzicode
import pandas as pd

df = pd.DataFrame(pd.read_excel('data_r.xls'))
df_t = df.T
with pd.ExcelWriter('data_w_pandas.xlsx',engine='openpyxl') as writer:
    df_t.to_excel(writer,header=False)

方法3，用pandas读出文件生成df实例后，使用它的transpose()方法完成转置：

#juzicode.com/VX公众号:juzicode
import pandas as pd

df = pd.DataFrame(pd.read_excel('data_r.xls'))
df_t = df.transpose()
with pd.ExcelWriter('data_w_pandas2.xlsx',engine='openpyxl') as writer:
    df_t.to_excel(writer,header=False)

方法4，用openpyxl读出文件生成wb_r实例，再用另外一个写入实例wb_w生成对应大小的cell表格，然后遍历表格内容完成转置：

#juzicode.com/VX公众号:juzicode
from openpyxl import Workbook
from openpyxl import load_workbook

wb_r = load_workbook('data_r.xlsx') # 注意openpyxl仅支持xlsx格式
ws_r = wb_r.active 
 
wb_w = Workbook()
ws_w = wb_w.active
ws_w.cell(row=ws_r.max_column, column=ws_r.max_row) # 创建单元格
 
for row,col in zip(ws_r.iter_rows(),ws_w.iter_cols()): # 迭代读出
    for x,y in zip(row,col):
        y.value=x.value
 
wb_w.save('data_w_openpyxl.xlsx')

方法5，用openpyxl读出文件生成wb_r实例，读出的数据用numpy转置后再逐行追加写入：

#juzicode.com/VX公众号:juzicode
from openpyxl import Workbook
from openpyxl import load_workbook
import numpy as np

# 读表格并转置
wb_r = load_workbook('data_r.xlsx') 
ws_r = wb_r.active 
vals = []
for row in ws_r.iter_rows(values_only=True): # 迭代读出
    vals.append(row)
vals = np.array(vals).T  # 转置

# 写表格
wb_w = Workbook()
ws_w = wb_w.active
for val in vals:  
    ws_w.append(list(val))
wb_w.save('data_w_numpy.xlsx')

扩展阅读：

1. Python桔子教程