Skip to content
Advertisement

Pandas: Counting number of times registers appear in rows in new columns for all rows

I have a dataframe like this:

   Player          Serie
0   Peter           1
1   Louis           2
2   Andrew          1
3   Peter           2
4   Michael         1
5   Alfred          1
6   Peter           1
7   Michael         2
8   Andrew          1
9   Michael         2

I need 3 new columns:

  • ‘Times’ would count how many times the Player appears and in all the rows of that Player would appear the total number. So if Peter appears in 3 rows, here would show number 3 in all Peter rows.
  • ‘Serie 1’ would count how many times this Player has the number 1 in the column Serie.
  • ‘Serie 2’ would count how many times this Player has the number 2 in the column Serie.

So in my example the final result would be:

   Player          Serie   Times   Serie 1   Serie 2
0   Peter           1        3        2        1
1   Louis           2        1        0        2
2   Andrew          1        2        2        0
3   Peter           2        3        2        1
4   Michael         1        3        1        2
5   Alfred          1        1        1        0
6   Peter           1        3        2        1
7   Michael         2        3        1        2
8   Andrew          1        2        2        0
9   Michael         2        3        1        2

As you can see, the values of the new columns when a Player appears more than once are the same, but I need it like this. Thanks in advance for your help.

Advertisement

Answer

Use Series.map with Series.value_counts and for new counts columns crosstab with DataFrame.add_prefix appended to original by DataFrame.join:

df['Times'] = df['Player'].map(df['Player'].value_counts())
df = df.join(pd.crosstab(df['Player'], df['Serie']).add_prefix('Serie '), on='Player')
print (df)
    Player  Serie  Times  Serie 1  Serie 2
0    Peter      1      3        2        1
1    Louis      2      1        0        1
2   Andrew      1      2        2        0
3    Peter      2      3        2        1
4  Michael      1      3        1        2
5   Alfred      1      1        1        0
6    Peter      1      3        2        1
7  Michael      2      3        1        2
8   Andrew      1      2        2        0
9  Michael      2      3        1        2
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement