mmeclimate mmeclimate - 3 months ago 17
Python Question

Python pandas - construct multivariate pivot table to display count of NaNs and non-NaNs

I have a dataset based on different weather stations for several variables (Temperature, Pressure, etc.),

stationID | Time | Temperature | Pressure |...
----------+------+-------------+----------+
123 | 1 | 30 | 1010.5 |
123 | 2 | 31 | 1009.0 |
202 | 1 | 24 | NaN |
202 | 2 | 24.3 | NaN |
202 | 3 | NaN | 1000.3 |
...


and I would like to create a pivot table that would show the number of NaNs and non-NaNs per weather station, such that:

stationID | nanStatus | Temperature | Pressure |...
----------+-----------+-------------+----------+
123 | NaN | 0 | 0 |
| nonNaN | 2 | 2 |
202 | NaN | 1 | 2 |
| nonNaN | 2 | 1 |
...


Below I show what I have done so far, which works (in a cumbersome way) for Temperature. But how can I get the same for both variables, as shown above?

import pandas as pd
import bumpy as np
df = pd.DataFrame({'stationID':[123,123,202,202,202], 'Time':[1,2,1,2,3],'Temperature':[30,31,24,24.3,np.nan],'Pressure':[1010.5,1009.0,np.nan,np.nan,1000.3]})

dfnull = df.isnull()
dfnull['stationID'] = df['stationID']
dfnull['tempValue'] = df['Temperature']
dfnull.pivot_table(values=["tempValue"], index=["stationID","Temperature"], aggfunc=len,fill_value=0)


The output is:

----------------------------------
tempValue
stationID | Temperature
123 | False 2
202 | False 2
| True 1

Answer

UPDATE: thanks to @root:

In [16]: df.groupby('stationID')[['Temperature','Pressure']].agg([nans, notnans]).astype(int).stack(level=1)
Out[16]:
                   Temperature  Pressure
stationID
123       nans               0         0
          notnans            2         2
202       nans               1         2
          notnans            2         1

Original answer:

In [12]: %paste
def nans(s):
    return s.isnull().sum()

def notnans(s):
    return s.notnull().sum()
## -- End pasted text --

In [37]: df.groupby('stationID')[['Temperature','Pressure']].agg([nans, notnans]).astype(np.int8)
Out[37]:
          Temperature         Pressure
                 nans notnans     nans notnans
stationID
123                 0       2        0       2
202                 1       2        2       1