I am new to R but have turned to it to solve a problem with a large data set I am trying to process. Currently I have a 4 columns of data (Y values) set against minute-interval timestamps (month/day/year hour:min) (X values) as below:

        timestamp          tr            tt         sr         st  
    1   9/1/01 0:00   1.018269e+02   -312.8622   -1959.393   4959.828  
    2   9/1/01 0:01   1.023567e+02   -313.0002   -1957.755   4958.935  
    3   9/1/01 0:02   1.018857e+02   -313.9406   -1956.799   4959.938  
    4   9/1/01 0:03   1.025463e+02   -310.9261   -1957.347   4961.095  
    5   9/1/01 0:04   1.010228e+02   -311.5469   -1957.786   4959.078

The problem I have is that some timestamp values are missing - e.g. there may be a gap between 9/1/01 0:13 and 9/1/01 0:27 and such gaps are irregular through the data set. I need to put several of these series into the same database and because the missing values are different for each series, the dates do not currently align on each row.

I would like to generate rows for these missing timestamps and fill the Y columns with blank values (no data, not zero), so that I have a continuous time series.

I&#39;m honestly not quite sure where to start (not really used R before so learning as I go along!) but any help would be much appreciated. I have thus far installed chron and zoo, since it seems they might be useful.

Thanks!

Insert rows for missing dates/times

I&#39;m trying to convert a data frame to xts object using the as.xts()-method. Here is my input dataframe q:

    q
                          t x  
    1  2006-01-01 00:00:00  1  
    2  2006-01-01 01:00:00  2  
    3  2006-01-01 02:00:00  3

    str(q)
        &#39;data.frame&#39;:   10 obs. of  2 variables:
     $ t: POSIXct, format: &quot;2006-01-01 00:00:00&quot; &quot;2006-01-01 01:00:00&quot; &quot;2006-01-01 02:00:00&quot; &quot;2006-01-01 03:00:00&quot; ...  
     $ x: int  1 2 3 4 5 6 7 8 9 10

The result is:

    &gt; as.xts(q)
    Error in as.POSIXlt.character(x, tz, ...) : 
      character string is not in a standard unambiguous format

This is the simplest example I can think of, so it&#39;s quite frustrating not getting it to work... Any help is appreciated!

Converting a data frame to xts

I have a Numpy array of shape (6,2):

    [[ 0, 1],
     [10,11],
     [20,21],
     [30,31],
     [40,41],
     [50,51]]

I need a sliding window with step size `1` and window size `3` like this:

    [[ 0, 1,10,11,20,21],
     [10,11,20,21,30,31],
     [20,21,30,31,40,41],
     [30,31,40,41,50,51]]

I&#39;m looking for a Numpy solution. If your solution could parametrise the shape of the original array as well as the window size and step size, that&#39;d be great. 

---

I found this related answer https://stackoverflow.com/questions/4936620/using-strides-for-an-efficient-moving-average-filter but I don&#39;t see how to specify the stepsize there and how to collapse the window from the 3d to a continuous 2d array. Also this https://stackoverflow.com/questions/6822725/rolling-or-sliding-window-iterator-in-python but that&#39;s in Python and I&#39;m not sure how efficient that is. Also, it supports elements but does not join them together in the end if each element has multiple features.

Sliding window of M-by-N shape numpy.ndarray

Time series decomposition is a method that separates a time-series data set into three (or more) components. For example:

    x(t) = s(t) + m(t) + e(t)

where 

    t is the time coordinate
    x is the data
    s is the seasonal component
    e is the random error term
    m is the trend

In R I would do the functions [`decompose`][1] and [`stl`][2]. How would I do this in python?


  [1]: http://stat.ethz.ch/R-manual/R-devel/library/stats/html/decompose.html
  [2]: http://stat.ethz.ch/R-manual/R-devel/library/stats/html/stl.html

Time Series Decomposition function in Python

Are there any lossless compression methods that can be applied to floating point time-series data, and will significantly outperform, say, writing the data as binary into a file and running it through gzip?

Reduction of precision might be acceptable, but it must happen in a controlled way (i.e. I must be able to set a bound on how many digits must be kept)

I am working with some large data files which are series of correlated `double`s, describing a function of time (i.e. the values are correlated).  I don&#39;t generally need the full `double` precision but I might need more than `float`.

Since there are specialized lossless methods for images/audio, I was wondering if anything specialized exists for this situation.

**Clarification:**  I am looking for existing practical tools rather than a paper describing how to implement something like this.  Something comparable to gzip in speed would be excellent. 

