-4
$\begingroup$

I am new to both python and pandas, numpy etc. I am trying to do some data analysis on batsmen performances for a given team. I have successfully written python code to scrape data and store it in sqlite db in following format

    PLAYER_ID   RUNS    BALLS_FACED STRIKE_RATE NAME    MATCH_DATE
4327    9   13  69.23   Anderson, J 6/18/2015 0:00
4327    0   0   0   Anderson, J 6/19/2015 0:00
4327    3   5   60  Anderson, J 6/20/2015 0:00
4327    0   0   0   Anderson, J 6/21/2015 0:00
3475    0   0   0   Cusack, A R 8/2/2008 0:00
3475    5   7   71.43   Cusack, A R 8/3/2008 0:00
3475    2   2   100 Cusack, A R 8/4/2008 0:00
3475    0   0   0   Cusack, A R 8/5/2008 0:00
3475    0   0   0   Cusack, A R 6/8/2009 0:00
3475    12  6   200 Cusack, A R 6/10/2009 0:00
3475    20  12  166.67  Cusack, A R 6/11/2009 0:00
3475    2   3   66.67   Cusack, A R 6/14/2009 0:00
3475    2   2   100 Cusack, A R 6/15/2009 0:00
3475    0   0   0   Cusack, A R 2/1/2010 0:00
3475    15  13  115.38  Cusack, A R 2/9/2010 0:00
3475    2   4   50  Cusack, A R 2/11/2010 0:00
3475    65  44  147.73  Cusack, A R 2/13/2010 0:00
3475    28  22  127.27  Cusack, A R 2/13/2010 0:00
3475    2   4   50  Cusack, A R 4/30/2010 0:00
3475    0   0   0   Cusack, A R 5/4/2010 0:00
3475    14  11  127.27  Cusack, A R 2/22/2012 0:00
3475    0   0   0   Cusack, A R 3/14/2012 0:00
3475    22  13  169.23  Cusack, A R 3/18/2012 0:00
3475    1   7   14.29   Cusack, A R 7/18/2012 0:00
3475    0   0   0   Cusack, A R 7/20/2012 0:00
3475    15  14  107.14  Cusack, A R 9/19/2012 0:00
3475    0   0   0   Cusack, A R 9/24/2012 0:00
3475    0   0   0   Cusack, A R 11/16/2013 0:00
3475    1   1   100 Cusack, A R 11/30/2013 0:00
3475    0   0   0   Cusack, A R 2/19/2014 0:00
3475    8   6   133.33  Cusack, A R 2/21/2014 0:00
3475    0   1   0   Cusack, A R 3/17/2014 0:00
3475    0   0   0   Cusack, A R 3/19/2014 0:00
3475    0   0   0   Cusack, A R 3/21/2014 0:00
3475    0   0   0   Cusack, A R 6/19/2015 0:00
3475    0   0   0   Cusack, A R 6/20/2015 0:00
3475    0   0   0   Cusack, A R 6/21/2015 0:00
3475    0   0   0   Cusack, A R 7/13/2015 0:00
3475    13  4   325 Cusack, A R 7/15/2015 0:00
3475    0   0   0   Cusack, A R 7/17/2015 0:00
3475    0   1   0   Cusack, A R 7/25/2015 0:00
3752    0   0   0   Dockrell, G H   2/1/2010 0:00
3752    0   0   0   Dockrell, G H   2/9/2010 0:00
3752    0   0   0   Dockrell, G H   2/11/2010 0:00
3752    0   0   0   Dockrell, G H   2/13/2010 0:00
3752    0   0   0   Dockrell, G H   2/13/2010 0:00
3752    0   6   0   Dockrell, G H   4/30/2010 0:00
3752    0   0   0   Dockrell, G H   5/4/2010 0:00
3752    0   0   0   Dockrell, G H   2/22/2012 0:00
3752    0   0   0   Dockrell, G H   2/23/2012 0:00
3752    2   2   100 Dockrell, G H   2/24/2012 0:00
3752    0   0   0   Dockrell, G H   3/14/2012 0:00
3752    0   0   0   Dockrell, G H   3/18/2012 0:00
3752    0   0   0   Dockrell, G H   3/22/2012 0:00
3752    0   0   0   Dockrell, G H   3/23/2012 0:00
3752    0   0   0   Dockrell, G H   3/24/2012 0:00

I need to find the top five performers based on the performance in last five matches based on average, runs & strike rate and plot them.

