Count of consecutive nulls grouped by key column in Pandas dataframe

user_12952955 0
Viewed 311

My dataset(yearly data) looks like this

CODE        Date        PRCP    TAVG        TMAX        TMIN
AE000041196 01-01-2020  0       21.1        
AE000041196 02-01-2020  0       21.4        
AE000041196 03-01-2020  0       21.2                    15.4
AE000041196 04-01-2020  0       21.9                    14.9
AE000041196 05-01-2020  0       23.7                    16.5
AE000041196 06-01-2020  0.5     20.7        
AE000041196 07-01-2020  0       18.1                    11.5
AE000041196 08-01-2020  0       19.6                    10.3
AE000041196 09-01-2020  0.3     20.6                    13.8

I am trying to find out the longest run of consecutive missing values[Max count of consecutive NaN for each 'CODE'] for columns TMAX and TMIN for each value in CODE. eg. From the limited dataset above:
Max consecutive missing value for TMAX would be 9, and for TMIN would be 2

The code I am using

df['TMAX_nullccount'] = df.TMAX.isnull().astype(int).groupby(df['TMAX'].notnull().astype(int).cumsum()).cumsum()

This leads to errors in dataset when

CODE        Date        PRCP  TAVG  TMAX TMIN   TMAX_nullccount
CA1AB000014 10-03-2021  2.3                     297
CA1AB000014 11-03-2021  0                       298
CA1AB000014 12-03-2021  0                       299
CA1AB000014 13-03-2021  0                       300
CA1AB000014 14-03-2021  0                       301
CA1AB000015 01-01-2021  0                       302
CA1AB000015 02-01-2021  0                       303
CA1AB000015 03-01-2021  0                       304
CA1AB000015 04-01-2021  0                       305

In theory the count(TMAX_nullcount) should have started from 0 again code changed from CA1AB000014 to CA1AB000015. Also value in column TMAX_nullcount cannot exceed 365(yearly dataset) but my code give values way more than that.

Expected Output file(values are made up)

CODE            TMAX_maxcnullcount  TMIN_maxcnullcount  TAVG_maxcnullcount
AE000041196             2               2                       0
AEM00041194             1               1                       0
AEM00041217             3               1                       0
AEM00041218             1               2                       45
AFM00040938             65              65                      0
AFM00040948             132             132                     0
AG000060390             155             141                     0

How can I fix this? Thanks in advance

pandas-groupby pandas dataframe python
2 Answers

You can try something like this:

df.groupby(['CODE', df['PRCP'].ne(df['PRCP'].shift()).cumsum()]).size().max()

groupby by CODE and the consecutive zeros then compute size.

Your groupby result (aggr->size) will be:

CODE         PRCP
AE000041196  1       5
             2       1
             3       2
             4       1

Now you can find max and min.

So your final solution will look like this:

df1 = df.fillna(0)
df1.groupby(['CODE', df1['TMAX'].ne(df1['TMAX'].shift()).cumsum()]).size().max()

9
user_66603730

You can use:

First test if match missing values:

print (df.isna())
    CODE   Date   PRCP   TAVG  TMAX   TMIN
0  False  False  False  False  True   True
1  False  False  False  False  True   True
2  False  False  False  False  True  False
3  False  False  False  False  True  False
4  False  False  False  False  True  False
5  False  False  False  False  True   True
6  False  False  False  False  True  False
7  False  False  False  False  True  False
8  False  False  False  False  True  False

#columsn for test missing values 
cols = ['TMAX','TMIN','TAVG']
#CODe to index, filter columns and create one Series
m = df.set_index('CODE')[cols].isna().unstack()

#create consecutive groups and count them with maximal count per column and group
df = (m.ne(m.shift()).cumsum()
       .where(m)
       .groupby(level=[0,1]).value_counts()
       .max(level=[0,1])
       .unstack(0)
       .add_suffix('_maxcnullcount'))
print (df)
             TMAX_maxcnullcount  TMIN_maxcnullcount
CODE                                               
AE000041196                   9                   2
user_29010020
Related