Clean way to convert quarterly periods to datetime in pandas

by

You can (and should) use pd.PeriodIndex as a first step, then convert to timestamp using PeriodIndex.to_timestamp:

qs = df['Quarter'].str.replace(r'(Q\d) (\d+)', r'\2-\1')
qs

0    1996-Q3
1    1996-Q4
2    1997-Q1
Name: Quarter, dtype: object

df['date'] = pd.PeriodIndex(qs, freq='Q').to_timestamp()
df

   Quarter       date
0  Q3 1996 1996-07-01
1  Q4 1996 1996-10-01
2  Q1 1997 1997-01-01

The initial replace step is necessary as PeriodIndex expects your periods in the %Y-%q format.

Another option is to use pd.to_datetime after performing string replacement in the same way as before. 

df['date'] = pd.to_datetime(
    df['Quarter'].str.replace(r'(Q\d) (\d+)', r'\2-\1'), errors="coerce")
df

   Quarter       date
0  Q3 1996 1996-07-01
1  Q4 1996 1996-10-01
2  Q1 1997 1997-01-01

If performance is important, you can split and join, but you can do it cleanly:

df['date'] = pd.to_datetime([
    '-'.join(x.split()[::-1]) for x in df['Quarter']])

df

   Quarter       date
0  Q3 1996 1996-07-01
1  Q4 1996 1996-10-01
2  Q1 1997 1997-01-01

More Related Contents:

  1. Convert Excel style date with pandas
  2. Find the end of the month of a Pandas DataFrame Series
  3. Generate a list of datetimes between an interval
  4. Splitting timestamp column into separate date and time columns
  5. Which is the fastest way to extract day, month and year from a given date?
  6. How to combine year, month, and day columns to single datetime column?
  7. How do I convert timestamp to datetime.date in pandas dataframe?
  8. Convert dates to pd.to_datetime where month could be either a number or month name
  9. Filtering Pandas DataFrames on dates
  10. Keep only date part when using pandas.to_datetime
  11. How to print pandas DataFrame without index
  12. How to convert string to datetime format in pandas python?
  13. How to format date string via multiple formats in python
  14. Add months to a date in Pandas
  15. Pandas: Subtracting two date columns and the result being an integer
  16. How to change the datetime tick label frequency for matplotlib plots
  17. Matplotlib DateFormatter for axis label not working
  18. Comparing two date strings in Python
  19. Get week start date (Monday) from a date column in Python (pandas)?
  20. group by week in pandas
  21. Python pandas convert datetime to timestamp effectively through dt accessor
  22. In Pandas how do I convert a string of date strings to datetime objects and put them in a DataFrame?
  23. Locate first and last non NaN values in a Pandas DataFrame
  24. pd.to_datetime is getting half my dates with flipped day / months
  25. Formatting “yesterday’s” date in python
  26. Python: Datetime to season
  27. filter pandas dataframe by time
  28. Pandas datetimes with different formats in the same column
  29. Get weekday/day-of-week for Datetime column of DataFrame
  30. Plot pandas dates in matplotlib

Leave a Comment