topic How can I use a lag function applied by groups? in Discussions

How can I use a lag function applied by groups?

dale_lehman — Sat, 10 Jun 2023 23:49:09 GMT

I have data where I'd like to compute the lag from one row to the next (it is a time series), but I have data for many groups. I want the lag created within each group separately. For many statistical functions (e.g., Col Mean) there is the option to add one or more By Group variables. There does not appear to be the same functionality for the Lag or Dif functions and I can't find anything in the scripting documentation to indicate how that might be done. Is there a way to easily compute the lags for each group separately? The only way I have currently found is to first sort the data by date and by group, and then use an IF formula that depends on the Group equaling the lag (Group), such as IF (Group) - lag (Group), then lag (column of interest), else missing.

Re: How can I use a lag function applied by groups?

dale_lehman — Sun, 05 Jun 2022 13:20:45 GMT

To make this more concrete, I've attached a simple example. The lagged deaths column is what I want - but I'd like to be able to do it more simply or elegantly.

Re: How can I use a lag function applied by groups?

txnelson — Sun, 05 Jun 2022 15:07:03 GMT

Here is a formula that appears to work, even when the rows are not sorted by the 2 levels. (i.e. sorted by date) The lagList in the formula, is there to handle the issue that the Col Moving Average() function returns the current row, even on the first occurrence.

If( Row() == 1, lagList = {} );
If( Contains( lagList, :administrative_area_level_1 || :administrative_area_level_2 ),
	Col Moving Average(
		:deaths,
		weighting = 1,
		before = 1,
		:administrative_area_level_1,
		:administrative_area_level_2
	) * 2 - :deaths,
	Insert Into( lagList, :administrative_area_level_1 || :administrative_area_level_2 )
);

Here is an inefficient formula that also works

curLevel1 = :administrative_area_level_1;
curLevel2 = :administrative_area_level_2;
curRow = Row();
Try(
	:deaths[Max(
		Current Data Table() << get rows where(
			:administrative_area_level_1 == curLevel1 & (:administrative_area_level_2 == curLevel2 & Row() < curRow)
		)
	)]
);

Re: How can I use a lag function applied by groups?

dale_lehman — Sun, 05 Jun 2022 15:07:18 GMT

I might have guessed that txnelson would have a solution! It isn't really more elegant or simpler than what I had, but it avoids the need to sort the data - which makes it considerably more flexible. Thanks.