2
$\begingroup$

I have a time series data-set that contains two separate trends(one trends has relatively lower values/ the other has higher values).If you plot it in excel, it will be very clear to see those two trends.

My question is: how can I separate those two different trends? I want to separate those two trends based on the value of the data over time. Thus, I come up with two different time series that has two different trends(those trends could be nonlinear) Is there anyone knows how to deal with this problem?

The data is as following: the first column is the data the second column is the time stamp the data is collected.

TT  EST <br/>
741 1/5/2012 15:30  <br/>
662 1/5/2012 15:31  <br/>
722 1/5/2012 15:32  <br/>
743 1/5/2012 15:32  <br/>
688 1/5/2012 15:32  <br/>
776 1/5/2012 15:34  <br/>
735 1/5/2012 15:34  <br/>
797 1/5/2012 15:37  <br/>
667 1/5/2012 15:37  <br/>
843 1/5/2012 15:37  <br/>
1013    1/5/2012 15:37  <br/>
688 1/5/2012 15:37  <br/>
672 1/5/2012 15:37  <br/>
784 1/5/2012 15:37  <br/>
799 1/5/2012 15:38  <br/>
759 1/5/2012 15:38  <br/>
823 1/5/2012 15:39  <br/>
659 1/5/2012 15:39  <br/>
814 1/5/2012 15:40  <br/>
784 1/5/2012 15:40  <br/>
760 1/5/2012 15:41  <br/>
843 1/5/2012 15:41  <br/>
806 1/5/2012 15:41  <br/>
792 1/5/2012 15:42  <br/>
794 1/5/2012 15:42  <br/>
618 1/5/2012 15:42  <br/>
843 1/5/2012 15:43  <br/>
723 1/5/2012 15:43  <br/>
893 1/5/2012 15:44  <br/>
736 1/5/2012 15:44  <br/>
914 1/5/2012 15:44  <br/>
819 1/5/2012 15:45  <br/>
674 1/5/2012 15:45  <br/>
692 1/5/2012 15:46  <br/>
850 1/5/2012 15:47  <br/>
735 1/5/2012 15:49  <br/>
707 1/5/2012 15:49  <br/>
891 1/5/2012 15:49  <br/>
852 1/5/2012 15:49  <br/>
741 1/5/2012 15:51  <br/>
767 1/5/2012 15:51  <br/>
748 1/5/2012 15:51  <br/>
886 1/5/2012 15:52  <br/>
884 1/5/2012 15:53  <br/>
915 1/5/2012 15:53  <br/>
773 1/5/2012 15:53  <br/>
874 1/5/2012 15:53  <br/>
682 1/5/2012 15:53  <br/>
682 1/5/2012 15:53  <br/>
687 1/5/2012 15:53  <br/>
1125    1/5/2012 15:53  <br/>
940 1/5/2012 15:54  <br/>
899 1/5/2012 15:54  <br/>
886 1/5/2012 15:55  <br/>
723 1/5/2012 15:55  <br/>
891 1/5/2012 15:55  <br/>
896 1/5/2012 15:55  <br/>
923 1/5/2012 15:56  <br/>
936 1/5/2012 15:56  <br/>
884 1/5/2012 15:56  <br/>
926 1/5/2012 15:56  <br/>
921 1/5/2012 15:57  <br/>
912 1/5/2012 15:57  <br/>
878 1/5/2012 15:58  <br/>
880 1/5/2012 15:59  <br/>
860 1/5/2012 15:59  <br/>
885 1/5/2012 15:59  <br/>
786 1/5/2012 15:59  <br/>
894 1/5/2012 16:01  <br/>
735 1/5/2012 16:02  <br/>
740 1/5/2012 16:02  <br/>
1109    1/5/2012 16:03  <br/>
706 1/5/2012 16:03  <br/>
701 1/5/2012 16:03  <br/>
895 1/5/2012 16:03  <br/>
831 1/5/2012 16:03  <br/>
818 1/5/2012 16:03  <br/>
829 1/5/2012 16:04  <br/>
698 1/5/2012 16:04  <br/>
829 1/5/2012 16:05  <br/>
845 1/5/2012 16:05  <br/>
830 1/5/2012 16:06  <br/>
718 1/5/2012 16:06  <br/>
802 1/5/2012 16:06  <br/>
830 1/5/2012 16:07  <br/>
720 1/5/2012 16:07  <br/>
840 1/5/2012 16:08  <br/>
793 1/5/2012 16:08  <br/>
1237    1/5/2012 16:08  <br/>
721 1/5/2012 16:09  <br/>
721 1/5/2012 16:09  <br/>
789 1/5/2012 16:09  <br/>
800 1/5/2012 16:09  <br/>
795 1/5/2012 16:09  <br/>
800 1/5/2012 16:10  <br/>
661 1/5/2012 16:11  <br/>
828 1/5/2012 16:13  <br/>
1399    1/5/2012 16:14  <br/>
786 1/5/2012 16:14  <br/>
801 1/5/2012 16:14  <br/>
835 1/5/2012 16:15  <br/>
767 1/5/2012 16:16  <br/>
843 1/5/2012 16:16  <br/>
779 1/5/2012 16:17  <br/>
695 1/5/2012 16:17  <br/>
700 1/5/2012 16:17  <br/>
785 1/5/2012 16:18  <br/>
1450    1/5/2012 16:18  <br/>
770 1/5/2012 16:18  <br/>
735 1/5/2012 16:18  <br/>
779 1/5/2012 16:19  <br/>
789 1/5/2012 16:20  <br/>
788 1/5/2012 16:20  <br/>
852 1/5/2012 16:20  <br/>
793 1/5/2012 16:20  <br/>
894 1/5/2012 16:21  <br/>
685 1/5/2012 16:21  <br/>
792 1/5/2012 16:21  <br/>
837 1/5/2012 16:23  <br/>
824 1/5/2012 16:24  <br/>
700 1/5/2012 16:25  <br/>
831 1/5/2012 16:25  <br/>
730 1/5/2012 16:26  <br/>
826 1/5/2012 16:27  <br/>
720 1/5/2012 16:27  <br/>
844 1/5/2012 16:28  <br/>
875 1/5/2012 16:28  <br/>
737 1/5/2012 16:29  <br/>
737 1/5/2012 16:30  <br/>
859 1/5/2012 16:30  <br/>
847 1/5/2012 16:31  <br/>
741 1/5/2012 16:31  <br/>
911 1/5/2012 16:33  <br/>
934 1/5/2012 16:34  <br/>
812 1/5/2012 16:36  <br/>
937 1/5/2012 16:37  <br/>
925 1/5/2012 16:37  <br/>
949 1/5/2012 16:39  <br/>
934 1/5/2012 16:39  <br/>
976 1/5/2012 16:40  <br/>
1016    1/5/2012 16:41  <br/>
947 1/5/2012 16:41  <br/>
863 1/5/2012 16:41  <br/>
869 1/5/2012 16:41  <br/>
989 1/5/2012 16:41  <br/>
948 1/5/2012 16:44  <br/>
727 1/5/2012 16:44  <br/>
985 1/5/2012 16:45  <br/>
974 1/5/2012 16:45  <br/>
715 1/5/2012 16:45  <br/>
979 1/5/2012 16:45  <br/>
998 1/5/2012 16:46  <br/>
750 1/5/2012 16:46  <br/>
1078    1/5/2012 16:47  <br/>
746 1/5/2012 16:48  <br/>
1017    1/5/2012 16:48  <br/>
976 1/5/2012 16:48  <br/>
973 1/5/2012 16:49  <br/>
1069    1/5/2012 16:50  <br/>
869 1/5/2012 16:50  <br/>
906 1/5/2012 16:51  <br/>
865 1/5/2012 16:51  <br/>
1011    1/5/2012 16:52  <br/>
727 1/5/2012 16:52  <br/>
960 1/5/2012 16:52  <br/>
1013    1/5/2012 16:54  <br/>
1024    1/5/2012 16:54  <br/>
769 1/5/2012 16:56  <br/>
1068    1/5/2012 16:57  <br/>
1067    1/5/2012 16:57  <br/>
758 1/5/2012 16:57  <br/>
768 1/5/2012 16:57  <br/>
1087    1/5/2012 16:57  <br/>
1064    1/5/2012 16:57  <br/>
1158    1/5/2012 16:59  <br/>
1332    1/5/2012 17:00  <br/>
1024    1/5/2012 17:00  <br/>
1025    1/5/2012 17:00  <br/>
1073    1/5/2012 17:01  <br/>
1078    1/5/2012 17:01  <br/>
819 1/5/2012 17:01  <br/>
1098    1/5/2012 17:02  <br/>
1038    1/5/2012 17:03  <br/>
783 1/5/2012 17:04  <br/>
1076    1/5/2012 17:05  <br/>
761 1/5/2012 17:06  <br/>
1049    1/5/2012 17:06  <br/>
754 1/5/2012 17:07  <br/>
780 1/5/2012 17:07  <br/>
780 1/5/2012 17:07  <br/>
795 1/5/2012 17:08  <br/>
778 1/5/2012 17:08  <br/>
772 1/5/2012 17:08  <br/>
1077    1/5/2012 17:09  <br/>
789 1/5/2012 17:09  <br/>
1077    1/5/2012 17:10  <br/>
1076    1/5/2012 17:10  <br/>
1083    1/5/2012 17:12  <br/>
1099    1/5/2012 17:12  <br/>
1638    1/5/2012 17:15  <br/>
1117    1/5/2012 17:15  <br/>
1104    1/5/2012 17:15  <br/>
765 1/5/2012 17:16  <br/>
749 1/5/2012 17:16  <br/>
801 1/5/2012 17:16  <br/>
1073    1/5/2012 17:16  <br/>
1068    1/5/2012 17:16  <br/>
1099    1/5/2012 17:17  <br/>
1152    1/5/2012 17:19  <br/>
797 1/5/2012 17:19  <br/>
782 1/5/2012 17:19  <br/>
1121    1/5/2012 17:20  <br/>
838 1/5/2012 17:20  <br/>
1107    1/5/2012 17:21  <br/>
1204    1/5/2012 17:21  <br/>
1122    1/5/2012 17:22  <br/>
1133    1/5/2012 17:22  <br/>
1246    1/5/2012 17:22  <br/>
1174    1/5/2012 17:22  <br/>
823 1/5/2012 17:22  <br/>
812 1/5/2012 17:22  <br/>
1373    1/5/2012 17:23  <br/>
815 1/5/2012 17:24  <br/>
1729    1/5/2012 17:24  <br/>
842 1/5/2012 17:25  <br/>
842 1/5/2012 17:25  <br/>
1257    1/5/2012 17:25  <br/>
1230    1/5/2012 17:26  <br/>
1286    1/5/2012 17:27  <br/>
1243    1/5/2012 17:27  <br/>
1019    1/5/2012 17:27  <br/>
1382    1/5/2012 17:29  <br/>
1861    1/5/2012 17:30  <br/>
825 1/5/2012 17:30  <br/>
1356    1/5/2012 17:33  <br/>
867 1/5/2012 17:33  <br/>
850 1/5/2012 17:33  <br/>
1314    1/5/2012 17:34  <br/>
1345    1/5/2012 17:34  <br/>
1471    1/5/2012 17:34  <br/>
1661    1/5/2012 17:35  <br/>
1325    1/5/2012 17:36  <br/>
1023    1/5/2012 17:38  <br/>
940 1/5/2012 17:38  <br/>
1348    1/5/2012 17:38  <br/>
1396    1/5/2012 17:41  <br/>
1374    1/5/2012 17:41  <br/>
1484    1/5/2012 17:42  <br/>
1485    1/5/2012 17:42  <br/>
956 1/5/2012 17:42  <br/>
956 1/5/2012 17:42  <br/>
945 1/5/2012 17:42  <br/>
980 1/5/2012 17:44  <br/>
1407    1/5/2012 17:45  <br/>
959 1/5/2012 17:46  <br/>
1428    1/5/2012 17:47  <br/>
2085    1/5/2012 17:47  <br/>
1398    1/5/2012 17:48  <br/>
1805    1/5/2012 17:48  <br/>
1572    1/5/2012 17:48  <br/>
1365    1/5/2012 17:49  <br/>
1474    1/5/2012 17:50  <br/>
1567    1/5/2012 17:50  <br/>
1382    1/5/2012 17:50  <br/>
1382    1/5/2012 17:50  <br/>
1757    1/5/2012 17:51  <br/>
1390    1/5/2012 17:51  <br/>
951 1/5/2012 17:51  <br/>
1479    1/5/2012 17:52  <br/>
1400    1/5/2012 17:54  <br/>
1491    1/5/2012 17:54  <br/>
1426    1/5/2012 17:55  <br/>
2074    1/5/2012 17:56  <br/>
913 1/5/2012 17:56  <br/>
1449    1/5/2012 17:56  <br/>
1439    1/5/2012 17:57  <br/>
920 1/5/2012 17:57  <br/>
1501    1/5/2012 17:57  <br/>
1385    1/5/2012 17:57  <br/>
1522    1/5/2012 17:57  <br/>
936 1/5/2012 17:58  <br/>
1011    1/5/2012 17:58  <br/>
916 1/5/2012 17:58  <br/>
1423    1/5/2012 17:59  <br/>
1505    1/5/2012 17:59  <br/>
908 1/5/2012 17:59  <br/>
1505    1/5/2012 17:59  <br/>
907 1/5/2012 18:00  <br/>
1415    1/5/2012 18:00  <br/>
1226    1/5/2012 18:02  <br/>
1464    1/5/2012 18:02  <br/>
872 1/5/2012 18:04  <br/>
874 1/5/2012 18:04  <br/>
1544    1/5/2012 18:04  <br/>
1424    1/5/2012 18:06  <br/>
1432    1/5/2012 18:07  <br/>
1447    1/5/2012 18:07  <br/>
967 1/5/2012 18:08  <br/>
1544    1/5/2012 18:08  <br/>
1442    1/5/2012 18:08  <br/>
1527    1/5/2012 18:08  <br/>
852 1/5/2012 18:11  <br/>
1546    1/5/2012 18:12  <br/>
1535    1/5/2012 18:12  <br/>
1512    1/5/2012 18:12  <br/>
1517    1/5/2012 18:12  <br/>
1522    1/5/2012 18:13  <br/>
856 1/5/2012 18:13  <br/>
1504    1/5/2012 18:15  <br/>
1488    1/5/2012 18:15  <br/>
1567    1/5/2012 18:15  <br/>
965 1/5/2012 18:15  <br/>
964 1/5/2012 18:17  <br/>
944 1/5/2012 18:17  <br/>
1314    1/5/2012 18:17  <br/>
1502    1/5/2012 18:17  <br/>
920 1/5/2012 18:18  <br/>
1397    1/5/2012 18:18  <br/>
1448    1/5/2012 18:20  <br/>
1342    1/5/2012 18:21  <br/>
1323    1/5/2012 18:23  <br/>
1359    1/5/2012 18:25  <br/>
1350    1/5/2012 18:25  <br/>
888 1/5/2012 18:26  <br/>
1357    1/5/2012 18:26  <br/>
1335    1/5/2012 18:27  <br/>
1514    1/5/2012 18:27  <br/>
1555    1/5/2012 18:27  <br/>
Improve this question
$\endgroup$
5
  • 1
    $\begingroup$ you should consider removing the data set and just being clear what the issue is and where you're running into trouble. Adding the data doesn't seem to help your question. You may want to migrate this over to stackoverflow.com, as it looks potentially more like a computing/programming question. $\endgroup$
    – Eric Peterson
    May 29, 2013 at 23:14
  • $\begingroup$ Following up on @ClarkW.Griswold 's later comment - to have your question moved, flag it for moderator attention; don't repost it. $\endgroup$
    – Glen_b
    May 29, 2013 at 23:22
  • $\begingroup$ You should start with a smaller, but fully-worked example (the date is beside the point to your question; you just need an integer to stand for the different values time takes - such as the number of minutes since 15:00; the solution to the simpler problem solves the bigger one apart from some fiddling) - i.e. also give the desired result, including for all tricky cases. e.g. what happens if there's only one value at that time? And say (and tag with) what you're doing it in since solutions in R will differ radically from solutions in Excel and both would differ from SAS or C. $\endgroup$
    – Glen_b
    May 29, 2013 at 23:30
  • $\begingroup$ Your data has at least one case with three data points for a single moment in time, but you say that it contains only two trends. So how do you separate the trends? Is it by size? But do you know that the two trends stay within disjoint intervals? If so, I can give you some simple Python code to separate them (possibly on StackOverflow if you are required to move your question there). $\endgroup$
    – Rohit Chatterjee
    May 30, 2013 at 11:20
  • $\begingroup$ This is definitely not (just) a programming question: there's no program possible until there's an algorithm! $\endgroup$
    – whuber ♦
    May 30, 2013 at 21:37

