python - 数据帧转换 |更好的方法？

Question

我打算转换 DataFrame 以添加从现有数据派生的列的方式似乎非常痛苦。起初，我考虑使用 apply，但我意识到这些附加列取决于我的原始 DataFrame 的某些方面(例如，MMR 信息对于“ReplayID”是唯一的，以及团队是否获胜，如“Is Winner”中所示) 。我找到了前进的方法，但我想知道是否有更有效的方法来实现它。数据框如下，我的代码如下:

转型前

改造后

import pandas as pd
import numpy as np

df = pd.read_csv('small.csv', index_col=False)
del df['Unnamed: 0']

replayGroup = df['MMR Before'].groupby([df['ReplayID'], df['Is Winner']])
summaryReplayGroup = replayGroup.agg([np.max, np.mean, np.min, np.std])

# i[0] -> Index
# i[1] -> ReplayID
# i[2] -> Is Auto Select
# i[3] -> HeroID
# i[4] -> Hero Level
# i[5] -> Is Winner
# i[6] -> MMR Before
maxMMR = []
meanMMR = []
minMMR = []
stdMMR = []

for i in df.itertuples():
    key = (i[1], i[5])
    maxMMR.append(summaryReplayGroup.loc[key]['amax'])
    meanMMR.append(summaryReplayGroup.loc[key]['mean'])
    minMMR.append(summaryReplayGroup.loc[key]['amin'])
    stdMMR.append(summaryReplayGroup.loc[key]['std'])

df['Max Team MMR'] = maxMMR
df['Mean Team MMR'] = meanMMR
df['Min Team MMR'] = minMMR
df['Std Team MMR'] = stdMMR

# Negative diff indicates disadvantage
maxDiffMMR = []
meanDiffMMR = []
minDiffMMR = []
stdDiffMMR = []

# i[0] -> Index
# i[1] -> ReplayID
# i[2] -> Is Auto Select
# i[3] -> HeroID
# i[4] -> Hero Level
# i[5] -> Is Winner
# i[6] -> MMR Before
# i[7] -> Max Team MMR
# i[8] -> Mean Team MMR
# i[9] -> Min Team MMR
# i[10] -> Std Team MMR
for i in df.itertuples():
    if i[5]:
        opposite = 0
    else:
        opposite = 1

    replayId = i[1]

    oppTeamMaxMMR = df.loc[df['ReplayID'] == replayId].loc[df['Is Winner'] == opposite]['Max Team MMR'].mean()
    teamMaxMMR = i[7]
    diffMaxMMR = teamMaxMMR - oppTeamMaxMMR
    oppTeamMeanMMR = df.loc[df['ReplayID'] == replayId].loc[df['Is Winner'] == opposite]['Mean Team MMR'].mean()
    teamMeanMMR = i[8]
    diffMeanMMR = teamMeanMMR - oppTeamMeanMMR
    oppTeamMinMMR = df.loc[df['ReplayID'] == replayId].loc[df['Is Winner'] == opposite]['Min Team MMR'].mean()
    teamMinMMR = i[8]
    diffMinMMR = teamMinMMR - oppTeamMinMMR
    oppTeamStdMMR = df.loc[df['ReplayID'] == replayId].loc[df['Is Winner'] == opposite]['Std Team MMR'].mean()
    teamStdMMR = i[9]
    diffStdMMR = teamStdMMR - oppTeamStdMMR

    maxDiffMMR.append(diffMaxMMR)
    meanDiffMMR.append(diffMeanMMR)
    minDiffMMR.append(diffMinMMR)
    stdDiffMMR.append(diffStdMMR)

df['Diff Max MMR'] = maxDiffMMR
df['Diff Mean MMR'] = meanDiffMMR
df['Diff Min MMR'] = minDiffMMR
df['Diff Std MMR'] = stdDiffMMR

感谢您花时间查看!

Answer 1

df3 是您的最终数据框

funcs = {
    'Max Team MMR': 'max',
    'Mean Team MMR': 'mean',
    'Min Team MMR': 'min',
    'Std Team MMR': 'std'
}

idx_cols = ['ReplayID', 'Is Winner']
val_col = 'MMR Before'

df1 = df.set_index(idx_cols)
summaryReplayGroup = df1[val_col].groupby(level=idx_cols).agg(funcs)

df2 = df1.join(summaryReplayGroup)

diffs = summaryReplayGroup.unstack(idx_cols[0]).diff().dropna().squeeze().unstack(0)
diffs.columns = diffs.columns.str.replace(r'(.+) Team', r'Diff \1')
diffs = pd.concat([diffs, -diffs], keys=[1, 0])

df3 = df2.reset_index().join(diffs, on=idx_cols[::-1])
df3

结果

设置
供其他人尝试

df = pd.DataFrame([
        [57010496, 0, 36,  9, 0, 2589],
        [57010496, 0, 20,  9, 1, 2354],
        [57010496, 0, 14,  6, 1, 2314],
        [57010496, 0, 12, 10, 0, 2288],
        [57010496, 0, 39, 10, 0, 2486],
        [57010496, 0, 19, 10, 1, 2292],
        [57010496, 0, 27,  9, 1, 2385],
        [57010496, 0, 11,  7, 0, 2183],
        [57010496, 0, 24,  9, 1, 2471],
        [57010496, 0, 35,  3, 0, 2166],
        [57010518, 0, 22,  4, 0, 2582],
        [57010518, 0, 29,  6, 1, 2470],
        [57010518, 0, 36,  8, 1, 2590],
        [57010518, 0, 31,  9, 1, 2313],
        [57010518, 0, 19,  8, 1, 2159],
        [57010518, 0, 13,  7, 0, 1996],
        [57010518, 0,  7,  6, 1, 2441],
        [57010518, 0, 21,  7, 0, 2220],
        [57010518, 0, 42,  9, 0, 2465],
        [57010518, 0, 18, 11, 0, 2392],
    ], columns=['ReplayID', 'Is Auto Select', 'HeroID',
                'Hero Level', 'Is Winner', 'MMR Before'])