python - 在 Python 中读取 Excel 文件透视图

Question

我想从我的 Excel 文件中检索数据。

以下是数据：

这是我想要的结果：

这是我开始编写的代码：

from openpyxl import load_workbook
wb = load_workbook('C:\\Fichiers_Excel\\Team.xlsx')
m = wb.active
# Retrieve the value of a certain cell
team_1_captain=m['A2'].value,m['B2'].value,m['D2'].value,m['E2'].value
team_1_player=m['A6'].value,m['B6'].value,m['D6'].value,m['E6'].value
team_1= m['A2'].value,m['B2'].value,m['D2'].value,m['E2'].value,m['B6'].value,m['D6'].value,m['E6'].value
print (team_1)

标签： pythonexcelpivotpivot-table

Answer 1

使用pandas非常简单。

出于演示目的，我创建了您的原始工作表并将其读入。然后我将“状态”列等于“队长”的数据框合并到“状态”列等于“玩家”的数据框。然后我将这个新数据框输出为 xlsx 文件。完全可重现的代码和预期的输出如下。

import pandas as pd

df = pd.DataFrame({"Team": [ 1, 3, 3, 2, 1, 2],
                   "Name": ["Mike", "Mc", "Dany", "Tom", "Steve", "Hector"],
                   "Status": ["captain", "captain", "player", "captain", "player", "player"],
                   "Monday": [10, 5, 2, 11, 10, 10],
                   "Tuesday": [7, 1, 1, 8, 5, 8]})

writer = pd.ExcelWriter('start.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', index=False)
writer.save()

df = pd.read_excel('start.xlsx')
captain_df = df[df['Status'] == 'captain']
player_df = df[df['Status'] == 'player']


final_df = captain_df.merge(player_df, how='left', on='Team' , suffixes=('_1', '_2'))
final_df = final_df.rename(columns={"Name_1": "Captain", "Name_2": "Player"})
final_df = final_df[['Team', 'Captain', 'Monday_1', 'Tuesday_1', 'Player', 'Monday_2', 'Tuesday_2']]

writer = pd.ExcelWriter('finish.xlsx')
final_df.to_excel(writer, sheet_name='Sheet1', index=False)
#optional rename of the column names in the next 6 lines since Monday and Tuesday are duplicate names
workbook  = writer.book
worksheet = writer.sheets['Sheet1']
worksheet.write('C1', 'Monday')
worksheet.write('D1', 'Tuesday')
worksheet.write('F1', 'Monday')
worksheet.write('G1', 'Tuesday')
writer.save()

预期的 start.xlsx：

预期完成.xlsx：