<commit_before>playbyplay = LOAD '/user/hive/warehouse/playbyplay' USING PigStorage('\u0001') as (
	game:chararray,
	quarter:int,
	gameminutes:int,
	gameseconds:int,
	offense:chararray,
	defense:chararray,
	down:int,
	yardstogo:int,
	yardline:int,
	playdesc:chararray,
	offensescore:int,
	defensescore:int,
	year:int,
	qb:chararray,
	offensiveplayer:chararray,
	defensiveplayer1:chararray,
	defensiveplayer2:chararray,
	penalty:boolean,
	fumble:boolean,
	incomplete:boolean,
	isgoalgood:boolean,
	playtype:chararray,
	hometeam:chararray,
	awayteam:chararray,
	dateplayed:chararray,
	playid:chararray,
	winner:chararray,
	hometeamscore:int,
	awayteamscore:int,
	playerarrested:boolean,
	offenseplayerarrested:boolean,
	defenseplayerarrested:boolean,
	hometeamplayerarrested:boolean,
	awayteamplayerarrested:boolean,
	stadium:chararray,
	capacity:int,
	expandedcapacity:int,
	stadiumlocation:chararray,
	playingsurface:chararray,
	isartificial:boolean,
	team:chararray,
	opened:int,
	weatherstation:chararray,
	rooftype:chararray,
	elevation:int,
	station:chararray,
	station_name:chararray,
	readingdate:chararray,
	mdpr:int,
	mdsf:int,
	dapr:int,
	prcp:int,
	snwd:int,
	snow:int,
	psun:int,
	tsun:int,
	tmax:int,
	tmin:int,
	tobs:int,
	wesd:int,
	wesf:int,
	awnd:int,
	wdf2:int,
	wdf5:int,
	wdfg:int,
	wsf2:int,
	wsf5:int,
	wsfg:int,
	pgtm:int,
	fmtm:int,
	wv07:int,
	wv01:int,
	wv20:int,
	wv03:int,
	wt09:int,
	wt14:int,
	wt07:int,
	wt01:int,
	wt15:int,
	wt17:int,
	wt06:int,
	wt21:int,
	wt05:int,
	wt02:int,
	wt11:int,
	wt22:int,
	wt04:int,
	wt13:int,
	wt16:int,
	wt08:int,
	wt18:int,
	wt03:int,
	wt10:int,
	wt19:int,
	hasweatherinvicinity:boolean,
	hasweathertype:boolean,
	hasweather:boolean,
	drive:int,
	play:int,
	driveresult:chararray,
	maxplays:int
);

playfiltered = FILTER playbyplay BY play == 1 and driveresult != 'KICKOFF' and prcp > 0;

playgroup = GROUP playfiltered ALL;

playaverages = FOREACH playgroup GENERATE AVG(playfiltered.maxplays), MAX(playfiltered.maxplays);

sh echo "Average number of plays in a drive and the maximum number of plays in a drive with precipitation";
DUMP playaverages;

sh echo "Yardline where drives start";
#select playbyplay.yardline, playbyplay.totalperplay, totalstable.total, ((playbyplay.totalperplay / totalstable.total) * 100) as percentage from  
#(select yardline, count(*) as totalperplay from playbyplay where play = 1 and driveresult <> "KICKOFF" GROUP BY yardline) playbyplay 
#full outer join
#  (select count(*) as total from playbyplay where play = 1 and driveresult <> "KICKOFF") totalstable 
#order by yardline;

firstplay = FILTER playbyplay BY play == 1 and driveresult != 'KICKOFF';

playbyyardline = GROUP firstplay BY yardline;

yardcounts = FOREACH playbyyardline {
	sorted = ORDER firstplay by group;
	GENERATE group, COUNT(firstplay) as totalperyard;
};

firstplaygroup = GROUP firstplay ALL;

firstplaycounts = FOREACH firstplaygroup GENERATE COUNT(firstplay) as totalplays;

yardscrossed = CROSS firstplaycounts, yardcounts;
# yardcounts::group, 
yardscalculated = FOREACH yardscrossed GENERATE yardcounts::totalperyard, firstplaycounts::totalplays, 
	(yardcounts::totalperyard / firstplaycounts::totalplays) * 100;
