Parsing baseball box score

[327]

I am working on a project with baseball data from retrosheet.org. I want to create variables for the score of each team in each inning (VisInning1, HomeInning1, VisInning2, HomeInning2, etc). The problem is that the variable for the box score is coded strangely. Each team has its own variable for the whole game and each inning gets one value. Because leading zeros are cut off, a value of "12(10)1X" would mean that a team did not score in the first 4 innings, scored once in the fifth, twice in the sixth, ten times in the seventh, once in the eighth, and they did not have to play the ninth because they had won by that point. There is a variable for the total number of outs, so I can calculate whether or not there are extra innings.This is part of a project that I am doing as a gift for my father's 64th birthday in August. He's a huge baseball fan but likes leaving games early to beat the traffic. I'm writing an analysis of the likelihood of a leader change under different score conditions.

 

[Oeldere]

You also posted your question here.

 

[martindwilson]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	data	helper 1	helper 2	helper 3	innings 1	innings 2	innings 3	innings 4	innings 5	innings 6	innings 7	innings 8	innings 9	*	lookup	list
2	12(10)1X	12a1X	12a1X	000012a1X	0	0	0	0	1	2	10	1	x	*	0	0
3	12(11)13(12)	12b13c	12b13c	00012b13c	0	0	0	1	2	11	1	3	12	*	1	1
4	123456789	123456789	123456789	123456789	1	2	3	4	5	6	7	8	9	*	2	2
5	(18)12(16)x	(18)12(16)x	i12gx	0000i12gx	0	0	0	0	18	1	2	16	x	*	3	3
6	(10)(10)0(11)1(15)67x	aa0b1(15)67x	aa0b1f67x	aa0b1f67x	10	10	0	11	1	15	6	7	x	*	4	4
7	1xxx	1xxx	1xxx	000001xxx	0	0	0	0	0	1	x	x	x	*	5	5
8	*	*	*	*	*	*	*	*	*	*	*	*	*	*	6	6
9	*	*	*	*	*	*	*	*	*	*	*	*	*	*	7	7
10	*	*	*	*	*	*	*	*	*	*	*	*	*	*	8	8
11	*	*	*	*	*	*	*	*	*	*	*	*	*	*	9	9
12	*	*	*	*	I assumed since the modern record is17 in an innings 19 would be high enough	*	*	*	*	*	*	*	*	*	a	10
13	*	*	*	*	*	*	*	*	*	*	*	*	*	*	b	11
14	*	*	*	*	*	*	*	*	*	*	*	*	*	*	c	12
15	*	*	*	*	*	*	*	*	*	*	*	*	*	*	d	13
16	*	*	*	*	*	*	*	*	*	*	*	*	*	*	e	14
17	*	*	*	*	*	*	*	*	*	*	*	*	*	*	f	15
18	*	*	*	*	*	*	*	*	*	*	*	*	*	*	g	16
19	*	*	*	*	*	*	*	*	*	*	*	*	*	*	h	17
20	*	*	*	*	*	*	*	*	*	*	*	*	*	*	i	18
21	*	*	*	*	*	*	*	*	*	*	*	*	*	*	j	19
22	*	*	*	*	*	*	*	*	*	*	*	*	*	*	x	x
Sheet1

Excel Workbook
	D
2	000012a1X
Sheet1

Excel Workbook
	E	F	G	H	I	J	K	L	M
2	0	0	0	0	1	2	10	1	x
Sheet1

x post at http://www.excelforum.com/excel-pro...baseball-box-score.html?p=2835647#post2835647