CODEWITHSUNDEEP
pythonpandas
j.j
j.j

Reputation: 25

convert datetimeindex to Qx-YY format

I have a csv file with table that looks like

Date        Open
11/1/2016   59.970001
10/3/2016   57.41
9/1/2016    57.009998
8/1/2016    56.599998
7/1/2016    51.130001
6/1/2016    52.439999
5/2/2016    50
4/1/2016    55.049999

I only need quarterly date rows (mar, jun, sep, dec) and convert the date columns to Q1-16/ Q2-16/ Q3-16 etc.

Code:

DF_sp = pd.read_csv(shareprice,  index_col = 'Date', parse_dates =[0])
DF_Q= DF_sp.groupby(pd.TimeGrouper('Q')).nth(-1)
DF_Q['Qx-YY'] = ????

Upvotes: 1

Views: 483

Answers (1)

jezrael
jezrael

Reputation: 862761

You can use Series.dt.to_period and then dt.quarter with dt.year, but first need convert Index.to_series:

df = df.groupby(df.Date.dt.to_period('Q')).Open.mean()
print (df)
Date
2016Q2    52.496666
2016Q3    54.913332
2016Q4    58.690000
Freq: Q-DEC, Name: Open, dtype: float64

df.index = 'Q' + df.index.to_series().dt.quarter.astype(str) + '-' 
               + df.index.to_series().dt.year.astype(str).str[2:]
print (df)
Date
Q2-16    52.496666
Q3-16    54.913332
Q4-16    58.690000
Name: Open, dtype: float64

Another solution:

df = df.groupby(df.Date.dt.to_period('Q')).Open.mean()
print (df)
Date
2016Q2    52.496666
2016Q3    54.913332
2016Q4    58.690000
Freq: Q-DEC, Name: Open, dtype: float64

y = df.index.strftime('%y')
df.index = df.index.quarter.astype(str)
df.index = 'Q' + df.index + '-' + y
print (df)
Q2-16    52.496666
Q3-16    54.913332
Q4-16    58.690000
Name: Open, dtype: float64

The best is use period.Period.strftime - link from old documentation but works very well:

df = df.groupby(df.Date.dt.to_period('Q')).Open.mean()
print (df)
Date
2016Q2    52.496666
2016Q3    54.913332
2016Q4    58.690000
Freq: Q-DEC, Name: Open, dtype: float64

df.index = df.index.strftime('Q%q-%y')
print (df)
Q2-16    52.496666
Q3-16    54.913332
Q4-16    58.690000
Name: Open, dtype: float64

Upvotes: 1

Related Questions