<commit_msg>Worked around Pig bug by moving a sort.<commit_after>playbyplay = LOAD '/user/hive/warehouse/playbyplay' USING PigStorage('\u0001') as (
	game:chararray,
	quarter:int,
	gameminutes:int,
	gameseconds:int,
	offense:chararray,
	defense:chararray,
	down:int,
	yardstogo:int,
	yardline:int,
	playdesc:chararray,
	offensescore:int,
	defensescore:int,
	year:int,
	qb:chararray,
	offensiveplayer:chararray,
	defensiveplayer1:chararray,
	defensiveplayer2:chararray,
	penalty:boolean,
	fumble:boolean,
	incomplete:boolean,
	isgoalgood:boolean,
	playtype:chararray,
	hometeam:chararray,
	awayteam:chararray,
	dateplayed:chararray,
	playid:chararray,
	winner:chararray,
	hometeamscore:int,
	awayteamscore:int,
	playerarrested:boolean,
	offenseplayerarrested:boolean,
	defenseplayerarrested:boolean,
	hometeamplayerarrested:boolean,
	awayteamplayerarrested:boolean,
	stadium:chararray,
	capacity:int,
	expandedcapacity:int,
	stadiumlocation:chararray,
	playingsurface:chararray,
	isartificial:boolean,
	team:chararray,
	opened:int,
	weatherstation:chararray,
	rooftype:chararray,
	elevation:int,
	station:chararray,
	station_name:chararray,
	readingdate:chararray,
	mdpr:int,
	mdsf:int,
	dapr:int,
	prcp:int,
	snwd:int,
	snow:int,
	psun:int,
	tsun:int,
	tmax:int,
	tmin:int,
	tobs:int,
	wesd:int,
	wesf:int,
	awnd:int,
	wdf2:int,
	wdf5:int,
	wdfg:int,
	wsf2:int,
	wsf5:int,
	wsfg:int,
	pgtm:int,
	fmtm:int,
	wv07:int,
	wv01:int,
	wv20:int,
	wv03:int,
	wt09:int,
	wt14:int,
	wt07:int,
	wt01:int,
	wt15:int,
	wt17:int,
	wt06:int,
	wt21:int,
	wt05:int,
	wt02:int,
	wt11:int,
	wt22:int,
	wt04:int,
	wt13:int,
	wt16:int,
	wt08:int,
	wt18:int,
	wt03:int,
	wt10:int,
	wt19:int,
	hasweatherinvicinity:boolean,
	hasweathertype:boolean,
	hasweather:boolean,
	drive:int,
	play:int,
	driveresult:chararray,
	maxplays:int
);

playfiltered = FILTER playbyplay BY play == 1 and driveresult != 'KICKOFF' and prcp > 0;

playgroup = GROUP playfiltered ALL;

playaverages = FOREACH playgroup GENERATE AVG(playfiltered.maxplays), MAX(playfiltered.maxplays);

sh echo "Average number of plays in a drive and the maximum number of plays in a drive with precipitation";
DUMP playaverages;

-- Next query

firstplay = FILTER playbyplay BY play == 1 and driveresult != 'KICKOFF';

playbyyardline = GROUP firstplay BY yardline;

yardcounts = FOREACH playbyyardline GENERATE group, COUNT(firstplay) as totalperyard;

firstplaygroup = GROUP firstplay ALL;

firstplaycounts = FOREACH firstplaygroup GENERATE COUNT(firstplay) as totalplays;

yardscrossed = CROSS firstplaycounts, yardcounts;

yardscalculated = FOREACH yardscrossed GENERATE yardcounts::group, yardcounts::totalperyard, firstplaycounts::totalplays, 
	(yardcounts::totalperyard / firstplaycounts::totalplays) * 100;

yardscalculatedsorted = ORDER yardscalculated by group;

sh echo "Yardline where drives start";
DUMP yardscalculatedsorted;

{'fraction_non_alphanumeric': 0.10400527443547058, 'fraction_numerical': 0.021921872424592054, 'mean_word_length': 6.651954602774275, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 7, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4372591', 'n_tokens_mistral': 2576, 'n_tokens_neox': 2393, 'n_words': 475}