Understanding productivity or expenses over time without falling victim to stochastic interruptions

Question

Help me here, please. Perhaps before even giving me an answer you may need to help me ask the question. I have never learned about time series analysis and do not know if that is indeed what I need. I have never learned about time smoothed averages and do not know if that is indeed what I need. My statistics background: I have 12 credits in biostatistics (multiple linear regression, multiple logistic regression, survival analysis, multi-factorial anova but never repeated measures anova).

So please look at my scenarios below. What are the buzzwords I should be searching for and can you suggest a resource to learn what I need to learn?

I want to look at several different data sets for totally different purposes but common to all of them is that there are dates as one variable. So a couple of examples spring to mind: clinical productivity over time (as in how many surgeries or how many office visits) or electric bill over time (as in money paid to electricity company per month).

For both of the above the near universal way to do it is to create a spreadsheet of month or quarter in one column and in the other column would be something such as electricity payment or number of patients seen in the clinic. However, counting per month leads to a lot of noise that has no meaning. For instance, if I usually pay the electricity bill on the 28th of every month but on one occasion I forget and so I only pay it 5 days later on the 3rd of the next month then one month will appear as if there was zero expense and the next month will show ginormous expense. Since one has the actual dates of payment why would one purposefully throw away the very granular data by boxing it into expenses by calendar month.

Similarly if I am out of town for 6 days at a conference then that month will appear to be very unproductive and if those 6 days fell near the end of the month, the next month will be uncharacteristicaly busy since there will be a whole waiting list of people who wanted to see me but had to wait till I returned.

Then of course there are the obvious seasonal variations. Air conditioners use a lot of electricity so obviously one has to adjust for summer heat. Billions of children are referred to me for recurrent acute otitis media in the winter and hardly any in the summer and early fall. No child of school age gets scheduled for elective surgery in the first 6 weeks that schools return following the long summer vacation. Seasonality is just one independent variable that affects the dependent variable. There must be other independent variables some of which can be guessed and others that are not known.

A whole bunch of different issues crop up when looking at enrollment in a longstanding clinical study.

What branch of statistics lets us look at this over time by simply looking at events and their actual dates but without creating artificial boxes (months/quarters/years) that do not really exist.

I thought of making the weighted average count for any event. For instance number of patients seen this week is equal to 0.5*nr seen this week + 0.25*nr seen last week + 0.25*nr seen next week.

I want to learn more about this. What buzzwords should I be searching for?

Answer 1

I would start with robust time series filters (i.e. time varying medians) because these are more simple and intuitive.

Basically, the robust time filter is to time series smoothers what the median is to the mean; a summary measures (in this case a time varying one) that is not sensitive to 'wired' observations so long as they do not represent the majority of the data. For a summary see here.

If you need more sophisticated smoothers (i.e. non linear ones), you could do with robust Kalman filtering (although this requieres a slightly higher level of mathematical sophistication)

This document contains the following example ( a code to run under R, the open source stat software):

library(robfilter)
data(Nile)
nile <- as.numeric(Nile)
obj <- wrm.filter(nile, width=11)
plot(obj)

. The last documents contains a large number of references to papers and books. Other types of filters are implemented in the package, but the repeated median is a very simple one.

Answer 2

A simple solution that does not require the acquisition of specialized knowledge is to use control charts. They're ridiculously easy to create and make it easy to tell special cause variation (such as when you are out of town) from common cause variation (such as when you have an actual low-productivity month), which seems to be the kind of information you want.

They also preserve the data. Since you say you'll use the charts for many different purposes, I advise against performing any transformations in the data.

Here is a gentle introduction. If you decide that you like control charts, you may want to dive deeper into the subject. The benefits to your business will be huge. Control charts are reputed to have been a major contributor to the post-war Japanese economic boom.

There is even an R package.

Answer 3

I have heard of 'time-based boxcar' functions which might solve your problem. A time-based boxcar sum of 'window size' $\Delta t$ is defined at time $t$ to be the sum of all values between $t - \Delta t$ and $t$. This will be subject to discontinuities which you may or may not want. If you want older values to be downweighted, you can employ a simple or exponential moving average within your time based window.

edit:

I interpret the question as follows: suppose some events occur at times $t_i$ with magnitudes $x_i$. (for example, $x_i$ might be the amount of a bill paid.) Find some function $f(t)$ which estimates the sum of the magnitudes of the $x_i$ for times "near" $t$. For one of the examples posed by the OP, $f(t)$ would represent "how much one was paying for electricity" around time $t$.

Similar to this problem is that of estimating the "average" value around time $t$. For example: regression, interpolation (not usually applied to noisy data), and filtering. You could spend a lifetime studying just one of these three problems.

A seemingly unrelated problem, statistical in nature, is Density Estimation. Here the goal is, given observations of magnitudes $y_i$ generated by some process, to estimate, roughly, the probability of that process generating an event of magnitude $y$. One approach to density estimation is via a kernel function. My suggestion is to abuse the kernel approach for this problem.

Let $w(t)$ be a function such that $w(t) \ge 0$ for all $t$, $w(0) = 1$ (ordinary kernels do not all share this property), and $w'(t) \le 0$. Let $h$ be the bandwidth, which controls how much influence each data point has. Given data $t_i, x_i$, define the sum estimate by $$f(t) = \sum_{i=1}^n x_i w(|t - t_i|/h).$$ Some possible values of the function $w(t)$ are as follows:

