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