Create a generic version of convert_to_annual function in scikits.timeseries

[wesm]

The name is a misnomer. It's really a pivot operation

[timmie]

Link to scikits.timeseries meta issue:
https://github.com/wesm/pandas/issues/630

[wesm]

This kind of stuff would be nice to be able to do too: http://stackoverflow.com/questions/10458493/pandas-how-to-plot-yearly-data-on-top-of-each-other

[timmie]

Is it planned to integrate this into 0.8 final?

[changhiskhan]

No, we pushed this off to 0.9.0 for now

[wesm]

@timmie: there is a version of it already in pandas/tseries/util.py, but I need people like you to give me some more guidance on what features of it are actually required (we're talking about a 15 line function here). I suspect that pandas's groupby capability makes convert_to_annual less useful than it was in scikits.timeseries

[timmie]

OK, very nice.
I will give it a try until the end of the week.

Is it also possible to do such operations on dataframes?
Case:
I have a data frame with datetime index with temperature, precipitation and wind speed for 10 years. Now I would like to get the annual average for all 3 parameters at once.

[wesm]

Wouldn't df.resample('A') be what you want?

[wesm]

Or otherwise df.groupby(lambda x: x.year).mean()

[timmie]

Please help, I am getting the following errors:

>>> pivot_annual(ser, freq='A')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/pymodules/python2.7/pandas/tseries/util.py", line 58, in pivot_annual
    raise NotImplementedError(freq)
NotImplementedError: A

>>> pivot_annual(ser)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/pymodules/python2.7/pandas/tseries/util.py", line 58, in pivot_annual
    raise NotImplementedError(freq)
NotImplementedError: None
>>> 

[timmie]

Please addd util to namespace
I had to do

from pandas.tseries.util import pivot_annual

[timmie]

I suspect that

df.groupby(lambda x: x.year).mean()

Does not achieve a equal alignement over years for all parameters as commented in #736 (comment)

[wesm]

Why wouldn't it?

[timmie]

Hmm. I feel I need to set up a Ipython notebook somewhere to have a test playground.

[timmie]

And what would the NotImplementedError(freq) mean?

[wesm]

If you look at the source for pivot_annual, only D and M/BM frequencies are implemented.

[wesm]

You should also try df.resample('A', how='mean', kind='period'). It seems unlikely that convert_to_annual / pivot_annual is strictly necessary for what you're doing if the goal is to aggregate data.

[timmie]

OK, I see. Then there must be a misunderstanding here.

did you try ts.convert_to_annual with a timeseries of hourly frequency covering at > 1 year?

[wesm]

You're computing means over years or over hours? If I understand correctly now you want the mean value for each hour of the year across the data set? I agree that you can do that with convert_to_annual but not with groupby/resample. It would be very helpful to be looking at some real (or fake) data =P

[timmie]

If I understand correctly now you want the mean value for each hour of the year across the data set?

Yes. Could alos apply for daily, etc.

I agree that you can do that with convert_to_annual but not with groupby/resample. It would be very helpful to be looking at some real (or fake) data

Agree. I will come up with an example data soon.

[timmie]

It would be very helpful to be looking at some real (or fake) data =P

Here's the code:

import numpy as np
import scikits.timeseries as ts

# generate a time series with 10 years of random temperature data

data = np.random.uniform(low=-10.0, high=35.0, size=87671)
start_date = ts.Date(freq='H',year=2000,month=1,day=1, hour=1)

pytseries = ts.time_series(data, dtype=np.int, freq='H', 
                           start_date=start_date)



# alignment of years in respect of the leap days

pytseries_annual = ts.extras.convert_to_annual(pytseries)

# 10 years average of hourly temperatures
lt_ave = pytseries_annual.mean(0).size

I also have this as htmlnotebook file and print but I donno how to attach here.

[wesm]

Here is the pandas equivalent for daily data (one of the implemented frequencies) using pivot_annual:

In [21]: rng = date_range('1/1/2000', '1/1/2010', freq='D')

In [22]: ts = Series(np.random.randn(len(rng)), rng)

In [23]: util.pivot_annual(ts).mean(0)
Out[23]: 
1    -0.424473
2    -0.221603
3     0.388556
4     0.383484
5    -0.196986
6     0.152771
7    -0.430865
8    -0.278414
9     0.272135
10   -0.216587
11   -0.468601
12    0.010926
13    0.101746
14    0.112212
15   -0.462306
...
352   -0.417838
353    0.459750
354   -0.041899
355   -0.011174
356    0.220537
357    0.242754
358    0.313742
359    0.300249
360    0.040765
361    0.046944
362    0.040023
363   -0.217559
364    0.367547
365    0.119563
366    0.149244
Length: 366

I'll see if I can find the time in the next 10 days to finish the other frequencies--- the hardest part is writing test cases, honestly.

[timmie]

The htmlnotebook is now at:
http://www.sendspace.com/file/5ukamo

I'll see if I can find the time in the next 10 days to finish the other frequencies--- the hardest part is writing test cases, honestly.

Happy to read that we talked about teh same idea and purpose of the fuction.

For 0.9 I could see soem more improvements: although not being associated with a certain year, ths data is still datetime (months, days, hours, etc.). This is linked with my comment on the plots:
http://permalink.gmane.org/gmane.comp.python.pystatsmodels/8439

But one after the other.

[timmie]

Could you give some guidance for adding higher frequencies?
I woulf appreciate to have at least hourly & minutely.

[timmie]

While I am trying to get moving on the hourly freq., I still would like to get an opinion from @wesm

Why did we not copy the method at:
https://github.com/pierregm/scikits.timeseries/blob/master/scikits/timeseries/extras.py#L135

Thanks in advance & sorry if I am too pushy on this.

[wesm]

That method needs quite a bit of work to be adapted to work with pandas-- I already started doing it but ran out of time. It's just a matter of time and resources--note that you are the only person to ever bring this up with us.

[timmie]

It's just a matter of time and resources--

Noted that. And started to expand it by myself.

note that you are the only person to ever bring this up with us.

Allow one more question:

So would you suppose: Others do not need this functionality or they just have their own function based n what already exists within pandas?
In the case of the latter, I will ask for help on the ML or Stack-X-change.

[timmie]

@wesm:

I finally got a working solution for the hourly frequency. But it diverts a but from the existing function.

I found that using years as rows and hours (of the year) as columns is difficult in e.g. excel.

So I transposed the thing.

How would be best proceed.

Shall I share a working example via email?

If you are fine, I would then try to make a test and prepare a qualified PR.

BTW: let's rename this issue to "Create a generic version of convert_to_annual function in scikits.timeseriesfinalise in pivot_annual". This way it would get found found better under the right topic.

[timmie]

I have a code up for the hourly frequency.

please look at #2153

[ispmarin]

Sorry about digging something old as this, but is there a way to do year over year aggregation directly?

[jreback]

maybe show an example of what you are looking

[ispmarin]

http://stackoverflow.com/questions/30379789/plot-pandas-data-frame-with-year-over-year-data

[sinhrks]

@ispmarin Answered in so. Can you check?

[ispmarin]

Sure. Gonna test and will report back.

[ispmarin]

Works. Thanks!

This should be in the manual somewhere. It seems to be a very common question on stack overflow and very useful on financial circles.

[jreback]

@sinhrks let's add this recipe to ththe cookbook and close this issue then

[ispmarin]

I can help writing the docs, if needed.

[jreback]

@ispmarin that would be gr8!.

[jorisvandenbossche]

BTW, we do have a pivot_annual (https://github.com/pydata/pandas/blob/master/pandas/tseries/util.py), although I don't think we should put that in the picture ..

[sinhrks]

Thanks for help, @ispmarin

pivot_annual looks to be replaced by dt property/accessor and TimeGrouper. Enhance time grouping docs and deprecate it?

[ispmarin]

So where do this issue stands? Is the answer on stack overflow the right way to do this, or there is another way?

[jorisvandenbossche]

yes, @sinhrks 's answer at stackoverflow is the way to go

[ispmarin]

Ok, got it. How is the best way to create the doc? Create a ipython notebook, annotated? Is there any guidelines for it?

[wesm]

Can this issue be closed?

[jreback]

idea was to add a recipe in the cookbook
but not sure it's that big of a deal