OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

Replicating excel COUNTIFS with multiple criteria from one dataset used on values in another dataset

  • Thread starter Thread starter lb_so
  • Start date Start date
L

lb_so

Guest
I have a variant to the "how do I do an Excel COUNTIFS equivalent statement in python?' question.

In my instance, I wish to count how many items appear in a population with a score greater than or equal to a target score. The target score for a given changes each row.

The first dataset would be the required parameters.

Code:
    type    required_score
    A   4
    A   5
    A   6
    A   7
    B   1
    B   3
    B   8
    B   9
    C   2
    C   5
    C   6
    C   7
    C   8

The second dataset would be the population to be assessed vs. those requirements.

Code:
    type    actual_score
    A   3
    A   2
    A   7
    A   5
    B   1
    B   3
    B   8
    B   9
    C   8
    C   8
    C   3
    C   2

My aim would be to produce a column ('Available Population') that tells me how many scores in the second table are available that were GREATER THAN OR EQUAL TO the required score defined in the first table. It's the GT/E bit that's got me stumped.

Code:
    type    required_score  available_population
    A   4              2
    A   5              2
    A   6              1
    A   7              1
    B   1              4
    B   3              3
    B   8              2
    B   9              1
    C   2              4
    C   5              2
    C   6              2
    C   7              2
    C   8              2

Without constraining any answers, I would prefer pandas based solutions (if only to increase my understanding of how to use this library a bit better!). I've started to understand various groupby, agg and transform functions. I know the answer is going to be a combination of some of those methods!
<p>I have a variant to the "how do I do an Excel COUNTIFS equivalent statement in python?' question.</p>
<p>In my instance, I wish to count how many items appear in a population with a score <strong>greater than or equal</strong> to a target score. The target score for a given changes each row.</p>
<p>The first dataset would be the required parameters.</p>
<pre><code>
type required_score
A 4
A 5
A 6
A 7
B 1
B 3
B 8
B 9
C 2
C 5
C 6
C 7
C 8

</code></pre>
<p>The second dataset would be the population to be assessed vs. those requirements.</p>
<pre><code>
type actual_score
A 3
A 2
A 7
A 5
B 1
B 3
B 8
B 9
C 8
C 8
C 3
C 2

</code></pre>
<p>My aim would be to produce a column ('Available Population') that tells me how many scores in the second table are available that were <strong>GREATER THAN OR EQUAL</strong> TO the required score defined in the first table. It's the GT/E bit that's got me stumped.</p>
<pre><code>
type required_score available_population
A 4 2
A 5 2
A 6 1
A 7 1
B 1 4
B 3 3
B 8 2
B 9 1
C 2 4
C 5 2
C 6 2
C 7 2
C 8 2

</code></pre>
<p>Without constraining any answers, I would prefer pandas based solutions (if only to increase my understanding of how to use this library a bit better!). I've started to understand various groupby, agg and transform functions. I know the answer is going to be a combination of some of those methods!</p>
 

Latest posts

Top