Pandas table computation

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, May 28 '19 at 17:09
also relevant: https://stackoverflow.com/questions/17679089/pandas-dataframe-groupby-two-columns-and-get-counts — wpercy, May 28 '19 at 17:10
So just a standard groupby then? `df.groupby(['SPAnr', 'Event']).count()` — Dan, May 28 '19 at 17:19

score 3 · Accepted Answer · edited May 28 '19 at 18:22

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

When I run the above command, I get ValueError: Wrong number of items passed 2, placement implies 1 — srkdb, May 28 '19 at 17:21
Change the first line to include Event column as aggregation column. `df.groupby(['SPAnr', 'Event'])['Event']` — Scott Boston, May 28 '19 at 17:25

Pandas table computation

1 Answers1