Archived

This topic is now archived and is closed to further replies.

JDawg

Football Database General Discussion

181 posts in this topic

@J-dawg and I were typing at the same time it seems. Oh and as far as the star rankings go if the total is:

0-25: 2 Star

>25-50: 3 star

>50 to 75: 4 star

>75 to 100: 5 star

0

Share this post


Link to post
Share on other sites

Actually.... I can't tackle the Grade formula by myself..... HAVE to have nova for it. It's lengthy.... And he's smarter than me.

0

Share this post


Link to post
Share on other sites

And he's smarter than me.

I wouldn't say that. I just have a lot experience conducting data analysis lol

0

Share this post


Link to post
Share on other sites

Let me see. I can't see formulas on my phone so I'm having to reconstruct this from memory...

(Average star rating / Max Stars) * Recruiting service weight + (Average AQ offers / Max AQ offers) * AQ offer weight + (Average NAQ Offers / Max NAQ Offers) * NAQ Weight + (Offer quality factor * Offer quality factor weight)

where

Max Stars = 5

Recruiting Service Weight = 30

Max AQ offers = 17

AQ Offer Weight = 35

Max NAQ Offers = 12

NAQ Offer Weight = 5

Offer quality factor weight = 30

and

Offer quality factor = (Average AQ offers) / (Average AQ offers + Average NAQ offers) if (Average AQ offers + Average NAQ offers) => 2, else 0.1

I think that's pretty close anyway...

And with these "weights".... that essentially means that of the total formula,

-Recruiting service star average is worth 30% of the total.

-Average # of AQ offers is worth 35% of the total.

-Average # of Non-AQ offers is worth 5% of the total.

-OQF is worth 30 %

1

Share this post


Link to post
Share on other sites

I wouldn't say that. I just have a lot experience conducting data analysis lol

I'm getting there.... we have an ongoing project right now where I'm doing water quality data analysis for 450+ sites on water bodies in Mississippi. Talk about headache.

1

Share this post


Link to post
Share on other sites

I'm getting there.... we have an ongoing project right now where I'm doing water quality data analysis for 450+ sites on water bodies in Mississippi. Talk about headache.

See I spent 3 years crunching non-dimensional helicopter performance data for input into performance models, then crunching output from the models to make sure it was accurate enough to safely plan missions with. Lot's of curve fitting and statistical analysis going on. Sometimes I was processing hundreds of thousands of data points, made me feel like my eyeballs were gonna bleed at times...

0

Share this post


Link to post
Share on other sites

@@Benny must be busy coding cuz the database in not accessible at the moment...

0

Share this post


Link to post
Share on other sites

@@Benny must be busy coding cuz the database in not accessible at the moment...

Haven't touched it... it's working for me.. what's it say?

0

Share this post


Link to post
Share on other sites

Haven't touched it... it's working for me.. what's it say?

