更多彙總資料的方法

在 Python 中匯入與管理財務資料

Stefan Jansen

Instructor

多種彙總方式

上一節：依一個變數分組並彙總
更細的資料摘要方式：
- 依兩個以上變數分組
- 套用多種彙總統計
範例
- 各產業與 IPO 年的市值中位數
- 各年的股價平均與標準差

類別的多種彙總

nasdaq['market_cap_m'] = nasdaq['Market Capitalization'].div(1e6)
by_sector = nasdaq.groupby('Sector')

by_sector.market_cap_m.agg(['size', 'mean']).sort_values('size')

Sector                 size          mean         
Transportation           52   2869.660007
Energy                   66    826.607608
Public Utilities         66   2357.865315
Basic Industries         78    724.899934
...
Consumer Services       348   5582.344175
Technology              433  10883.434214
Finance                 627   1044.090205
Health Care             645   1758.709197

多種彙總與新標籤

by_sector.market_cap_m.agg(['size', 'mean'])
      .rename(columns={'size': '#Obs', 'mean': 'Average'})

Sector                #Obs       Average           
Basic Industries        78    724.899934
Capital Goods          172   1511.237373
Consumer Durables       88    839.802607
Consumer Non-Durables  103   3104.051206
Consumer Services      348   5582.344175
...
Health Care            645   1758.709197
Miscellaneous           89   3445.655935
Public Utilities        66   2357.865315
Technology             433  10883.434214
Transportation          52   2869.660007

欄位對應不同統計

by_sector.agg({'market_cap_m': 'size', 'IPO Year':'median'})

Sector                 market_cap_m  IPO Year    
Basic Industries                 78    1972.0
Capital Goods                   172    1972.0
Consumer Durables                88    1983.0
Consumer Non-Durables           103    1972.0
Consumer Services               348    1981.0
...
Health Care                     645    1981.0
Miscellaneous                    89    1987.0
Public Utilities                 66    1981.0
Technology                      433    1972.0
Transportation                   52    1986.0

以兩個類別彙總

by_sector_year = nasdaq.groupby(['Sector', 'IPO Year'])

by_sector_year.market_cap_m.mean()

Sector            IPO Year
Basic Industries  1972.0      877.240005
                  1973.0     1445.697371
                  1986.0     1396.817381
                  ...
Transportation    1986.0     1176.179710
                  1991.0     6646.778622
                  1992.0       56.074572
                  ...
                  2009.0      552.445919
                  2011.0     3711.638317
                  2013.0      125.740421

從 MultiIndex 擷取

mcap_sector_year = by_sector_year.market_cap_m.mean()
mcap_sect_year.loc['Basic Industries']

IPO Year
1972.0     877.240005
1973.0    1445.697371
1986.0    1396.817381
1988.0      24.847526
...
2012.0     381.796074
2013.0      22.661533
2015.0     260.075564
2016.0      81.288336
Name: market_cap_m, dtype: float64

從 MultiIndex 擷取

mcap_sect_year.loc[['Basic Industries', 'Transportation']]

Sector            IPO Year
Basic Industries  1972.0      877.240005
                  1973.0     1445.697371
                  1986.0     1396.817381
                  ...
Transportation    1986.0     1176.179710
                  1991.0     6646.778622
                  1992.0       56.074572
                  ...

一起來練習吧！

在 Python 中匯入與管理財務資料