wesm/pandas — Issue #638: NaN in a join-result despite know value
Author: elpres
Created: 2012-01-17 13:43:08+00:00
State: open

I've got a dataset with, among others, a column for date ('e_datum' below) and another one for location ('gem'). There are some transformations that have to be done (calculating seasons from dates and subtracting the first season for each 'gem' from all others for the same 'gem'; basically an offset in seasons from the first known case). This is the code:

def season(d):
return d.year * 4 + (d.month - 1) // 3

min_dates = data[['gem', 'e_datum']].groupby('gem').aggregate(np.min)
min_dates['seas'] = min_dates['e_datum'].apply(season)
data['season'] = (data['e_datum'].apply(season) -
data[['gem']].join(min_dates['seas'], on='gem')['seas'])

After the last line is executed, 'data' (which is sorted by 'gem' and 'e_datum') has a NaN in the very last row. I could track it down to the 'join':

data[['gem', 'e_datum']].join(min_dates['seas'], on='gem')[-5:]

e_datum gem seas
165589 2000-09-08 1004 7974
165590 2000-10-20 1004 7974
165591 2000-10-20 1004 7974
165592 2000-11-29 1004 7974
165593 2000-11-29 1004 NaN

As you can see from the line before the last one, the 'seas' value should not be NaN but 7974, since it's the same 'e_datum' and 'gem'. I didn't yet try to minimize the dataset to make it available for you to reproduce the bug since the place where the error occurs is perhaps already quite telling (the last row of the result), but would do that if requested. The pandas version I am using is 0.6.1 stable, installed via pip.