calculate number and length of non NaN values in a pandas row

user_2123706 0
Viewed 133

I have:

pd.DataFrame({'A':[1,2,3],'B':[np.NaN,4,5],'C':[6,7,np.NaN],'D':[8,9,np.NaN],'E':[np.NaN,11,12],'F':[13,14,15]})

    A   B   C   D   E       F
0   1   NaN 6.0 8   NaN     13
1   2   4.0 7.0 9   11.0    14
2   3   5.0 NaN NaN 12.0    15

and I want to calculate the number of non NaN sequences (cnt) and length of each run (runs). Ie, row 0 has a non NaN sequences of length 1,2, and 1, for a total of 3 sequences.

pd.DataFrame({'runs':[[1,2,1],[5],[2,2]],'cnt':[3,1,2]})
    runs        cnt
0   [1, 2, 1]   3
1   [5]         1
2   [2, 2]      2

Any suggestions

pandas
1 Answers

We can do stack then groupby with subgroup created by cumsum and isna

s = df.stack(dropna=False).reset_index(name='value')
out = s[s['value'].notna()].groupby([s['level_0'],s['value'].isna().cumsum()]).size().groupby(level=[0]).agg([list,len])
out
Out[269]: 
              list  len
level_0                
0        [1, 2, 1]    3
1              [6]    1
2           [2, 2]    2
user_79645270
Related