5
$\begingroup$

I have a pandas DataFrame containing a time series column. The years are shifted in the past, so that I have to add a constant number of years to every element of that column.

The best way I found is to iterate through all the records and use

x.replace(year=x.year + years)  # x = current element, years = years to add

It is cythonized as below, but still very slow (proofing)

cdef list _addYearsToTimestamps(list elts, int years):
    cdef cpdatetime x
    cdef int i
    for (i, x) in enumerate(elts):
        try:
            elts[i] = x.replace(year=x.year + years)
        except Exception as e:
            logError(None, "Cannot replace year of %s - leaving value as this: %s" % (str(x), repr(e)))
    return elts

def fixYear(data):
    data.loc[:, 'timestamp'] = _addYearsToTimestamps(list(data.loc[:, 'timestamp']), REAL_YEAR-(list(data[-1:]['timestamp'])[0].year))
    return data

I'm pretty sure that there is a way to change the year without iterating, by using Pandas's Timestamp features. Unfortunately, I don't find how. Could someone elaborate?

$\endgroup$
2
$\begingroup$

Make a pandas Timedelta object then add with the += operator:

x = pandas.Timedelta(days=365)
mydataframe.timestampcolumn += x

So the key is to store your time series as timestamps. To do that, use the pandas to_datetime function:

mydataframe['timestampcolumn'] = pandas.to_datetime(x['epoch'], unit='s')

assuming you have your timestamps as epoch seconds in the dataframe x. That's not a requirement of course; see the to_datetime documentation for converting other formats.

| improve this answer | |
$\endgroup$
  • $\begingroup$ Pete, sorry, but I remember now that I've used your solution and ... it didn't worked as expected. Indeed, time elapsed (incl. nr of days, but also other things) between two years is not constant. Due to that, when I have applied your solution, in the best case, the minutes/seconds parts were changed, which is not expected. $\endgroup$ – Michael Hooreman Apr 15 '15 at 19:10
  • $\begingroup$ Please add what happened on to your question. Include a small representation of your data. When I apply this solution to my data, it only changes the year, leaving the mintues/seconds unchanged. $\endgroup$ – Pete Apr 15 '15 at 19:13
  • $\begingroup$ Thanks Pete, I don't know what I did previously, but now it works. I'll create another answer to give the source code if it can help somebody in the future. In fact, the "complex" stuff is around how many days are in a year, for which I'm computing delta between two datetime.date objects. $\endgroup$ – Michael Hooreman Apr 16 '15 at 7:35
1
$\begingroup$

Adapted from Pete's answer, here's an implementation of the solution, and the demonstration.

#!/usr/bin/env python3

import random
import pandas
import time
import datetime

def getRandomDates(n):
    tsMin = time.mktime(time.strptime("1980-01-01 00:00:00", "%Y-%m-%d %H:%M:%S"))
    tsMax = time.mktime(time.strptime("2005-12-31 23:59:59", "%Y-%m-%d %H:%M:%S"))
    return pandas.Series([datetime.datetime.fromtimestamp(tsMin + random.random() * (tsMax - tsMin)) for x in range(0, n)])

def setMaxYear(tss, target):
    maxYearBefore = tss.max().to_datetime().year
    # timedelta cannot be given in years, so we compute the number of days to add in the next line
    deltaDays = (datetime.date(target, 1, 1) - datetime.date(maxYearBefore, 1, 1)).days
    return tss + pandas.Timedelta(days=deltaDays)

data = pandas.DataFrame({'t1': getRandomDates(1000)})
data['t2'] = setMaxYear(data['t1'], 2015)
data['delta'] = data['t2'] - data['t1']
print(data)
print("delta min: %s" % str(min(data['delta'])))
print("delta max: %s" % str(max(data['delta'])))
| improve this answer | |
$\endgroup$
1
$\begingroup$

datetutil provides a relativedelta that allows moving forward/backward in year increments. As opposed to pd.Timedelta (which is fixed lenght) it takes the leap year into account.

from dateutil.relativedelta import relativedelta
pd.Timestamp('20000105') + relativedelta(years=1)

To apply this to an index use

df.index.map(lambda x: x-relativedelta(years=1))

This is less convenient than pd.Timedelta but seems quite fast still.

The right way to do this in pandas as indicated in the comment by @ALollz (Thanks for the hint!):

df.index+pd.offsets.DateOffset(years=1)
| improve this answer | |
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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