1 Answer 1

Reset to default
2
$\begingroup$

One approach is to make a rough initial assignment of each value to a group, then iteratively improve the assignments by fitting a model separately to each group and reassigning each value to the fit for which it is the smaller standardized residual. (Although this could be improved by jackknifing--that is, by systematically removing each value from the data, fitting the remaining values in both groups, and performing the reassignment, with so much data the extra work would likely make no difference anyway.)

Let's use the posted data as an example. Here is an implementation of the incremental improvement function in R. It just regresses TT against EST, thereby fitting straight lines; this regression can be replaced by any model one pleases, such as ARIMA times series fits.

improve <- function(x, y, i, method=rlm) {
  # `i` indicates which fit should be used.
  library(MASS) # rlm()
  d <- data.frame(x=x, y=y)
  fit.1 <- method(y ~ x, data=d, subset=(i))
  fit.0 <- method(y ~ x, data=d, subset=(!i))
  #
  # Re-assign the data according to relative nearness.
  #
  p.1 <- predict(fit.1, d, se.fit=TRUE)
  p.0 <- predict(fit.0, d, se.fit=TRUE)

  delta.1 <- (p.1$fit - y) / p.1$se.fit
  delta.0 <- (p.0$fit - y) / p.0$se.fit
  j <- abs(delta.1) < abs(delta.0)
  return(j)
}

