How to Calculate the Average of a Variable Between Two Date Ranges Using a Loop or Apply Function

Find average values of a column in terms of date range of another column in R

Assuming that x$Date, y$StartDate, and y$EndDate are of class Date (or, character), the following apply approach should be doing the trick:

 y$AvGroupSize<- apply(y, 1, function(z) {
                 round(mean(x$Group.Size[which(x$Date >= z[1] & x$Date <=z[2])], na.rm=T),0)
    }
)

How to average a subset of data in SAS conditional on a date?

A SQL statement is by far the most succinct code for obtaining your result set.
The query will join with 2 independent references to volume data. The first for obtaining the event date volume, and the second for computing the average volume over the three prior days.

The date data should be read in as a SAS date, so that the BETWEEN condition will be correct.

Data event;
     input Date: yymmdd8.;
     cards;
     20200428
     20200429
     ;
run;

Data vol;
     input Date: yymmdd8. Volume;
     cards;
     20200430  100
     20200429  110
     20200428  86
     20200427  95
     20200426  80
     20200425  90
     ;
run;

* SQL query with GROUP BY ;

proc sql;
  create table want as
  select 
    event.date
  , volume_one.volume
  , mean(volume_two.volume) as avg
  from event
  left join vol as volume_one
  on event.date = volume_one.date
  left join vol as volume_two
  on volume_two.date between event.date-1 and event.date-3
  group by 
  event.date, volume_one.volume
  ;

* alternative query using correlated sub-query;

  create table want_2 as
  select 
    event.date
  , volume
  , ( select mean(volume) as avg from vol where vol.date between event.date-1 and event.date-3 )
    as avg
  from event
  left join vol
  on event.date = vol.date
  ;

Write a loop code to calculate average 77 different times, using another column as criteria

Try this code:

import pandas as pd, random

# make dummy data
src = []
for i in range(77):
    for k in range(10):
        src.append([i + 1, random.randint(-10, 10)])
df = pd.DataFrame(src, columns=('N', 'Return'))
print(df)

# process data
df = df.groupby('N').head(5).groupby('N').mean().reset_index()
print(df)

Output

     N  Return
0    1    -1.4
1    2    -2.6
2    3     2.0
3    4    -0.6
4    5    -1.0
..  ..     ...
72  73    -2.0
73  74    -0.2
74  75    -2.0
75  76    -7.0
76  77     1.8

[77 rows x 2 columns]

How to filter a data set and calculate a new variable faster in R?

you can try this. use make_date from the lubridate package to make a new date_time column using the year , month, day and hour columns of your dataset. Then group and summarise on the new column

library(dplyr)
library(lubridate)
 df %>% 
   mutate(date_time = make_datetime(year, m, d, hr)) %>%  
   group_by(date_time) %>% 
   summarise(eg_bf = mean(pw))

How to calculate average number of days between series of dates?

You can take rowwise differences of 'Date' columns and take the average.

library(dplyr)

df %>%
  mutate(across(starts_with('Date'), as.Date, '%m/%d/%y')) %>%
  rowwise() %>%
  mutate(diff = as.numeric(mean(diff(c_across(starts_with('Date'))), na.rm = TRUE)))

#  Variable Date1      Date2      Date3      Date4       diff
#  <chr>    <date>     <date>     <date>     <date>     <dbl>
#1 VarA     2021-09-01 2021-09-02 2021-09-03 2021-09-04     1
#2 VarB     2021-08-01 2021-08-17 2021-09-02 NA            16
#3 VarC     2021-09-25 NA         NA         NA           NaN

data

It is easier to help if you provide data in a reproducible format

df <- structure(list(Variable = c("VarA", "VarB", "VarC"), Date1 = c("09/01/21", 
"08/01/21", "09/25/21"), Date2 = c("09/02/21", "08/17/21", ""
), Date3 = c("09/03/21", "09/02/21", ""), Date4 = c("09/04/21", 
"", "")), class = "data.frame", row.names = c(NA, -3L))

How to calculate the difference between two dates using PHP?

Use this for legacy code (PHP < 5.3). For up to date solution see jurka's answer below

You can use strtotime() to convert two dates to unix time and then calculate the number of seconds between them. From this it's rather easy to calculate different time periods.

$date1 = "2007-03-24";
$date2 = "2009-06-26";

$diff = abs(strtotime($date2) - strtotime($date1));

$years = floor($diff / (365*60*60*24));
$months = floor(($diff - $years * 365*60*60*24) / (30*60*60*24));
$days = floor(($diff - $years * 365*60*60*24 - $months*30*60*60*24)/ (60*60*24));

