Filtering a pivot table by non pivot dataframe column

Question

I have a dataframe (df)

                 id                       company          sector currency           price 
0     BBG.MTAA.MS.S                  MEDIASET SPA  Communications      EUR        4.334000
1    BBG.MTAA.TIT.S            TELECOM ITALIA SPA  Communications      EUR        1.091000    
2    BBG.XETR.DTE.S       DEUTSCHE TELEKOM AG-REG  Communications      EUR       15.460000   
3   BBG.XLON.BARC.S                  BARCLAYS PLC       Financial      GBp        3.414498    
4    BBG.XLON.BTA.S                  BT GROUP PLC  Communications      GBp        5.749122    
5   BBG.XLON.HSBA.S             HSBC HOLDINGS PLC       Financial      GBp        6.716041    
6   BBG.XLON.LLOY.S      LLOYDS BANKING GROUP PLC       Financial      GBp        1.027752    
7   BBG.XLON.STAN.S        STANDARD CHARTERED PLC       Financial      GBp        9.707300    
8   BBG.XLON.TRIL.S        THOMSON REUTERS UK LTD  Communications      GBp             NaN         
9    BBG.XLON.VOD.S            VODAFONE GROUP PLC  Communications      GBp        3.035487    
10  BBG.XMCE.BBVA.S  BANCO BILBAO VIZCAYA ARGENTA       Financial      EUR        7.866000

I can create a pivot table on the sector field (to find out how many companies are in the same sector) using the following code:

sectorPivot = df.pivot_table(index=['sector'], aggfunc='count')

Which looks like this:

                currency  id     company
sector                            
Communications         6   6           6
Financial              5   5           5

However I would like to filter out the companies with a price that is equal to 'NaN' so I have a pivot table that looks like

                currency  id     company
sector                            
Communications         5   5           5
Financial              5   5           5

(Note that the count of the communications sector has decreased by 1 from 6 to 5 due to the 'NaN' price for one of the broad_sector stocks).

How can I do this?

Answer 1

Use dropna(subset=['price'] ahead of your pivot.

df.dropna(subset=['price']).pivot_table(index=['sector'], aggfunc='count')