I know I need to group by each player and by match date then sort and somehow get the top 5 results based on average or runs and below is what I have so far.

dataFrame = pd.read_sql_query("select pm.player_id, pm.runs, pm.balls_faced, pm.strike_rate, pd.name, pm.match_date from player_match_details pm inner join player_data pd where pm.player_id = pd.player_id and pd.country = '" + country + "' and match_type = '" + matchType + "'", conn)

#print(dataFrame)

playerByDate = dataFrame.groupby(['PLAYER_ID','MATCH_DATE'])

I know it doesn't sound too difficult but I am clueless on how to go forward on this.

Edit : Added dataframe details as requested

RangeIndex: 600 entries, 0 to 599
Data columns (total 6 columns):
PLAYER_ID      600 non-null int64
RUNS           600 non-null int64
BALLS_FACED    600 non-null int64
STRIKE_RATE    600 non-null float64
NAME           600 non-null object
MATCH_DATE     600 non-null object
dtypes: float64(1), int64(3), object(2)
memory usage: 23.5+ KB
$\endgroup$
2
  • 1
    $\begingroup$ Your SQL query does not match your sample data. Please give us something that can be read with pandas.read_csv. $\endgroup$
    – Emre
    Jun 28 '18 at 18:56
  • $\begingroup$ Just a curious question you need csv file for your analysis in actual program it doesn't matter right? $\endgroup$
    – Neel
    Jun 29 '18 at 14:00
1
$\begingroup$

Can you pls show the results of the following commands to see datatypes and general information about the data in your dataFrame?

dataFrame.head(3)    
dataFrame.describe()    
dataFrame.dtypes    
dataFrame.info()

This bit of code was invaluable for me:

 (df
   ...: .groupby(['Person','ExpNum','Threshold', 'WL'])
   ...: .agg({
   ...:     'RevPos': ['mean', 'count', 'min', 'max'], 
   ...:     'RevNum': ['max', 'min', 'count']
   ...: }))

For you, it might look like this:

 (your_df_name_here
   ...: .groupby(['player_id','match_date'])
   ...: .agg({
   ...:     'runs': ['mean', 'count', 'min', 'max'], 
   ...:     'strike_rate': ['mean', 'count', 'min', 'max']
   ...: }))

Helpful:

I'm working on something similar, with experiments, participants, wavelengths etc. Using Seaborn for plotting.

# import libraries, aka packages (you have to install them with pip the very first time before use)
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from datetime import datetime
from dateutil.parser import parse
import seaborn as sns

#create data frame from values in csv file
df = pd.read_csv('ReversalsImport1a.csv', sep=',', delimiter=None, 
    header='infer', names=['LH', 'RevID', 'OrigID', 'Person', 'Date', 'File', 
    'Threshold', 'StepSize', 'RevNum', 'WL', 'RevPos', 'ExpNum', 'Light', 
    'ThExp'], usecols=['OrigID', 'Person', 'Date', 'Threshold', 'RevNum', 'WL', 
    'RevPos', 'ExpNum', 'Light','ThExp'], engine='python', skiprows=1, 
    infer_datetime_format=True)

# remove rows with longest wavelengths not needed for my analysis
# if you know you want to omit rows with certain values in a column use this
df = df[df.WL != 668]
df = df[df.WL != 680]

Based on your question, I might do something similar with omitting all but the last 5 matches for every player in your dataframe. I don't know if you have something in your data that indicates the number of the match for that person? If you don't already have it, you could create a column that indicated the match number per person and use that to eliminate all but the last 5 per person.

# removed all rows with text in a column that should have been integers, and then changed it datatype to Integer, so that I can do mathematical things

# remove rows with "not applicable" in RevNum column
df = df[df.RevNum != "not applicable"]

# change RevNum to be integer
df['RevNum'] = df['RevNum'].astype('int')

...

Seaborn's facetgrid makes plotting several graphs at the same time nice.

This is what I am using for graphing.
You may want to try something similar with your baseball data.

# seaborn set style
sns.set(style="ticks")
grid = sns.FacetGrid(df_Revs_Exp2, col="WL", hue="ThExp", col_wrap=5, size=4)
grid.map(plt.axhline, y=0, ls=":", c=".5")