printf("%d years, %d months, %d days\n", $years, $months, $days);

Edit: Obviously the preferred way of doing this is like described by jurka below. My code is generally only recommended if you don't have PHP 5.3 or better.

Several people in the comments have pointed out that the code above is only an approximation. I still believe that for most purposes that's fine, since the usage of a range is more to provide a sense of how much time has passed or remains rather than to provide precision - if you want to do that, just output the date.

Despite all that, I've decided to address the complaints. If you truly need an exact range but haven't got access to PHP 5.3, use the code below (it should work in PHP 4 as well). This is a direct port of the code that PHP uses internally to calculate ranges, with the exception that it doesn't take daylight savings time into account. That means that it's off by an hour at most, but except for that it should be correct.

<?php

/**
 * Calculate differences between two dates with precise semantics. Based on PHPs DateTime::diff()
 * implementation by Derick Rethans. Ported to PHP by Emil H, 2011-05-02. No rights reserved.
 * 
 * See here for original code:
 * http://svn.php.net/viewvc/php/php-src/trunk/ext/date/lib/tm2unixtime.c?revision=302890&view=markup
 * http://svn.php.net/viewvc/php/php-src/trunk/ext/date/lib/interval.c?revision=298973&view=markup
 */

function _date_range_limit($start, $end, $adj, $a, $b, $result)
{
    if ($result[$a] < $start) {
        $result[$b] -= intval(($start - $result[$a] - 1) / $adj) + 1;
        $result[$a] += $adj * intval(($start - $result[$a] - 1) / $adj + 1);
    }

    if ($result[$a] >= $end) {
        $result[$b] += intval($result[$a] / $adj);
        $result[$a] -= $adj * intval($result[$a] / $adj);
    }

    return $result;
}

function _date_range_limit_days($base, $result)
{
    $days_in_month_leap = array(31, 31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31);
    $days_in_month = array(31, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31);

    _date_range_limit(1, 13, 12, "m", "y", &$base);

    $year = $base["y"];
    $month = $base["m"];

    if (!$result["invert"]) {
        while ($result["d"] < 0) {
            $month--;
            if ($month < 1) {
                $month += 12;
                $year--;
            }

            $leapyear = $year % 400 == 0 || ($year % 100 != 0 && $year % 4 == 0);
            $days = $leapyear ? $days_in_month_leap[$month] : $days_in_month[$month];

            $result["d"] += $days;
            $result["m"]--;
        }
    } else {
        while ($result["d"] < 0) {
            $leapyear = $year % 400 == 0 || ($year % 100 != 0 && $year % 4 == 0);
            $days = $leapyear ? $days_in_month_leap[$month] : $days_in_month[$month];

            $result["d"] += $days;
            $result["m"]--;

            $month++;
            if ($month > 12) {
                $month -= 12;
                $year++;
            }
        }
    }

    return $result;
}

function _date_normalize($base, $result)
{
    $result = _date_range_limit(0, 60, 60, "s", "i", $result);
    $result = _date_range_limit(0, 60, 60, "i", "h", $result);
    $result = _date_range_limit(0, 24, 24, "h", "d", $result);
    $result = _date_range_limit(0, 12, 12, "m", "y", $result);

    $result = _date_range_limit_days(&$base, &$result);

    $result = _date_range_limit(0, 12, 12, "m", "y", $result);

    return $result;
}

/**
 * Accepts two unix timestamps.
 */
function _date_diff($one, $two)
{
    $invert = false;
    if ($one > $two) {
        list($one, $two) = array($two, $one);
        $invert = true;
    }

    $key = array("y", "m", "d", "h", "i", "s");
    $a = array_combine($key, array_map("intval", explode(" ", date("Y m d H i s", $one))));
    $b = array_combine($key, array_map("intval", explode(" ", date("Y m d H i s", $two))));

    $result = array();
    $result["y"] = $b["y"] - $a["y"];
    $result["m"] = $b["m"] - $a["m"];
    $result["d"] = $b["d"] - $a["d"];
    $result["h"] = $b["h"] - $a["h"];
    $result["i"] = $b["i"] - $a["i"];
    $result["s"] = $b["s"] - $a["s"];
    $result["invert"] = $invert ? 1 : 0;
    $result["days"] = intval(abs(($one - $two)/86400));

    if ($invert) {
        _date_normalize(&$a, &$result);
    } else {
        _date_normalize(&$b, &$result);
    }

    return $result;
}

$date = "1986-11-10 19:37:22";

print_r(_date_diff(strtotime($date), time()));
print_r(_date_diff(time(), strtotime($date)));

---

Related Topics