CODEWITHSUNDEEP
python-3.xpandaspivot-table
Dance Party
Dance Party

Reputation: 3713

Pandas Pivot Table Count Values (Exclude "NaN")

Given the following data frame:

import numpy as np
import pandas as pd
df = pd.DataFrame({'Site':['a','a','a','b','b','b'],
                   'x':[1,1,0,1,np.nan,0],
                   'y':[1,np.nan,0,1,1,0]
                   })

df

    Site    y   x
0   a      1.0  1
1   a      NaN  1
2   a      0.0  0
3   b      1.0  1
4   b      1.0  NaN
5   b      0.0  0

I'd like to pivot this data frame to get the count of values (excluding "NaN") for each column.

I tried what I found in other posts, but nothing seems to work (maybe there was a change in pandas 0.18)?

Desired result:

    Item  count 
Site            
a   y     2     
b   y     3     
a   x     3     
b   x     2

Thanks in advance!

Upvotes: 1

Views: 6770

Answers (1)

user2285236
user2285236

Reputation: 

pvt = pd.pivot_table(df, index = "Site", values = ["x", "y"], aggfunc = "count").stack().reset_index(level = 1)
pvt.columns = ["Item", "count"]

pvt
Out[38]: 
     Item  count
Site            
a       x      3
a       y      2
b       x      2
b       y      3

You can add pvt.sort_values("Item", ascending = False) if you want y's to appear first.

Upvotes: 1

Related Questions