Approximating trend in time-series

[Anton Ivanov]

Hello!

I am trying to come up with a metric to capture the temporal pattern of the average of numerical ratings (overall), i.e. the trend over time. One metric that comes to mind could be the cumulative up/down scores. For example, if each month-to-month up is a +1 and down -1 (flat = 0), then we could have two "counters" like in the hand-coded data example below.

Can you please suggest how to automate such calculation.

* Example generated by -dataex-. For more info, type help dataex
clear
input str10 asin int month double overall float(up down)
"373250460" 528         4  0 0
"373250460" 530         5  1 0
"373250460" 531         5  1 0
"373250460" 534         3  1 1
"373250460" 535         1  1 2
"373250460" 536         2  2 2
"373250460" 540         3  3 2
"373250460" 541         4  4 2
"373250460" 543         4  4 2
"373250460" 544         1  4 3
"373250460" 545         1  4 3
"373250460" 547         1  4 3
"373250460" 548         1  4 3
"373250460" 551         4  5 3
"373250460" 553       1.5  5 4
"373250460" 555         1  5 5
"373250460" 564         3  6 5
"373250460" 567         4  7 5
"373250460" 568         4  7 5
"373250460" 569         2  7 6
"373250460" 579         2  7 6
"373250460" 587         3  8 6
"373250460" 591         4  9 6
"373250460" 629         3  9 7
"373250460" 634       4.5 10 7
"373250460" 677         4 10 8
"446697192" 594 3.9000001  0 0
"446697192" 595 4.3333335  1 0
"446697192" 596         3  1 1
"446697192" 598         5  2 1
"446697192" 601         4  2 2
"446697192" 605         3  2 3
"446697192" 611         3  2 3
"446697192" 614         4  3 3
"446697192" 624         2  3 4
"446697192" 626         4  4 4
"446697192" 628         4  4 4
"446697192" 672         4  4 4
"575074205" 567 3.6666667  0 0
"575074205" 574         4  1 0
"575074205" 583         4  1 0
"575074205" 656         4  1 0
"575074205" 670         2  1 1
"575074205" 687         3  2 1
end

[Clyde Schechter]

isid asin month, sort
assert !missing(overall)
by asin (month): gen up = sum(overall > overall[_n-1])
by asin (month): gen down = sum(cond(_n == 1, 0, overall < overall[_n-1]))

Note: This code assumes, and verifies, that asin and month uniquely identify observations and that overall is never missing.

[Anton Ivanov]

Clyde Schechter Thank you very much for your reply, Clyde.