Count positive, negative or zero values numbers for multiple columns in Python-CodePudding

Given a dataset as follows:

[{'id': 1, 'ltp': 2, 'change': nan},
 {'id': 2, 'ltp': 5, 'change': 1.5},
 {'id': 3, 'ltp': 3, 'change': -0.4},
 {'id': 4, 'ltp': 0, 'change': 2.0},
 {'id': 5, 'ltp': 5, 'change': -0.444444},
 {'id': 6, 'ltp': 16, 'change': 2.2}]

   id  ltp    change
0   1    2       NaN
1   2    5  1.500000
2   3    3 -0.400000
3   4    0  2.000000
4   5    5 -0.444444
5   6   16  2.200000

I would like to count the number of positive, negative and 0 values for columns ltp and change, the result may like this:

  columns  positive  negative  zero
0     ltp         5         0     1
1  change         3         2     0

How could I do that with Pandas or Numpy? Thanks.

Updated: if I need groupby type and count following the logic above

   id  ltp    change type
0   1    2       NaN    a
1   2    5  1.500000    a
2   3    3 -0.400000    a
3   4    0  2.000000    b
4   5    5 -0.444444    b
5   6   16  2.200000    b

The expected output:

  type columns  positive  negative  zero
0    a     ltp         3         0     0
1    a  change         1         1     0
2    b     ltp         2         0     1
3    b  change         2         1     0

CodePudding user response：

Use np.sign with selected columns first, then counts values in value_counts, transpose, replaced missing values and last rename columns names by dictionary with convert index to column columns:

d= {-1:'negative', 1:'positive', 0:'zero'}
df = (np.sign(df[['ltp','change']])
        .apply(pd.value_counts)
        .T
        .fillna(0)
        .astype(int)
        .rename(columns=d)
        .rename_axis('columns')
        .reset_index())
print (df)
  columns  negative  zero  positive
0     ltp         0     1         5
1  change         2     0         3

EDIT: Another solution with type column with DataFrame.melt, mapping column with np.sign and count values by crosstab:

d= {-1:'negative', 1:'positive', 0:'zero'}

df1 = df.melt(id_vars='type', value_vars=['ltp','change'], var_name='columns')
df1['value'] = np.sign(df1['value']).map(d)
df1 = (pd.crosstab([df1['type'],df1['columns']], df1['value'])
         .rename_axis(columns=None)
         .reset_index())

print (df1)
  type columns  negative  positive  zero
0    a  change         1         1     0
1    a     ltp         0         3     0
2    b  change         1         2     0
3    b     ltp         0         2     1