CODEWITHSUNDEEP
pythonpandasdate
Niccola Tartaglia
Niccola Tartaglia

Reputation: 1667

How to convert dates to quarters in Python?

I would like to convert my date column into an indicator of the quarter of that particular year, say 2018q1 or 2018q2 etc.

My data looks like this, I have stock returns once per quarter (not showing the return column here), and a corresponding date, the column quarter is what I would like to get (or something similar)

data = [
    {'date': '3/22/18', 'quarter': 1},
    {'date': '3/22/18', 'quarter': 1}, 
    {'date': '6/22/18', 'quarter': 3},
    {'date': '6/22/18', 'quarter': 3},
    {'date': '9/22/18', 'quarter': 2},
    {'date': '9/22/18', 'quarter': 2}]
df = pd.DataFrame(data, index=['s1', 's2','s1','s2','s1','s2'])

        date  quarter
 s1  3/22/13   2013q1
 s2  3/24/13   2013q1
 s1  6/21/13   2013q2
 s2  6/26/13   2013q2
 s1  9/21/13   2013q3
 s2  9/28/13   2013q3

Upvotes: 35

Views: 86428

Answers (7)

Ṃųỻịgǻňạcểơửṩ
Ṃųỻịgǻňạcểơửṩ

Reputation: 2613

Just extract the month part of your date string. The quarter can simply be obtained through (month - 1) // 3 + 1.

Since your data is a dictionary whose 'date' key is a str of form (\d{1:2})/(\d{1:2})/(\d\d), you can get the "month" part of the date (the first group), convert it to an int, and use (month - 1) // 3 + 1 to get the quarter.

Extracting the month part of the date string can be done using regex or even simple string slicing. The quarter therefore ranges from 1 to 4 and is determined by:

  • m // 3 is 0 for 0 <= m <= 2 (Q1)
  • m // 3 is 1 for 3 <= m <= 5 (Q2)
  • m // 3 is 2 for 6 <= m <= 8 (Q3)
  • m // 3 is 3 for 9 <= m <= 11 (Q4)

where m = month - 1

Upvotes: 4

Ravi Kumar
Ravi Kumar

Reputation: 31

One way to extract the quarter number is as below.

In [56]: datetime.date.today()
Out[56]: datetime.date(2021, 10, 20)

In [57]: ts = pd.Timestamp(datetime.date.today()).quarter

In [58]: ts
Out[58]: 4

Upvotes: 1

Abideen Muhammed
Abideen Muhammed

Reputation: 96

Pandas has a method to help you, it's called pd.PeriodIndex(monthcolumn, freq= 'Q'). You may need to convert your month column to datatype first by using datetime libray.

Pandas also have a method called 'to_date' that you can use to convert a column to a date column.

For example:

df["year"] = pd.to_date(df["year"])

Upvotes: 5

ALollz
ALollz

Reputation: 59579

Series.dt.to_period

import pandas as pd
df['date'] = pd.to_datetime(df['date'])

df['quarter'] = df['date'].dt.to_period('Q')

         date quarter
s1 2018-03-22  2018Q1
s2 2018-03-22  2018Q1
s1 2018-06-22  2018Q2
s2 2018-06-22  2018Q2
s1 2018-09-22  2018Q3
s2 2018-09-22  2018Q3

Upvotes: 27

haritha c
haritha c

Reputation: 141

datecolumn.dt.quarter feature will help.

df.date = pd.to_datetime(df.date)
df["Quarter"] = df.date.dt.quarter

Upvotes: 11

Kush Modi
Kush Modi

Reputation: 51

.date will not work as it is a function of data frame.

df_q8['Date'] = pd.to_datetime(df_q8['Date'])
df_q8['quarter'] = pd.PeriodIndex(df_q8['Date'] ,freq='Q')

Upvotes: 1

user3483203
user3483203

Reputation: 51185

to_datetime:

df.date = pd.to_datetime(df.date)

PeriodIndex

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

         date quarter
s1 2018-03-22  2018Q1
s2 2018-03-22  2018Q1
s1 2018-06-22  2018Q2
s2 2018-06-22  2018Q2
s1 2018-09-22  2018Q3
s2 2018-09-22  2018Q3

Upvotes: 48

Related Questions