试试这个:
输入:
import pandas as pd
raw_df = pd.DataFrame({"GameId": [1, 1, 2, 2],
"Team": ["Spirit", "Rockets", "Lighting", "Flames"],
"Home": [1, 0, 1, 0],
"Score": [81, 66, 73, 82]})
print(raw_df)
输出:
GameId Team Home Score
0 1 Spirit 1 81
1 1 Rockets 0 66
2 2 Lighting 1 73
3 2 Flames 0 82
输入:
raw_df.loc[:, "Home"] = raw_df.Home.map({
1: "Home",
0: "Away"
})
result = raw_df.pivot_table(index=["GameId"],
columns=["Home"],
values=["Team", "Score"],
aggfunc={"Team": lambda team: " ".join(team.tolist()),
"Score": lambda score: score})
result = result.sort_index(axis="columns", level=[0, "Home"], ascending=False)
result.columns = [' '.join(reversed(col)) for col in result.columns]
print(result)
输出:
Home Team Away Team Home Score Away Score
GameId
1 Spirit Rockets 81 66
2 Lighting Flames 73 82
import pandas as pd
df=pd.DataFrame({'GameId':[1,1,2,2],'Team': ['Spirit','Rockers','Lighting','Flames'],'Home':[1,0,1,0],'Score':[81,66,73,82]})
merge=pd.merge(df,df,left_on='GameId',right_on='GameId')
merge=merge[merge['Home_x']!=merge['Home_y']]
merge=merge.drop_duplicates(subset=['GameId'])
merge=merge[['GameId','Team_x','Team_y','Score_x','Score_y']]
merge.columns=['GameId','Home Team','Away Team','Home Score','Away Score']
解释:使用pd.merge()函数进行自连接。然后,删除在home和away列中具有相同队名的行。之后,在gameId上去重,接着选择所需的列并将它们重命名。
首先使用.pivot
,然后进行一些列表推导来将列名从元组重命名为所需的名称(由于在枢轴时将Home
设置为列,因此列是元组)。 [::-1]
在列表推导中连接元组时将名称从例如Team Home反转为Home Team。
df = pd.pivot(df, columns='Home', values=['Team','Score'], index='GameId').reset_index()
df.columns = [' '.join(str(s).strip().replace('1', 'Home').replace('0', 'Away') for s in col[::-1]) for col in df.columns]
输出:
GameId Away Team Home Team Away Score Home Score
0 1 Rockers Spirit 66 81
1 2 Flames Lightning 82 73
.map
只需44ms。1.maps = {0:'Away',1:'Home'}
2.raw_df.Home = raw_df.Home.map(maps)
。两种方式都足够快。我个人认为.map
看起来更清晰,特别是对于更多的映射。不管怎样,非常好的答案+10。 - Trenton McKinney