Moving Average in PHP

orientep · 02-17-2012, 12:00 AM

Moving Average in php

Nominal Animal · 02-17-2012, 02:48 AM

Start by reading the data into an array, indexed by e.g. year*12 + month to get them in the correct, consecutive order; i.e.

PHP Code:



$monthly = array( 24109=>260, 24110=>220, 24111=>270, 
                  24112=>300, 24113=>269, 24114=>271, ... );

If you use YYYYMM as the key, you'll find new year very difficult; you'll want the keys to be consecutive.

A weighted average can then be calculated using e.g.

PHP Code:



function weighted_average($array, $key, $weights = array(1)) { 
    $sum = 0; 
    $div = 0; 
    $key = intval($key); 
 
    foreach ($weights as $w) { 
        if (array_key_exists($key, $array)) { 
            $sum += $w * $array[$key]; 
            $div += $w; 
        } 
        $key++; 
    } 
 
    if ($div == 0) 
        return FALSE; 
 
    return $sum / $div; 
}

where the data is in the first parameter, the second parameter is the key for the first month of the window, and the third parameter contains the desired weights for the desired window, by default just one month. For a sliding quarter year, you could use array(1,1,1). For a half year, array(1,1,1,1,1,1). For a Gaussian distribution centered on the fourth month with one month variance use array(0.006,0.061,0.242,0.383,0.242,0.061,0.006) .

The averaging function above will correctly ignore months with missing data. The weights are relative, they do not need to sum up to one. If there is no data at all, the function will return FALSE.

You should be able to tweak the above to suit your purposes. For example, it might be easier to define separate functions for the sliding average you'll be using, and define the weight array in the function (or as a global variable) instead.

orientep · 02-17-2012, 08:33 PM

moving average

Nominal Animal · 02-18-2012, 12:00 PM

$key is an integer, year*12+month, where year is the year including century, and month is between 1 and 12, inclusive.

Here is a full example. Just in case this is homework -- I don't write your homework for you -- I'm using the command-line PHP interpreter for this:

PHP Code:



#!/usr/bin/php 
<?PHP 
 
function YearMonth($yyyymm) 
{ 
    $str = strval($yyyymm); 
    if (strlen($str) == 6 && strspn($str, "0123456789") == 6) 
        return intval(substr($str, 0, 4), 10) * 12 
             + intval(substr($str, 4, 2), 10); 
 
    $str = strtolower($str); 
    $str = preg_replace('/[^0-9a-z]+/', ' ', $str); 
 
    $year = 0; 
    $month = 0; 
    foreach(explode(' ', $str . ' ') as $item) { 
            $item = intval($item, 10); 
            if ($item >= 1000 && $item <= 9999) 
                $year = $item; 
            else 
            if ($item >= 1 && $item <= 12) 
                $month = $item; 
        } 
 
    if ($year && $month) 
        return 12 * $year + $month; 
 
    if (!$year) 
        return FALSE; 
 
    $str = preg_replace('/[0-9]+/', '', $str); 
 
    foreach (explode(' ', $str . ' ') as $item) 
        switch (substr($item, 0, 3)) { 
            case 'jan': return  1 + 12 * $year; 
            case 'feb': return  2 + 12 * $year; 
            case 'feb': return  2 + 12 * $year; 
            case 'mar': return  3 + 12 * $year; 
            case 'apr': return  4 + 12 * $year; 
            case 'may': return  5 + 12 * $year; 
            case 'jun': return  6 + 12 * $year; 
            case 'jul': return  7 + 12 * $year; 
            case 'aug': return  8 + 12 * $year; 
            case 'sep': return  9 + 12 * $year; 
            case 'oct': return 10 + 12 * $year; 
            case 'nov': return 11 + 12 * $year; 
            case 'dec': return 12 + 12 * $year; 
        } 
 
    return FALSE; 
} 
 
function Quarter($data, $key) 
{ 
    $sum = 0; 
    $div = 0; 
    $key = YearMonth($key); 
 
    if (!$key) 
        return FALSE; 
 
    if (!is_array($data)) 
        return FALSE; 
 
    for ($offset = 0; $offset < 3; $offset++) 
        if (array_key_exists($key + $offset, $data)) { 
            $sum += floatval($data[$key + $offset]); 
            $div += 1; 
        } 
 
    if ($div < 1) 
        return FALSE; 
 
    return $sum / floatval($div); 
} 
 
$data = array( 
    24109=>260, 24110=>220, 24111=>270, 24112=>300, 24113=>269, 24114=>271, 24115=>240, 24116=>234, 24117=>220, 24118=>240, 24119=>262, 24120=>255, 
    24121=>270, 24122=>210, 24123=>260, 24124=>280, 24125=>259, 24126=>280, 24127=>220, 24128=>240, 24129=>240, 24130=>250, 24131=>271, 24132=>260, 24133=>240,  
    24134=>250, 24135=>250, 24136=>260, 24137=>225, 24138=>257, 24139=>230, 24140=>250, 24141=>230, 24142=>244, 24143=>257, 24144=>250, 
); 
 
for ($arg = 1; $arg < $argc; $arg++) { 
    $result = Quarter($data, $argv[$arg]); 
    if ($result === FALSE) 
        echo $argv[$arg], ": No data for such quarter.\n"; 
    else 
        echo $argv[$arg], ": Quarter average is ", $result, ".\n"; 
} 
 
?>

The YearMonth function takes any year and month pair (or YYYYMM as a single number or string), and returns it as a suitable key. It's rather clever, using string manipulation tricks, so that you can use e.g. 2009jan or "Feb-2010" or "August, 2011" or even '02/2010' or '2011/8' and all will just work.

If you save the above as quarter.php , you can run it (after marking it executable, chmod u+x quarter.php) using e.g.

Code:

./quarter.php '2009 Jan' Feb-2010 "August, 2011"

The answers it reports are 250 (=(260+220+270)/3), 250 (=(210+260+280)/3), and 241.333 (=(250+230+244)/3).

Since you are so keen on just using a sliding quarter-year window, I simplified the averaging function to do that and that alone; it is named Quarter here. I recommend you compare it with the weighted_average function. Mathematically the Quarter logic is trivial: the average is the sum of items divided by the number of items. A quarter is three months (12 months / 4 quarters = 3 months / quarter). Again, if you don't have data for some specific month, the function will skip it; if there is no data for the period at all, it'll return FALSE.

Note that you do not need to transcribe the data to an array by hand. If you read the lines using fgets(), then compact all whitespace (using for example $line = trim(preg_replace('/[\t\n\v\f\r ]+/', ' ', $line)); ), you can explode the line into an array using $fields = explode(' ', $line); The length of the array (count($fields)) will be one more than the number of years you have. The first field, $field[0], will contain 'Month' on the header line, and month name on the other fields. On the header line, the rest of the fields state the years; they contain the values for other lines. Since you can populate an associative array in any order you want, it should be very easy to read the data array from a file using the table format from your first post in this thread.