Compressing floating point data

I am creating a system which polls devices for data on varying metrics such as CPU utilisation, disk utilisation, temperature etc. at (probably) 5 minute intervals using SNMP. The ultimate goal is to provide visualisations to a user of the system in the form of time-series graphs.

I have looked at using RRDTool in the past, but rejected it as storing the captured data indefinitely is important to my project, and I want higher level and more flexible access to the captured data. So my question is really:

*What is better, a relational database (such as MySQL or PostgreSQL) or a non-relational or NoSQL database (such as MongoDB or Redis) with regard to performance when querying data for graphing.*

## Relational

Given a relational database, I would use a `data_instances` table, in which would be stored every instance of data captured for every metric being measured for all devices, with the following fields:

Fields: `id` `fk_to_device` `fk_to_metric` `metric_value` `timestamp`

When I want to draw a graph for a particular metric on a particular device, I must query this singular table *filtering out* the other devices, and the other metrics being analysed for this device:

    SELECT metric_value, timestamp FROM data_instances
        WHERE fk_to_device=1 AND fk_to_metric=2

The number of rows in this table would be:

    d * m_d * f * t

where `d` is the number of **devices**, `m_d` is the accumulative **number of metrics** being recorded for all devices, `f` is the **frequency** at which data is polled for and `t` is the total amount of **time** the system has been collecting data.

For a user recording 10 metrics for 3 devices every 5 minutes for a year, we would have just under **5 million** records. 

### Indexes

Without indexes on `fk_to_device` and `fk_to_metric` scanning this continuously expanding table would take too much time. So indexing the aforementioned fields and also `timestamp` (for creating graphs with localised periods) is a requirement.

## Non-Relational (NoSQL)

MongoDB has the concept of a *collection*, unlike tables these can be created programmatically without setup. With these I could partition the storage of data for each device, or even each metric recorded for each device.

I have no experience with NoSQL and do not know if they provide any query performance enhancing features such as indexing, however the previous paragraph proposes doing most of the traditional relational query work in the structure by which the data is stored under NoSQL.

## Undecided

Would a relational solution with correct indexing reduce to a crawl within the year? Or does the collection based structure of NoSQL approaches (which matches my mental model of the stored data) provide a noticeable benefit?

Storing time-series data, relational or non?

