Pandas table computation [duplicate]

Question

I have a table as follows:

+-------+-------+-------------+
| Code  | Event | No. of runs |
+-------+-------+-------------+
|    66 |     1 |             |
|    66 |     1 |           2 |
|    66 |     2 |             |
|    66 |     2 |             |
|    66 |     2 |           3 |
|    66 |     3 |             |
|    66 |     3 |             |
|    66 |     3 |             |
|    66 |     3 |             |
|    66 |     3 |           5 |
|    70 |     1 |             |
|    70 |     1 |             |
|    70 |     1 |             |
|    70 |     1 |           4 |
+-------+-------+-------------+

Let's call each row a run. I want to count the no. of runs in each Event, separately for each Code. Would I need to use the groupby function? I have added the expected output in the No. of runs column.

" no. of runs in each Event" means ? can you show the expected df? — anky
– anky, Commented May 28, 2019 at 17:09
So just a standard groupby then? df.groupby(['SPAnr', 'Event']).count() — Dan
– Dan, Commented May 28, 2019 at 17:19

srkdb · Accepted Answer · 2019-05-28 18:22:35Z

3

Try using groupby with transfrom then mask duplicated rows:

df['Runs'] = df.groupby(['Code', 'Event'])['Event']\
               .transform('count')\
               .mask(df.duplicated(['Code','Event'], keep='last'), '')

Output (add new column to output dataframe from comparison to desired result):

    Code     Event    No. of runs Runs
0      66      1                    
1      66      1             2     2
2      66      2                    
3      66      2                    
4      66      2             3     3
5      66      3                    
6      66      3                    
7      66      3                    
8      66      3                    
9      66      3             5     5
10     70      1                    
11     70      1                    
12     70      1                    
13     70      1             4     4

edited May 28, 2019 at 18:22

srkdb

8154 gold badges16 silver badges30 bronze badges

answered May 28, 2019 at 17:17

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

srkdb Over a year ago

When I run the above command, I get ValueError: Wrong number of items passed 2, placement implies 1

Scott Boston Over a year ago

Change the first line to include Event column as aggregation column. df.groupby(['SPAnr', 'Event'])['Event']

Collectives™ on Stack Overflow

Pandas table computation [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related