87197

Working with mixed datetime formats in pandas

<h3>Question</h3>

I read a file into a pandas dataframe with dates that vary in their format:

<ul><li>

either the American: YYYY-MM-DD

</li> <li>

or the European: DD.MM.YYYY

</li> </ul>

They come as a string. I would like to format them all as a date object so pandas.Series.dt can work with them and ideally have them in the second format (DD.MM.YYYY).

pandas.Series.dt gets confuesed with the two different spellings in one column.


<h3>Answer1:</h3>

Use to_datetime with both formats separately, so get missing values if format not match, so for new column use Series.fillna:

df = pd.DataFrame({'date': ['2000-01-12', '2015-01-23', '20.12.2015', '31.12.2009']}) print (df) date 0 2000-01-12 1 2015-01-23 2 20.12.2015 3 31.12.2009 date1 = pd.to_datetime(df['date'], errors='coerce', format='%Y-%m-%d') date2 = pd.to_datetime(df['date'], errors='coerce', format='%d.%m.%Y') df['date'] = date1.fillna(date2) print (df) date 0 2000-01-12 1 2015-01-23 2 2015-12-20 3 2009-12-31 <blockquote>

and ideally have them in the second format

</blockquote>

Format of datetimes in python/pandas is by default YYYY-MM-DD, if need custom one it is possible, but values are converted to strings, so datetimelike functions failed:

df['date'] = df['date'].dt.strftime('%d.%m.%Y') print (df) date 0 12.01.2000 1 23.01.2015 2 20.12.2015 3 31.12.2009 print (type(df.loc[0, 'date'])) <class 'str'>
<h3>Answer2:</h3>

Just check which of the two format you have and apply pandas.to_datetime with that format.

df = pd.DataFrame({'date': ['2000-01-12', '2015-01-23', '20.12.2015', '31.12.2009']}) print(df) date 0 2000-01-12 1 2015-01-23 2 20.12.2015 3 31.12.2009 def date_formator(date): if '-' in date: return pd.to_datetime(date, format = '%Y-%m-%d') else: return pd.to_datetime(date, format = '%d.%m.%Y') df.date.apply(date_formator) 0 2000-01-12 1 2015-01-23 2 2015-12-20 3 2009-12-31 Name: date, dtype: datetime64[ns]

来源:https://stackoverflow.com/questions/60390709/working-with-mixed-datetime-formats-in-pandas

Recommend

  • Working with mixed datetime formats in pandas
  • Working with mixed datetime formats in pandas
  • Angular Highcharts Series name in piechart with API data
  • Webpack Loaders - create and write files inside loader
  • How do I write a generic Save() method that handles single objects and collections?
  • How to replace the characters in a string
  • In order recursion in binary trees
  • Android: openFileOutput throws NullPointerException
  • Starting a Thin cluster on Windows
  • opencv error: assertion failed (size.width>0 && size.height>0) in unknown function lin
  • How can i separate app id and version in ionic framework
  • Is DateTimeToString in Delphi XE5 doesn't work?
  • C++ how to read from unicode files by ignoring first character of each line
  • Where is the buffer allocated in opencl?
  • Avmutable composition , lost orientation when adding audio to a video made with frames
  • Sending EoS to filesink while removing branch from tee
  • Errors with Codename One “Send iOS Build” and “Send Android Build”
  • Modification of innerHTML stored in variable not working
  • Problem in JPA-Mapping
  • Alternative to bcrypt when saving passwords in PHP 5.2
  • Arc gradients in Flutter?
  • Java 11 and E(fx)clipse JavaFX plugin on Eclipse 4.9: An error has occurred - see the log file
  • android : speech recognition what are the technologies available
  • $this->a->b->c->d calling methods from a superclass in php
  • Creating 2d platforms using JavaScript
  • How to write seo friendly url's using htaccess?
  • Is there a better way for handling SpatialPolygons that cross the antimeridian (date line)?
  • matrix multiplication apache pig
  • Spring Boot fails to start
  • Unity3d lost directional light shadows after generate assetBundle (.unity3d file)
  • Grails - How to implement a foreign key relationship not using an id column?
  • How to check if object is null in Java?