Counting number of feature values in ranges in python dataframe

user_7373787 0
Viewed 66

I have a dataframe that contains multiple positive measurements per patient, like so:

    patientID       values
0   patient1        6.2
1   patient1        3.9
2   patient2        7.4
3   patient2        3.4
4   patient2        7.7
5   patient3        9.2

I would like to count how many values per patient belong to the following ranges:

  • low: 0 - 4
  • normal: 4 - 7.8
  • high: 7.8 - 20

So I would like to transform the previous dataframe into:

    patientID   low  normal  high
0   patient1    1    1       0
1   patient2    1    2       0
2   patient3    0    0       1

To do this I have tried to use value_counts with the bins = [0, 4, 7.8, 20], like so:

df.groupby(['patientID'])["values"].value_counts(bins=[0, 4, 7.8, 20], sort=False)

Which led to the following output:

patientID    values       
patient1     (-0.001, 4.0]      1
             (4.0, 7.8]        1
             (7.8, 20.0]        0
patient2     (-0.001, 4.0]      1
             (4.0, 7.8]       2
             (7.8, 20.0]        0
patient3     (-0.001, 4.0]      0
             (4.0, 7.8]        0
             (7.8, 20.0]       1

So my questions are:

  • Is this a correct method to achieve my goal?
  • How do I extract the 3 outputs per patients of value_counts to the columns (low, normal, high) to get the dataframe above.

Many thanks.

pandas dataframe python
1 Answers

We can do cut with crosstab

out = pd.crosstab(df.patientID, pd.cut(df['values'],
                                       bins=[0,4,7.8,20],
                                       labels=['low','normal','high']))
#out.reset_index(inplace=True)

Out[5]: 
values     low  normal  high
patientID                   
patient1     1       1     0
patient2     1       2     0
patient3     0       0     1
user_79645270
Related