我有一个熊猫数据帧df像:
a b
A 1
A 2
B 5
B 5
B 4
C 6
我想按第一列分组,并将第二列作为行中的列表:
A [1,2]
B [5,5,4]
C [6]
是否有可能使用pandas groupby来做这样的事情?
我有一个熊猫数据帧df像:
a b
A 1
A 2
B 5
B 5
B 4
C 6
我想按第一列分组,并将第二列作为行中的列表:
A [1,2]
B [5,5,4]
C [6]
是否有可能使用pandas groupby来做这样的事情?
当前回答
根据@EdChum对他的回答的评论来回答。评论是这样的
groupby is notoriously slow and memory hungry, what you could do is sort by column A, then find the idxmin and idxmax (probably store this in a dict) and use this to slice your dataframe would be faster I think
让我们首先创建一个数据框架,第一列中有500k个类别,总df形状为2000万。
df = pd.DataFrame(columns=['a', 'b'])
df['a'] = (np.random.randint(low=0, high=500000, size=(20000000,))).astype(str)
df['b'] = list(range(20000000))
print(df.shape)
df.head()
# Sort data by first column
df.sort_values(by=['a'], ascending=True, inplace=True)
df.reset_index(drop=True, inplace=True)
# Create a temp column
df['temp_idx'] = list(range(df.shape[0]))
# Take all values of b in a separate list
all_values_b = list(df.b.values)
print(len(all_values_b))
# For each category in column a, find min and max indexes
gp_df = df.groupby(['a']).agg({'temp_idx': [np.min, np.max]})
gp_df.reset_index(inplace=True)
gp_df.columns = ['a', 'temp_idx_min', 'temp_idx_max']
# Now create final list_b column, using min and max indexes for each category of a and filtering list of b.
gp_df['list_b'] = gp_df[['temp_idx_min', 'temp_idx_max']].apply(lambda x: all_values_b[x[0]:x[1]+1], axis=1)
print(gp_df.shape)
gp_df.head()
上面的代码花费2分钟处理第一列中的2000万行和500k个类别。
其他回答
排序耗时O(nlog(n)),是上述方案中耗时最多的操作
对于简单的解决方案(含单列)pd.Series。除非考虑其他框架,否则To_list可以工作并且可以被认为更有效
e.g.
import pandas as pd
from string import ascii_lowercase
import random
def generate_string(case=4):
return ''.join([random.choice(ascii_lowercase) for _ in range(case)])
df = pd.DataFrame({'num_val':[random.randint(0,100) for _ in range(20000000)],'string_val':[generate_string() for _ in range(20000000)]})
%timeit df.groupby('string_val').agg({'num_val':pd.Series.to_list})
对于2000万条记录,大约需要17.2秒。相比之下,apply(list)大约需要19.2秒,lambda函数大约需要20.6秒
我们用df。带有列表和系列构造函数的groupby
pd.Series({x : y.b.tolist() for x , y in df.groupby('a')})
Out[664]:
A [1, 2]
B [5, 5, 4]
C [6]
dtype: object
只是把之前的答案加起来,在我的情况下,我想要列表和其他函数,如min和max。这样做的方法是:
df = pd.DataFrame({
'a':['A','A','B','B','B','C'],
'b':[1,2,5,5,4,6]
})
df=df.groupby('a').agg({
'b':['min', 'max',lambda x: list(x)]
})
#then flattening and renaming if necessary
df.columns = df.columns.to_flat_index()
df.rename(columns={('b', 'min'): 'b_min', ('b', 'max'): 'b_max', ('b', '<lambda_0>'): 'b_list'},inplace=True)
基于@B。M的答案,这里是一个更通用的版本,并更新为与更新的库版本一起工作:(numpy版本1.19.2,pandas版本1.2.1) 这个解决方案也可以处理多指标:
然而,这并没有经过严格的测试,请谨慎使用。
如果性能是重要的,下降到numpy级别:
import pandas as pd
import numpy as np
np.random.seed(0)
df = pd.DataFrame({'a': np.random.randint(0, 10, 90), 'b': [1,2,3]*30, 'c':list('abcefghij')*10, 'd': list('hij')*30})
def f_multi(df,col_names):
if not isinstance(col_names,list):
col_names = [col_names]
values = df.sort_values(col_names).values.T
col_idcs = [df.columns.get_loc(cn) for cn in col_names]
other_col_names = [name for idx, name in enumerate(df.columns) if idx not in col_idcs]
other_col_idcs = [df.columns.get_loc(cn) for cn in other_col_names]
# split df into indexing colums(=keys) and data colums(=vals)
keys = values[col_idcs,:]
vals = values[other_col_idcs,:]
# list of tuple of key pairs
multikeys = list(zip(*keys))
# remember unique key pairs and ther indices
ukeys, index = np.unique(multikeys, return_index=True, axis=0)
# split data columns according to those indices
arrays = np.split(vals, index[1:], axis=1)
# resulting list of subarrays has same number of subarrays as unique key pairs
# each subarray has the following shape:
# rows = number of non-grouped data columns
# cols = number of data points grouped into that unique key pair
# prepare multi index
idx = pd.MultiIndex.from_arrays(ukeys.T, names=col_names)
list_agg_vals = dict()
for tup in zip(*arrays, other_col_names):
col_vals = tup[:-1] # first entries are the subarrays from above
col_name = tup[-1] # last entry is data-column name
list_agg_vals[col_name] = col_vals
df2 = pd.DataFrame(data=list_agg_vals, index=idx)
return df2
测试:
In [227]: %timeit f_multi(df, ['a','d'])
2.54 ms ± 64.7 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
In [228]: %timeit df.groupby(['a','d']).agg(list)
4.56 ms ± 61.5 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
结果:
对于随机种子0,将得到:
实现这一目标的简便方法是:
df.groupby('a').agg({'b':lambda x: list(x)})
考虑编写自定义聚合:https://www.kaggle.com/akshaysehgal/how-to-group-by-aggregate-using-py