Pandas: Count Higher Ranks For Current Experiment Participants In Later Experiments (Part 2)

matekus 0
Viewed 36

Learning Experiments

In a series of learning experiments, I would like to count the number of participants in each experiment that improved their performance in subsequent experiments (Rank 1 is the highest). In addition, I would also like to count the number of participants in each experiment that subsequently reached the top rank. @mozway has answered the original questions here.

Ideally, I would also like to output the number of improved participants for each participant. For example, two participants (Charlie and Echo) from experiment 'A' improve their performances in subsequent experiments (Charlie ('B') and Echo ('B')) giving experiment 'A' a score of 2. Similarly, Juliet from experiment 'B' improves her performance in experiment 'C' giving a score of 1 to experiment 'C'. In essence, every time there is improved performance by participants in the current experiment in subsequent experiments then it adds to the score of the current experiment.

For improved performance:

Experiment Score Subjects
A 2 Bravo, Charlie
B 1 Juliet
C 0

For top performance:

Experiment Score Subjects
A 1 Alpha
B 1 Juliet
C 0

How do I calculate these and the equivalent scores for experiments whose participants subsequently reached the 'top-rank'?

Here is a short, sanitized version of the learning experiment csv file that I have loaded into a pandas dataframe (df_learning).

Experiment Subject Rank
A Alpha 1
A Bravo 2
A Charlie 3
A Delta 4
A Echo 5
B Alpha 1
B Charlie 2
B Echo 3
B Foxtrot 4
B Golf 5
B India 6
B Juliet 7
C Juliet 1
C Bravo 2
C Charlie 3

Please advise?

pandas python
1 Answers

As in your previous question, start by getting the rows where the performance improved:

m = df['Rank'].sub(df.groupby('Subject')['Rank'].cummax()).lt(0)

Then you can computed the shifted sum per group:

out = (m
  .groupby(df['Experiment']).sum()
  .shift(-1, fill_value=0)
  .reset_index(name='Score')
)

  Experiment  Score
0          A      2
1          B      1
2          C      0

For the top ranks:

out = ((m&df['Rank'].eq(1))
  .groupby(df['Experiment']).sum()
  .shift(-1, fill_value=0)
  .reset_index(name='Score')
)

  Experiment  Score
0          A      0
1          B      1
2          C      0
mozway0
Related