0
$\begingroup$

I have reduced the data set to only the columns I need:

| yearID | POS | PO | A | E |
|--------|:---:|:--:|:-:|:-:|
|  1871  | SS  | 0.0|3.0|1.0|
|  1871  | 2B  |30.0|1.0|0.0|
|  ...   |  .. | ...|...|...|

source: Sean Lahmans 2015 Baseball Data set Using the Fielding.csv file.

I am trying to calculate the Fielding Percentage:

values = df['E'] / (df['PO'] + df['A'] + df['E'])

Where there are multiple records for each 'yearID'. I am not sure if I need to transpose, apply a function or map one. Additionaly, in what order should I be moving the pieces around in.

data.loc[:,('C')] = middle_infielders.PO + middle_infielders.A + data.E
data.loc[:,('FP')] = 1 - (data.E / data.C)

| yearID | POS | PO | A | E |  C  |  FP  |
|--------|:---:|:--:|:-:|:-:|     |      |
|  1871  | SS  | 0.0|3.0|1.0| 123 | .960 |
|  1871  | 2B  |30.0|1.0|0.0|  12 | .452 |
|  ...   |  .. | ...|...|...| ... |  ... |

I would like it in this form to plot a line graph:

| yearID |  SS  |  2B  |
|--------|:----:|:----:|
|  1871  |0.3745|0.1245|
|  1872  |0.8940|0.3366|
|  ...   |  ... | ...  |

End result: One mean for each 'POS' (SS and 2B) each year.

UPDATE

Expecting pivoting to aggregate the values='FP' from the statement:
data.pivot(index='yearID', columns='POS', values='FP')
But, I get the error:

"ValueError: Index contains duplicate entries, cannot reshape." 

Should I apply a Lambda to calculate the Fielding Percentage ('FP') I want in the values instead of pre-calculating it?

$\endgroup$
1
$\begingroup$

The magic word is "pivoting":

records = [(1871,"SS",0.0,3.0,1.0), (1871,"2B",30.0,1.0,0.0)]
pandas.DataFrame.from_records(records, columns=("yearID", "POS", "PO", "A", "E")
     ).assign(result = df.apply(lambda x: x['E']/(x['PO']+x['A']+x['E']), 1)
     ).pivot(index='yearID', columns='POS', values='result')

Result:

| POS    | 2B | SS   |
|--------|----|------|
| yearID |    |      |
| 1871   | 0  | 0.25 |

I went to the trouble of looking at your file now that you linked to it, and the problem is that you have removed to much information, so your index is not unique. Indices always have to be unique, so either you add that information back, or you preprocess the data before pivoting such that the duplication is resolved. I chose to retain the extra columns; I don't know if this is what you want, but it should help you understand what it takes to make pivoting work:

from pandas import read_csv

field_cols = ("playerID", "yearID", "teamID", "POS", "PO", "A", "E")
df = read_csv('Fielding.csv', usecols=field_cols).dropna(
             ).query('not PO == A == E == 0')

df.assign(ID = df[['playerID', 'yearID', 'teamID']].apply(tuple, 1), 
          FP = df.apply(lambda x: 1-x['E']/(x['PO']+x['A']+x['E']), 1)
          ).drop(set(df.columns) - {'POS'}, 1).drop_duplicates('ID'
          ).pivot(index='ID', values='FP', columns='POS')

Result:

| POS                    | 1B  | 2B  | 3B  | C   | CF  | LF  | OF  | P        | RF  | SS  |
|------------------------|-----|-----|-----|-----|-----|-----|-----|----------|-----|-----|
| ID                     |     |     |     |     |     |     |     |          |     |     |
| (aardsda01, 2006, CHN) | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 1.000000 | NaN | NaN |
| (aardsda01, 2007, CHA) | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.857143 | NaN | NaN |
| (aardsda01, 2008, BOS) | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 1.000000 | NaN | NaN |
| (aardsda01, 2009, SEA) | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 1.000000 | NaN | NaN |
| (aardsda01, 2010, SEA) | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.833333 | NaN | NaN |
  ...
$\endgroup$
  • $\begingroup$ I do this, expecting it to aggregate the values='FP' from the statement: data.pivot(index='yearID', columns='POS', values='FP') But, I get the error: "ValueError: Index contains duplicate entries, cannot reshape." Should I apply a Lambda to calculate the Fielding Percentage ('FP') I want in the values instead of pre-calculating it? $\endgroup$ – ratchet Sep 28 '16 at 23:57
  • $\begingroup$ You need one year for each 2B and SS, otherwise it does not make sense. $\endgroup$ – Emre Sep 29 '16 at 3:37
  • $\begingroup$ Yes, that is the idea. Is my syntax wrong for pivoting with this intention, or must i sum each before so. $\endgroup$ – ratchet Sep 30 '16 at 16:06
  • $\begingroup$ No, reread the error: Index contains duplicate entries. That means you need to ensure the same value is not repeated in the index. Use a more granular index and/or drop duplicates. $\endgroup$ – Emre Oct 1 '16 at 3:56
0
$\begingroup$

Is this what you want?

df = pd.read_csv('Fielding.csv')
df['FP'] = df['E'] / (df['PO'] + df['A'] + df['E'])
df.groupby(['yearID', 'POS']).sum().reset_index().pivot_table(index='yearID', columns='POS', values='FP')

Result:

POS      1B    2B    3B     C   CF  DH   LF    OF     P   RF    SS
yearID
1871   1.84  5.98 10.05  6.26  nan nan  nan 12.50  2.10  nan  8.24
1872   2.67  9.71  9.46  6.29  nan nan  nan 23.92  5.84  nan  9.48
1873   3.02  9.03  7.72  5.36  nan nan  nan 15.26  6.74  nan  8.48
1874   3.74 10.29  8.32 10.04  nan nan  nan 15.72  3.20  nan 12.85
1875   5.81 12.65 15.62 13.35  nan nan  nan 30.67  9.10  nan 14.42
1876   2.08  7.31  3.98  8.34  nan nan  nan 14.48  5.14  nan  3.55
......
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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