Pandas Dataframe and Converting DateTime Objects

Question

I have multiple datettime columns in my dataframe and when I export the datetime to csv, I need to convert the datetime from Month/Day/Year to Month/Year. Is it possible to do this?

I was trying this:

if date_mask == "MMM":
    df[name].apply(lambda x: x.strftime('%b %Y'))
else:
    df[name].apply(lambda x: x.strftime('%m %Y'))

When I look at the exported CSV I still see the old datetime values.

Any ideas on how to do this?

Thanks

##########################################################

Solution

def modify_date(x):
    try:
        if pd.isnull(x) == False:
            return x.strftime('%b %Y')
        else:
            print pd.NaT
    except:
        return pd.NaT

df = pd.DateFrame.from_records(<some list from database>)

df[name] = df[name].apply(modify_date)

Thanks for all the help!

Assign it back to `df[name]`: `df[name] = df[name].apply(lambda x: x.strftime('%m %Y'))` — mechanical_meat, Aug 01 '16 at 19:48
You need to assign the results of the `apply` method, because it doesn't modify the `DataFrame` inplace. — juanpa.arrivillaga, Aug 01 '16 at 19:48
Which application did you use to view the csv file (e.g. Excel)? Is it the raw CSV, or a date-parsed version? — Alexander, Aug 01 '16 at 19:59
@bernie it seems to fail when the column value is NaT (null). — code base 5000, Aug 01 '16 at 20:00
Not that I know of. I think you'll need some kind of a datetime value in there. You could make it `dt.date(0001,1,1,0,0,0)` and then replace that with `''` after doing the `.strftime()` — mechanical_meat, Aug 01 '16 at 20:17
As for `NaT`, maybe try `df[name].dt.strftime(...)` instead of `apply`. — ptrj, Aug 02 '16 at 03:21
@bernie - the df[name] = df[name].apply() is not assigning the values back to the old column — code base 5000, Aug 02 '16 at 10:12

score 0 · Accepted Answer · answered Aug 02 '16 at 10:30

0

Solution

def modify_date(x):
    try:
        if pd.isnull(x) == False:
            return x.strftime('%b %Y')
        else:
            print pd.NaT
    except:
        return pd.NaT

df = pd.DateFrame.from_records(<some list from database>)

df[name] = df[name].apply(modify_date)

Thanks for all the help!

answered Aug 02 '16 at 10:30

code base 5000

3,812
13
44
73

@bernie - this answer is very slow, my script goes from 1 minute to 4 minutes over 400K records with 6 datetime columns. Any ideas on how to speed this up? – code base 5000 Aug 02 '16 at 11:53
Doesn't this work: `df[name] = df[name].dt.strftime('%b %Y')`? – ptrj Aug 02 '16 at 12:26
@ptrj: It doesn't because he has NaTs in there. – mechanical_meat Aug 02 '16 at 13:10
@josh1234: it's slow because it's being done in Python as opposed to C. See this related answer: http://stackoverflow.com/a/38711326/42346 – mechanical_meat Aug 02 '16 at 13:12
@bernie The thing is this takes care of NaTs (at least in pandas 0.18.1). If not, then maybe the dtype is a problem and `pd.to_datetime()` should be applied first. – ptrj Aug 02 '16 at 13:22
Oh really. Oh I see. – mechanical_meat Aug 02 '16 at 13:25

Pandas Dataframe and Converting DateTime Objects

1 Answers1