ajbentley ajbentley - 13 days ago 5
Python Question

Convert date in unpadded m/d/y format to datetime in python pandas

I know similar questions have been asked a million times, but I can't actually find this particular answer, which seems like it should be a really common situation in data cleaning.

I have a column of dates as strings without padding and in American layout, so February 19, 2009 is 2/19/09.

I'm trying to convert them to datetime in python pandas

Here's example data:

member state country zip joined . pet
16081 NY UNITED STATES 11215 9/4/09 . parrot
21186 NY UNITED STATES 5325 8/9/11 . crunchyfrog
34999 NY UNITED STATES 11218 11/4/16 lumberjack
34999 NY FOOBAR STATES NaN 11/4/16 blind bat
5033 NY UNITED STATES 11238-1630 11/7/16 norwegianblue
35079 NY FOOBAR STATES SW4 9JX 11/13/16 . spanishinq
35084 NY UNITED STATES 11217-2181 11/14/16 . spam


my latest attempt was

for x in df['joined']:
x = dt.strptime(x, '%m/%d/%Y')


which got back "time data '2/16/09' does not match format '%m/%d/%Y'"

Answer

Try to_datetime:

df.joined = pd.to_datetime(df.joined)
print (df)
   member state        country         zip     joined            pet
0   16081    NY  UNITED STATES       11215 2009-09-04         parrot
1   21186    NY  UNITED STATES        5325 2011-08-09    crunchyfrog
2   34999    NY  UNITED STATES       11218 2016-11-04     lumberjack
3   34999    NY  FOOBAR STATES         NaN 2016-11-04      blind bat
4    5033    NY  UNITED STATES  11238-1630 2016-11-07  norwegianblue
5   35079    NY  FOOBAR STATES     SW4 9JX 2016-11-13      panishinq
6   35084    NY  UNITED STATES  11217-2181 2016-11-14           spam

You can also specify format if necessary:

df.joined = pd.to_datetime(df.joined, format='%m/%d/%y')
print (df)
   member state        country         zip     joined            pet
0   16081    NY  UNITED STATES       11215 2009-09-04         parrot
1   21186    NY  UNITED STATES        5325 2011-08-09    crunchyfrog
2   34999    NY  UNITED STATES       11218 2016-11-04     lumberjack
3   34999    NY  FOOBAR STATES         NaN 2016-11-04      blind bat
4    5033    NY  UNITED STATES  11238-1630 2016-11-07  norwegianblue
5   35079    NY  FOOBAR STATES     SW4 9JX 2016-11-13      panishinq
6   35084    NY  UNITED STATES  11217-2181 2016-11-14           spam
Comments