熊猫:如何查找列中每个类别的最大n值

I have a huge municipal library catalog dataset with book title, the library it's in, the library's borough, and the number of times it was loaned out.

我有一个很大的市立图书馆目录数据集，有书名，它所在的图书馆，图书馆的行政区，以及借出的次数。

I want to find the top 3 most loaned books for each neighbourhood.

我想找到每一个街区出借最多的三本书。

Ideally, I'd get something like this:

理想情况下，我会得到这样的东西:

Borough    Title    Total_loans
A          Book1    35615 
A          Book2    34895
A          Book3    2548
B          Book1    6541
B          Book2    5425

etc.

等。

This is the closest I was able to get, but the resulting data frame is not grouped by borough and hard to read.

这是我所能得到的最接近的数据，但最终的数据帧并没有分组，很难读取。

import pandas as pd

df = pd.DataFrame({"borough":["A", "B", "B", "A", "A"], "title":["Book2", "Book1", "Book2", "Book2", "Book1"], "total_loans":[4, 48, 46, 78, 15]})

top_boroughs = df.groupby(['borough','title'])
top_boroughs.aggregate(sum).sort(['total_loans','title'], ascending=False)

Thanks for your help.

谢谢你的帮助。

3 个解决方案

#1

In short:

简而言之:

df.groupby(level=[0,1]).sum().reset_index().sort_values(['borough', 'total_loans'], ascending=[1,0]).groupby('borough').head(3)

The steps:

的步骤:

Do the correct grouping and sum
做正确的分组和求和
Sort by borough and maximum values
按行政区和最大值排序。
group by borough and take 3 first
一组一组，第3组

This is superior to the accepted answer due to both

由于这两个原因，这比公认的答案要好

readability (yes, one long line, but you could equally split it up): all standard operations
可读性(是的，一行很长，但是你可以把它分开):所有的标准操作
performance (standard optimized operations compared to enlarging a dataframe iteratively with concat, wasting memory
性能(与用concat迭代扩展一个dataframe相比，标准的优化操作会浪费内存

My output (using head(1) since test data has only 2 rows per group:

我的输出(使用head(1)，因为测试数据每组只有2行:

Out[484]: 
  borough  title  total_loans
1       A  Book2           82
2       B  Book1           48

#2

something like this:

是这样的:

t = df.groupby(['borough', 'title']).sum()
t.sort('total_loans', ascending=True)
t = t.groupby(level=[0,1]).head(3).reset_index()
t.sort(['borough', 'title'], ascending=(True, False)) #not sure if this is necessary, tough to tell with limited data, but just in case...

#3

'''
Created on Jul 30, 2014

class TopX():



    def __init__(self, top,sortFunction):
        self.topX=top
        self.sortFunction=sortFunction
        self.data=[]


    def addNewItem(self,item):
        self.data.append(item)
        self.data.sort( key=self.sortFunction,reverse=True)
        self.data=self.data[:self.topX]
    def getMax(self):
        return self.data



def runMe():
    top = TopX(3, lambda x:int(x[2]))
    with open("lib.txt","r") as f:
        string= f.readlines()
        for line in string:
            data= [x.strip() for x in line.split(' ')]
            top.addNewItem(data)

    print top.getMax()    


if __name__ == '__main__':
    runMe()

Works with input file in format:

处理输入文件的格式:

A Book1 1
A Book2 10
A Book3 3
B Book1 7
B Book2 5

Giving results:

给结果:

[['A', 'Book2', '10'], ['B', 'Book1', '7'], ['B', 'Book2', '5']]

You may specify amount of top books and sorting key if you need to adjust criteria later on.

如果稍后需要调整标准，您可以指定*图书的数量和排序键。

#1