我有以下数据框:
Name1 Scr1 Name2 Scr2 Name3 Scr3
NY 21 CA 45 SF 37
AZ 31 BK 46 AK 23
我正在尝试获取每一行的最大值和每一行的对应名称:
df.idxmax(axis=1)
但是我该如何获得相应的名称呢?
预期产量:
Name Hi_Scr
CA 45
BK 46
使用:
Scr
by过滤列filter
,将值转换为numpy array byvalues
argmax
Name
并通过索引选择DataFrame
由构造函数创建a = df.filter(like='Scr').values
b = a.argmax(axis=1)
c = df.filter(like='Name').values[np.arange(len(df.index)), b]
d = a.max(axis=1)
df = pd.DataFrame({'Name':c, 'Hi_Scr':d}, columns=['Name','Hi_Scr'])
print (df)
Name Hi_Scr
0 CA 45
1 BK 46
Pandas解决方案非常相似-在by列中创建MultiIndex extract
,然后选择byxs
和查找值,请使用lookup
:
a = df.columns.to_series().str.extract('(\D+)(\d+)', expand=False)
df.columns = pd.MultiIndex.from_tuples(a.values.tolist())
a = df.xs('Scr', axis=1)
b = a.idxmax(axis=1)
c = df.xs('Name', axis=1).lookup(df.index, b)
d = a.max(axis=1)
df = pd.DataFrame({'Name':c, 'Hi_Scr':d}, columns=['Name','Hi_Scr'])
print (df)
Name Hi_Scr
0 CA 45
1 BK 46
时间:
df = pd.concat([df]*10000).reset_index(drop=True)
def jez2(df):
a = df.columns.to_series().str.extract('(\D+)(\d+)', expand=False)
df.columns = pd.MultiIndex.from_tuples(a.values.tolist())
a = df.xs('Scr', axis=1)
b = a.idxmax(axis=1)
c = df.xs('Name', axis=1).lookup(df.index, b)
d = a.max(axis=1)
return pd.DataFrame({'Name':c, 'Hi_Scr':d}, columns=['Name','Hi_Scr'])
def jez1(df):
a = df.filter(like='Scr').values
b = a.argmax(axis=1)
c = df.filter(like='Name').values[np.arange(len(df.index)), b]
d = a.max(axis=1)
return pd.DataFrame({'Name':c, 'Hi_Scr':d}, columns=['Name','Hi_Scr'])
def dark(df):
df['id'] = df.index
ndf = pd.wide_to_long(df, ["Name", "Scr"], i="id", j="number").reset_index(0).set_index('Name')
return ndf.groupby('id')['Scr'].agg(['max','idxmax']).rename(columns= {'max':'Hi_Scr','idxmax':'Name'})
import time
t0 = time.time()
print (jez1(df).head())
t1 = time.time() - t0
print (t1)
print (dark(df).head())
t2 = time.time() - t1
print (t2)
print (jez2(df).head())
t3 = time.time() - t2
print (t3)
Name Hi_Scr
0 CA 45
1 BK 46
2 CA 45
3 BK 46
4 CA 45
#jez1 solution
0.015599966049194336
Hi_Scr Name
id
0 45 CA
1 46 BK
2 45 CA
3 46 BK
4 45 CA
#dark solution
1515070100.961423
Name Hi_Scr
0 CA 45
1 BK 46
2 CA 45
3 BK 46
4 CA 45
#jez2 solution
0.04679989814758301
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句