回答此问题可获得 20 贡献值,回答如果被采纳可获得 50 分。
<p>我有一个大的数据帧(超过1百万行,10+列),我需要执行以下操作:</p>
<ul>
<li>按两列分组(示例中为<code>A</code>&;<code>B</code>)</li>
<li>在分组中按另一列排序(示例中为<code>C</code>)</li>
<li>向另一列追加一个递增计数器,按<code>C</code>(示例中为<code>E</code>)的排序顺序递增</li>
<li>不编辑其他列(示例中为<code>D</code>)</li>
</ul>
<p>我已经得到了下面的代码工作,它给出了正确的结果。然而,这是非常缓慢的。有人能推荐一些熊猫魔术来提高表演水平吗?在</p>
<pre><code>import pandas as pd
import numpy as np
np.random.seed(0)
A = list()
B = list()
C = list()
D = list()
E = list()
np_alphabet = np.array(list('ABCEEFGHIJKLMNOPQRSTUVWXYZ'), dtype="|S1")
np_codes = np.random.choice(np_alphabet, [5, 10])
for a in np_codes:
for b in range(2):
for i in range(5):
A.<a href="https://www.cnpython.com/list/append" class="inner-link">append</a>(''.join(a))
B.append('{}_{}'.format(b, A[-1]))
C.append(np.random.rand())
D.append(i)
E.append(B[-1])
df = pd.DataFrame({
'A': A,
'B': B,
'C': C,
'D': D,
'E': E
})
df.set_index(['A', 'B'], drop=False, inplace=True)
df.sort_index(inplace=True)
print(df)
grouped_sizes = df.groupby(level=[0, 1]).size()
num_indices = grouped_sizes.shape[0]
print_num = max(1, num_indices // 20)
for idx in grouped_sizes.index:
if grouped_sizes[idx] > 1:
tmp_df = df.loc[idx].sort_values('C', inplace=False)
tmp_df['E'] = map(lambda x: '{}_{}'.format(*x), zip(range(1, tmp_df.size + 1), tmp_df['E']))
df.loc[idx] = tmp_df
else:
df.loc[idx, 'E'] = '1_{}'.format(df.loc[idx, 'E'])
print(df)
</code></pre>
<p>它给出了以下输出</p>
^{pr2}$
<p>以及</p>
<pre><code># after
A B C D E
A B
AXEJWVIZMZ 0_AXEJWVIZMZ AXEJWVIZMZ 0_AXEJWVIZMZ 0.154262 4 1_0_AXEJWVIZMZ
0_AXEJWVIZMZ AXEJWVIZMZ 0_AXEJWVIZMZ 0.758615 1 2_0_AXEJWVIZMZ
0_AXEJWVIZMZ AXEJWVIZMZ 0_AXEJWVIZMZ 0.903142 3 3_0_AXEJWVIZMZ
0_AXEJWVIZMZ AXEJWVIZMZ 0_AXEJWVIZMZ 0.952573 2 4_0_AXEJWVIZMZ
0_AXEJWVIZMZ AXEJWVIZMZ 0_AXEJWVIZMZ 0.954914 0 5_0_AXEJWVIZMZ
1_AXEJWVIZMZ AXEJWVIZMZ 1_AXEJWVIZMZ 0.115331 2 1_1_AXEJWVIZMZ
1_AXEJWVIZMZ AXEJWVIZMZ 1_AXEJWVIZMZ 0.147092 4 2_1_AXEJWVIZMZ
1_AXEJWVIZMZ AXEJWVIZMZ 1_AXEJWVIZMZ 0.380718 3 3_1_AXEJWVIZMZ
1_AXEJWVIZMZ AXEJWVIZMZ 1_AXEJWVIZMZ 0.528869 1 4_1_AXEJWVIZMZ
1_AXEJWVIZMZ AXEJWVIZMZ 1_AXEJWVIZMZ 0.560586 0 5_1_AXEJWVIZMZ
BVBSPSACVA 0_BVBSPSACVA BVBSPSACVA 0_BVBSPSACVA 0.264456 1 1_0_BVBSPSACVA
0_BVBSPSACVA BVBSPSACVA 0_BVBSPSACVA 0.282663 2 2_0_BVBSPSACVA
0_BVBSPSACVA BVBSPSACVA 0_BVBSPSACVA 0.409996 4 3_0_BVBSPSACVA
0_BVBSPSACVA BVBSPSACVA 0_BVBSPSACVA 0.678287 3 4_0_BVBSPSACVA
0_BVBSPSACVA BVBSPSACVA 0_BVBSPSACVA 0.824997 0 5_0_BVBSPSACVA
1_BVBSPSACVA BVBSPSACVA 1_BVBSPSACVA 0.149984 0 1_1_BVBSPSACVA
1_BVBSPSACVA BVBSPSACVA 1_BVBSPSACVA 0.290150 4 2_1_BVBSPSACVA
1_BVBSPSACVA BVBSPSACVA 1_BVBSPSACVA 0.711210 1 3_1_BVBSPSACVA
1_BVBSPSACVA BVBSPSACVA 1_BVBSPSACVA 0.804939 3 4_1_BVBSPSACVA
1_BVBSPSACVA BVBSPSACVA 1_BVBSPSACVA 0.840399 2 5_1_BVBSPSACVA
FHNMIRQRSP 0_FHNMIRQRSP FHNMIRQRSP 0_FHNMIRQRSP 0.021955 1 1_0_FHNMIRQRSP
0_FHNMIRQRSP FHNMIRQRSP 0_FHNMIRQRSP 0.119058 0 2_0_FHNMIRQRSP
0_FHNMIRQRSP FHNMIRQRSP 0_FHNMIRQRSP 0.179845 4 3_0_FHNMIRQRSP
0_FHNMIRQRSP FHNMIRQRSP 0_FHNMIRQRSP 0.299527 2 4_0_FHNMIRQRSP
0_FHNMIRQRSP FHNMIRQRSP 0_FHNMIRQRSP 0.449371 3 5_0_FHNMIRQRSP
1_FHNMIRQRSP FHNMIRQRSP 1_FHNMIRQRSP 0.075765 0 1_1_FHNMIRQRSP
1_FHNMIRQRSP FHNMIRQRSP 1_FHNMIRQRSP 0.265494 4 2_1_FHNMIRQRSP
1_FHNMIRQRSP FHNMIRQRSP 1_FHNMIRQRSP 0.371984 3 3_1_FHNMIRQRSP
1_FHNMIRQRSP FHNMIRQRSP 1_FHNMIRQRSP 0.413373 1 4_1_FHNMIRQRSP
1_FHNMIRQRSP FHNMIRQRSP 1_FHNMIRQRSP 0.835250 2 5_1_FHNMIRQRSP
TJSECSLWFT 0_TJSECSLWFT TJSECSLWFT 0_TJSECSLWFT 0.274501 3 1_0_TJSECSLWFT
0_TJSECSLWFT TJSECSLWFT 0_TJSECSLWFT 0.376646 1 2_0_TJSECSLWFT
0_TJSECSLWFT TJSECSLWFT 0_TJSECSLWFT 0.804553 0 3_0_TJSECSLWFT
0_TJSECSLWFT TJSECSLWFT 0_TJSECSLWFT 0.820866 4 4_0_TJSECSLWFT
0_TJSECSLWFT TJSECSLWFT 0_TJSECSLWFT 0.904908 2 5_0_TJSECSLWFT
1_TJSECSLWFT TJSECSLWFT 1_TJSECSLWFT 0.116743 4 1_1_TJSECSLWFT
1_TJSECSLWFT TJSECSLWFT 1_TJSECSLWFT 0.198887 1 2_1_TJSECSLWFT
1_TJSECSLWFT TJSECSLWFT 1_TJSECSLWFT 0.326926 3 3_1_TJSECSLWFT
1_TJSECSLWFT TJSECSLWFT 1_TJSECSLWFT 0.857795 2 4_1_TJSECSLWFT
1_TJSECSLWFT TJSECSLWFT 1_TJSECSLWFT 0.886687 0 5_1_TJSECSLWFT
WXEKPQSLQK 0_WXEKPQSLQK WXEKPQSLQK 0_WXEKPQSLQK 0.082703 3 1_0_WXEKPQSLQK
0_WXEKPQSLQK WXEKPQSLQK 0_WXEKPQSLQK 0.235062 2 2_0_WXEKPQSLQK
0_WXEKPQSLQK WXEKPQSLQK 0_WXEKPQSLQK 0.249891 0 3_0_WXEKPQSLQK
0_WXEKPQSLQK WXEKPQSLQK 0_WXEKPQSLQK 0.894169 4 4_0_WXEKPQSLQK
0_WXEKPQSLQK WXEKPQSLQK 0_WXEKPQSLQK 0.945414 1 5_0_WXEKPQSLQK
1_WXEKPQSLQK WXEKPQSLQK 1_WXEKPQSLQK 0.567448 3 1_1_WXEKPQSLQK
1_WXEKPQSLQK WXEKPQSLQK 1_WXEKPQSLQK 0.595575 0 2_1_WXEKPQSLQK
1_WXEKPQSLQK WXEKPQSLQK 1_WXEKPQSLQK 0.769144 1 3_1_WXEKPQSLQK
1_WXEKPQSLQK WXEKPQSLQK 1_WXEKPQSLQK 0.832299 4 4_1_WXEKPQSLQK
1_WXEKPQSLQK WXEKPQSLQK 1_WXEKPQSLQK 0.917691 2 5_1_WXEKPQSLQK
</code></pre>
<h2>新的答案,建议的测试时间</h2>
<pre><code>from __future__ import print_function, division
from timeit import Timer
import pandas as pd
import numpy as np
def create_df():
np.random.seed(0)
A = list()
B = list()
C = list()
D = list()
E = list()
np_alphabet = np.array(list('ABCEEFGHIJKLMNOPQRSTUVWXYZ'), dtype="|S1")
np_codes = np.random.choice(np_alphabet, [100, 10])
for a in np_codes:
for b in range(2):
for i in range(5):
A.append(''.join(a))
B.append('{}_{}'.format(b, A[-1]))
C.append(np.random.rand())
D.append(i)
E.append(B[-1])
df = pd.DataFrame({
'A': A,
'B': B,
'C': C,
'D': D,
'E': E
})
return df.copy()
def method1(df):
df.set_index(['A', 'B'], drop=False, inplace=True)
df.sort_index(inplace=True)
grouped_sizes = df.groupby(level=[0, 1]).size()
for idx in grouped_sizes.index:
if grouped_sizes[idx] > 1:
tmp_df = df.loc[idx].sort_values('C', inplace=False)
tmp_df['E'] = map(lambda x: '{}_{}'.format(*x), zip(range(1, tmp_df.size + 1), tmp_df['E']))
df.loc[idx] = tmp_df
else:
df.loc[idx, 'E'] = '1_{}'.format(df.loc[idx, 'E'])
return df
def method1a(df):
df.set_index(['A', 'B'], drop=False, inplace=True)
df.sort_values(['A', 'B', 'C'], inplace=True)
grouped_sizes = df.groupby(level=[0, 1]).size()
for idx in grouped_sizes.index:
if grouped_sizes[idx] > 1:
df.loc[idx, 'E'] = map(lambda x: '{}_{}'.format(*x), zip(range(1, grouped_sizes[idx] + 1), df.loc[idx, 'E']))
else:
df.loc[idx, 'E'] = '1_{}'.format(df.loc[idx, 'E'])
return df
def method2(df):
df.set_index(['A', 'B'], drop=False, inplace=True)
df['F'] = 0
df.sort_values(['A', 'B', 'C'], inplace=True)
grouped_sizes = df.groupby(level=[0, 1]).size()
for idx in grouped_sizes.index:
if grouped_sizes[idx] > 1:
df.loc[idx, 'F'] = range(1, grouped_sizes[idx] + 1)
else:
df.loc[idx, 'F'] = 1
df['E'] = df['F'].map(str) + '_' + df['E']
df.drop('F', axis=1, inplace=True)
return df
def method3(df):
df.set_index(['A', 'B'], drop=False, inplace=True)
df['F'] = 0
df.sort_values(['A', 'B', 'C'], inplace=True)
grouped_sizes = df.groupby(level=[0, 1]).size()
for idx in grouped_sizes.index:
if grouped_sizes[idx] > 1:
df.loc[idx, 'F'] = map(str, range(1, grouped_sizes[idx] + 1))
else:
df.loc[idx, 'F'] = '1'
df['E'] = df['F'] + '_' + df['E']
df.drop('F', axis=1, inplace=True)
return df
def method4(df):
prefixes = df.groupby(['A', 'B']).C.apply(pd.Series.argsort).add(1).astype(str)
df['E'] = prefixes + '_' + df.E
return df
def method5(df):
df.set_index(['A', 'B'], drop=False, inplace=True)
df.sort_values(['A', 'B', 'C'], inplace=True)
df['E'] = df.groupby(level=[0, 1]).cumcount().add(1).astype(str) + '_' + df['E']
return df
def assert_success(df):
row = df[(df['A'] == 'AEVFGIURPE') & (df['B'] == '0_AEVFGIURPE') & (df['D'] == 2)].iloc[0]
if not np.allclose(row['C'], 0.381397) or row['E'] != '3_0_AEVFGIURPE':
print('A: method{}() failed: {} != 0.871083 or {} != 5_1_XOYRFZNIJU'.format(func, row['C'], row['E']))
return
row = df[(df['A'] == 'XOYRFZNIJU') & (df['B'] == '1_XOYRFZNIJU') & (df['D'] == 1)].iloc[0]
if not np.allclose(row['C'], 0.871083) or row['E'] != '5_1_XOYRFZNIJU':
print('B: method{}() failed: {} != 0.871083 or {} != 5_1_XOYRFZNIJU'.format(func, row['C'], row['E']))
return
functions = list()
functions.append('1')
functions.append('1a')
functions.append('2')
functions.append('3')
functions.append('4')
functions.append('5')
for func in functions:
print('method{}'.format(func),
Timer(setup='from __main__ import create_df, assert_success, method{} as func'.format(func),
stmt='df = create_df(); df = func(df); assert_success(df)').repeat(number=10))
</code></pre>
<p>结果如下:</p>
<pre><code>method1 [6.581194877624512, 6.625822067260742, 6.722187042236328]
method1a [1.9003210067749023, 1.9387969970703125, 1.9142169952392578]
method2 [0.9547598361968994, 0.9532740116119385, 0.9760739803314209]
method3 [1.0121638774871826, 1.0000989437103271, 0.9709858894348145]
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
B: method4() failed: 0.871082572438 != 0.871083 or 1_1_XOYRFZNIJU != 5_1_XOYRFZNIJU
method4 [0.3202958106994629, 0.3348369598388672, 0.33800482749938965]
method5 [0.11518096923828125, 0.10490703582763672, 0.09626197814941406]
</code></pre>