python pandas，DF.groupby().agg()，agg()中的列引用

python pandas, DF.groupby().agg(), column reference in agg()

提问by jf328

On a concrete problem, say I have a DataFrame DF

在一个具体的问题上，假设我有一个 DataFrame DF

     word  tag count
0    a     S    30
1    the   S    20
2    a     T    60
3    an    T    5
4    the   T    10 

I want to find, for every "word", the "tag" that has the most "count". So the return would be something like

我想为每个“单词”找到“计数”最多的“标签”。所以回报会是这样的

     word  tag count
1    the   S    20
2    a     T    60
3    an    T    5

I don't care about the count column or if the order/Index is original or messed up. Returning a dictionary {'the' : 'S', ...} is just fine.

我不关心计数列，也不关心订单/索引是原始的还是混乱的。返回字典 { 'the' : 'S', ...} 就好了。

I hope I can do

我希望我能做到

DF.groupby(['word']).agg(lambda x: x['tag'][ x['count'].argmax() ] )

but it doesn't work. I can't access column information.

但它不起作用。我无法访问列信息。

More abstractly, what does the functionin agg(function) see as its argument?

更抽象地说，agg( function)中的函数将什么视为其参数？

btw, is .agg() the same as .aggregate() ?

顺便说一句， .agg() 和 .aggregate() 是一样的吗？

Many thanks.

非常感谢。

采纳答案by unutbu

aggis the same as aggregate. It's callable is passed the columns (Seriesobjects) of the DataFrame, one at a time.

agg与 相同aggregate。一次一个地传递 的列（Series对象）是可调用的DataFrame。

---

---

You could use idxmaxto collect the index labels of the rows with the maximum count:

您可以使用idxmax最大计数来收集行的索引标签：

idx = df.groupby('word')['count'].idxmax()
print(idx)

yields

产量

word
a       2
an      3
the     1
Name: count

and then use locto select those rows in the wordand tagcolumns:

然后用于loc选择word和tag列中的那些行：

print(df.loc[idx, ['word', 'tag']])

yields

产量

  word tag
2    a   T
3   an   T
1  the   S

Note that idxmaxreturns index labels. df.loccan be used to select rows by label. But if the index is not unique -- that is, if there are rows with duplicate index labels -- then df.locwill select all rowswith the labels listed in idx. So be careful that df.index.is_uniqueis Trueif you use idxmaxwith df.loc

请注意，idxmax返回索引标签。df.loc可用于按标签选择行。但是如果索引不是唯一的——也就是说，如果有带有重复索引标签的行——那么df.loc将选择所有带有列在中的标签的行idx。所以，要小心，df.index.is_unique是True如果你使用idxmax与df.loc

---

---

Alternative, you could use apply. apply's callable is passed a sub-DataFrame which gives you access to all the columns:

或者，您可以使用apply. apply的 callable 传递了一个子 DataFrame ，它使您可以访问所有列：

import pandas as pd
df = pd.DataFrame({'word':'a the a an the'.split(),
                   'tag': list('SSTTT'),
                   'count': [30, 20, 60, 5, 10]})

print(df.groupby('word').apply(lambda subf: subf['tag'][subf['count'].idxmax()]))

yields

产量

word
a       T
an      T
the     S

---

---

Using idxmaxand locis typically faster than apply, especially for large DataFrames. Using IPython's %timeit:

使用idxmax和loc通常比 快apply，尤其是对于大型数据帧。使用 IPython 的 %timeit：

N = 10000
df = pd.DataFrame({'word':'a the a an the'.split()*N,
                   'tag': list('SSTTT')*N,
                   'count': [30, 20, 60, 5, 10]*N})
def using_apply(df):
    return (df.groupby('word').apply(lambda subf: subf['tag'][subf['count'].idxmax()]))

def using_idxmax_loc(df):
    idx = df.groupby('word')['count'].idxmax()
    return df.loc[idx, ['word', 'tag']]

In [22]: %timeit using_apply(df)
100 loops, best of 3: 7.68 ms per loop

In [23]: %timeit using_idxmax_loc(df)
100 loops, best of 3: 5.43 ms per loop

---

---

If you want a dictionary mapping words to tags, then you could use set_indexand to_dictlike this:

如果你想要一个将单词映射到标签的字典，那么你可以使用set_index并to_dict像这样：

In [36]: df2 = df.loc[idx, ['word', 'tag']].set_index('word')

In [37]: df2
Out[37]: 
     tag
word    
a      T
an     T
the    S

In [38]: df2.to_dict()['tag']
Out[38]: {'a': 'T', 'an': 'T', 'the': 'S'}

回答by Jeff

Here's a simple way to figure out what is being passed (the unutbu) solution then 'applies'!

这是一种简单的方法来确定正在传递的内容（unutbu）解决方案然后“应用”！

In [33]: def f(x):
....:     print type(x)
....:     print x
....:     

In [34]: df.groupby('word').apply(f)
<class 'pandas.core.frame.DataFrame'>
  word tag  count
0    a   S     30
2    a   T     60
<class 'pandas.core.frame.DataFrame'>
  word tag  count
0    a   S     30
2    a   T     60
<class 'pandas.core.frame.DataFrame'>
  word tag  count
3   an   T      5
<class 'pandas.core.frame.DataFrame'>
  word tag  count
1  the   S     20
4  the   T     10

your function just operates (in this case) on a sub-section of the frame with the grouped variable all having the same value (in this cas 'word'), if you are passing a function, then you have to deal with the aggregation of potentially non-string columns; standard functions, like 'sum' do this for you

你的函数只是在框架的一个子部分上运行（在这种情况下），分组变量都具有相同的值（在这个 cas 'word' 中），如果你正在传递一个函数，那么你必须处理聚合潜在的非字符串列；标准函数，比如“sum”为你做这件事

Automatically does NOT aggregate on the string columns

自动不会在字符串列上聚合

In [41]: df.groupby('word').sum()
Out[41]: 
      count
word       
a        90
an        5
the      30

You ARE aggregating on all columns

您正在聚合所有列

In [42]: df.groupby('word').apply(lambda x: x.sum())
Out[42]: 
        word tag count
word                  
a         aa  ST    90
an        an   T     5
the   thethe  ST    30

You can do pretty much anything within the function

你可以在函数内做几乎任何事情

In [43]: df.groupby('word').apply(lambda x: x['count'].sum())
Out[43]: 
word
a       90
an       5
the     30