There seems to be no function that simply calculates the moving average on numpy/scipy, leading to [convoluted solutions](https://stackoverflow.com/questions/12816011/weighted-moving-average-with-numpy-convolve).

My question is two-fold:

 - What&#39;s the easiest way to (correctly) implement a moving average with numpy?
 - Since this seems non-trivial and error prone, is there a good reason not to have the [batteries included][1] in this case?


  [1]: https://www.python.org/dev/peps/pep-0206/#batteries-included-philosophy

How to calculate rolling / moving average using python + NumPy / SciPy?

I have a pandas dataframe with the following columns:

```python
data = {&#39;Date&#39;: [&#39;01-06-2013&#39;, &#39;02-06-2013&#39;, &#39;02-06-2013&#39;, &#39;02-06-2013&#39;, &#39;02-06-2013&#39;, &#39;03-06-2013&#39;, &#39;03-06-2013&#39;, &#39;03-06-2013&#39;, &#39;03-06-2013&#39;, &#39;04-06-2013&#39;],
        &#39;Time&#39;: [&#39;23:00:00&#39;, &#39;01:00:00&#39;, &#39;21:00:00&#39;, &#39;22:00:00&#39;, &#39;23:00:00&#39;, &#39;01:00:00&#39;, &#39;21:00:00&#39;, &#39;22:00:00&#39;, &#39;23:00:00&#39;, &#39;01:00:00&#39;]}
df = pd.DataFrame(data)

         Date      Time
0  01-06-2013  23:00:00
1  02-06-2013  01:00:00
2  02-06-2013  21:00:00
3  02-06-2013  22:00:00
4  02-06-2013  23:00:00
5  03-06-2013  01:00:00
6  03-06-2013  21:00:00
7  03-06-2013  22:00:00
8  03-06-2013  23:00:00
9  04-06-2013  01:00:00
```

How do I combine data[&#39;Date&#39;] &amp; data[&#39;Time&#39;]  to get the following? Is there a way of doing it using `pd.to_datetime`?

    Date
    01-06-2013 23:00:00
    02-06-2013 01:00:00
    02-06-2013 21:00:00
    02-06-2013 22:00:00
    02-06-2013 23:00:00
    03-06-2013 01:00:00
    03-06-2013 21:00:00
    03-06-2013 22:00:00
    03-06-2013 23:00:00
    04-06-2013 01:00:00




Combine Date and Time columns using python pandas

I&#39;ve taken my Series and coerced it to a datetime column of dtype=`datetime64[ns]` (though only need day resolution...not sure how to change). 

    import pandas as pd
    df = pd.read_csv(&#39;somefile.csv&#39;)
    column = df[&#39;date&#39;]
    column = pd.to_datetime(column, coerce=True)

but plotting doesn&#39;t work:

    ipdb&gt; column.plot(kind=&#39;hist&#39;)
    *** TypeError: ufunc add cannot use operands with types dtype(&#39;&lt;M8[ns]&#39;) and dtype(&#39;float64&#39;)

I&#39;d like to plot a histogram that just **shows the count of dates by week, month, or year**.

Surely there is a way to do this in `pandas`?

Can Pandas plot a histogram of dates?

Even though I found Hadley&#39;s post in the google group on `POSIXct` and `geom_vline`, I could not get it done. I have a time series from and would like to draw a vertical line for years 1998, 2005 and 2010 for example. I tried with `ggplot` and `qplot` syntax, but still I either see no vertical line at all or the vertical line is drawn at the very first vertical grid and the whole series is shifted somewhat strangely to the right.

    gg &lt;- ggplot(data=mydata,aes(y=somevalues,x=datefield,color=category)) +
          layer(geom=&quot;line&quot;)
    gg + geom_vline(xintercept=mydata$datefield[120],linetype=4)
    # returns just the time series plot I had before, 
    # interestingly the legend contains dotted vertical lines

My date field has format &quot;1993-07-01&quot; and is of class `Date`.

How to get a vertical geom_vline to an x-axis of class date?

I have a query like this that nicely generates a series of dates between 2 given dates:

    select date &#39;2004-03-07&#39; + j - i as AllDate 
    from generate_series(0, extract(doy from date &#39;2004-03-07&#39;)::int - 1) as i,
         generate_series(0, extract(doy from date &#39;2004-08-16&#39;)::int - 1) as j

It generates 162 dates between `2004-03-07` and `2004-08-16` and this what I want. The problem with this code is that it wouldn&#39;t give the right answer when the two dates are from different years, for example when I try `2007-02-01` and `2008-04-01`.

Is there a better solution?


Generating time series between two dates in PostgreSQL

I&#39;ve got a bunch of polling data; I want to compute a Pandas rolling mean to get an estimate for each day based on a three-day window. According to [this question][1], the `rolling_*` functions compute the window based on a specified number of values, and not a specific datetime range. 

How do I implement this functionality?

Sample input data: 

    polls_subset.tail(20)
    Out[185]: 
                favorable  unfavorable  other
    
    enddate                                  
    2012-10-25       0.48         0.49   0.03
    2012-10-25       0.51         0.48   0.02
    2012-10-27       0.51         0.47   0.02
    2012-10-26       0.56         0.40   0.04
    2012-10-28       0.48         0.49   0.04
    2012-10-28       0.46         0.46   0.09
    2012-10-28       0.48         0.49   0.03
    2012-10-28       0.49         0.48   0.03
    2012-10-30       0.53         0.45   0.02
    2012-11-01       0.49         0.49   0.03
    2012-11-01       0.47         0.47   0.05
    2012-11-01       0.51         0.45   0.04
    2012-11-03       0.49         0.45   0.06
    2012-11-04       0.53         0.39   0.00
    2012-11-04       0.47         0.44   0.08
    2012-11-04       0.49         0.48   0.03
    2012-11-04       0.52         0.46   0.01
    2012-11-04       0.50         0.47   0.03
    2012-11-05       0.51         0.46   0.02
    2012-11-07       0.51         0.41   0.00

Output would have only one row for each date. 

  [1]: https://stackoverflow.com/questions/9762193/pandas-rolling-median-for-duplicate-time-series-data

Pandas: rolling mean by time interval

How do I use `strptime` or any other functions to parse time stamps with milliseconds in R?

    time[1]
    # [1] &quot;2010-01-15 13:55:23.975&quot;
    strptime(time[1], format=&quot;%Y-%m-%d %H:%M:%S.%f&quot;)
    # [1] NA
    strptime(time[1], format=&quot;%Y-%m-%d %H:%M:%S&quot;)
    # [1] &quot;2010-01-15 13:55:23&quot;`


How to parse milliseconds?

I am trying to predict weekly sales using &lt;strike&gt; ARMA &lt;/strike&gt; ARIMA models. I could not find a function for tuning the order(p,d,q) in `statsmodels`. Currently R has a function `forecast::auto.arima() ` which will tune the (p,d,q) parameters. 

How do I go about choosing the right order for my model? Are there any libraries available in python for this purpose?

auto.arima() equivalent for python

I have some hierarchical data which bottoms out into time series data which looks something like this:

    df = pandas.DataFrame(
        {&#39;value_a&#39;: values_a, &#39;value_b&#39;: values_b},
        index=[states, cities, dates])
    df.index.names = [&#39;State&#39;, &#39;City&#39;, &#39;Date&#39;]
    df

                                   value_a  value_b
    State   City       Date                        
    Georgia Atlanta    2012-01-01        0       10
                       2012-01-02        1       11
                       2012-01-03        2       12
                       2012-01-04        3       13
            Savanna    2012-01-01        4       14
                       2012-01-02        5       15
                       2012-01-03        6       16
                       2012-01-04        7       17
    Alabama Mobile     2012-01-01        8       18
                       2012-01-02        9       19
                       2012-01-03       10       20
                       2012-01-04       11       21
            Montgomery 2012-01-01       12       22
                       2012-01-02       13       23
                       2012-01-03       14       24
                       2012-01-04       15       25

I&#39;d like to perform time resampling per city, so something like
    
    df.resample(&quot;2D&quot;, how=&quot;sum&quot;)

would output

                                 value_a  value_b
    State   City       Date                        
    Georgia Atlanta    2012-01-01        1       21
                       2012-01-03        5       25
            Savanna    2012-01-01        9       29
                       2012-01-03       13       33
    Alabama Mobile     2012-01-01       17       37
                       2012-01-03       21       41
            Montgomery 2012-01-01       25       45
                       2012-01-03       29       49

as is, `df.resample(&#39;2D&#39;, how=&#39;sum&#39;)` gets me

    TypeError: Only valid with DatetimeIndex or PeriodIndex

Fair enough, but I&#39;d sort of expect this to work:

    &gt;&gt;&gt; df.swaplevel(&#39;Date&#39;, &#39;State&#39;).resample(&#39;2D&#39;, how=&#39;sum&#39;)
    TypeError: Only valid with DatetimeIndex or PeriodIndex


at which point I&#39;m really running out of ideas... is there some way stack and unstack might be able to help me?

Resampling Within a Pandas MultiIndex

By processing a time series graph, I Would like to detect patterns that look similar to this:

![enter image description here][1]

Using a sample time series as an example, I would like to be able to detect the patterns as marked here:

![enter image description here][2]

What kind of AI algorithm (I am assuming marchine learning techniques) do I need to use to achieve this? Is there any library (in C/C++) out there that I can use?


  [1]: http://i.stack.imgur.com/irfjs.png
  [2]: http://i.stack.imgur.com/tPu3y.png

Pattern recognition in time series

---

**Update:** The best performing algorithm *so far* [**is this one**](https://stackoverflow.com/questions/22583391/peak-recognition-in-realtime-timeseries-data/22640362#22640362).

---

*This question explores robust algorithms for detecting sudden peaks in real-time timeseries data.*


Consider the following example data:

[![Plot of data][1]][1]

&lt;sub&gt;Example of this data is in Matlab format (but this question is not about the language but about the algorithm):&lt;/sub&gt;

    p = [1 1 1.1 1 0.9 1 1 1.1 1 0.9 1 1.1 1 1 0.9 1 1 1.1 1 1 1 1 1.1 0.9 1 1.1 1 1 0.9, ...
         1 1.1 1 1 1.1 1 0.8 0.9 1 1.2 0.9 1 1 1.1 1.2 1 1.5 1 3 2 5 3 2 1 1 1 0.9 1 1, ... 
         3 2.6 4 3 3.2 2 1 1 0.8 4 4 2 2.5 1 1 1];

You can clearly see that there are three large peaks and some small peaks. This dataset is a specific example of the class of timeseries datasets that the question is about. This class of datasets has two general features:

 1. There is basic noise with a general mean
 2. There are large &#39;*peaks*&#39; or &#39;*higher data points*&#39; that significantly deviate from the noise. 

Let&#39;s also assume the following:

 - The width of the peaks cannot be determined beforehand
 - The height of the peaks significantly deviates from the other values
 - The algorithm updates in realtime (so updates with each new datapoint)

For such a situation, a boundary value needs to be constructed which triggers signals. However, the boundary value cannot be static and must be determined realtime based on an algorithm.

---

**My Question: what is a good algorithm to calculate such thresholds in realtime?** Are there specific algorithms for such situations? What are the most well-known algorithms?

---

&lt;sub&gt;Robust algorithms or useful insights are all highly appreciated. (can answer in any language: it&#39;s about the algorithm)&lt;/sub&gt;


  [1]: https://i.stack.imgur.com/yUeKr.jpg

Peak signal detection in realtime timeseries data

A very newbish question, but say I have data like this:

    test_data &lt;-
      data.frame(
        var0 = 100 + c(0, cumsum(runif(49, -20, 20))),
        var1 = 150 + c(0, cumsum(runif(49, -10, 10))),
        date = seq(as.Date(&quot;2002-01-01&quot;), by=&quot;1 month&quot;, length.out=100)
      )

How can I plot both time series `var0` and `var1` on the same graph, with `date` on the x-axis, using `ggplot2`?  Bonus points if you make `var0` and `var1` different colours, and can include a legend!

I&#39;m sure this is very simple, but I can&#39;t find any examples out there.

Plotting two variables as lines using ggplot2 on the same graph

In multiple projects we have to store, aggregate, evaluate simple measurement values. One row typcially consists of a time stamp, a value and some attributes to the value. In some applications we would like to store 1000 values per second and more. These values must not only be inserted but also deleted at the same rate, since the lifetime of a value is restricted to a year or so (in different aggregation steps, we do not store 1000/s for the whole year).

Until now, we have developped different solutions. One based on Firebird, one on Oracle and one on some self-made storage mechanism. But none of these are very satisfying solutions.

Both RDBMS solutions cannot handle the desired data flow. Besides that, the applications that deliver the values (e.g. device drivers) cannot be easily attached to databases, the insert statements are cumbersome. And finally, while having an SQL interface to the data is strongly desired, typical evaluations are hard to formulate in SQL and slow in the execution. E.g. find the maximum value with time stamp per 15 minutes for all measurements during the last month.

The self-made solution can handle the insertion rate and has a client-friendly API to do it, but it has nothing like a query language and cannot be used by other applications via some standard interface e.g. for reporting.

The best solution in my dreams would be a database system that:  

 - has an API for very fast insertion  
 - is able to remove/truncate the values in the same speed  
 - provides a standard SQL interface with specific support for typical time series data  

Do you know some database that comes near those requirements or would you approach the problem in a different way?




Is there a powerful database system for time series data?

Given the below pandas DataFrame:

    In [115]: times = pd.to_datetime(pd.Series([&#39;2014-08-25 21:00:00&#39;,&#39;2014-08-25 21:04:00&#39;,
                                                &#39;2014-08-25 22:07:00&#39;,&#39;2014-08-25 22:09:00&#39;]))
              locations = [&#39;HK&#39;, &#39;LDN&#39;, &#39;LDN&#39;, &#39;LDN&#39;]
              event = [&#39;foo&#39;, &#39;bar&#39;, &#39;baz&#39;, &#39;qux&#39;]
              df = pd.DataFrame({&#39;Location&#39;: locations,
                                 &#39;Event&#39;: event}, index=times)
              df
    Out[115]:
                                   Event Location
              2014-08-25 21:00:00  foo	 HK
              2014-08-25 21:04:00  bar   LDN
              2014-08-25 22:07:00  baz   LDN
              2014-08-25 22:09:00  qux   LDN

I would like resample the data to aggregate it hourly by count while grouping by location to produce a data frame that looks like this:

    Out[115]:
                                   HK    LDN
              2014-08-25 21:00:00  1	 1
              2014-08-25 22:00:00  0     2

I&#39;ve tried various combinations of resample() and groupby() but with no luck. How would I go about this?

Pandas: resample timeseries with groupby

I&#39;m currently working in tick data with R and I would like to merge date and time into a single object as I need to get a precise time object to compute some statistics on my data. Here is how my data looks like:

                   date       time      price flag    exchange
    2   XXH10   2010-02-02   08:00:03   2787 1824        E
    3   XXH10   2010-02-02   08:00:04   2786    3        E
    4   XXH10   2010-02-02   08:00:04   2787    6        E
    5   XXH10   2010-02-02   08:00:04   2787    1        E
    6   XXH10   2010-02-02   08:00:04   2787    1        E

Basically, I would like to merge the columns &quot;date&quot; and &quot;time&quot; into a single one.


All Time Series Solutions on Gang of Coders

Total of 21 Time Series Solutions