# Draw a horizontal line showing min max constraints of staircase
# if the experiment number in df_Revs_Exp2 is 1 then ...
if df_Revs_Exp2.iloc[0,7] == 1:
    # draw a line at -60 and 40 on the y axis
    grid.map(plt.axhline, y=-60, ls=":", c=".5")
    grid.map(plt.axhline, y=40, ls=":", c=".5")

    # Draw a line plot to show reversals of staircase
    grid.map(plt.plot, "RevNum", "RevPos", marker="o", ms=4)
    # Draw tick marks on the x and y axes
    grid.set(xticks=np.arange(13), yticks=[-65, -60, -40, -20, 0, 20, 40, 
    45], xlim=(-.5, 15.5), ylim=(-65, 45))

    # Adjust the arrangement of the plots
    grid.fig.tight_layout(w_pad=.5)
    this_name = "NewTest"
    th_experiment=df_Revs_Exp2.iloc[0,9]
    this_experiment=th_experiment[-4:8]

    # Add Title, Save Fig, Show Fig
    plt.suptitle(this_name + ' ' + this_experiment, fontsize=20, ha='right')
    plt.savefig(this_name + ' ' + this_experiment + '.png')
    plt.show()
$\endgroup$
2
  • 1
    $\begingroup$ This is a cleaner version to get dtypes... df.get_ftype_counts() $\endgroup$
    – Aditya
    Jun 28 '18 at 19:35
  • $\begingroup$ Thanks. It appears that that will be deprecated in the near future. c:\users\rijekah\appdata\local\programs\python\python35\lib\site-packages\ipykernel_launcher.py:1: FutureWarning: get_ftype_counts is deprecated and will be removed in a future version """Entry point for launching an IPython kernel. $\endgroup$ Jul 2 '18 at 19:20
0
$\begingroup$

As the other person or answer suggested, take the output of the SQL query and make a .csv file.

I use the following code as one of the parameters or arguments given to pd.read_csv to specify which columns to use:

usecols=['OrigID', 'Person', 'Date', 'Threshold', 'RevNum', 'WL', 
    'RevPos', 'ExpNum', 'Light','ThExp'],

Read in the file, and use the parameter to indicate with columns to use:

df = pd.read_csv('ReversalsImport1a.csv', sep=',', delimiter=None, 
    names=['LH', 'RevID', 'OrigID', 'Person', 'Date', 'File', 
    'Threshold', 'StepSize', 'RevNum', 'WL', 'RevPos', 'ExpNum', 'Light', 
    'ThExp'], usecols=['OrigID', 'Person', 'Date', 'Threshold', 'RevNum', 'WL', 
    'RevPos', 'ExpNum', 'Light','ThExp'], engine='python', skiprows=1, 
    infer_datetime_format=True)

Yours might look something like this after you have your my_baseball_data.csv file ready:

# Then read in the file, and use the parameter to indicate with columns to use:
# use whatever sub set of data columns you must have and omit the rest.
df = pd.read_csv('my_baseball_data.csv', sep='|', 
    header='infer', names=['Id', 'player_id', 'match_type', versus', 'venue', 
    'runs', 'balls_faced', 'strike_rate', 'dismissal', 'run_aggr', 'avg_aggr', 
    'strike_rate_aggr', 'match_id', 'match_date'], 
    usecols=['player_id', 'runs', 'balls_faced', 'strike_rate', 'dismissal', 
    'run_aggr', 'avg_aggr', 'strike_rate_aggr', 'match_date'], engine='python',
    skiprows=1, infer_datetime_format=True)

Also, change your column names to match the case of the data your feeding in with your .csv file change from this:

playerByDate = dataFrame.groupby(['PLAYER_ID','MATCH_DATE'])

to this:

playerByDate = dataFrame.groupby(['player_id','match_date'])

Use this to start looking at averages, standard deviation, sums, max, min etc. of the subsets:

playerByDate = dataFrame.groupby(['player_id','match_date']).mean()
playerByDate = dataFrame.groupby(['player_id','match_date']).agg([np.mean, np.sum, np.std) 

Note: You can substitute np.max, np.min, etc. to obtain whatever you are looking for.

$\endgroup$
3
  • $\begingroup$ What is np here is it numpy ?? $\endgroup$
    – Neel
    Jun 30 '18 at 5:59
  • $\begingroup$ Yes. import numpy as np $\endgroup$ Jul 2 '18 at 19:15
  • $\begingroup$ Just as np.mean calculates a mean value for you, and np.sum adds up values for you, you can also use np.min to get the minimum value and np.max to get the maximum value for you. $\endgroup$ Jul 2 '18 at 19:17

Not the answer you're looking for? Browse other questions tagged or ask your own question.