• a uniform (or 'boxcar') kernel: $w(t) = 1$ for $t \le 1$ and $0$ otherwise;
• a triangular kernel: $w(t) = \max{(0,1-t)}$;
• a quadratic kernel: $w(t) = \max{(0,1-t^2)}$;
• a tricube kernel: $w(t) = \max{(0,(1-t^2)^3)}$;
• a Gaussian kernel: $w(t) = \exp{(-t^2 / 2)}$;

I call these kernels, but they are off by a constant factor here and there; see also a comprehensive list of kernels.

Some example code in Matlab:

%%kernels
ker0 = @(t)(max(0,ceil(1-t))); %uniform
ker1 = @(t)(max(0,1-t)); %triangular
ker2 = @(t)(max(0,1-t.^2)); %quadratic
ker3 = @(t)(max(0,(1-t.^2).^3)); %tricube
ker4 = @(t)(exp(-0.5 * t.^2)); %Gaussian

%%compute f(t) given x_i,t_i,kernel,h
ff = @(x_i,t_i,t,kerf,h)(sum(x_i .* kerf(abs(t - t_i) / h)));

%%some sample data: irregular electric bills
sdata = [
datenum(2009,12,30),141.73;...
datenum(2010,01,25),100.45;...
datenum(2010,02,23),98.34;...
datenum(2010,03,30),83.92;...
datenum(2010,05,01),56.21;...       %late this month;
datenum(2010,05,22),47.33;...       
datenum(2010,06,14),62.84;...       
datenum(2010,07,30),83.34;...       
datenum(2010,09,10),93.34;...       %really late this month
datenum(2010,09,22),78.34;...
datenum(2010,10,22),93.25;...
datenum(2010,11,14),83.39;...       %early this month;
datenum(2010,12,30),133.82];

%%some irregular observation times at which to sample the filtered version;
t_obs  = sort(datenum(2009,12,01) + 400 * rand(1,400));

t_i = sdata(:,1);x_i = sdata(:,2);

%%compute f(t) for each of the kernel functions;
h   = 60;    %bandwidth of 60 days;

fx0 = arrayfun(@(t)(ff(x_i,t_i,t,ker0,h)),t_obs);
fx1 = arrayfun(@(t)(ff(x_i,t_i,t,ker1,h)),t_obs);
fx2 = arrayfun(@(t)(ff(x_i,t_i,t,ker2,h)),t_obs);
fx3 = arrayfun(@(t)(ff(x_i,t_i,t,ker3,h)),t_obs);
fx4 = arrayfun(@(t)(ff(x_i,t_i,t,ker4,0.5*h)),t_obs);   %!!use smaller bandwidth

%%plot them;
lhand = plot(t_i,x_i,'--rs',t_obs,fx0,'m-+',t_obs,fx1,'b-+',t_obs,fx2,'k-+',...
t_obs,fx3,'g-+',t_obs,fx4,'c-+');
set(lhand(1),'MarkerSize',12);
set(lhand(2:end),'MarkerSize',4);
datetick(gca());
legend(lhand,{'data','uniform','triangular','quadratic','tricube','gaussian'});

The plot shows the use of a few kernels on some sample electric bill data.

Note that the uniform kernel is subject to the 'stochastic shocks' which the OP is trying to avoid. The tricube and Gaussian kernels give much smoother approximations. If this approach is acceptable, one only has to choose the kernel and the bandwidth (in general that is a hard problem, but given some domain knowledge, and some code-test-recode loops, it should not be too difficult.)

Answer 4

Buzzwords: interpolation, resampling, smoothing.

Your problem is similar to one encountered frequently in demography: people might have census counts broken down into age intervals, for example, and such intervals are not always of constant width. You want to interpolate the distribution by age. What this shares with your problem, aside from the variable width (= variable time intervals), is that the data tend to be non-negative. In addition, many such datasets can have noise, but it has a particular form of negative correlation: a count that appears in one bin will not appear in neighboring bins, but might have been assigned to the wrong bin. For example, older people may tend to round their ages to the nearest five years. They are not overlooked but they might contribute to the wrong age group. By and large, though, the data are complete and reliable. In terms of this analogy we're talking about a full census; in your datasets you have actual electric bills, actual enrollments, and so on. So it's just a question of apportioning the data reasonably to a set of intervals useful for further analysis (such as equally spaced times for time series analysis): that's where interpolation and resampling are involved.

There are many interpolation techniques. The commonest in demography were developed for simple calculation and are based on polynomial splines. Many share a trick worth knowing, regardless of how you plan to process your data: don't attempt to interpolate the raw data; instead, interpolate their cumulative sum. The latter will be monotonically increasing due to the non-negativity of the original values, and therefore will tend to be relatively smooth. This is why polynomial splines can work at all. Another advantage of this approach is that although the fit may deviate from the data points (slightly, one hopes), overall it correctly reproduces the totals, so that nothing is net lost or gained. Of course, after fitting the cumulative values (as a function of time or age), you take first differences to estimate totals within any bin you like.

The simplest example of this approach is a linear spline: just connect successive points on the plot of cumulative $x$ vs. cumulative $t$ by line segments. Estimate the counts in any time interval $[t_0, t_1]$ by reading off the values $x_0$ and $x_1$ of the splined curve at $t_0$ and $t_1$ respectively and using $x_1 - x_0$. Better splines (cubic in some areas; quintic in many demographic apps) sometimes improve the estimates. This is equivalent to your intuition of weighting the data and gives it a nice graphical interpretation.