Here are the results, using color to show the final assignments of the data into the two groups. The R code to produce them appears afterwards.

Figure 1

Clearly a better model could be used--the top fit is not good--but the separation into two groups still looks pretty good anyway, in part because the bottom fit is pretty good.

#
# Obtain the data.
# (Data are in a CSV file formatted like this:
#    TT  DATE TIME
#    741 1/5/2012 15:30
#    662 1/5/2012 15:31
# ....)
#
df <- read.table("f:/temp/data.txt", header=TRUE, as.is=TRUE)
df$t <-sapply(strsplit(df$TIME, ":"), function(x) as.numeric(x) %*% c(1, 1/60))
#
# Begin by assigning all the maxima to the same group.
# (This works because many times have multiple observations. Otherwise, a
# windowed approach might work.)
#
x <- unique(df$t) #$ (prevent an SE bug from indenting the code)
y <- sapply(x, function(z) max(df$TT[abs(df$t-z)*60 < 1/2]))
d <- merge(df, data.frame(t=x, t.max=y))
j <- d$TT==d$t.max
#
# Iteratively improve the assignments.
#
i <- j * 0
n.iter <- 20
while(sum(i!=j) > 0 && n.iter > 0) {
  n.iter <- n.iter-1; i <- j; j <- improve(d$t, d$TT, i, method=lm)
}
# Polish with robust fits
while(sum(i!=j) > 0 && n.iter > 0) {
  n.iter <- n.iter-1; i <- j; j <- improve(d$t, d$TT, i)
}
#
# Plot the results.
#
par(mfrow=c(1,1))
plot(df$t, df$TT, xlab="Hour", ylab="Y", col="Gray")
plot(d$t, d$TT, xlab="Hour", ylab="Y")
points(d$t[i], d$TT[i], col="Red", pch=19, cex=0.75)
abline(lm(TT ~ t, data=d, subset=(i))$coeff, col="Gray") #$ 
abline(lm(TT ~ t, data=d, subset=(!i))$coeff, col="Gray")
Improve this answer
$\endgroup$
2
  • 1
    $\begingroup$ Thank you very much @whuber, I tested the code and it works as I desired. I will try the ARIMA fit:) $\endgroup$
    – user2380022
    May 31, 2013 at 2:26
  • 1
    $\begingroup$ I recommend doing something about the outlying data first, if you can: I suspect they might give ARIMA some trouble. $\endgroup$
    – whuber ♦
    May 31, 2013 at 8:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.