我有一個這種形式的數(shù)據(jù)框： value1 value2 value3 value4 random string column groupindex1 10 2 3 4 stuff group 2index2 5 4 3 2 other stuff group 1index3 6 7 8 9 other stuff group 1index4 1 2 2 4 yet other stuff group 2index5 6 1 8 11 other stuff group 1可以使用以下代碼生成此測試示例：df = pd.DataFrame([[10, 2, 3, 4, 'stuff', 'group 2'], [5, 4, 3, 2, 'other stuff', 'group 1'], [6, 7, 8, 9, 'other stuff', 'group 1'], [1, 2, 2, 4, 'yet other stuff', 'group 2'], [6, 1, 8, 11, 'other stuff', 'group 1']], columns = ['value1', 'value2', 'value3', 'value4', 'random string column', 'group'], index=['index1', 'index2', 'index3', 'index4', 'index5'])我想根據(jù)此規(guī)范創(chuàng)建一個名為“組識別列”的新列：按組列分組取每個值列的總和獲取每組總和最大的值列的列名在此示例中，預期輸出為： value1 value2 value3 value4 random string column group Group Identifying Columnindex1 10 2 3 4 stuff group 2 value1index2 5 4 3 2 other stuff group 1 value4index3 6 7 8 9 other stuff group 1 value4index4 1 2 2 4 yet other stuff group 2 value1index5 6 1 8 11 other stuff group 1 value4我已經(jīng)嘗試了幾次 groupby / apply / transform 等，但我不能完全讓它正確。
2 回答
繁星點點滴滴
TA貢獻1803條經(jīng)驗 獲得超3個贊
讓我們按組提取列然后映射：
max_cols = (df.filter(like='value') # choose the value columns, also df.iloc[:, :4]
.groupby(df['group']).sum() # calculate sum per group
.idxmax(axis=1) # find col with max value
)
df['Column'] = df['group'].map(max_cols)
還groupby().transform()：
df['Column'] = (df.filter(like='value')
.groupby(df['group']).transform('sum')
.idxmax(axis=1)
)
輸出：
value1 value2 value3 value4 random string column group Column
index1 10 2 3 4 stuff group 2 value1
index2 5 4 3 2 other stuff group 1 value4
index3 6 7 8 9 other stuff group 1 value4
index4 1 2 2 4 yet other stuff group 2 value1
index5 6 1 8 11 other stuff group 1 value4
江戶川亂折騰
TA貢獻1851條經(jīng)驗 獲得超5個贊
這是map和的一種潛在解決方案dictionary：
#creates a dictionary with the maximum sum per group
d = df.groupby('group').sum().idxmax(axis=1).to_dict()
#mapping the dictionary to 'group' column to generated a new column
df['Group Identifying Column'] = df['group'].map(d)
或者，您可以切斷該dictionary部分并簡單地執(zhí)行以下操作：
df['Group Identifying Column'] = df.group.map(df.groupby('group').sum().idxmax(axis=1))
輸出：
value1 value2 ... group Group Identifying Column
index1 10 2 ... group 2 value1
index2 5 4 ... group 1 value4
index3 6 7 ... group 1 value4
index4 1 2 ... group 2 value1
index5 6 1 ... group 1 value4
添加回答
舉報
0/150
提交
取消