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')
>Solution :
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