CODEWITHSUNDEEP
pythondatetimepandasdataframe
eWizardII
eWizardII

Reputation: 1936

Pandas Key Error date

df['ts'] = pd.to_datetime(df['_created_at'])
df = df.set_index('ts')

def f(x):
    x = x.reindex(df.index)
    x = x.sort_values('battery')
    x['ts'] = x['ts'].fillna(method='ffill')  
    x['battery'] = x['battery'].combine_first(df['battery'])
    x['model'] = x['model'].combine_first(df['model'])
    x['user'] = x['user'].combine_first(df['user'])
    x['version'] = x['version'].combine_first(df['version'])
    return x

I have the above code and it seems I run into an error when I get to the x['ts'] = x['ts'].fillna(method='ffill') line. This occurs when I run the following command:

df = df.groupby(level=0, sort=False).apply(f).reset_index(level=0, drop=True).reset_index()

My ts values look like : 2013-03-04 13:56:29.662 and are datetime64; I don't understand what I am doing wrong that is causing this key error on ts as I thought seeing them as to_datetime would put the index in a format pandas understands. Ideas on how to fix this?

Upvotes: 0

Views: 22267

Answers (1)

jezrael
jezrael

Reputation: 862611

I think you have to omit this problematic row like, because column ts is set to index and is filled values by x.reindex(df.index). I think you need delete column _created_at by drop:

print df
               _created_at user  battery model  version
0  2013-03-04 13:56:29.662    R        3     A        1
1  2013-03-05 13:56:29.662    S        5     B        3
2  2013-03-06 13:56:29.662    J        6     C        2

df['ts'] = pd.to_datetime(df['_created_at'])

df = df.drop('_created_at', axis=1)

df = df.set_index(['ts'])

def f(x):
    #print x
    x = x.reindex(df.index)
    x = x.sort_values('battery')
    #x['ts'] = x['ts'].fillna(method='ffill')  
    x['battery'] = x['battery'].combine_first(df['battery'])
    x['model'] = x['model'].combine_first(df['model'])
    x['user'] = x['user'].combine_first(df['user'])
    x['version'] = x['version'].combine_first(df['version'])
    return x

df = df.groupby(level=0, sort=False).apply(f).reset_index(level=0, drop=True).reset_index()
print df
                       ts user  battery model  version
0 2013-03-04 13:56:29.662    R        3     A        1
1 2013-03-05 13:56:29.662    S        5     B        3
2 2013-03-06 13:56:29.662    J        6     C        2
3 2013-03-05 13:56:29.662    S        5     B        3
4 2013-03-04 13:56:29.662    R        3     A        1
5 2013-03-06 13:56:29.662    J        6     C        2
6 2013-03-06 13:56:29.662    J        6     C        2
7 2013-03-04 13:56:29.662    R        3     A        1
8 2013-03-05 13:56:29.662    S        5     B        3

But maybe you need fillna for other column e.g. user:

df['ts'] = pd.to_datetime(df['_created_at'])

df = df.drop('_created_at', axis=1)

df = df.set_index(['ts'])

def f(x):
    #print x
    x = x.reindex(df.index)
    x = x.sort_values('battery')
    #x['ts'] = x['ts'].fillna(method='ffill')
    x['battery'] = x['battery'].combine_first(df['battery'])
    x['model'] = x['model'].combine_first(df['model'])
    x['user'] = x['user'].fillna(method='ffill')  
    x['version'] = x['version'].combine_first(df['version'])
    return x

df = df.groupby(level=0, sort=False).apply(f).reset_index(level=0, drop=True).reset_index()
print df
                       ts user  battery model  version
0 2013-03-04 13:56:29.662    R        3     A        1
1 2013-03-05 13:56:29.662    R        5     B        3
2 2013-03-06 13:56:29.662    R        6     C        2
3 2013-03-05 13:56:29.662    S        5     B        3
4 2013-03-04 13:56:29.662    S        3     A        1
5 2013-03-06 13:56:29.662    S        6     C        2
6 2013-03-06 13:56:29.662    J        6     C        2
7 2013-03-04 13:56:29.662    J        3     A        1
8 2013-03-05 13:56:29.662    J        5     B        3

Upvotes: 1

Related Questions