所以我試圖根據過去的資料在時間序列中創建新資料。例如，我在這里有玩家資料，每一行都是在某個年齡積累的統計資料。我想在 Dataframe 中創建新行，在其中將最大年齡增加 1，然后取之前兩年的sa和ga列的平均值。
這是資料
import pandas as pd
data = [['Adam Wilcox', 8476330, 25, 14.0, 0.0],
['Adin Hill', 8478499, 21, 129.0, 14.0],
['Adin Hill', 8478499, 22, 322.0, 32.0],
['Adin Hill', 8478499, 23, 343.0, 28.0],
['Adin Hill', 8478499, 24, 530.0, 46.0],
['Adin Hill', 8478499, 25, 237.0, 26.0],
['Al Montoya', 8471219, 24, 120.0, 9.0],
['Al Montoya', 8471219, 26, 585.0, 46.0],
['Al Montoya', 8471219, 27, 832.0, 89.0],
['Al Montoya', 8471219, 28, 168.0, 17.0]]
model_df = pd.DataFrame(data,
columns=['player', 'player_id', 'season_age', 'sa', 'ga'])
例如，我想要創建的是['Al Montoya', 8471219, 29, 500, 53]（記住最后兩個值是28 和 27 歲的sa和ga列的平均值）。
我已經使用iterrows并創建了一個新的 Dataframe 并像這樣附加：
max_ages = model_df.groupby(['player', 'player_id'])[['season_age']].max().reset_index()
added_ages = []
for player in max_ages.iterrows():
row = [player[1][0],
player[1][1],
player[1][2] 1,
(model_df[(model_df['player_id'] == player[1][1]) &
(model_df['season_age'] == player[1][2] - 1)]['sa'].sum()
model_df[(model_df['player_id'] == player[1][1]) &
(model_df['season_age'] == player[1][2] - 2)]['sa'].sum())/2,
(model_df[(model_df['player_id'] == player[1][1]) &
(model_df['season_age'] == player[1][2] - 1)]['ga'].sum()
model_df[(model_df['player_id'] == player[1][1]) &
(model_df['season_age'] == player[1][2] - 2)]['ga'].sum())/2
]
added_ages.append(row)
added_ages_df = pd.DataFrame(added_ages,
columns=['player', 'player_id', 'season_age', 'sa', 'ga'])
model_df = pd.concat([model_df, added_ages_df])
顯然，這是一個非常脆弱的臨時解決方案，我的問題是是否有一種內置的方法可以pandas不使用iterrows
預期的 Dataframe 看起來更容易以串列形式表示
data = [['Adam Wilcox', 8476330, 25, 14.0, 0.0],
['Adin Hill', 8478499, 21, 129.0, 14.0],
['Adin Hill', 8478499, 22, 322.0, 32.0],
['Adin Hill', 8478499, 23, 343.0, 28.0],
['Adin Hill', 8478499, 24, 530.0, 46.0],
['Adin Hill', 8478499, 25, 237.0, 26.0],
['Adin Hill', 8478499, 26, 502, 36],
['Al Montoya', 8471219, 24, 120.0, 9.0],
['Al Montoya', 8471219, 26, 585.0, 46.0],
['Al Montoya', 8471219, 27, 832.0, 89.0],
['Al Montoya', 8471219, 28, 168.0, 17.0],
['Al Montoya', 8471219, 29, 500, 53]]
uj5u.com熱心網友回復：
您可以定義一個呼叫的函式add_row并將其傳遞給 groupby。我假設如果一個球員沒有兩年的資料，你會想要列sa并ga填充NaN：
def add_row(x):
last_row = x.iloc[-1]
last_row['season_age'] = last_row['season_age'] 1
if len(x) < 2:
last_row['sa'], last_row['ga'] = float("nan"), float("nan")
return x.append(last_row)
else:
last_row['sa'], last_row['ga'] = x[['sa','ga']].iloc[-2:].mean()
return x.append(last_row)
new_model_df = model_df.groupby("player").apply(add_row).reset_index(drop=True)
輸出：
>>> new_model_df
player player_id season_age sa ga
0 Adam Wilcox 8476330 25 14.0 0.0
1 Adam Wilcox 8476330 26 NaN NaN
2 Adin Hill 8478499 21 129.0 14.0
3 Adin Hill 8478499 22 322.0 32.0
4 Adin Hill 8478499 23 343.0 28.0
5 Adin Hill 8478499 24 530.0 46.0
6 Adin Hill 8478499 25 237.0 26.0
7 Adin Hill 8478499 26 383.5 36.0
8 Al Montoya 8471219 24 120.0 9.0
9 Al Montoya 8471219 26 585.0 46.0
10 Al Montoya 8471219 27 832.0 89.0
11 Al Montoya 8471219 28 168.0 17.0
12 Al Montoya 8471219 29 500.0 53.0
uj5u.com熱心網友回復：
對分組的物件做一些計算，并將結果合并到model_df：
grouper = ['player', 'player_id']
grouped = model_df.groupby(grouper, sort = False)
tail = grouped.nth(-1) # get the last row per group
tail = tail.assign(season_age = tail.season_age 1)
# get the average of the last two columns with rolling
# a second groupby is called here to get single rows per group
sa_ga = (group[['sa', 'ga']]
.rolling(2)
.mean()
.groupby(grouper)
.nth(-1)
)
tail = tail.assign(**sa_ga).reset_index()
# final output
(pd.concat([model_df, tail])
.sort_values(grouper, ignore_index = True)
)
player player_id season_age sa ga
0 Adam Wilcox 8476330 25 14.0 0.0
1 Adam Wilcox 8476330 26 NaN NaN
2 Adin Hill 8478499 21 129.0 14.0
3 Adin Hill 8478499 22 322.0 32.0
4 Adin Hill 8478499 23 343.0 28.0
5 Adin Hill 8478499 24 530.0 46.0
6 Adin Hill 8478499 25 237.0 26.0
7 Adin Hill 8478499 26 383.5 36.0
8 Al Montoya 8471219 24 120.0 9.0
9 Al Montoya 8471219 26 585.0 46.0
10 Al Montoya 8471219 27 832.0 89.0
11 Al Montoya 8471219 28 168.0 17.0
12 Al Montoya 8471219 29 500.0 53.0
uj5u.com熱心網友回復：
您可以嘗試以下操作。
df_new = df.shift()
df_new['season_age'] = df['season_age'].max() 1
df_new[['sa','ga']] = df[['sa','ga']].rolling(2).mean()
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/369040.html