Pandas datetimes with different formats in the same column

by

You can use pd.to_datetime().dt.strftime() to efficienty convert the entire column to a datetime object and then to a string with Pandas intelligently guessing the date formatting:

df = pd.Series('''3/14/2019 5:15:32 AM
2019-08-03 05:15:35
2019-01-03 05:15:33
2019-01-03 05:15:33
2/28/2019 5:15:31 AM
2/27/2019 11:18:39 AM'''.split('\n'), name="date", dtype=str).to_frame()

print(pd.to_datetime(df.date).dt.strftime('%Y-%m-%d'))

0    2019-03-14
1    2019-08-03
2    2019-01-03
3    2019-01-03
4    2019-02-28
5    2019-02-27
Name: date, dtype: object

If that doesn’t give you what you want, you will need to identify the different kinds of formats and apply different settings when you convert them to datetime objects:

# Classify date column by format type
df['format'] = 1
df.loc[df.date.str.contains("https://stackoverflow.com/"), 'format'] = 2
df['new_date'] = pd.to_datetime(df.date)

# Convert to datetime with two different format settings
df.loc[df.format == 1, 'new_date'] = pd.to_datetime(df.loc[df.format == 1, 'date'], format="%Y-%d-%m %H:%M:%S").dt.strftime('%Y-%m-%d')
df.loc[df.format == 2, 'new_date'] = pd.to_datetime(df.loc[df.format == 2, 'date'], format="%m/%d/%Y %H:%M:%S %p").dt.strftime('%Y-%m-%d')
print(df)

                    date  format    new_date
0   3/14/2019 5:15:32 AM       2  2019-03-14
1    2019-08-03 05:15:35       1  2019-03-08
2    2019-01-03 05:15:33       1  2019-03-01
3    2019-01-03 05:15:33       1  2019-03-01
4   2/28/2019 5:15:31 AM       2  2019-02-28
5  2/27/2019 11:18:39 AM       2  2019-02-27

More Related Contents:

  1. Filtering Pandas DataFrames on dates
  2. Keep only date part when using pandas.to_datetime
  3. Convert pandas timezone-aware DateTimeIndex to naive timestamp, but in certain timezone
  4. How to print pandas DataFrame without index
  5. Convert Excel style date with pandas
  6. datetime dtypes in pandas read_csv
  7. How to convert string to datetime format in pandas python?
  8. pandas dataframe groupby datetime month
  9. How to convert columns into one datetime column in pandas?
  10. Find the end of the month of a Pandas DataFrame Series
  11. Create a day-of-week column in a Pandas dataframe using Python
  12. Pandas: Subtracting two date columns and the result being an integer
  13. How to change the datetime tick label frequency for matplotlib plots
  14. Splitting timestamp column into separate date and time columns
  15. Pandas: Datetime Improperly selecting day as month from date [duplicate]
  16. Pandas converting row with unix timestamp (in milliseconds) to datetime
  17. Python pandas convert datetime to timestamp effectively through dt accessor
  18. Specifying date format when converting with pandas.to_datetime
  19. Which is the fastest way to extract day, month and year from a given date?
  20. In Pandas how do I convert a string of date strings to datetime objects and put them in a DataFrame?
  21. Locate first and last non NaN values in a Pandas DataFrame
  22. Datetime conversion – How to extract the inferred format?
  23. pd.to_datetime is getting half my dates with flipped day / months
  24. Pandas custom function to find whether it is the 1st, 2nd etc Monday, Tuesday, etc – all suggestions welcome
  25. Clean way to convert quarterly periods to datetime in pandas
  26. Python: Adding hours to pandas timestamp
  27. how to specify the datetime format in read_csv
  28. pandas extract year from datetime: df[‘year’] = df[‘date’].year is not working
  29. Python Pandas : pandas.to_datetime() is switching day & month when day is less than 13
  30. Python generating a list of dates between two dates

Leave a Comment