python - 排序和过滤 pandas 数据透视表

问题描述

使用这些数据

import pandas as pd 
import numpy as np
df=pd.read_excel(
    "https://github.com/chris1610/pbpython/blob/master/data/sample-salesv3.xlsx?raw=True"
    )
df["date"] = pd.to_datetime(df['date'])

我使用下一个代码来获取年、月和日：

df['year'],df['month'],df['day'] = df.date.dt.year, df.date.dt.month, df.date.dt.day

   account number                         name       sku  quantity  \
0          740150                   Barton LLC  B1-20000        39   
1          714466              Trantow-Barrows  S2-77896        -1   
2          218895                    Kulas Inc  B1-69924        23   
3          307599  Kassulke, Ondricka and Metz  S1-65481        41   
4          412290                Jerde-Hilpert  S2-34077         6   

   unit price  ext price                date  year  month  day  
0       86.69    3380.91 2014-01-01 07:21:51  2014      1    1  
1       63.16     -63.16 2014-01-01 10:00:47  2014      1    1  
2       90.70    2086.10 2014-01-01 13:24:58  2014      1    1  
3       21.05     863.05 2014-01-01 15:05:22  2014      1    1  
4       83.21     499.26 2014-01-01 23:26:55  2014      1    1

然后我使用下一个代码来获取数据透视表

df.pivot_table(index=['year','month','name'],values='ext price',aggfunc=np.sum).head(25)


                                         ext price
year month name                                      
2014 1     Barton LLC                         6177.57
           Cronin, Oberbrunner and Spencer    1141.75
           Frami, Hills and Schmidt           5112.34
           Fritsch, Russel and Anderson      15130.77
           Halvorson, Crona and Champlin      9997.17
           Herman LLC                        10749.84
           Jerde-Hilpert                     11274.33
           Kassulke, Ondricka and Metz        7322.83
           Keeling LLC                        6847.86
           Kiehn-Spinka                       8097.50
           Koepp Ltd                         10768.33
           Kuhn-Gusikowski                    7309.54
           Kulas Inc                         15398.87
           Pollich LLC                        1004.22
           Purdy-Kunde                        4689.37
           Sanford and Sons                   9544.13
           Stokes LLC                         5809.34
           Trantow-Barrows                   14328.26
           White-Trantow                     13703.77
           Will LLC                          20953.87
     2     Barton LLC                        12218.03
           Cronin, Oberbrunner and Spencer   13976.26
           Frami, Hills and Schmidt           4124.53
           Fritsch, Russel and Anderson       9595.35
           Halvorson, Crona and Champlin      7082.15

我想知道我是否可以编辑我的数据透视表来获取和排序每个月的前 5 个name（顶部ext price）。

我试图得到这个：

year month name                                      
2014 1     Barton LLC                         6177.57
           Cronin, Oberbrunner and Spencer    1141.75
           Frami, Hills and Schmidt           5112.34
           Fritsch, Russel and Anderson      15130.77
           Halvorson, Crona and Champlin      9997.17
     2     Barton LLC                        12218.03
           Cronin, Oberbrunner and Spencer   13976.26
           Frami, Hills and Schmidt           4124.53
           Fritsch, Russel and Anderson       9595.35
           Halvorson, Crona and Champlin      7082.15
...                                               ...
     11    Koepp Ltd                          4882.27
           Kuhn-Gusikowski                    7197.89
           Kulas Inc                          4149.34
           Pollich LLC                        6334.21
     12    Barton LLC                         2772.90
           Cronin, Oberbrunner and Spencer    7640.60
           Frami, Hills and Schmidt          16249.81
           Fritsch, Russel and Anderson      12345.64

我尝试使用 groupby 进行排序，但仍然找不到。

标签： pythonpandassortingpivot-table

>>> df.sort_values('ext price', ascending = False).groupby( ['year', 'month']).head(5).set_index(['year', 'month'])['name'] year month 2014 7 Kiehn-Spinka 7 Kuhn-Gusikowski 12 Koepp Ltd 7 Pollich LLC 3 Kulas Inc 2 Barton LLC 3 Keeling LLC 10 Koepp Ltd 7 Trantow-Barrows 9 Kassulke, Ondricka and Metz

python - 排序和过滤 pandas 数据透视表

问题描述

解决方案

推荐阅读