Count values in column with ranges given a specific condition-CodePudding

I have this code

df = pd.DataFrame({'R': {0: '1', 1: '2', 2: '3', 3: '4', 4: '5', 5: '6', 6: '7'}, 'an': {0: 'f', 1: 'i', 2: '-', 3: '-', 4: 'f', 5: 'c,f,i,j', 6: 'c,d,e,j'}, 'nv1': {0: [-1.0], 1: [-1.0], 2: [], 3: [], 4: [-2.0], 5: [-2.0, -1.0, -3.0, -1.0], 6: [-2.0, -1.0, -2.0, -1.0]}})

yielding the following dataframe:

    nv1
0   [-1.0]
1   [-1.0]
2   []
3   []
4   [-2.0]
5   [-2.0, -1.0, -3.0, -1.0]
6   [-2.0, -1.0, -2.0, -1.0]

I wish to create new column to calculate how many values on column df['nv1'] are below -1 in each row.

Desired output as follows:

    nv1                      ct
0   [-1.0]                    
1   [-1.0]                   
2   []                       
3   []                       
4   [-2.0]                    1  
5   [-2.0, -1.0, -3.0, -1.0]  2
6   [-2.0, -1.0, -2.0, -1.0]  2

I tried both line of codes below independently, but I ran into errors:

df['ct'] = np.sum((df['nv1']>-1))
df['ct'] = df['nv1'].mask(lambda x: x.ne(x>[-1])).transform('count')

CodePudding user response：

You need to loop here.

Either using Series.apply with a lambda function and sum:

df['ct'] = df['nv1'].apply(lambda s: sum(e<-1 for e in s))

or with a classical loop comprehension:

df['ct'] = [sum(e<-1 for e in s) for s in df['nv1']]

output:

   R       an                       nv1  ct
0  1        f                    [-1.0]   0
1  2        i                    [-1.0]   0
2  3        -                        []   0
3  4        -                        []   0
4  5        f                    [-2.0]   1
5  6  c,f,i,j  [-2.0, -1.0, -3.0, -1.0]   2
6  7  c,d,e,j  [-2.0, -1.0, -2.0, -1.0]   2

If you really want empty strings in place of zeros:

df['ct'] = [S if (S:=sum(e<-1 for e in s)) else '' for s in df['nv1']]

output:

   R       an                       nv1 ct
0  1        f                    [-1.0]   
1  2        i                    [-1.0]   
2  3        -                        []   
3  4        -                        []   
4  5        f                    [-2.0]  1
5  6  c,f,i,j  [-2.0, -1.0, -3.0, -1.0]  2
6  7  c,d,e,j  [-2.0, -1.0, -2.0, -1.0]  2

CodePudding user response：

Use lambda function with sum:

df['ct'] = df['nv1'].apply(lambda x: sum(y <-1 for y in x))

print (df)
   R       an                       nv1  ct
0  1        f                    [-1.0]   0
1  2        i                    [-1.0]   0
2  3        -                        []   0
3  4        -                        []   0
4  5        f                    [-2.0]   1
5  6  c,f,i,j  [-2.0, -1.0, -3.0, -1.0]   2
6  7  c,d,e,j  [-2.0, -1.0, -2.0, -1.0]   2

Another idea is create DataFrame by lists and compare for less -1 with sum:

df['ct'] = pd.DataFrame(df['nv1'].tolist(), index=df.index).lt(-1).sum(axis=1)