Pandas Groupby唯一多列 [英] Pandas Groupby Unique Multiple Columns

问题描述

我有一个数据框.

import pandas as pd
df = pd.DataFrame(           
{'number': [0,0,0,1,1,2,2,2,2], 'id1': [100,100,100,300,400,700,700,800,700], 'id2': [100,100,200,500,600,700,800,900,1000]})

   id1   id2  number
0  100   100       0
1  100   100       0
2  100   200       0
3  300   500       1
4  400   600       1
5  700   700       2
6  700   800       2
7  800   900       2
8  700  1000       2

(这代表我正在使用的更大的数据帧〜数百万行).

(This represents a much larger dataframe I am working with ~millions of rows).

我可以将groupby().unique应用于一列:

df.groupby(['number'])['id1'].unique()

number
0         [100]
1    [300, 400]
2    [700, 800]
Name: id1, dtype: object

df.groupby(['number'])['id2'].unique()

number
0               [100, 200]
1               [500, 600]
2    [700, 800, 900, 1000]
Name: id2, dtype: object

我想同时对两列进行唯一性处理，以使其在数据帧中排序:

I want to do the unique over both columns simultaneously to get it ordered in a dataframe:

number
0               [100, 200]
1     [300, 400, 500, 600]
2    [700, 800, 900, 1000]

当我尝试对两列进行此操作时，都会收到错误消息:

When I try and do this for both columns I get the error:

pd.Data.Frame(df.groupby(['number'])['id1', 'id2'].unique())

Traceback (most recent call last):
  File "C:\Python34\lib\site-packages\IPython\core\interactiveshell.py", line 2885, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-15-bfc6026e241e>", line 9, in <module>
    df.groupby(['number'])['id1', 'id2'].unique()
  File "C:\Python34\lib\site-packages\pandas\core\groupby.py", line 498, in __getattr__
    (type(self).__name__, attr))
AttributeError: 'DataFrameGroupBy' object has no attribute 'unique'

怎么办?使用多索引是否更可取?

What do? Is it preferable to use a multi-index?

此外，还可以按如下方式获得输出:

In addition is it possible to get the output as follows:

number
0 100
0 200
1 300
1 400
1 500
1 600
2 700
2 800
2 900
2 1000

推荐答案

您可以通过[]选择所有列:

You can select all column by []:

s = (df.groupby(['number'])['id1', 'id2']
       .apply(lambda x: pd.unique(x.values.ravel()).tolist()))

print (s)
number
0               [100, 200]
1     [300, 500, 400, 600]
2    [700, 800, 900, 1000]
dtype: object

或者:

s2 = (df.groupby(['number'])['id1', 'id2']
        .apply(lambda x: np.unique(x.values.ravel()).tolist()))
print (s2)
number
0               [100, 200]
1     [300, 400, 500, 600]
2    [700, 800, 900, 1000]
dtype: object

如果需要将输出作为列，请首先通过 stack ，然后 drop_duplicates :

If need output as column, first reshape by stack and then drop_duplicates:

df1 = (df.set_index('number')[['id1', 'id2']]
         .stack()
         .reset_index(level=1, drop=True)
         .reset_index(name='a')
         .drop_duplicates())
print (df1)
    number     a
0        0   100
5        0   200
6        1   300
7        1   500
8        1   400
9        1   600
10       2   700
13       2   800
15       2   900
17       2  1000

这篇关于Pandas Groupby唯一多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！