Python: How to get count of items within a range in a dataframe on the basis of the day of the week?

user_6259472 0
Viewed 112

I am working on a Python code in which the dataframe looks like this:

        score      start_time                      date
0        2.551020  1611848652 2021-01-28 09:44:12-06:00
1        2.596491  1613653380 2021-02-18 07:03:00-06:00
2        2.045455  1614137036 2021-02-23 21:23:56-06:00
3        2.260870  1612352809 2021-02-03 05:46:49-06:00
4        2.654867  1614185082 2021-02-24 10:44:42-06:00
...           ...         ...                       ...
208258   2.683871  1613422487 2021-02-15 14:54:47-06:00
208553   2.568000  1612922522 2021-02-09 20:02:02-06:00
208580   2.943925  1612786920 2021-02-08 06:22:00-06:00
208584   2.160256  1612550480 2021-02-05 12:41:20-06:00
208683   2.315385  1612667953 2021-02-06 21:19:13-06:00

I need to get count of scores within the given ranges basis of the day of the week.

Expected output

           2.0-2.2    2.2-2.5   2.5-3.0
Day     
Friday      10000     12344     23123         
Monday      24892     24892     24892
Saturday    23424     23424     23424
Sunday      41232     23132     23315
Thursday    41232     23152     4123
Tuesday     41232     53457     6663
Wednesday   41232     23131     34423

I should be able to get the count of scores in the ranges 2.0-2.2, 2.2-2.5 & 2.5-3.0 on the basis of date in the dataframe which should be displayed according to the day of the week.

How do I do it? I have been stuck on this step for quite a while.

Note: The given counts in the Expected Output are just random values given for the ease of understanding.

pandas python
1 Answers

pd.cut and crosstab

We can use pd.cut to categorize/bin the values in column score into discrete intervals then we can use crosstab to create a frequency table

pd.crosstab(df['date'].dt.day_name(), pd.cut(df['score'], [2, 2.2, 2.5, 3.0]))

score      (2.0, 2.2]  (2.2, 2.5]  (2.5, 3.0]
date                                         
Friday              1           0           0
Monday              0           0           2
Saturday            0           1           0
Thursday            0           0           2
Tuesday             1           0           1
Wednesday           0           1           1
user_128331660
Related