Warm tip: This article is reproduced from stackoverflow.com, please click
group-by python

Python: groupy-by to find percentage of occurrences in rows and columns of df

发布于 2020-03-27 15:43:47

I have a Dataframe below that has multiple people in the entryName column. Based on this column (assuming groupby) I would like check the other rows to see if the person in C1,C2,etc was used again in the entryName persons other entries. So Player1 has two entries, he used Zach Parise in both entries. Zachs usage percentage would be 100% for Player1. Player2 has 3 entries, he used Patrick Kane in 1 of 3 entries. Patricks usage percentage would be 33% for Player2. Above I assumed this would use some sort of groupby but I am not sure how it would check the rest. Any tips would be very helpful. 

import pandas as pd
import numpy as np

df = pd.DataFrame(data=np.array([['Player1','Mark Scheifele','Pierre-Luc Dubois','Mats Zuccarello','Oliver Bjorkstrand','Nick Foligno','Ryan Suter','Seth Jones','Devan Dubnyk','Zach Parise'],['Player2','Kyle Connor','Pierre-Luc Dubois','Mats Zuccarello','Oliver Bjorkstrand','Nick Foligno','Ryan Suter','Seth Jones','Devan Dubnyk','Boone Jenner'],['Player2','Patrick Kane','Joseph Walter','Luke Kunin','Oliver Bjorkstrand','Mark Foligno','Ryan Suter','Matt Dumba','Alex Stalock','Eric Staal'],['Player2','Boone Jenner','Pierre-Luc Dubois','Mats Zuccarello','Oliver Bjorkstrand','Nick Foligno','Ryan Suter','Seth Jones','Devan Dubnyk','Kyle Connor'],['Player1','Patrick Kane','David Backes','Zach Parise','Oliver Bjorkstrand','Mark Foligno','Jonas Brodin','Matt Dumba','Alex Stalock','Eric Staal']]), columns=['entryName','C1','C2','W1','W2','W3','D1','D2','G','UTIL'])

df   

  entryName              C1  ...             G          UTIL
0   Player1  Mark Scheifele  ...  Devan Dubnyk   Zach Parise
1   Player2     Kyle Connor  ...  Devan Dubnyk  Boone Jenner
2   Player2    Patrick Kane  ...  Alex Stalock    Eric Staal
3   Player2    Boone Jenner  ...  Devan Dubnyk   Kyle Connor
4   Player1    Patrick Kane  ...  Alex Stalock    Eric Staal

[5 rows x 10 columns]



df_result = pd.DataFrame(data=np.array([['Player1','Mark Scheifele','50.00','Pierre-Luc Dubois','50.00','Mats Zuccarello','50.00','Oliver Bjorkstrand','100.00','Nick Foligno','50.00','Ryan Suter','50.00','Seth Jones','50.00','Devan Dubnyk','50.00','Zach Parise','100.00'],['Player2','Kyle Connor','66.66','Pierre-Luc Dubois','66.66','Mats Zuccarello','66.66','Oliver Bjorkstrand','100.00','Nick Foligno','66.66','Ryan Suter','100.00','Seth Jones','66.66','Devan Dubnyk','66.66','Boone Jenner','66.66'],['Player2','Patrick Kane','33.33','Joseph Walter','33.33','Luke Kunin','33.33','Oliver Bjorkstrand','100.00','Mark Foligno','33.33','Ryan Suter','100.00','Matt Dumba','33.33','Alex Stalock','33.33','Eric Staal','33.33'],['Player2','Boone Jenner','66.66','Pierre-Luc Dubois','66.66','Mats Zuccarello','66.66','Oliver Bjorkstrand','100.00','Nick Foligno','66.66','Ryan Suter','100.00','Seth Jones','66.66','Devan Dubnyk','66.66','Kyle Connor','66.66'],['Player1','Patrick Kane','50.00','David Backes','50.00','Zach Parise','100.00','Oliver Bjorkstrand','100.00','Mark Foligno','50.00','Jonas Brodin','50.00','Matt Dumba','50.00','Alex Stalock','50.00','Eric Staal','50.00']]), columns=['entryName','C1','C1_Spr','C2','C2_Spr','W1','W1_Spr','W2','W2_Spr','W3','W3_Spr','D1','D1_Spr','D2','D2_Spr','G','G_Spr','UTIL','UTIL_Spr'])

df_result

  entryName              C1 C1_Spr  ...  G_Spr          UTIL UTIL_Spr
0   Player1  Mark Scheifele  50.00  ...  50.00   Zach Parise   100.00
1   Player2     Kyle Connor  66.66  ...  66.66  Boone Jenner    66.66
2   Player2    Patrick Kane  33.33  ...  33.33    Eric Staal    33.33
3   Player2    Boone Jenner  66.66  ...  66.66   Kyle Connor    66.66
4   Player1    Patrick Kane  50.00  ...  50.00    Eric Staal    50.00

