topic Re: Calculate Rate of Change Grouped by Column in Discussions

Calculate Rate of Change Grouped by Column

MarkDayton — Tue, 31 Mar 2020 20:25:06 GMT

I, like many others I suppose, are looking at the COVID-19 data (I got mine from https://raw.githubusercontent.com/nytimes/covid-19-data/master/us-states.csv). I would like to plot the daily change in confirmed cases by State. I've been able to work it out by first sorting the data by State, and then calculating the deltas:

If(:state == Lag(:state, 1),
	:cases - Lag(:cases, 1)
)

But is there any way to do this without sorting the data first, using some kind of Group By formulation?

Re: Calculate Rate of Change Grouped by Column

txnelson — Tue, 31 Mar 2020 21:02:07 GMT

Something like this might work.....but it will be slow if you have a lot of data

dt = Current Data Table();
curDate = :Date;
curState = :State;
Try( :cases - :cases[(dt < get rows where( :date == (curDate - In Days( 1 )) & :State == curState ))[1]] );

Re: Calculate Rate of Change Grouped by Column

MarkDayton — Tue, 31 Mar 2020 21:38:14 GMT

With one correction, change from: "dt < get rows", to: "dt << get rows"

Re: Calculate Rate of Change Grouped by Column

txnelson — Tue, 31 Mar 2020 21:37:41 GMT

oops....my error......good catch