horse racing pivot table jockey stats

[Jack_]

Hi everyone,

I am trying to use a pivot table to initially analyze jockey stats and I am having difficulties in getting totals for different items.

What I am trying to produce is:

Name..Rides..Win................Place...............Show
Pardo 4 ........ 1 25% $4.00 2 50% $5.50 3 75% $ 6.60

The data column headers are:
Horse_Name, Finish, jockey, winpay, placepay, showpay

The data is:
Lone Boy 1 Pardo 4.00 3.00 2.00
Mary One 2 Pardo 0.00 2.50 2.10
Good Two 3 Pardo 0.00 0.00 0.00
Blackacre 4 Pardo 0.00 0.00 2.50

Obviously there are other horses, jockeys, races, etc.

I want to use a pivot table because I can filter the data by year, odds rank, odds, and other header labels. The original data set has about 100,000 lines of data and 80 columns.

I can get the total number of rides by counting on the jockey's name. The problem is breaking out the rides to reflect only a 1st, 2nd, and 3rd place finish and having the percentage relate back to the total number of rides. I have tried various filtering mechanisms and calculated fields without sucess.The problem is usually that the order of finishes gets lumped into the same number, so 1st, 2nd, 3rd are all the same numbers.

I am using Win 7 and Excel 2010.

Any suggestions would be greatly appreciated.

I have not posted before and I think I have included the information needed to solve the problem. If I have not, just let me know and I will include it.

Thanks in advance,

Jack

 

[GlennUK]

Normalize your table, so that it looks like this:

Excel Workbook
	A	B	C	D	E
7	Horse_Name	Finish	jockey	Pay category	Amount
8	Lone Boy	1	Pardo	winpay	4.00
9	Lone Boy	1	Pardo	placepay	3.00
10	Lone Boy	1	Pardo	showpay	2.00
11	Mary One	2	Pardo	placepay	2.50
12	Mary One	2	Pardo	showpay	2.10
13	Blackacre	4	Pardo	showpay	2.50
Sheet1

... by using this method:
http://www.tek-tips.com/faqs.cfm?fid=5287
( to get to the PivotTable Wizard in Excel 2007 it's Alt-D-P ... I hope it's the same in Excel 2010 )
Tip: concatenate the horse/finish/jockey into a single field before doing the normalization, so that you have a single row identifier. Split it afterwards whent the process is complete ( e.g. separate your field with "/", and use Data/Text To Columns to split your field afterwards ).

This is my pivottable of the normalized data:

Excel Workbook
	F	G	H	I	J	K	L	M	N	O
17		Pay category	Data
18		winpay			placepay			showpay
19	jockey	Count of Amount	Sum of Amount	Sum of Amount2	Count of Amount	Sum of Amount	Sum of Amount2	Count of Amount	Sum of Amount	Sum of Amount2
20	Pardo	1	24.84%	4.00	2	34.16%	5.50	3	40.99%	6.60
Sheet1

For this I added the Amount to the data area 3 times ... for one of them I changed the Field setting so as to summarize by Count, and another one I chose to Show Values as % of row.

 

[Jack_]

Thanks Glen,

A very good approach to solve the problem that I put forth and one which shows an impressive level of technical competence. I am going to have to study it further. I have already begun to do some conversion on the example data I gave, but I have to study the references you included in more detail.

I must admit that normalizing the data never occurred to me within this context. The data arrived from Equibase Results Charts as XML flat files with individual records for each horse in the race at each call. It is some bulky and very redundant. I had initially thought of putting the data into MS Access to cut down on the size of the database and make it more efficient, but then decided against it because while Excel comes as a part of most MS Office packages, Access is found either as a stand alone or part of much higher end Office bundles.

When I have a better grasp of the situation I will write back – probably with some more questions. In the meantime I can see one problem with both approaches and, from the bolded words at the end of your post, I suspect you have seen it also.

What I don’t know how to do within the context of a pivot table is to have the individual counts expressed as a percentage of another field total as opposed to the end row total. Specifically, I need to have columns with win_finishes/starts, place_finishes/starts, etc. The way the results come out now are win_finishes/(win_finishes+place_finishes+show_finishes). I think that is what you pointed out with you bolded statement, % of row.

I can produce a column with total starts for each jockey using the unnormalized data and have tried everything including calculated fields, but I can’t find a way of doing the percentages of other than the end row. Any suggestions?

Thanks again and take care,

Jack

 

[Jack_]

HI Glen,

Actually have another question I forgot about.

Is there a way to do a count on a field that depends upon the value of the field?

For example, a column contains the following values
0
0
2
4
0
5
-6
-2

Can I do a count only when the values are greater than zero?

Using the above data the count I would want is 3

Thanks

Jack

 

[GlennUK]

I can produce a column with total starts for each jockey using the unnormalized data and have tried everything including calculated fields, but I can’t find a way of doing the percentages of other than the end row. Any suggestions?

Ah yes, I see what you mean about the percentages now. If you have a dummy column of zeros, headed Starts, in your unnormalized data, before you convert it to be normalized, then once you have the normalized version you can use that field in your Pivot settings. But you will end up with a dummy entry in the pivottable, like this:

Excel Workbook
	G	H	I	J	K	L	M	N	O	P	Q	R	S
23	*	Category	Data	*	*	*	*	*	*	*	*	*	*
24	*	winpay	*	*	placepay	*	*	showpay	*	*	starts	*	*
25	jockey	Counts	Pcents	Sum of Amount	Counts	Pcents	Sum of Amount	Counts	Pcents	Sum of Amount	Counts	Pcents	Sum of Amount
26	Pardo	1	25.00%	4.00	2	50.00%	5.50	3	75.00%	6.60	4	100.00%	0.00
Sheet6

The Pcent field is actually a Count Of Amount, with a setting of Show as % of Base Field:= Category, Item:=Starts

As for your other query about counts of items greater than zero ... I recommend another new column in the normalized data.

I hope that helps.

 

[Jack_]

Hi Glen,

I have been working hard and I think I have most of the problems solved.

I do have a few remaining problems to solve but I won't be able to get to those until Sunday.

Thanks again.

Jack

 

[GlennUK]

That sounds great. Good luck with it all