[5 rows x 19 columns]
Questioner
Mike.J
Viewed
39
分享
Mykola Zotko 2020-01-31 16:50

You can use the following solution:

df_melt = df.melt(id_vars='entryName')
df_cross = pd.crosstab(df_melt['entryName'], df_melt['value'])
df_pct = df_cross / df.groupby('entryName').size().values[:, None] * 100
print(df_pct.T.sort_index())

This table nicely summarizes all the data:

entryName           Player1     Player2
value                                  
Alex Stalock           50.0   33.333333
Boone Jenner            0.0   66.666667
David Backes           50.0    0.000000
Devan Dubnyk           50.0   66.666667
Eric Staal             50.0   33.333333
Jonas Brodin           50.0    0.000000
Joseph Walter           0.0   33.333333
Kyle Connor             0.0   66.666667
Luke Kunin              0.0   33.333333
Mark Foligno           50.0   33.333333
Mark Scheifele         50.0    0.000000
Mats Zuccarello        50.0   66.666667
Matt Dumba             50.0   33.333333
Nick Foligno           50.0   66.666667
Oliver Bjorkstrand    100.0  100.000000
Patrick Kane           50.0   33.333333
Pierre-Luc Dubois      50.0   66.666667
Ryan Suter             50.0  100.000000
Seth Jones             50.0   66.666667
Zach Parise           100.0    0.000000

To achieve your desired result you can do:

# append percents to main dataframe
df_stack = df_pct.stack().round(2)
for col in df.columns[1:]:
    df = df.merge(df_stack.to_frame(f'{col}_Spr'), left_on=['entryName', col], right_index=True)

# sort columns in desired order
cols = [df.columns[0]]
for col in df.columns[1:10]:
    cols.append(col)
    cols.append(f'{col}_Spr')

df = df.reindex(cols, axis=1).sort_index()

Output:

  entryName              C1  C1_Spr                 C2  C2_Spr  \
0   Player1  Mark Scheifele   50.00  Pierre-Luc Dubois   50.00   
1   Player2     Kyle Connor   66.67  Pierre-Luc Dubois   66.67   
2   Player2    Patrick Kane   33.33      Joseph Walter   33.33   
3   Player2    Boone Jenner   66.67  Pierre-Luc Dubois   66.67   
4   Player1    Patrick Kane   50.00       David Backes   50.00   

                W1  W1_Spr                  W2  W2_Spr            W3  W3_Spr  \
0  Mats Zuccarello   50.00  Oliver Bjorkstrand   100.0  Nick Foligno   50.00   
1  Mats Zuccarello   66.67  Oliver Bjorkstrand   100.0  Nick Foligno   66.67   
2       Luke Kunin   33.33  Oliver Bjorkstrand   100.0  Mark Foligno   33.33   
3  Mats Zuccarello   66.67  Oliver Bjorkstrand   100.0  Nick Foligno   66.67   
4      Zach Parise  100.00  Oliver Bjorkstrand   100.0  Mark Foligno   50.00   

             D1  D1_Spr          D2  D2_Spr             G  G_Spr  \
0    Ryan Suter    50.0  Seth Jones   50.00  Devan Dubnyk  50.00   
1    Ryan Suter   100.0  Seth Jones   66.67  Devan Dubnyk  66.67   
2    Ryan Suter   100.0  Matt Dumba   33.33  Alex Stalock  33.33   
3    Ryan Suter   100.0  Seth Jones   66.67  Devan Dubnyk  66.67   
4  Jonas Brodin    50.0  Matt Dumba   50.00  Alex Stalock  50.00   

           UTIL  UTIL_Spr  
0   Zach Parise    100.00  
1  Boone Jenner     66.67  
2    Eric Staal     33.33  
3   Kyle Connor     66.67  
4    Eric Staal     50.00
Mike.J 2020-02-04 11:45:04

Great Job Mykola! Thank you very much for your help!

Related issues

1
How to use python cut method to create bins, accept one parameter and return appropriate bin?
2
Create a dictionary from a list of lists with certain criteria
3
selecting columns based on row value, Python, Pandas
4
plotting count of zeros and ones in a dataframe
5
BeautifulSoup find.all() web scraping returns empty
6
python function. output a keys list from a dictionary if the key is todays date
7
Best way to perform multiple amount of Pandas lookups between two DataFrames
8
How to get the number of columns and the width of each column in a Pandas pivot table?
9
Display a column when a desired value is missing while grouping in Pandas dataframe
10
Python hide ticks but show tick labels