在Python中读取Excel文件Pivot

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)

Answer 1

使用pandas，可以操作数据，使团队成为行，其中captain和{}为列，然后加入day值：

import pandas as pd

df = pd.read_excel("<path to your file>.xlsx")

teams = df.set_index(["Team", "Status"])["Name"].unstack()

cols = ["Monday", "Tuesday"]
days = []
for i in ["player", "captain"]:
    days.append(df[df["Status"] == i].groupby("Team")[cols].first().rename(columns={col: "{}_{}".format(i, col) for col in cols}))

days = pd.concat(days, axis=1)

result = teams.join(days)
result[["captain", "captain_Monday", "captain_Tuesday", "player", "player_Monday", "player_Tuesday"]]

Answer 2

使用pandas可以很容易地实现这一点。在

出于演示目的，我创建了您的原始工作表并将其读入。然后，我将一个“Status”列等于“captain”的数据帧合并为“Status”列等于“player”的数据帧。然后我将这个新的数据帧作为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()

期望开始.xlsx公司名称：

期望完成.xlsx公司名称：