Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
MarkDayton
Level IV

Calculate Rate of Change Grouped by Column

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Calculate Rate of Change Grouped by Column

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]] );

 

Jim

View solution in original post

3 REPLIES 3
Highlighted
txnelson
Super User

Re: Calculate Rate of Change Grouped by Column

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]] );

 

Jim

View solution in original post

Highlighted
MarkDayton
Level IV

Re: Calculate Rate of Change Grouped by Column

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

Highlighted
txnelson
Super User

Re: Calculate Rate of Change Grouped by Column

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

Jim
Article Labels

    There are no labels assigned to this post.