[#10CCS2] We could not find the page you were looking for.

When I try to use the link in the first thread you started...

0

Share this post


Link to post
Share on other sites

[#10CCS2] We could not find the page you were looking for.

When I try to use the link in the first thread you started...

Use the link from the group.... I pinned it as it's own topic.

1

Share this post


Link to post
Share on other sites

// Constants that can be easily changed later
$maxstars = 5;
$starweight = 30;
$maxaq = 17;
$aqweight = 35;
$maxnaq = 12;
$naqweight = 30;
$oqfweight = 30;
// Star ratings + average
$srating =
$rrating =
$247rating =
$erating =
$avgrating = ($srating + $rrating + $247rating + $erating) / 4;
// AQ offers + average
$saq =
$raq =
$247aq =
$eaq =
$avgaq = ($saq + $raq + $247aq + $eaq) / 4;
// Non-AQ offers + average
$snaq =
$rnaq =
$247naq =
$enaq =
$avgnaq = ($snaq + $rnaq + $247naq + $enaq) / 4;
// Offer quality factor
if (($avgaq + $avgnaq) > 2)
 {
$oqf = $avgaq / ($avgaq + $avgnaq);
 }
else
 {
$oqf = "0.1";
 }
// Calculate the numerical rating
$numrating = (($avgrating / $maxstars) * $starweight) + (($avgaq / $maxaq) * $aqweight) + (($avgnaq / $maxnaq) * $naqweight) + ($oqf * $oqfweight);
// Round it off
$numrating = round($numrating);
// Convert to stars
if ($numrating >= 75)
 {
$starrating = 5;
 }
elseif ($numrating >= 50)
 {
$starrating = 4;
 }
elseif ($numrating >= 25)
 {
$starrating = 3;
 }
else
 {
$starrating = 2;
 }

Does that look right?

0

Share this post


Link to post
Share on other sites

@@Benny

"if (($avgaq + $avgnaq) > 2) "

Should be greater than or equal to I think...

And I think all of these should be just greater thans...

"

if ($numrating >= 75)

{

$starrating = 5;

}

elseif ($numrating >= 50)

{

$starrating = 4;

}

elseif ($numrating >= 25)

{

$starrating = 3;

}

else

{

$starrating = 2;

}"

0

Share this post


Link to post
Share on other sites

BRB Army IT has decided 10:20 AM is a great time to update my computer and force a reboot...

1

Share this post


Link to post
Share on other sites

@@Benny

"if (($avgaq + $avgnaq) > 2) "

Should be greater than or equal to I think...

And I think all of these should be just greater thans...

"

if ($numrating >= 75)

{

$starrating = 5;

}

elseif ($numrating >= 50)

{

$starrating = 4;

}

elseif ($numrating >= 25)

{

$starrating = 3;

}

else

{

$starrating = 2;

}"

Gotcha, so a 75 is a 4 star?

0

Share this post


Link to post
Share on other sites

Gotcha, so a 75 is a 4 star?

Yep you got it

0

Share this post


Link to post
Share on other sites

Updated

// Constants that can be easily changed later
$maxstars = 5;
$starweight = 30;
$maxaq = 17;
$aqweight = 35;
$maxnaq = 12;
$naqweight = 30;
$oqfweight = 30;
// Star ratings + average
$srating =
$rrating =
$247rating =
$erating =
$avgrating = ($srating + $rrating + $247rating + $erating) / 4;
// AQ offers + average
$saq =
$raq =
$247aq =
$eaq =
$avgaq = ($saq + $raq + $247aq + $eaq) / 4;
// Non-AQ offers + average
$snaq =
$rnaq =
$247naq =
$enaq =
$avgnaq = ($snaq + $rnaq + $247naq + $enaq) / 4;
// Offer quality factor
if (($avgaq + $avgnaq) >= 2)
 {
$oqf = $avgaq / ($avgaq + $avgnaq);
 }
else
 {
$oqf = "0.1";
 }
// Calculate the numerical rating
$numrating = (($avgrating / $maxstars) * $starweight) + (($avgaq / $maxaq) * $aqweight) + (($avgnaq / $maxnaq) * $naqweight) + ($oqf * $oqfweight);
// Round it off
$numrating = round($numrating);
// Convert to stars
if ($numrating > 75)
 {
$starrating = 5;
 }
elseif ($numrating > 50)
 {
$starrating = 4;
 }
elseif ($numrating > 25)
 {
$starrating = 3;
 }
else
 {
$starrating = 2;
 }

Sorry I'm just putting it here cause I'm at work with nowhere to save it.

2

Share this post


Link to post
Share on other sites

It is finished.

http://www.sectalk.com/recruiting/football/updateratings

Just go to that page anytime you want to update rankings. It's going to update ALL of them and show you the numerical result/star rating it entered. Someone plug in some of the offer data and see if it is accurate. I'll post the code in the next post in case someone can find some errors.

0

Share this post


Link to post
Share on other sites
// Constants that can be easily changed later
$maxstars = 5;
$starweight = 30;
$maxaq = 17;
$aqweight = 35;
$maxnaq = 12;
$naqweight = 30;
$oqfweight = 30;
// Clear up some stuff
$records = array();
$commit = array();
$i = 0;
// Get the records from DB
$this->DB->build( array( 'select'  => 'primary_id_field, field_15, field_17, field_18, field_20, field_29, field_30, field_31, field_32, field_33, field_34, field_35, field_36, field_37 ',
  'from'   => 'ccs_custom_database_4',
  'limit'   => array( '0', '500' ),
  'order'   => 'field_15 ASC') );
$this->DB->execute();
while($row = $this->DB->fetch() )
{
$records[] = $row;
}
echo "<table>\n";
echo "<tr><td><strong>#</strong></td><td><strong>Commit</strong></td><td><strong>Num Rating</strong></td><td><strong>Star Rating</strong></td></tr>\n";
foreach($records as $commit)
{
// Start the count
$i++;
$id = $commit['primary_id_field'];
// Star ratings + average
$srating = $commit['field_17'];
$rrating = $commit['field_18'];
$trating = $commit['field_20'];
$erating = $commit['field_29'];
$avgrating = ($srating + $rrating + $trating + $erating) / 4;
// AQ offers + average
$saq = $commit['field_30'];
$raq = $commit['field_32'];
$taq = $commit['field_34'];
$eaq = $commit['field_36'];
$avgaq = ($saq + $raq + $taq + $eaq) / 4;
// Non-AQ offers + average
$snaq = $commit['field_31'];
$rnaq = $commit['field_33'];
$tnaq = $commit['field_35'];
$enaq = $commit['field_37'];
$avgnaq = ($snaq + $rnaq + $tnaq + $enaq) / 4;
// Offer quality factor
if (($avgaq + $avgnaq) >= 2)
 {
$oqf = $avgaq / ($avgaq + $avgnaq);
 }
else
 {
$oqf = "0.1";
 }
// Calculate the numerical rating
$numrating = (($avgrating / $maxstars) * $starweight) + (($avgaq / $maxaq) * $aqweight) + (($avgnaq / $maxnaq) * $naqweight) + ($oqf * $oqfweight);
// Round it off
$numrating = round($numrating);
// Convert to stars
if ($numrating > 75)
 {
$starrating = 5;
 }
elseif ($numrating > 50)
 {
$starrating = 4;
 }
elseif ($numrating > 25)
 {
$starrating = 3;
 }
else
 {
$starrating = 2;
 }
// Set the rating
$this->DB->update( 'ccs_custom_database_4', array( 'field_27' => $starrating  ), 'primary_id_field='.$id );
echo "<tr>\n";
echo "<td>\n";
echo $i;
echo "</td>\n";
echo "<td>\n";
echo $commit['field_15'];
echo "</td>\n";
echo "<td>\n";
echo $numrating;
echo "</td>\n";
echo "<td>\n";
echo $starrating;
echo "</td>\n";
echo "</tr>\n";
}
echo "</table>\n";
echo $i;
echo " records updated";

0

Share this post


Link to post
Share on other sites

Something is wrong somewhere.

I entered the data from J-Dawg's MSU spreadsheet and compared the numerical ratings with what my code is doing.

BJ Hammond should have gotten a 46, he got a 53. Cord Sandberg should have gotten a 74, he got a 79.

But Brandon Wells got a 22 which is right. So something is wrong somewhere.

Either our equations differ, or I just made an error.

0

Share this post


Link to post
Share on other sites

Something is wrong somewhere.

One of your weights is wrong. Currently:

"$naqweight = 30; " and it should be

"$naqweight = 5;"

A quick sanity check is that if you sum all the weights, they should equal 100...

2

Share this post


Link to post
Share on other sites

One of your weights is wrong. Currently:

"$naqweight = 30; " and it should be

"$naqweight = 5;"

A quick sanity check is that if you sum all the weights, they should equal 100...

Awesome. They all came out right that time.

1

Share this post


Link to post
Share on other sites

One of your weights is wrong. Currently:

"$naqweight = 30; " and it should be

"$naqweight = 5;"

A quick sanity check is that if you sum all the weights, they should equal 100...

Maybe not. Devanta Scott should be 17, he got 20. Kent Flowers should be 25, he got 52 (????).. I'm sure theirs more errors.

0

Share this post


Link to post
Share on other sites

Maybe not. Devanta Scott should be 17, he got 20. Kent Flowers should be 25, he got 52 (????).. I'm sure theirs more errors.

Data inputs are different between the 2. The spreadsheet is outdated compared to whats on SECT for him. The spreadsheet has him as two 1 stars and two 3 stars. SECT has him as one 1 star and three 3 stars...

1

Share this post


Link to post
Share on other sites

Maybe not. Devanta Scott should be 17, he got 20. Kent Flowers should be 25, he got 52 (????).. I'm sure theirs more errors.

Scratch the above, the inputs are wrong on SECT. ESPN should be one star and it's 3. I'll correct that shortly...

ETA

Now Scott is at 20. Checking flowers now. Long story short though I think we're down to differences in data input, not problems in the formula and code...

2

Share this post


Link to post
Share on other sites