Pandas Python: Keyerror Date

I am import into python where it will automatically create a date time object. However I want the first column to be a datetime object in Python. Data looks like Date,cost 41330.66

Solution 1:

This looks like an excel datetime format. This is called a serial date. To convert from that serial date you can do this:

data['Date'].apply(lambda x: datetime.fromtimestamp( (x - 25569) *86400.0))

Which outputs:

>>>data['Date'].apply(lambdax:datetime.fromtimestamp((x-25569)*86400.0))02013-02-25 10:00:00.28812013-02-26 10:00:00.28822013-02-27 10:00:00.28832013-02-28 10:00:00.288

To assign it to data['Date'] you just do:

data['Date']=data['Date'].apply(lambdax:datetime.fromtimestamp((x-25569)*86400.0))#dfDatecost02013-02-25 16:00:00.288   10012013-02-26 16:00:00.288   10122013-02-27 16:00:00.288   10232013-02-28 16:00:00.288   103

Solution 2:

Unfortunately, read_csv does not cope with date columns given as numbers. But the good news is that Pandas does have a suitable function to do it. After read_csv call:

df.Date = pd.to_datetime(df.Date - 25569, unit='D').dt.round('ms')

As I undestand, your Date is actually the number of days since 30.12.1899 (plus fractional part of the day). The above "correction factor" (25569) works OK. For Date == 0 it gives just the above start of Excel epoch date.

Rounding to miliseconds (or maybe even seconds) is advisable. Otherwise you will get weird effects resulting from inaccurate rounding of fractional parts of day. E.g. 0.33333333 corresponding to 8 hours can be computed as 07:59:59.999712.

Solution 3:

Well you have two problems here.

1. We don't know what data and columns the CSV has, but in order for pandas to pick up the date as a column, it must be a column on that csv file.

2. Apply doesn't work in place. You would have to assign the result of apply back to date, as data['Date'] = data['Date'].apply(lambda x: datetime.strptime(x, '%d/%m/%Y'))